1. File-based Log Shipping Replication
Verifying the File-based Log Shipping Replication Setup
You can check whether file-based log shipping is configured by looking at the PostgreSQL processes. On the Main Server, the archiver process runs. On the Standby Server, a process runs that waits for the WAL files sent by the Main Server.
Main Server processes
This means the Main Server has sent WAL file 000000010000000000000014 to the Standby Server.
Standby Server processes
This means the Standby Server is waiting to receive WAL file 000000010000000000000015 from the Main Server. The process may also appear as "postgres: startup recovering 000000010000000000000015". It says recovering, but it is still waiting for WAL file 000000010000000000000015. In other words, on the Standby Server the difference between waiting and recovering does not matter much; both mean it is waiting to receive the WAL file listed after them.
Verifying File-based Log Shipping Replication Synchronization
With file-based log shipping, WAL files are transferred at the OS level with commands such as scp or cp, so you cannot check synchronization through the catalog. Instead, you can judge whether the servers are in sync by checking whether the processes are running and by reading the logs on the Main Server and the Standby Server.
When a WAL switch occurs on the Main Server, the WAL file is sent to the Standby Server by the archive_command defined in postgresql.conf.
The Standby Server restores the WAL files it receives from the Main Server.
The Standby Server keeps trying to restore the WAL files it needs using restore_command. Once the required WAL file arrives in the archive directory from the Main Server, it is restored and the Standby Server moves on to the next WAL file. This is why lines like
cp: cannot stat … keep appearing in the log.File-based Log Shipping Replication Failure
If a WAL file the Standby Server needs is missing for any reason, such as the Standby Server being shut down, replication can be considered stopped. In the example below, the Standby Server tries to restore WAL file 00000001000000000000001A, but fails because the file does not exist.
A WAL switch occurs on the Main Server, and the WAL file (00000001000000000000001B) is sent to the Standby Server by archive_command.
The Standby Server's archive directory does not contain WAL file 00000001000000000000001A.
As in the example above, if WAL file 00000001000000000000001A is missing but the next file, 00000001000000000000001B, exists, the Standby Server does not know that 00000001000000000000001A is missing, and keeps trying to restore 00000001000000000000001A as shown below. WAL files after 00000001000000000000001A keep arriving from the Main Server, but because the Standby Server could not restore 00000001000000000000001A, it never tries to restore the later WAL files.
If the WAL file the Standby Server needs is still kept somewhere, such as the Main Server's archive directory, it can be restored and replication can continue. If it is not kept anywhere, replication can be considered stopped (failed). In that case, you must rebuild the Standby Server from the current state of the Main Server to resume replication.
2. Streaming Replication
Verifying the Streaming Replication Setup
You can verify the streaming replication setup by looking at the PostgreSQL processes, and also through the catalog. When checking the processes, confirm that three processes are running:
walsender, walreceiver, and startup. The walsender process runs on the Main Server, and the walreceiver and startup processes run on the Standby Server. The walreceiver process on the Standby Server receives the WAL records sent by the walsender process on the Main Server, so if walreceiver is not running, the replication setup can be considered failed.Main Server processes
Standby Server processes
On the Main Server, you can see that the walsender process is sending WAL record 000060 of WAL file 7 to the Standby Server.
On the Standby Server, you can see that walreceiver is receiving WAL record 000060 of WAL file 7 from the Main Server's walsender.
Monitoring Streaming Replication
Once streaming replication is set up, each server lets you check replication details in the catalogs below.
Catalogs available on the Main Server
Catalog Name | Description |
pg_stat_replication | Replication statistics |
pg_replication_slots | Replication slot information |
Catalogs available on the Standby Server
Catalog Name | Description |
pg_stat_wal_receiver | WAL receiver statistics |
For a description of the catalogs, see
PostgreSQL Replication - Catalog.Before looking at the catalogs, let's first go over the terms
write, flush, and replay that you will see when monitoring replication.Term | Description |
Write | When a transaction commits, the changes it made are written sequentially to WAL records. |
Flush | The changes recorded in WAL records are safely stored on disk. This ensures durability and prevents data loss if the server fails. It also means the data sent to the Standby Server can be trusted. |
Replay | WAL records received from the Main Server are applied to the database in the order they were generated, keeping it in sync with the Main Server. |
Checking the Main Server catalogs
- The WAL sender process PID is 42435, connected as replicauser. (The oid of replicauser is 16384.)
- The WAL receiver connected from server IP 10.10.45.231 through port 57364 at 2023-05-17 15:58:22. client_hostname and backend_xmin show NULL because the related parameters are not set.
- Replication is currently streaming. WAL records have been sent up to 0/2F08ED40, and the Standby Server is applying WAL record 0/2F08ED40.
- The lag at the Write, Flush, and Replay stages is under 1 second.
- The replication slot named physical_slot was created as a physical slot and is used by the process with PID 42435.
- To keep this slot, transaction IDs from xmin 756 onward are required.
- The oldest WAL record the Standby Server may need is 0/2F08ED78.
Checking the Standby Server catalogs
- The WAL receiver process PID is 24021, and it is currently streaming.
- WAL data (LSN) from 0/2D000000 to 0/2F08ED78 has been received and applied.
- The timeline ID was 1 when replication started and is still 1.
- The WAL sender process (Main Server) is at IP 10.10.45.230 on port 5432, and WAL data is sent through the replication slot named physical_slot.
- The amount of WAL data received since replication was connected is about 33MB. (0/2F08ED78 - 0/2D000000)
You can check the size of WAL data sent with the pg_wal_lsn_diff function.
SELECT pg_wal_lsn_diff( '0/2F08ED78', '0/2D000000' ) / 1024 / 1024 ;
For a description of replication-related functions, see PostgreSQL Replication - Function.Streaming Replication TEST
Let's check whether data inserted on the Main Server is reflected on the Standby Server. On the Main Server, create a table and insert the current time.
You can see that the data inserted on the Main Server can be queried on the Standby Server.
Because the Standby Server is read-only, only SELECT is allowed.
3. Logical Replication
Verifying the Logical Replication Setup
You can verify the logical replication setup by looking at the PostgreSQL processes, and also through the catalog. When checking the processes, confirm that two processes are running:
walsender and logical replication worker. The walsender process runs on the Main Server, and the logical replication worker process runs on the Standby Server. Main Server processes (Publisher)
If the walsender process is running, logical replication is set up.
Standby Server processes (Subscriber)
If the logical replication worker process is running, logical replication is set up.
Monitoring Logical Replication
Once logical replication is set up, each server lets you check replication details in the catalogs below.
Catalogs available on the Main Server
Catalog Name | Description |
pg_publication | Information about logical replication publications |
pg_publication_tables | Tables included in a publication |
pg_replication_slots | Information about slots configured for logical replication |
pg_stat_replication_slots | Replication slot statistics (since PostgreSQL 14) |
pg_stat_replication | Replication statistics |
Catalogs available on the Standby Server
Catalog Name | Description |
pg_subscription | Subscription information |
For a description of the catalogs, see
PostgreSQL Replication - Catalog.Checking the Main Server catalogs
You can check publication information through the
pg_publication catalog. - The publication name is my_publication (pubname), and the owner is postgres (pubowner).
- It includes only some tables (puballtables=f), and it replicates INSERT, UPDATE, DELETE, and TRUNCATE. (pubinsert, pubupdate, pubdelete, pubtruncate)
You can list the tables in the publication found in the
pg_publication catalog through the pg_publication_tables catalog.- my_publication consists of the replication_table_01 and replication_table_02 tables in the public schema.
Logical replication uses replication slots. You can check information about the slots used for logical replication through the
pg_replication_slots catalog.- The replication slot name is my_subscription. (slot_name)
📢 In this example, the subscription was created on the Standby Server without specifying a logical slot, so the replication slot was named after the subscription. (default)
- Logical replication is performed with the pgoutput module (plugin) (slot_type), and the slot is not temporary (temporary).
- The database being replicated is postgres (datoid, database).
- The replication slot is currently active (active), and the process ID handling replication is 3290 (active_pid).
- The catalog transaction xmin needed to keep the replication slot is 758 (catalog_xmin).
Starting with PostgreSQL 14, the
pg_stat_replication_slots catalog was added, which lets you check replication slot statistics.- During logical decoding, 2 transactions exceeded
logical_decoding_work_mem(spill_txns), data spilled to disk 237 times (spill_count), and the amount of data spilled to disk was 15,851,997,849 bytes (spill_bytes).
- 37 decoded transactions (total_txns) were sent to the plugin through the my_subscription replication slot. Their total size is 178,269,828 bytes (total_bytes).
📢 How often data spills to disk depends on the
logical_decoding_work_mem parameter. The default is 64MB.You can check logical replication statistics in the pg_stat_replication catalog.
- The WAL sender process PID is 13161, connected as repluser. (The oid of repluser is 16384.)
- The WAL receiver connected from server IP 10.10.45.231 through port 55036 at 2023-05-03 17:25:14. client_hostname and backend_xmin show NULL because the related parameters are not set.
- Replication is currently streaming. WAL records have been sent up to 4/5620E6E0, and the Standby Server has applied WAL record 4/5620E6E0.
- There is currently no lag at the Write, Flush, and Replay stages, and the last reply from the Standby Server was received at 2023-06-08 10:07:13.
Checking the Standby Server catalogs
The Standby Server simply replays what it receives from the Main Server's walsender, so you cannot check replication statistics there. On the Standby Server, you can only check the subscription, that is, the connection information for the Main Server.
Related articles
