pg_create_physical_replication_slot()

A function for creating a physical replication slot

pg_create_physical_replication_slot() is a system function for creating a physical replication slot.

pg_create_physical_replication_slot() was added in PostgreSQL 9.4.

Usage

PostgreSQL 10 and later:

pg_create_physical_replication_slot ( slot_name name [, immediately_reserve boolean, temporary boolean ] )
        → record ( slot_name name, lsn pg_lsn )

PostgreSQL 9.6:

pg_create_physical_replication_slot ( slot_name name [, immediately_reserve boolean )
        → record ( slot_name name, lsn pg_lsn )

PostgreSQL 9.4 and PostgreSQL 9.5:

pg_create_physical_replication_slot ( slot_name name )
        → record ( slot_name name, lsn pg_lsn )

The slot name must contain only a combination of lower case letters, numbers, and the underscore character, and can be a maximum of 63 characters long (NAMEDATALEN). If the name exceeds this limit, it will be silently truncated to 63 chararacters.

If the immediately_reserve parameter is provided and set to TRUE, WAL will be reserved from the most recent checkpoint.

If the temporary parameter is provided and set to TRUE, the replication slot will not be stored permanently and will only be available for the duration of the current session.

Change history

Examples

Creating a replication slot using pg_create_physical_replication_slot():

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

Creating a replication slot and ensuring WAL is retained from the point-in-time the slot was created:

postgres=# SELECT cc.checkpoint_lsn,
                  cprs.*
             FROM pg_control_checkpoint() cc,
                  pg_create_physical_replication_slot(
                    slot_name := 'bar',
                    immediately_reserve := TRUE
                  ) cprs;
 checkpoint_lsn | slot_name |    lsn    
----------------+-----------+-----------
 0/30004E8      | bar       | 0/30004B0
(1 row)

Creating a temporary replication slot; note the slot's file in the pg_replslot directory will only persist as long as the current session:

postgres=# SELECT * FROM pg_create_physical_replication_slot('tmp_slot', temporary := TRUE);
 slot_name | lsn 
-----------+-----
 tmp_slot  | 
(1 row)

postgres=# SELECT pg_ls_dir('./pg_replslot');
 pg_ls_dir 
-----------
 tmp_slot
(1 row)

postgres=# \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# SELECT pg_ls_dir('./pg_replslot');
 pg_ls_dir 
-----------
(0 rows)

Attempting to create a replication slot with a name of 64 characters or more:

postgres=# SELECT * FROM pg_create_physical_replication_slot(repeat('x', 100));
                            slot_name                            | lsn 
-----------------------------------------------------------------+-----
 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 
(1 row)

Attempting to create a replication slot with a name containing invalid characters:

postgres=# SELECT * FROM pg_create_physical_replication_slot('!"#$ ');
ERROR:  replication slot name "!"#$ " contains invalid character
HINT:  Replication slot names may only contain lower case letters, numbers, and the underscore character.

Categories

Replication, System function, WAL

See also

pg_replication_slots, pg_copy_physical_replication_slot(), pg_drop_replication_slot(), pg_create_logical_replication_slot()