Introduction

PostgreSQL is a powerful, open-source object-relational database system that offers advanced features for database management. Setting up replication is essential for ensuring high availability and load balancing. In this guide, we will walk you through the process of setting up PostgreSQL replication on Debian 11, which can be effectively utilized with Windows VPS UK for enhanced performance.

Prerequisites

• Debian 11 installed on your server
• PostgreSQL installed
• Two servers for primary and secondary databases
• Basic understanding of command-line operations

Step 1: Configure the Primary Server

Start by editing the PostgreSQL configuration file on the primary server. Open the postgresql.conf file using your preferred text editor:

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

Locate the following settings and modify them:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
max_wal_senders = 3

After saving your changes, restart PostgreSQL:

sudo systemctl restart postgresql

Step 2: Configure the Secondary Server

Next, set up the secondary server to replicate from the primary server. First, create a base backup using the following command:

pg_basebackup -h primary_server_ip -D /var/lib/postgresql/13/main -U replication_user -P --wal-method=stream

Replace primary_server_ip with the actual IP address of your primary server. Once the backup is complete, configure the secondary server by creating a recovery.conf file:

sudo nano /var/lib/postgresql/13/main/recovery.conf

In this file, add the following lines:

standby_mode = 'on'
primary_conninfo = 'host=primary_server_ip port=5432 user=replication_user password=your_password'
trigger_file = '/tmp/postgresql.trigger.5432'

Finally, restart the PostgreSQL service on the secondary server:

sudo systemctl restart postgresql

Step 3: Verify Replication

To verify that replication is working, you can check the replication status on the primary server:

SELECT * FROM pg_stat_replication;

You should see the status of the connected secondary server. If you’re using a Windows VPS, ensure that the necessary ports are open in your firewall settings.

Conclusion

Setting up PostgreSQL replication on Debian 11 is a straightforward process that enhances your database's reliability and performance. This setup can be particularly beneficial for those using UK Windows VPS solutions. For more information on server hosting options, including Windows Virtual Private Server Hosting, consider exploring various VPS UK Windows providers for your needs.