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_name
name
[,immediately_reserve
boolean
,temporary
boolean
] )
→ record (slot_name
name
,lsn
pg_lsn
)
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
- PostgreSQL 10
- optional parameter
temporary
added for creating temporary replication slots (commit a924c327)
- optional parameter
- PostgreSQL 9.6
- optional parameter
immediately_reserve
added 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