pg_copy_physical_replication_slot()

A function for duplicating a physical replication slot

pg_copy_physical_replication_slot() is a system function for copying an existing physical replication slot.

pg_copy_physical_replication_slot() was added in PostgreSQL 12.

Usage

pg_copy_physical_replication_slot ( src_slot_name name, dst_slot_name name [, temporary boolean ] )
        → record ( slot_name name, lsn pg_lsn )

The slot to be copied must have been created with reserved WAL (i.e. pg_create_physical_replication_slot() executed with immediately_reserve set to TRUE).

Change history

Examples

Copying a replication slot using pg_copy_physical_replication_slot():

postgres=# SELECT * FROM pg_create_physical_replication_slot('foo', immediately_reserve := TRUE);
 slot_name |    lsn    
-----------+-----------
 foo       | 0/30004B0
(1 row)

postgres=# SELECT * FROM pg_copy_physical_replication_slot('foo', 'bar');
 slot_name | lsn 
-----------+-----
 bar       | 
(1 row)

Note that as of PostgreSQL 14, pg_copy_physical_replication_slot() does not return a value in the lsn column, even though one is reported via pg_replication_slots:

postgres=# SELECT slot_name, slot_type, restart_lsn, wal_status FROM pg_replication_slots;
 slot_name | slot_type | restart_lsn | wal_status 
-----------+-----------+-------------+------------
 foo       | physical  | 0/30004B0   | reserved
 bar       | physical  | 0/30004B0   | reserved
(2 rows)

The source replication slot must have been created with reserved WAL:

postgres=# SELECT * FROM pg_create_physical_replication_slot('boo');
 slot_name | lsn 
-----------+-----
 boo       | 
(1 row)

postgres=# SELECT * FROM pg_copy_physical_replication_slot('boo', 'baz');
ERROR:  cannot copy a replication slot that doesn't reserve WAL

Attempting to copy a non-existent replication slot:

postgres=# SELECT * FROM pg_copy_physical_replication_slot('zoo', 'baz');
ERROR:  replication slot "zoo" does not exist

Categories

Replication, System function, WAL

See also

pg_create_physical_replication_slot(), pg_drop_replication_slot(), pg_copy_logical_replication_slot()