Fivetran HVR 02 (Install Portgres on a Azure windows VM) - Source Setup Part 1

 

Step 1: Create an Azure Windows VM

Log in to Azure Portal:

• Go to the Azure portal (https://portal.azure.com/).
• Click on "Create a resource" and select "Virtual Machine".

Configure the VM:

• Choose an appropriate size and image (Windows Server).
• Configure the instance details, including the resource group, VM name, region, and size.
• Set up an administrator account (username and password).

Review and Create:

• Review the settings and click "Create" to deploy the VM. (you can update the networking rule while creating the VM or do it later as mentioned in the next step)

Set up Networking:

• Configure the networking settings, ensuring that the VM has a public IP address.
• Open the necessary ports in the Network Security Group (NSG). You need to allow:
• Port 3389 for RDP access.
• Port 5432 for PostgreSQL.
  
  

Step 2: Install PostgreSQL on the Windows VM

RDP into the VM:

• Use Remote Desktop to connect to the VM.
• Open the Remote Desktop Connection application and enter the public IP address of the VM.
• Log in with the administrator credentials.

Download PostgreSQL Installer:

• Open a web browser on the VM and go to the PostgreSQL download page: https://www.postgresql.org/download/windows/
• Download the PostgreSQL installer.

Install PostgreSQL:

• Run the installer and follow the installation steps.
• During the installation, set a password for the PostgreSQL superuser (Postgres).
• Complete the installation with the default settings.

Complete the Postgres setup on VM.

Step 3: Configure PostgreSQL to Allow Remote Connections

Edit the PostgreSQL configuration file:

• Open the file Postgresql.conf located in the PostgreSQL data directory (default: C:\Program Files\PostgreSQL\<version>\data).
• Find the line #listen_addresses = 'localhost' and change it to:

        listen_addresses = '*'

Edit the pg_hba.conf file:

• Open the file pg_hba.conf located in the PostgreSQL data directory.
• Add the following line to allow connections from any IP address (or specify a range for more security):

        host all all 0.0.0.0/0 md5

Restart PostgreSQL service:

• Open the Services application from the Start menu.
• Find the PostgreSQL service, right-click, and select "Restart".

Step 4: Configure Windows Firewall to Allow PostgreSQL Connections

Open Windows Firewall:

• Open the Control Panel, go to System and Security, and then Windows Defender Firewall.

Create a New Inbound Rule:

• Click on "Advanced settings" to open the Windows Firewall with Advanced Security window.
• Right-click on "Inbound Rules" and select "New Rule".
• Select "Port" and click "Next".
• Select "TCP" and specify port 5432. Click "Next".
• Allow the connection and click "Next".
• Specify when the rule applies and click "Next".
• Give the rule a name (e.g., "PostgreSQL") and click "Finish".

Add a rule 

I have allowed all the ports for the testing but ideally, you should only allow port 5432 for Postgres

Step 5: Access PostgreSQL from PGAdmin

Download and install PGAdmin:

• Download and install PGAdmin on your client machine from https://www.pgadmin.org/download/.

Add a new server in PGAdmin:

• Open PGAdmin and right-click on "Servers" to create a new server.
• In the "General" tab, provide a name for the server.
• In the "Connection" tab, enter the public IP address of your Azure VM, the username (Postgres), and the password you set earlier.
• Click "Save" to connect to the PostgreSQL server on your Azure VM.

 

You should now be able to access and manage your PostgreSQL database on the Azure Windows VM using PGAdmin from your client machine.

Create a table and add some data into the table which will be used for the replication 

drop table cars;

CREATE TABLE Cars (
  brand VARCHAR(255),
  model VARCHAR(255),
  year INT,
	creation_time TIMESTAMP
);

INSERT INTO cars( brand, model, year, creation_time) VALUES ('audi', 'a3', 2007, current_timestamp);
INSERT INTO cars( brand, model, year, creation_time) VALUES ('bmw', 'b1', 2012, current_timestamp);
INSERT INTO cars( brand, model, year, creation_time) VALUES ('merc', 'a4', 2107, current_timestamp);

select * from cars;