Contents
pg_create_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_namename[,immediately_reserveboolean,temporaryboolean] )
→ record (slot_namename,lsnpg_lsn)
pg_create_physical_replication_slot (slot_namename[,immediately_reserveboolean)
→ record (slot_namename,lsnpg_lsn)
PostgreSQL 9.4 and PostgreSQL 9.5:
pg_create_physical_replication_slot (slot_namename)
→ record (slot_namename,lsnpg_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
- PostgreSQL 10
- optional parameter
temporaryadded for creating temporary replication slots (commit a924c327)
- optional parameter
- PostgreSQL 9.6
- optional parameter
immediately_reserveadded to retain WAL from the point of slot creation (commit 6fcd8851)
- optional parameter
- PostgreSQL 9.4
- added (commit 858ec118)
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.
References
- PostgreSQL documentation: Replication Management Functions