Question: What are the PostgreSQL replication methods?

Answer

PostgreSQL supports several methods for replicating data to achieve high availability, load balancing, or read scaling. Here, we will explore the primary replication methods available in PostgreSQL:

1. Streaming Replication

Streaming replication allows a standby server to stay up-to-date with the master server almost in real-time. It works by streaming WAL (Write-Ahead Logging) records from the master to the standby as they're generated.

-- On the master:
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 3;
ALTER SYSTEM SET wal_keep_segments = 8;

-- On the standby:
standby_mode = 'on'
primary_conninfo = 'host=master_address port=5432 user=replicator password=secret'

2. Logical Replication

Introduced in PostgreSQL 10, logical replication allows more flexibility compared to streaming replication by allowing row-level changes to be replicated to different databases, potentially even to different versions of PostgreSQL or different systems altogether.

-- On the publisher:
CREATE PUBLICATION my_publication FOR TABLE my_table;

-- On the subscriber:
CREATE SUBSCRIPTION my_subscription CONNECTION 'dbname=mydb host=master_address user=replicator password=secret' PUBLICATION my_publication;

3. File-Based Log Shipping

This older method involves regularly saving WAL files from the master server and restoring them on the standby server. This can be less granular and more delayed than streaming replication but is simpler and requires less setup.

# Periodically run on the master:
pg_basebackup -h master_address -D /var/lib/postgresql/data/standby -U replicator -W -P

# And copy over WAL files as they are produced.

Each of these methods has its use cases and trade-offs. Streaming replication provides near real-time replication but requires continuous connectivity. Logical replication offers flexibility at the cost of additional setup complexity. File-based log shipping is simple but might not meet requirements for currency or granularity of data.

Choosing the right replication method depends on your specific needs for performance, data freshness, and system architecture.