Replication refers to copying data from a host computer to another computer, related to how data is stored and backed up. Replication is provided as an add-on by an RDBMS or to balance the load across multiple database servers. What is used in database replication is mostly in the area of database management, usually dealing with the original and the copy in a master/slave relationship.— Wikipedia (Replication)
Database Replication
The reasons for replicating a database broadly fall into load balancing, high availability, and backup.
Processing many operations such as DDL and DML on the main database can put load on it. If you keep one or more read-only standby databases, you can distribute SELECT queries across them and run the database more reliably.
Also, if the main database fails, you can fail over to the standby database and respond quickly to unexpected incidents.
As this shows, users consider a database replication setup for many reasons. PostgreSQL also offers a variety of replication methods and can be configured freely to fit your environment without a separate solution.
📢 In general, there are many terms for the original and the copy in database replication, and they are used interchangeably. The original server is called main, master, primary, publisher, active, and so on, and the replicated server is called standby, slave, secondary, subscriber, and the like. In this post and the rest of this series, the original server is called the main server and the copy is called the standby server.
Write-Ahead Log
Before explaining PostgreSQL replication, you need to understand the Write-Ahead Log (WAL). This is because most replication works on top of WAL.
Terminology varies, but WAL is the standard DBMS method for ensuring data integrity. It works by first writing log records that describe a change to storage, and only then applying the change to the data files.
PostgreSQL's WAL plays a role similar to Oracle's redo/archive logs. If the database crashes, WAL can be used to recover it.
WAL is a kind of statement that records the changes made to the database, and this property is used to replay the changes (WAL) on the standby server.
Why use the Write-Ahead Log
- Data pages do not need to be written to disk on every transaction commit.
- Changes not yet applied to data pages can be undone from the log records.
- Log files are written sequentially, and the cost of syncing log files is much lower than the cost of writing data pages.
- When processing a large number of small transactions, a single fsync of the log file is enough to commit many transactions.
- Online backup and PITR (Point-In-Time Recovery) are supported.
📢 fsync is the function called to save data stored in a buffer to disk. It requests that changed data and metadata files be written to the hard disk and waits until the write is complete.
Types of PostgreSQL replication
PostgreSQL replication comes in several types depending on the method. Not covered in this post are Shared Disk Failover, where multiple PostgreSQL servers share one disk, and File System Replication, where each server uses its own disk but replicates at the file system level. PostgreSQL also supports replication natively, and you can use 3rd party solutions as well. Each replication method has clear characteristics, pros, and cons, so you should understand them before choosing the right one.
Of these, this post looks at the replication features PostgreSQL provides natively, shown below.
PostgreSQL replication can be broadly divided into physical replication and logical replication.
Physical replication can be divided into ① File-based Log Shipping, which ships the WAL files themselves, and ② Streaming Replication, which ships WAL records regardless of whether the WAL file has been saved. File-based Log Shipping is further divided into Warm Standby and Hot Standby depending on the PostgreSQL version.
📢 Up to PostgreSQL 9.6 it was called Transaction Log Shipping, but from PostgreSQL 10 it was renamed Write-Ahead Log Shipping.
📢 A WAL file consists of multiple WAL records. When a change occurs, one change can be seen as one WAL record. You can inspect these WAL records with the pg_waldump utility.
Logical replication was introduced in PostgreSQL 10 to address the limitations of physical replication. Like physical replication, it works on top of WAL, but the difference is that when DML occurs, the walsender process runs the WAL records through logical decoding before sending them.
📢 Logical decoding is the process of extracting all changes to a table into a consistent, easy-to-understand format that can be interpreted regardless of the database architecture. In PostgreSQL, it is implemented by decoding WAL content into an application format such as tuples or SQL statements.
File-based Log Shipping
File-based Log Shipping, one type of physical replication, is a method that ships completed WAL files to the standby server and replicates them through recovery. In other words, an existing WAL file can be shipped only after the main server's WAL file has filled up to its set size (Default : 16MB) and a new file has been created.
This means data lag can occur between the main server and the standby server while the current WAL file is filling up, and that in an incident, up to one WAL file's worth of replicated changes can be lost.
📢 To reduce data lag, you can use the
archive_timeout parameter to set how often the WAL file is switched, which triggers archiving even when the WAL file is not full. However, setting it too short increases the size of the archive directory, so be careful.File-based Log Shipping can be divided into Warm Standby and Hot Standby depending on the PostgreSQL version. The difference between the two is whether you can connect to the standby server and run read (Read-Only, SELECT) operations.
Warm Standby
Warm Standby is the File-based Log Shipping method introduced in PostgreSQL 8.3. With Warm Standby, users cannot connect to the standby server, and read (SELECT) operations are not possible either. The parameters related to Warm Standby are as follows.
Category | Parameter |
Main Server | wal_level, archive_mode, archive_command, archive_timeout, max_wal_senders |
Standby Server | standby_mode, restore_command, archive_cleanup_command, trigger_file |
Hot Standby
Hot Standby is the File-based Log Shipping method introduced in PostgreSQL 9.0. It replicates the same way as Warm Standby, but users can connect to the standby server and run read (Read-Only, SELECT) operations. The parameters related to Hot Standby are as follows.
Category | Parameter |
Main Server | wal_level, archive_mode, archive_command, archive_timeout, max_wal_senders |
Standby Server | standby_mode, restore_command, hot_standby, archive_cleanup_command, trigger_file |
📢 The difference between Warm and Hot Standby comes down to whether you can connect to the standby server. For this reason, Warm Standby is rarely used today.
Pros and cons of File-based Log Shipping
The pros and cons of File-based Log Shipping are as follows.
Pros | Cons |
• Simpler to configure than other replication methods
• Easy to send WAL files to the standby server using the archive_command parameter
• Multiple standby servers can be configured | • Can be configured only if both the major version and bitness are the same
• Because it ships WAL files that are full (16MB) or switched by archive_timeout, data in WAL files not yet switched may be lost if the main server fails
• If communication with the standby server is lost and WAL files are deleted before being shipped, replication stops because the WAL files are missing, and the standby server must be rebuilt
• With Warm Standby, you cannot connect to the standby server or run SELECT (does not apply to Hot Standby) |
Streaming Replication
Streaming Replication, introduced in PostgreSQL 9.0, is a type of physical replication that streams WAL records in real time without waiting for the main server's WAL file to fill up.
To ship WAL records rather than files, dedicated processes are needed: the WAL sender on the main server and the WAL receiver on the standby server. These processes synchronize the servers.
With this method, there can be a small delay (roughly under 1s) between a transaction commit and its application on the standby server, but it is far smaller than with File-based Log Shipping.
If a WAL file containing WAL records that have not yet been synchronized is deleted, synchronization can no longer continue and replication must be rebuilt. To prevent this, you can set the number of WAL files to keep with the
wal_keep_size parameter so that WAL files are not deleted before synchronization. You can also keep WAL files in a separate location before they are deleted and recover them using the restore_command parameter. The parameters related to Streaming Replication are as follows.Category | Parameter |
Main Server | wal_level, wal_keep_size(wal_keep_segements), max_wal_senders |
Standby Server | hot_standby, primary_conninfo, restore_command |
📢
wal_keep_size is used from PostgreSQL 13; earlier versions use wal_keep_segments.Pros and cons of Streaming Replication
The pros and cons of Streaming Replication are as follows.
Pros
- Simpler to configure than other replication methods
- Keeps the standby server more up to date than File-based Log Shipping
- Because it receives WAL records, replication works regardless of whether the WAL file has been switched
Cons
- If a WAL file containing WAL records that have not yet been synchronized is deleted, replication stops because the WAL records are missing, and the standby server must be rebuilt
- Because it must keep streaming from the main server, it can affect the main server
Limitations of physical replication
Physical replication (File-Based Log Shipping, Streaming Replication) has the advantage of being relatively simple to configure, but it shares the following constraints.
- Cannot replicate only part of a database (cannot replicate only a specific database or table)
- No write operations on the standby server
- No replication between different PostgreSQL major versions
- No replication between different platforms on which PostgreSQL is installed
Logical replication, described next, was introduced to overcome these constraints.
Logical Replication
Logical replication, the last type covered here, is available from PostgreSQL 10 and was introduced to overcome the limitations of File System Replication and WAL Log Shipping.
Logical replication consists of one publisher and one or more subscribers. A subscriber can take the data of the publisher it subscribes to and re-publish it, becoming a publisher itself and allowing additional subscribers.
Replication between subscription and publication is based on the replica identity, which can be a primary key or a unique key. If there is no primary key or unique key, you can change the table property with the
REPLICA IDENTITY FULL option. However, this method replicates the entire table whenever the table changes, so it is inefficient and can consume a lot of resources for large, frequently updated tables.📢 Logical replication is possible without a primary key or unique key. However, it is less efficient, increases disk usage, and cannot guarantee data integrity, so setting a primary key or unique key is recommended.
Logical replication use cases
- Consolidating multiple databases into a single database
- Replication between different major versions
Pros and cons of logical replication
The pros and cons of logical replication are as follows.
Pros
- Can replicate only specific databases or tables
- Write operations such as DDL and DML are possible on the standby server
- Replication between different major versions and platforms (useful for migration and upgrades)
- Multi-tier setups where a subscriber becomes a publisher
- DML types for a subscription (INSERT, UPDATE, DELETE, TRUNCATE) can be chosen selectively
- A subscription can define more columns than the publication, and column order does not matter. However, the type and name of replicated columns must be the same
Cons
- Tables must use the same name in the publication and the subscription
- Tables must have a primary key or unique key
- No bidirectional replication
- Sequences, large objects, materialized views, partition root tables, and foreign tables are not supported
- Supported only for DML operations; DDL must be run manually
- If replicated data conflicts with a constraint, replication stops and can resume only after the cause of the conflict is resolved
Other replication
Besides the replication methods described above, there are various other methods, as follows.
- Trigger-Based Primary-Standby Replication
- SQL-Based Replication Middleware
- Asynchronous Multimaster Replication
- Synchronous Multimaster Replication
In addition to the replication features PostgreSQL provides by default, there are many commercial solutions that support replication. (See the table below)
Wrapping up
• Using Streaming Replication and Log Shipping Replication together for high availability • Using logical replication to audit specific tables • Replacing data migration for version upgrades with logical replication
As in the examples above, if you are considering replication, you need to understand not only your business requirements and constraints but also the limitations of each replication technology to find the right solution. To that end, this post classified the replication technologies PostgreSQL provides and looked at their characteristics.
In the next part of the series, we will look at how to configure physical replication (Log Shipping, Streaming) and logical replication.
Related articles
