Replication

Switchover to Replica

Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev

Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)

Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is

allowed without restrictions. Commercial use is possible only with the written

permission of Postgres Professional. It is prohibited to make changes to the

course materials.

Feedback

Please send your feedback, comments and suggestions to:

edu@postgrespro.ru

Disclaimer

Postgres Professional assumes no responsibility for any damages and

losses, including loss of income, caused by direct or indirect, intentional or

accidental use of course materials. Postgres Professional company

specifically disclaims any warranties on course materials. Course materials

are provided “as is,” and Postgres Professional company has no obligations

to provide maintenance, support, updates, enhancements, or modifications.

Topics

Switchover to Replica

Failback on Former Leader

Considerations for File-Based Archiving

Switchover to Replica

Reasons

planned switchover:

scheduled maintenance

emergency switchover (failover):

switch to replica after a leader failure

Procedure

make sure the leader is stopped

$ pg_ctl promote or

pg_promote()

automatic switchover: unavailable

(third-party cluster management software required)

You might need to switch to a standby server for maintenance work on the

primary server. In this case, the switchover can be performed at a

convenient time in a planned manner.

In the event of a main server failure, you need to switch to the standby

server as quickly as possible to minimize system downtime (failover).

A replica is promoted to independent mode by either executing the

command pg_ctl promote from the OS or the function pg_promote from

SQL. Switching with the trigger file (the promote_trigger_file option) is no

longer supported in PostgreSQL 16 and higher.

Before promoting a replica, you must make sure that the original leader is

stopped. If it continues to work and clients are still connected to it, data on

different servers can disperse, and reconciling them afterwards is messy,

non-automatable, and often resulting in some data being lost.

To switch to a standby server, you must redirect clients to it. This requires

the use of third-party software (as discussed in the “Cluster Technologies”

module), since PostgreSQL does not have its own tools for this.

https://postgrespro.com/docs/postgresql/16/warm-standby-failover

Replica Promotion

Apply WAL records

already received by the wal receiver but not yet applied by the startup

process

Terminate processes

wal receiver

startup

Start processes

wal writer

autovacuum launcher

archiver (if configured)

Switch to a new timeline

After receiving the signal, the replica gets promoted: it performs recovery

and becomes a regular server.

The startup process applies log records that have already been received by

the wal receiver process, but have not yet been applied. Then, the wal

receiver and startup processes terminate since they are not needed on the

leader.

On the other hand, the wal writer and autovacuum launcher processes are

started up (archiver too, if configured.)

After completing recovery, the server switches to a new timeline.

Tiny details aside, the process is essentially just a recovery from backup.

Failback

primary

(former replica)

archiver

WAL archive

WAL files

select, insert

update, delete

former leader

switch to new timeline

After promotion, the former replica acts as a standalone server. It processes

client queries and generates its own WAL archive – within a new timeline.

If the switchover wasn't caused by a serious server failure, you'll need to

perform a failback: quickly bring that server back online – this time as a

replica.

Simple Reconnect

The former leader connects to the new leader as a replica

works only if the replica received all WAL records from the former

leader before the switchover

leader

leaderstandby

standby

clean

shutdown

switchover

When the leader is shut down cleanly (using the fast or the smart shutdown

mode), all of its WAL records will most likely reach the replica, though this

isn't guaranteed.

The shutdown procedure first terminates all backend processes, then

performs a checkpoint, and only at the very end stops wal sender, giving the

replica time to receive the checkpoint record.

You can check the leader's position in WAL with pg_controldata (look for

"latest checkpoint location"), while the replica's position is shown by the

pg_last_wal_receive_lsn function. This function returns the current position

in WAL, so its value must be ahead of the latest checkpoint location by the

size of the checkpoint record.

If all records were actually received, then the former leader can immediately

connect to the new one just by adjusting the configuration accordingly.

If the leader is stopped without performing a checkpoint (due to a crash or

shutdown in the immediate mode), such a connection will likely be

impossible on a server with a current load. The server won't be able to start,

and an error will be logged.

Backup

The former leader is restored from a backup

creates a completely new replica, takes a long time

can be expedited with rsync if not much time have passed

(still takes a while for large databases)

leader

leaderstandby

standby

lost

WAL records

When a leader crashes, there's a good chance that some WAL records don't

make it to the replica. You can't just reconnect the former leader as a replica

in this case.

The simplest and most reliable approach is to build a completely new replica

by creating and deploying a fresh base backup. However, for large

databases, this can take a long time.

A variation of this approach is to skip pg_basebackup and instead create the

backup using the backup API and rsync.

If you create the backup immediately after promoting the replica, most files

won't have changed yet, which can significantly speed up the process.

But naturally, this does complicate things.

pg_rewind

Preparing for Recovery

revert the lost WAL records by replacing the affected pages on the target

server with pages from the source server

copy all system files from the source server

leader

leaderstandby

standby

lost

WAL records

at this

moment

target server

source server

An even faster approach is to use the stock tool pg_rewind.

It identifies where the two servers diverged, finds the nearest common

checkpoint before that point, and by scanning the WAL, determines which

pages have been modified since then.

These pages are then replaced with their counterparts from the source

server (the new leader), and there shouldn't be many of them. The utility

also copies all system files from the source server.

The server then applies all necessary WAL records from the new leader. At

this point, it's no longer the pg_rewind doing the work, but rather the

standard recovery process that runs when the server starts up. To ensure

recovery starts from the correct point, the utility creates a backup_label

control file.

https://postgrespro.com/docs/postgresql/16/app-pgrewind

Considerations with

pg_rewind

Target server (former leader)

all necessary WAL files must be either in pg_wal or in the archive

checksums must be enabled or wal_log_hints must be on

the parameter full_page_writes must be on

Source server (current leader)

clinet-server connection:

--source-server = connection string

cleanly shut down server:

--source-pgdata = directory

For the source server, if it is running, the --source-server switch specifies the

libpq client-server connection string (the utility does not use the replication

protocol). The connecting role must have the privileges required to run

pg_ls_dir, pg_stat_file, and pg_read_binary_file functions.

If the source server was shut down cleanly, you can just point to its data

directory with the --source-pgdata switch.

Several specific requirements must be met for the tool to work:

●

All WAL segments containing records from the last common checkpoint to

the current moment must be either in the target server's pg_wal directory

or present and available from the WAL archive.

In the second case, you need to set the restore_command parameter

and the --restore-target-wal switch.

●

Due to errors during writing, data mismatch within the pages is possible

(the non-atomic writing problem, discussed in the "Base backup" module).

This is solved by having the target server write each page's full image to

WAL after it is first changed since the checkpoint. This is enabled by

full_page_writes = on. This mode does ignores "minor" page changes

(hint bits), so the cluster also must either have page checksum calculation

enabled or have wal_log_hints = on.

●

If the target server was shut down incorrectly and without a checkpoint,

the tool will start it in single-user mode and immediately perform a clean

shutdown.

Switchover and Archiving

primary

(leader)

WAL segments

select, insert

update, delete

standby

(replica)

archiver

WAL archive

wal receiver

startup

wal sender

archive_mode = on

segment

not committed yet:

temporary issues

WAL segments

from leader

The write-ahead log archive, populated through continuous archiving, has an

problem when it comes to streaming replication and switchover:

on a leader failure, some WAL segments may not make it into the archive.

For example, if the disk runs out of space, archive_command willl return an

error.

Switchover and Archiving

primary

(former replica)

archiver

WAL archive

WAL files

select, insert

update, delete

former leader

lost segment, recovery possible only up to this point

archive_mode = on

The replica is unaware of the archiving settings on the leader. When the

former replica takes over as the leader, it won't write the missing file

segments to the archive (even though it could) because it assumes the

archive was functioning properly all along. The archive will end up

incomplete.

This means you can only restore the system from existing backups up to the

point where this gap occurred. If this situation has occurred (and you detect

it in time), you must take a backup immediately.

Stream archiving doesn't have this problem because the pg_receivewal

utility monitors the directory contents and requests any missing WAL records

from the server.

However, as mentioned in the “Backup” module, using this tool means

maintaining additional infrastructure.

Switchover and Archiving

primary

(leader)

WAL segments

select, insert

update, delete

standby

(replica)

archiver

WAL archive

wal receiver

startup

archive_mode = always

archiver

the command must process concurrent write attempts from two servers correctly

archive_command

wal sender

WAL segments

from leader

With archive_mode = always, the replica starts an archiver process that

writes segments to the archive just like the leader server does. This means

that the same file will be recorded twice, by two servers. This places two

strict requirements on archive_command:

●

it must not overwrite an existing file, but should report success if a file

with identical content is already present in the archive;

●

it must handle concurrent write attempts from both servers correctly.

With these requirements met, the segment won't be lost because the replica

will keep trying to write it even after the leader server stops.

Of course, you could configure archive_command so that the leader and the

replica save WAL segments to separate archives, but that's hardly practical.

Takeaways

Switchover is used both during normal operation and in

emergency situations

After a switchover, the former leader must be brought back

online

Both procedures must be tried and tested in advance

The write-ahead log archive requires extra attention

Practice

1. Set up streaming replication from alpha to beta, with a

replication slot, without continuous archiving.

2. Simulate alpha server failure and switch

over to beta.

3. Bring alpha back online as a replica by backing up from the new

leader beta and adjusting the configuration.

Verify that replication is functioning and using the slot.

4. Switch back to alpha so that the former leader becomes the main

server again.

To simulate a failure, you can stop the server in immediate mode:

pg_ctlcluster 16 cluster_name stop -m immediate --skip-systemctl-redirect

or terminate the main postgres process:

kill -QUIT номер_процесса

The process ID is the first line of the PGDATA/postmaster.pid file.

After completing the practice tasks, delete the slot and the created

database, and reset the configuration.

Practice+

1. Configure the alpha server to retain two additional WAL

segments after each checkpoint.

Start physical replication from alpha to beta without a replication

slot.

2. Crash alpha and promote beta. Restart alpha and modify some

data on it. Verify that the data on alpha and beta do not match.

3. Stop alpha and start it as a replica of beta using pg_rewind.

1. Set up replication with the simplest settings and without a replication slot.

To ensure that two additional WAL segments are retained, set a suitable

wal_keep_size value.

After completing the practice tasks, delete the slot and the created

database, and reset the configuration.