Replication And High Availability
The robust replication capabilities of PostgreSQL load balance, boost availability, and speed database system execution. Replication links master and subsidiary databases. This helps the secondary system handle read requests and transactions if the first fails. Built-in PostgreSQL replication ships WAL. Every database data file alteration is logged in the WAL to ensure data integrity. For replication, PostgreSQL standby servers employ WAL log transactions.
Evolution and Types of Replication
Different replication types are supported by PostgreSQL to accommodate varying requirements:
Physical Replication: The conventional technique, known as physical replication, uses precise block addresses and replicates data from the WAL byte by byte. The entire cluster of databases is replicated.
Streaming Replication: With streaming replication, WAL records from the primary are continuously streamed to a backup server as they are created, keeping it more current than file-based log shipping. It is the master-slave (or primary-standby) binary replication technology that comes pre-installed by default.
Asynchronous Streaming Replication: In the default mode, known as asynchronous streaming replication, a transaction on the main commits even if the standby hasn’t been updated. Although there is a chance of data loss if the primary collapses before WAL records are sent to the standby, it offers reduced latency. The archive_timeout setting has the ability to restrict this timeframe of data loss.
Synchronous Streaming Replication: Higher data durability is ensured via synchronous streaming replication, which requires a write transaction commit to wait for confirmation that it has been written to the primary and at least one synchronous standby’s WAL on disc. With the round-trip time between primary and standby serving as the minimum wait time, this boosts confidence against data loss but slows down commit processing.
Which synchronous standbys can be set using FIRST (priority-based) or ANY (quorum-based) techniques is determined by the synchronous_standby_names argument.Transaction commitments’ wait time for standby processing is managed by the synchronous_commit parameter. Various durability guarantees are provided by options such as remote_apply, on, and remote_write; remote_apply waits until standby changes become apparent.
Cascading Replication: A standby server serves as a relay in a cascading replication configuration, receiving WAL data from an upstream server (either the primary or another standby) and sending them to further downstream standbys. By doing this, inter-site bandwidth overheads are decreased and there are fewer direct connections to the primary. Cascaded replication is currently asynchronous.
Logical Replication: PostgreSQL 10 introduced Logical Replication, a technique that provides fine-grained control by replicating logical data alterations (such as revisions to certain tables) from the WAL using a replication identity (often a main key). Subscribers obtain information from publisher publications using a publish and subscribe paradigm. Principal advantages consist of:
- Replication across the various PostgreSQL main versions.
- Cross-platform replication across PostgreSQL instances (e.g., Linux to Windows).
- Transactional consistency refers to applying changes in the same sequence that they happened on the publisher’s end.
- The subscriber receives an initial snapshot of the data, followed by updates in real time.
- The published table must have a replica identity set up (such as a primary key or unique index) for UPDATE and DELETE operations in order to identify rows on the subscriber side.
- Limitations include the inability to duplicate sequence data, big objects, DDL instructions, and database schemas. Replicating views, materialised views, or foreign tables is not possible.
Code Example:
SHOW wal_level; -- should be 'replica' or 'logical'
CREATE TABLE demo_txn (id SERIAL PRIMARY KEY, note TEXT);
INSERT INTO demo_txn (note) VALUES ('normal commit');
SET synchronous_commit = 'remote_apply';
INSERT INTO demo_txn (note) VALUES ('waits for standby apply');
SET synchronous_commit = 'off';
INSERT INTO demo_txn (note) VALUES ('no standby wait');
SELECT * FROM demo_txn;
SHOW synchronous_commit;
SHOW synchronous_standby_names;Output:
wal_level
replica
(1 row)
CREATE TABLE
INSERT 0 1
SET
INSERT 0 1
SET
INSERT 0 1
id | note
----+-------------------------
1 | normal commit
2 | waits for standby apply
3 | no standby wait
(3 rows)
synchronous_commit
off
(1 row)
synchronous_standby_names
(1 row)High Availability and Recovery
Disaster recovery and high availability (HA) are predicated on replication.
Warm Standby: A setup where a backup server constantly uses the primary’s WAL records, prepared to take over operations in the event that the primary fails.
Hot Standby: During recovery or standby mode, users can connect to the standby server and execute read-only queries using Hot Standby, an extension of Warm Standby. The database’s state will gradually become consistent for queries running on a hot standby. If hot standby queries clash with incoming WAL records that the standby needs to apply for recovery, they can be cancelled. Postgresql.conf has to have hot_standby = on set in order to enable hot standby.
Failover: In the event that the primary fails, a standby server is promoted to take over as the new primary. The existence of a trigger_file or the promotion of pg_ctl can cause this. It is essential to have a method (like STONITH) in place to stop the old primary from resuming and causing inconsistent data in the event that it does come back online. Using an old primary to create a standby can be sped up with the pg_rewind program.
Continuous Archiving and Point-in-Time Recovery (PITR): This technique combines a continuous archive of WAL files with a backup at the file system level. It enables the database to be restored to its original state at any time since the base backup was made by replaying WAL records up to the specified point in time. A shell command to retrieve archived WAL segments is executed using the archive_command option.
Configuration and Management
Replication setup requires the following crucial actions and settings:
Replication User: A separate PostgreSQL replication user is needed to configure replication. For the standby server to connect to the primary and stream WAL records, this user needs special rights. Replication users are created using the CREATE ROLE SQL command or the createuser utility with REPLICATION and LOGIN rights. For instance, CREATE ROLE pgrepuser REPLICATION LOGIN PASSWORD ‘woohoo’; can create a password-protected user.
pg_hba.conf: The PostgreSQL configuration file pg_hba.conf (Host-Based Authentication) controls client authentication and database server access. It specifies which PostgreSQL users can connect to which databases, from which IP addresses or networks, and with which authentication technique. This file is usually in the database cluster’s data directory, although the hba_file argument might change its path. Initdb generates a default pg_hba.conf.
postgresql.conf: wal_level (set to replica or logical for streaming/logical replication), archive_mode = on, max_wal_senders (maximum concurrent WAL sender processes), and hot_standby = on for read-only queries on standbys are crucial settings in postgresql.conf.
Recovery.conf: This file (or similar settings) sets standby_mode = on, primary_conninfo (connection string to primary), and possibly primary_slot_name and trigger_file on the standby.
Base Backup: A PostgreSQL base backup is a complete, physical duplicate of a database cluster at a specified moment, essential to its high availability and disaster recovery plan. This binary snapshot bootstraps standby servers (replicas) to synchronise with the primary database and starts Point-in-Time Recovery (PITR) when combined with Write-Ahead Log (WAL) archiving.
Replication Slots: The standby won’t fall too far behind or need a fresh base backup with replication slots, an automated method that makes sure the primary doesn’t remove WAL segments until all standbys have received them. SQL functions are utilised to handle them.
Monitoring: Replication progress and lag information are provided via tools such as pg_stat_replication and pg_stat_wal_receiver.
Conclusion
The strong and flexible PostgreSQL replication system supports high availability (HA) and disaster recovery solutions. It supports real-time failover, load balancing, and cross-platform data dissemination with physical, streaming, and logical replication. Synchronous and asynchronous streaming replication trade performance and durability, cascade replication decreases bandwidth cost, and logical replication allows selective, version-independent data replication.
Warm and hot standby setups, automated failover, replication slots, and pg_stat_replication boost availability. With continuous archiving and point-in-time recovery (PITR), these capabilities maintain data integrity, minimise downtime, and provide a robust database ecosystem that can handle failures without service disruption.