What is Replication in PostgreSQL: Explanation and Examples

In PostgreSQL, replication is the process of copying data from one database server (the primary) to another (the standby) to keep them synchronized. This helps with data backup, load balancing, and high availability by ensuring multiple servers have the same data.

How It Works

Replication in PostgreSQL works like a live copy machine for your database. Imagine you have a main notebook where you write all your notes (the primary server). Replication makes sure that a second notebook (the standby server) gets the same notes copied over as soon as you write them.

This copying happens continuously and automatically. PostgreSQL sends changes made on the primary server to the standby server using a stream of data called Write-Ahead Logging (WAL). The standby server then applies these changes to keep its data up to date.

This setup helps if the primary server fails or gets too busy. The standby can take over or share the work, so your data stays safe and your applications keep running smoothly.

Example

This example shows how to set up simple streaming replication between a primary and standby PostgreSQL server using SQL commands and configuration changes.

sql

/* On the primary server: */
-- Allow replication connections
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 3;
ALTER SYSTEM SET wal_keep_size = '16MB';
-- Reload configuration
SELECT pg_reload_conf();

-- Create a replication user
CREATE ROLE replicator WITH REPLICATION PASSWORD 'replica_pass' LOGIN;

/* On the standby server: */
-- Stop PostgreSQL service
-- Use pg_basebackup to copy data from primary
pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P --wal-method=stream

-- Create recovery configuration file (standby.signal)
# standby.signal file indicates standby mode

-- Add connection info in postgresql.conf
primary_conninfo = 'host=primary_host port=5432 user=replicator password=replica_pass'

-- Start PostgreSQL service

Output

ALTER ROLE
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
 pg_reload_conf 
----------------
 t
(1 row)

CREATE ROLE

pg_basebackup progress output...

PostgreSQL standby server starts and connects to primary for streaming replication.

When to Use

Use replication in PostgreSQL when you want to improve data safety, availability, or performance. For example:

• High availability: If the main server crashes, a standby server can quickly take over to keep your application running.
• Load balancing: You can send read-only queries to standby servers to reduce the load on the primary server.
• Backup: Standby servers provide up-to-date copies of your data without interrupting the primary server.

Replication is especially useful for websites, financial systems, or any application that needs to stay online and keep data safe.

Key Points

• Replication copies data from a primary to standby servers to keep them synchronized.
• PostgreSQL uses Write-Ahead Logging (WAL) to stream changes continuously.
• It helps with data safety, high availability, and load balancing.
• Setting up replication requires configuration changes and a replication user.
• Standby servers can be used for read-only queries to reduce primary load.

Key Takeaways

Replication in PostgreSQL keeps data synchronized between primary and standby servers.

It uses streaming of Write-Ahead Logs to copy changes continuously.

Replication improves data safety, availability, and performance.

Setting up replication involves configuring the primary and standby servers and creating a replication user.

Standby servers can serve read-only queries to reduce load on the primary.