📢 This article covers issues that can come up when setting up PostgreSQL replication. For replication setup itself, see
PostgreSQL Replication - Configuration.1. Setting up ssh-keygen
2. When listen_addresses is not set
3. When pg_hba.conf is not set
4. When max_wal_senders is set to 1 or less
5. Setting hot_standby
6. Replication user cannot connect
7. When PostgreSQL database encoding/locale options differ
8. recovery.conf
9. Checking the location of postgresql.conf
10. Checking the location of pg_hba.conf
11. Backing up the data directory while online
1. Setting up ssh-keygen
📢 If SSH still asks for a password after setting up ssh-keygen, check the SELinux (Security-Enhanced Linux) setting. It must be set to
permissive or disabled. SELinux is configured in /etc/selinux/config.2. When listen_addresses is not set
The standby server cannot connect to the main server, and an error occurs.
Solution
So that the standby server can connect to the main server, set
listen_addresses = '*' or listen_addresses = 'Standby_Server_IP' in the main server's postgresql.conf.3. When pg_hba.conf is not set
In a streaming replication setup, the standby server follows the main server's WAL records. If it cannot connect to the main server as the replication user, replication fails.
Solution
For physical replication, pg_hba.conf must allow the standby server to access the main server as the replication user. The DATABASE field in pg_hba.conf must be set to
replication for connections through the replication user to work.Note that
all is also an allowed value, but all does not include replication.📢 For logical replication, DATABASE must be set to
all. If you set it to replication, the following error occurs. ERROR: could not connect to the publisher: connection to server at "main_server", port 5432 failed: FATAL: no pg_hba.conf entry for host "standby_server", user "repluser", database "postgres", no encryption4. When max_wal_senders is set to 1 or less
Replication always needs 1 sender to ship WAL files and 1 sender for pg_basebackup. So
max_wal_senders must be set to at least 2; with a smaller value, replication setup fails.Solution
Set
max_wal_senders to 2 or higher. The default of max_wal_senders is 0 up to PostgreSQL 9.6 and 10 starting with PostgreSQL 10.5. Setting hot_standby
If you set
hot_standby to off, the standby server only restores the WAL files received from the main server. In other words, users cannot connect to the standby server, let alone run queries.6. Replication user cannot connect
An error occurs when a dedicated replication DB user has been created but cannot connect because it lacks the login privilege. PostgreSQL uses users and roles interchangeably. CREATE USER grants the login privilege by default, but CREATE ROLE does not grant the login privilege.
Solution
Grant the login privilege to the dedicated replication DB user.
7. When PostgreSQL database encoding/locale options differ
If the encoding/locale options differ when the PostgreSQL databases are created, replication may not initialize. On Linux, running initdb without options gives UTF8 encoding, but on Windows it is WIN1252.
Solution
Rebuild the standby server's database with the same encoding/locale options as the main server.
8. recovery.conf
recovery.conf holds the replication-related parameters. The locations of configuration files such as postgresql.conf, pg_hba.conf, and pg_ident.conf can be changed, but recovery.conf must live inside the PostgreSQL data directory. A server with a recovery.conf file runs as a standby server, and recovery.conf is renamed to recovery.done.
recovery.conf has been used since PostgreSQL 8.0. In PostgreSQL 12, the parameters in recovery.conf were moved into postgresql.conf, and the file was replaced by 2 signal files, standby.signal and recovery.signal. So starting with PostgreSQL 12, the database will not start if recovery.conf exists in the PostgreSQL data directory.
standby.signal
A signal file used to run a database server as a standby server. It is used starting with PostgreSQL 12 and replaces standby_mode (on or off) in recovery.conf. It takes precedence over the recovery.signal file.
recovery.signal
A signal file used to start a database server in targeted recovery mode.
Parameter changes 1) Archive Recovery Setting
Starting with PostgreSQL 12, the parameters below fall under the Write_Ahead Log section of postgresql.conf.
recovery.conf | Description |
archive_clenaup_command | Specifies the shell command to clean up WAL files archived more than once |
recovery_end_command | Specifies the shell command to run at the end of recovery |
restore_command | Specifies the shell command used to restore the database from WAL archive files |
Parameter changes 2) Recovery Target Setting
Starting with PostgreSQL 12, the parameters below fall under the Write_Ahead Log section of postgresql.conf. The pause_at_recovery_target parameter was changed to recovery_target_action in PostgreSQL 9.6.
recovery.conf | Description |
pause_at_recovery_target | Determines the action to take when the recovery target is reached. Changed to recovery_target_action in version 9.5. |
recovery_target | Specifies that recovery should end as soon as a consistent state is reached (immediate is the only allowed value) |
recovery_target_action | Determines the action to take when the recovery target is reached. Changed from pause_at_recovery_target to recovery_target_action in version 9.5 (pause/promote/shutdown) |
recovery_target_inclusive | Specifies whether to stop just after (true) or just before (false) the specified recovery target. |
recovery_target_lsn | Sets the LSN of the WAL location up to which recovery proceeds |
recovery_target_name | Specifies a restore point created with the pg_create_restore_point function |
recovery_target_time | Specifies the time up to which recovery proceeds. At most one of recovery_target_time and recovery_target_xid can be specified. The exact stopping point is affected by recovery_target_inclusive |
recovery_target_timeline | Specifies the timeline to recover |
recovery_target_xid | Specifies the transaction ID up to which recovery proceeds. At most one of recovery_target_time and recovery_target_xid can be specified. The exact stopping point is affected by recovery_target_inclusive |
Parameter changes 3) Standby Server Setting
Starting with PostgreSQL 12, the parameters below fall under the Replication section of postgresql.conf. Starting with PostgreSQL 12, the standby_mode parameter was replaced by the standby.signal and recovery.signal files.
recovery.conf | Description |
primary_conninfo | Connection information for the replication source DB (main server) |
primary_slot_name | Name of the replication slot used when connecting to the main server in streaming replication |
recovery_min_apply_delay | Delays recovery by the specified time (unit: ms) |
standby_mode | Set to on to run as a standby server |
tirgger_file | Specifies the name of the file that signals the end of standby state. If the file exists, standby mode ends and the server runs as active. Ignored if standby_mode=off. Changed to promote_trigger_file in PostgreSQL 12 |
9. Checking the location of postgresql.conf
Checking the location from the process list
Checking the location with SQL
10. Checking the location of pg_hba.conf
Checking the location from the process list
Checking the location with SQL
11. Backing up the data directory while online
You can back up the data directory even while the database is online. This requires the following setup.
wal_level = replica
archive_mode = on
archive_command
To run an online backup, put the database into hot backup mode with
pg_start_backup and back up the data directory (at the OS level). After the backup finishes, end hot backup mode with the pg_stop_backup command. With the backed-up data directory and the WAL files generated during hot backup mode, a complete recovery is possible.pg_start_backup()
A function that puts the database in a state where a file-system-level backup can be taken. It returns the LSN (Log Sequence Number) of the minimum WAL file location from which the backup can be recovered, as of the time the function runs. A backup_label file is also created in the directory where the PostgreSQL data directory is backed up.
📢 Renamed to pg_backup_start in PostgreSQL 15.
backup_label
A file created to provide metadata about the backup. In particular, it contains metadata about the backup start LSN, so the backed-up server knows where to start applying WAL files when it first starts. Once the PostgreSQL database starts successfully on the recovery (replica) server, it is renamed to backup_label.old.
Item | Description |
START WAL LOCATION | |
CHECKPOINT LOCATION | LSN location where the checkpoint taken by pg_start_backup was recorded |
BACKUP METHOD | Backup method |
BACKUP FROM | Server the backup was taken from (primary or standby) |
START TIME | Time pg_start_backup was run |
LABEL | Label value entered when running pg_start_backup |
START TIMELINE | Introduced in PostgreSQL 11; records the timeline at the start of the backup |
pg_stop_backup()
A function that signals that pg_start_backup is finished. When it runs, it switches the WAL file and records in WAL that the backup has ended. It also creates a backup history file.
📢 Renamed to pg_backup_stop in PostgreSQL 15.
Related Articles
