uncategorized

PostgreSQL streaming replication with physical replication slot

Create dedicated replication user:

CREATE ROLE replica WITH REPLICATION PASSWORD 'xxxYYYzzz' LOGIN;

Then add physical replication slot, in order to stay in sync with WAL. Without it, if applying logs on replica will be too slow, main server could drop logs which replica did not consume yet, leading to broken replication:

select pg_create_physical_replication_slot('replica');

Then stop replica server and remove PGDIR/VERSION/main folder. Start copying:

pg_basebackup -D /var/lib/postgresql/14/main -P --wal-method=stream -R --checkpoint=fast -U replica --slot=replica -W -h a.b.c.d

Adjust configuraton discrepancies between main and replica servers, such connections limit and then start replica server.

To verify replication status, run on main server:

 select * from pg_replication_slots;