Question: How do you set up TLS for PostgreSQL replication?

Answer

Setting up TLS (Transport Layer Security) for PostgreSQL replication involves configuring your primary and replica servers to communicate over a secure channel. This is crucial for protecting sensitive data in transit between the primary database server and its replicas. Here's a step-by-step guide:

Step 1: Generate the TLS Certificates

First, you need to create SSL certificates. You can use self-signed certificates or obtain them from a Certificate Authority (CA). For simplicity, this example shows how to generate a self-signed certificate and key.

mkdir -p ~/certs cd ~/certs openssl req -new -x509 -days 365 -nodes -text -out server.crt \ -keyout server.key -subj \"/CN=postgres-server\" chmod og-rwx server.key

Copy server.crt and server.key to the PostgreSQL data directory on both the primary and replica servers. The location of the data directory can vary but is often found at /var/lib/postgresql/12/main.

Step 2: Configure the Primary Server

Edit the postgresql.conf file on the primary server to enable SSL:

ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key'

Also, configure the pg_hba.conf file to require SSL for replication connections:

# TYPE DATABASE USER ADDRESS METHOD # Replication connections hostssl replication all all md5

Step 3: Configure the Replica Server

On the replica server, modify the postgresql.conf similarly to enable SSL (as done for the primary in Step 2).

For setting up the replication itself, ensure the connection string in the primary_conninfo setting of the recovery configuration (recovery.conf for versions before PostgreSQL 12, or in the postgresql.conf for PostgreSQL 12 and later) includes SSL options:

primary_conninfo = 'host=primary_host port=5432 user=replication_user password=replication_password sslmode=require'

This tells the replica to connect to the primary using SSL.

Step 4: Restart PostgreSQL Servers

After configuring both the primary and replica servers, restart them to apply the changes:

sudo systemctl restart postgresql

Final Thoughts

By following these steps, you have configured your PostgreSQL servers for replication over a secure TLS connection. This setup enhances the security of your data by ensuring that replication traffic is encrypted.

Remember to keep your certificates and keys secure, and consider using certificates from a trusted CA for production environments.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.