To initiate streaming replication, the frontend sends the replication parameter in the startup message. A Boolean value of true (or on, yes, 1) tells the backend to go into physical replication walsender mode, wherein a small set of replication commands, shown below, can be issued instead of SQL statements.
Passing database as the value for the replication parameter instructs the backend to go into logical replication walsender mode, connecting to the database specified in the dbname parameter. In logical replication walsender mode, the replication commands shown below as well as normal SQL commands can be issued.
In either physical replication or logical replication walsender mode, only the simple query protocol can be used.
For the purpose of testing replication commands, you can make a replication connection via psql or any other libpq-using tool with a connection string including the replication option, e.g.:
psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
However, it is often more useful to use pg_receivewal (for physical replication) or pg_recvlogical (for logical replication).
Replication commands are logged in the server log when log_replication_commands is enabled.
The commands accepted in replication mode are:
IDENTIFY_SYSTEM #Requests the server to identify itself. Server replies with a result set of a single row, containing four fields:
systemid (text)The unique system identifier identifying the cluster. This can be used to check that the base backup used to initialize the standby came from the same cluster.
timeline (int8)Current timeline ID. Also useful to check that the standby is consistent with the primary.
xlogpos (text)Current WAL flush location. Useful to get a known location in the write-ahead log where streaming can start.
dbname (text)Database connected to or null.
SHOW name #Requests the server to send the current setting of a run-time parameter. This is similar to the SQL command SHOW.
nameThe name of a run-time parameter. Available parameters are documented in Chapter 19.
TIMELINE_HISTORY tli #Requests the server to send over the timeline history file for timeline tli. Server replies with a result set of a single row, containing two fields. While the fields are labeled as text, they effectively return raw bytes, with no encoding conversion:
filename (text)File name of the timeline history file, e.g., 00000002.history.
content (text)Contents of the timeline history file.
CREATE_REPLICATION_SLOT slot_name [ TEMPORARY ] { PHYSICAL | LOGICAL output_plugin } [ ( option [, ...] ) ] #Create a physical or logical replication slot. See Section 26.2.6 for more about replication slots.
slot_nameThe name of the slot to create. Must be a valid replication slot name (see Section 26.2.6.1). The name cannot be pg_conflict_detection as it is reserved for the conflict detection.
output_pluginThe name of the output plugin used for logical decoding (see Section 47.6).
TEMPORARYSpecify that this replication slot is a temporary one. Temporary slots are not saved to disk and are automatically dropped on error or when the session has finished.
The following options are supported:
TWO_PHASE [ boolean ]If true, this logical replication slot supports decoding of two-phase commit. With this option, commands related to two-phase commit such as PREPARE TRANSACTION, COMMIT PREPARED and ROLLBACK PREPARED are decoded and transmitted. The transaction will be decoded and transmitted at PREPARE TRANSACTION time. The default is false.
RESERVE_WAL [ boolean ]If true, this physical replication slot reserves WAL immediately. Otherwise, WAL is only reserved upon connection from a streaming replication client. The default is false.
SNAPSHOT { 'export' | 'use' | 'nothing' }Decides what to do with the snapshot created during logical slot initialization. 'export', which is the default, will export the snapshot for use in other sessions. This option can't be used inside a transaction. 'use' will use the snapshot for the current transaction executing the command. This option must be used in a transaction, and CREATE_REPLICATION_SLOT must be the first command run in that transaction. Finally, 'nothing' will just use the snapshot for logical decoding as normal but won't do anything else with it.
FAILOVER [ boolean ]If true, the slot is enabled to be synced to the standbys so that logical replication can be resumed after failover. The default is false.
In response to this command, the server will send a one-row result set containing the following fields:
slot_name (text)The name of the newly-created replication slot.
consistent_point (text)The WAL location at which the slot became consistent. This is the earliest location from which streaming can start on this replication slot.
snapshot_name (text)