<!DOCTYPE html>

Easy PostgreSQL 16 Replication on Ubuntu

<br> body {<br> font-family: sans-serif;<br> line-height: 1.6;<br> margin: 20px;<br> }</p> <div class="highlight"><pre class="highlight plaintext"><code> h1, h2, h3 { margin-top: 2em; } code { font-family: monospace; background-color: #eee; padding: 2px 4px; } pre { background-color: #eee; padding: 10px; overflow-x: auto; } img { max-width: 100%; height: auto; } </code></pre></div> <p>

Easy PostgreSQL 16 Replication on Ubuntu

PostgreSQL's replication feature is a powerful tool for ensuring high availability, data consistency, and scalability. It enables you to create a copy of your primary PostgreSQL database server on a secondary server. This secondary server, also known as a replica, will mirror the data changes happening on the primary server in real-time. This article will guide you through the process of setting up PostgreSQL 16 replication on Ubuntu.

Why PostgreSQL Replication?

PostgreSQL replication offers various benefits, making it an essential component for many database deployments:

High Availability: Replication ensures that your data remains accessible even if the primary server experiences downtime. The replica server can take over as the primary in case of failure.
Disaster Recovery: In case of a disaster affecting the primary server, a replica can be promoted as the new primary, allowing for quick recovery and minimal data loss.
Read Scalability: By setting up a read-only replica, you can offload read operations from the primary server, enhancing performance and overall database throughput.
Data Consistency: Replication ensures that all data changes are propagated to the replica, maintaining data consistency across multiple servers.

Setting Up the Environment

Before you begin setting up replication, ensure you have the following:

Two Ubuntu servers running PostgreSQL 16.
A user with administrative privileges on both servers.
SSH connectivity between the two servers.
A PostgreSQL database and users created on the primary server.

Step-by-Step Guide to Setting Up PostgreSQL 16 Replication

Configure the Primary Server

Let's configure the primary PostgreSQL server for replication. We'll assume the following:

Primary server hostname: primary.example.com
Primary server PostgreSQL data directory: /var/lib/postgresql/16/main
Secondary server hostname: secondary.example.com
Database name: mydatabase
Replication user: replication_user

1.1 Create a Replication User

Create a dedicated user for replication with restricted permissions:

CREATE USER replication_user WITH PASSWORD 'replication_password';
GRANT REPLICATION, CONNECT ON DATABASE mydatabase TO replication_user;

Replace
replication_password
with a strong password.

1.2 Enable Log Shipping

Ensure that the primary server is logging all changes to the write-ahead log (WAL) for replication:

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_wal_senders = 5;
ALTER SYSTEM SET wal_sender_timeout = 60;

The
max_wal_senders
and
wal_sender_timeout
settings control the number of concurrent connections and the timeout for the replication connection. Adjust these values based on your needs.

1.3 Configure
`postgresql.conf`

On the primary server, edit the
postgresql.conf
file:

# /etc/postgresql/16/main/postgresql.conf

# ...

wal_level = logical
max_wal_senders = 5
wal_sender_timeout = 60
hot_standby = on

Ensure these settings are enabled, and restart the PostgreSQL service for the changes to take effect:

sudo systemctl restart postgresql

Configure the Secondary Server

Now, let's prepare the secondary server to receive replication data:

2.1 Install PostgreSQL 16

Install PostgreSQL 16 on the secondary server using your preferred package manager. For example, on Ubuntu:

sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16

2.2 Create a Database and Users

Create the same database and users on the secondary server that exist on the primary server:

CREATE DATABASE mydatabase;
CREATE USER replication_user WITH PASSWORD 'replication_password';
GRANT CONNECT ON DATABASE mydatabase TO replication_user;

2.3 Configure
`postgresql.conf`

Edit the
postgresql.conf
file on the secondary server:

# /etc/postgresql/16/main/postgresql.conf

# ...

hot_standby = on
max_standby_streaming_delay = 10000
wal_receiver_status_interval = 10000
recovery.target_timeline = 'latest'
recovery.conf = '/var/lib/postgresql/16/main/recovery.conf'

The
recovery.conf
file is used to configure the replication process on the secondary server. We will create this file in the next step.

2.4 Create
`recovery.conf`

Create the
recovery.conf
file in the PostgreSQL data directory of the secondary server:

# /var/lib/postgresql/16/main/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=primary.example.com port=5432 user=replication_user password=replication_password'
trigger_file = '/tmp/recovery.signal'

Replace
primary.example.com
with the hostname of your primary server. The
trigger_file
setting will be used to signal the secondary server to start the replication process.

Start Replication

After configuring both the primary and secondary servers, we can start the replication process:

3.1 Start the Secondary Server

Start the PostgreSQL service on the secondary server:

sudo systemctl start postgresql

3.2 Signal the Secondary Server

Create the
trigger_file
on the secondary server to trigger the replication process:

sudo touch /tmp/recovery.signal

The secondary server will now connect to the primary server and start receiving the WAL files.

Verify Replication

You can verify the replication process by checking the following:

4.1 Check the Secondary Server Logs

On the secondary server, check the PostgreSQL log file for messages related to replication:

sudo tail -f /var/log/postgresql/postgresql-16-secondary.log

4.2 Check Replication Status

You can also use the following SQL command to check the replication status:

SELECT pg_is_in_recovery();

If the output is
true
, replication is running. You can also use the
pg_stat_replication
view to monitor replication statistics.

Promoting a Replica to Primary

In case of a primary server failure, you can promote a replica to become the new primary server. This process involves the following steps:

5.1 Stop the Primary Server

Stop the PostgreSQL service on the original primary server.

5.2 Promote the Replica

On the replica server, edit the recovery.conf file and remove or comment out the standby_mode and primary_conninfo lines. Then, restart the PostgreSQL service.

5.3 Update the `pg_hba.conf` file

On the promoted replica, update the pg_hba.conf file to allow connections from clients.

5.4 Update Application Connections

Finally, update your application connections to point to the new primary server.

Conclusion

PostgreSQL replication is a crucial tool for enhancing database availability, scalability, and consistency. By following this comprehensive guide, you can easily set up PostgreSQL 16 replication on Ubuntu. Remember to use strong passwords, carefully configure your primary and secondary servers, and monitor the replication process regularly. With proper configuration and maintenance, replication can ensure your database is resilient and performant, meeting your business needs.