DEV Community

Tonny Kirwa
Tonny Kirwa

Posted on

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
Enter fullscreen mode Exit fullscreen mode

Install PostgreSQL on both servers:

sudo apt install postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

listen_addresses = 'localhost,web-server-001-ip'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

host    replication     postgres        web-server-002-ip/32         md5
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Run the following SQL commands to create a replication user:

CREATE USER replication_user REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'your_password';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 8: Test Replication

Log in to PostgreSQL on web-server-001:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Run the following SQL command to check replication status:

SELECT pg_is_in_recovery();
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)