Question: What is PostgreSQL replication and how does it work?

Answer

PostgreSQL replication involves duplicating the data from one database server (the primary) to one or more other servers (the replicas). This process can serve several purposes, such as increasing data availability, enabling read scaling, and creating backups. Replication in PostgreSQL can be broadly divided into two types: physical replication and logical replication.

Physical Replication

Physical replication copies the binary data from the primary to the replicas. Since it operates at the file level, it's highly efficient but less flexible compared to logical replication. It ensures that replicas are exact byte-for-byte copies of the primary.

To set up physical replication, you typically use streaming replication, which works by sending WAL (Write-Ahead Logging) records from the primary to the replicas in real-time.

-- On the primary server:
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 3; -- Number of simultaneous connections from replicas.
ALTER SYSTEM SET wal_keep_segments = 64; -- Amount of WAL files to keep for the replicas.
-- And then restart your PostgreSQL server.

You also need to configure the pg_hba.conf file to allow connections from the replicas and create a replication user.

-- On the replicas:
CREATE_REPLICATION_SLOT slot_name PHYSICAL;
START_REPLICATION SLOT slot_name LOGICAL 0/3000000;

Logical Replication

Logical replication allows selective data replication at the table level, offering greater flexibility. It enables different use cases like replicating between different PostgreSQL versions, selective table replication, and transformation during replication.

To set up logical replication, you need to create a publication on the primary and a subscription on the replica.

-- On the primary server:
CREATE PUBLICATION my_publication FOR TABLE my_table;

-- On the replica:
CREATE SUBSCRIPTION my_subscription CONNECTION 'dbname=mydb host=primaryhost user=myuser password=mypass' PUBLICATION my_publication;

This example creates a publication for my_table on the primary and sets up a subscription on the replica. The replica will subscribe to changes of my_table and apply them.

Considerations

• Performance: Replication can affect the performance of your primary server, especially if you have many replicas or use logical replication extensively.
• Failover: While replication can increase availability, setting up automatic failover requires additional tools or scripts, such as Patroni.
• Data Consistency: Ensure that your applications can handle eventual consistency where replicas might lag slightly behind the primary.

In summary, PostgreSQL offers robust options for replication, each suited to different requirements. Proper configuration and understanding of both physical and logical replication are essential for leveraging their full benefits.