Configuring a PostgreSQL Database Cluster with Replication on ServerStadium

This tutorial explains how to set up a PostgreSQL database cluster with replication using ServerStadium’s infrastructure.

Prerequisites

• At least two ServerStadium VM instances or dedicated servers. Check ServerStadium VM Pricing and Dedicated Servers for options.
• Access to the ServerStadium Cloud Panel.
• Basic knowledge of PostgreSQL and Linux system administration.

Step 1: Setting Up the Primary PostgreSQL Server

1. Select and Set Up Your Servers: Use the ServerStadium Cloud Panel to set up at least two servers – one for the primary PostgreSQL server and the other for the replica.
2. Install PostgreSQL: SSH into your primary server and install PostgreSQL:
   

   sudo apt update
   sudo apt install postgresql postgresql-contrib

3. Configure PostgreSQL for Replication:

   Edit the PostgreSQL configuration file:
   

   sudo nano /etc/postgresql/12/main/postgresql.conf

   Make the following changes (note that the version number, here 12, may vary):

   -Set the listen_addresses to ‘*’ or the specific IP addresses of your replica servers.
   -Set the wal_level to replica.
   -Configure max_wal_senders to a number greater than or equal to the number of replicas you plan to have.
   -Set wal_keep_segments to a value that determines how much information to retain for the replicas (e.g., 64).

4. Configure Client Authentication:

   Edit the pg_hba.conf file:
   

   sudo nano /etc/postgresql/12/main/pg_hba.conf

   
   Add the following line to allow replication connections from your replicas (replace replica_ip with your replica’s IP):
   

   host replication all replica_ip md5

5. Restart PostgreSQL:
   

   sudo systemctl restart postgresql

Step 2: Setting Up the Replica PostgreSQL Server

1. Install PostgreSQL: Repeat the installation process on the replica server.
2. Data Copy: On the primary server, create a user for replication and grant replication privileges. Then use pg_basebackup to copy data from the primary to the replica.

   On the primary server:
   

   sudo -u postgres createuser –replication replica_user

   
   On the replica server:
   

   sudo systemctl stop postgresql
   sudo -u postgres pg_basebackup -h primary_ip -D /var/lib/postgresql/12/main -U replica_user -P –wal-method=stream

   
   Replace primary_ip with the IP of your primary server.
3. Configure Replica PostgreSQL:

   On the replica, create a recovery file:
   

   sudo -u postgres nano /var/lib/postgresql/12/main/recovery.conf

   
   Add the following configuration:
   

   standby_mode = 'on'
   primary_conninfo = 'host=primary_ip port=5432 user=replica_user password=replica_password'
   trigger_file = '/tmp/postgresql.trigger.5432'

   
   Replace primary_ip and replica_password with appropriate values.
4. Start the Replica Server:
   

   sudo systemctl start postgresql

Step 3: Testing and Verification

1. Testing: Create a test database or table on the primary server. Verify that changes are replicated to the replica server.
2. Monitoring: Use PostgreSQL’s built-in functions or external tools to monitor the replication status. Check logs for any errors.

Conclusion

You now have a PostgreSQL database cluster with replication set up using ServerStadium’s services. This configuration enhances your database’s availability and load distribution.

For further assistance, refer to our knowledge base or reach out to our support team.