Setting up PostgreSQL and Replication between Two Servers - Ubuntu Server

Setting up PostgreSQL and replication between two servers (web-server-001 and web-server-002) involves several steps. Here's a high-level guide to get you started. Please note that this is a general outline and might require adjustments based on your specific environment and requirements.

Step 1: Install PostgreSQL on Both Servers

SSH into each server and update the package list:

sudo apt update

Install PostgreSQL on both servers:

sudo apt install postgresql postgresql-contrib

Step 2: Configure PostgreSQL on web-server-001

Edit the PostgreSQL configuration file on web-server-001:

sudo nano /etc/postgresql/{version}/main/postgresql.conf

Make sure to replace {version} with the appropriate PostgreSQL version number. Find and update the following lines:

listen_addresses = 'localhost,web-server-001-ip'

Replace web-server-001-ip with the actual IP address of web-server-001.

Step 3: Allow Remote Access on web-server-001

Edit the PostgreSQL pg_hba.conf file on web-server-001:

sudo nano /etc/postgresql/{version}/main/pg_hba.conf

Add the following line to allow access from web-server-002:

host    replication     postgres        web-server-002-ip/32         md5

Replace web-server-002-ip with the actual IP address of web-server-002.

Step 4: Create Replication User

Log in to PostgreSQL on web-server-001:

sudo -u postgres psql

Run the following SQL commands to create a replication user:

CREATE USER replication_user REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'your_password';

Replace 'your_password' with a strong password.

Step 5: Take a Base Backup

On web-server-002, take a base backup from web-server-001:

pg_basebackup -h web-server-001-ip -U replication_user -D /var/lib/postgresql/{version}/main -P --xlog-method=stream

Step 6: Configure PostgreSQL on web-server-002

Edit the PostgreSQL configuration file on web-server-002 as in Step 2, but replace occurrences of web-server-001 with web-server-002.

Step 7: Start PostgreSQL on Both Servers

Start PostgreSQL on both servers:

sudo systemctl start postgresql

Step 8: Test Replication

Log in to PostgreSQL on web-server-001:

sudo -u postgres psql

Run the following SQL command to check replication status:

SELECT pg_is_in_recovery();

This should return true on web-server-002.

That's the basic outline for setting up PostgreSQL replication between two servers. Keep in mind that PostgreSQL replication can be complex and might require additional configurations and considerations based on your specific use case. It's recommended to refer to the official PostgreSQL documentation for more detailed guidance and troubleshooting.