엑셈

In this article, we look at the parameters related to PostgreSQL replication.

The figure below shows the parameters that need to be set for each type of replication. Each parameter is described below in alphabetical order under Replication Parameter.

Replication Parameter

archive_cleanup_command

Used to clean up archived WAL files that the standby server no longer needs. The pg_archivecleanup application is commonly used to make WAL file cleanup easy.

ㅤ	Description
Applies to	Standby Server
Category	WRITE-AHEAD LOG → Archive Recovery
Restart required	Not required
Changes	• This parameter was introduced in PostgreSQL 12.

archive_command

Specifies the shell command run to store a WAL file in the archive directory when the file fills up. The setting below means: copy the WAL file in the pg_wal directory to the /PostgreSQL/Archive directory.

ㅤ	Description
Applies to	Main Server
Category	WRITE-AHEAD LOG → Archiving
Restart required	Not required
Placeholders	• `%p` : absolute path of the WAL file • `%f` : archive file name

archive_mode

When archive mode is enabled, WAL files stored in the WAL directory (pg_wal) are passed to the archive directory through the command set in archive_command (usually copied). Because the oldest WAL files are deleted according to the wal_keep_size parameter (wal_keep_segments in PostgreSQL 12 and earlier), archive mode is used to keep the WAL files that get deleted. It must be set to wal_level = replica or higher; with wal_level = minimal archive mode cannot be used.

ㅤ	Description
Applies to	Main Server
Category	WRITE-AHEAD LOG → Archiving
Default	off
Restart required	Required
Values	`always`, `on`, `off`

archive_timeout

Normally a WAL file switches to a new WAL file only when it fills up, so when there is nothing to write to the WAL file, for example when there are no transactions, no archiving happens. Without archiving, the data lag between the main server and the standby server lasts longer in file-based replication and streaming replication setups. The archive_timeout parameter sets the interval at which WAL files are switched periodically, which makes archiving happen periodically. It is usually set to about 60 seconds; setting it too short makes the archive directory grow, so be careful.

ㅤ	Description
Applies to	Main Server
Category	WRITE-AHEAD LOG → Archiving
Default	0
Restart required	Not required

hot_standby

Must be set to on on a standby server where the database is in standby state and replication is running. If set to off, you cannot run SQL while replication is in progress.

ㅤ	Description
Applies to	Standby Server
Category	WRITE-AHEAD LOG → Standby Servers
Default	on
Restart required	Required
Values	`on`, `off`

max_replication_slots

Specifies the maximum number of replication slots the database can support. The default is 10, and if you set it lower than the number of replication slots that currently exist, the server will not start. To use replication slots, it must also be set to wal_level = replica or higher.

ㅤ	Description
Applies to	Main Server
Category	REPLICATION → Sending Servers
Default	10 (0 in PostgreSQL 9.6 and earlier)
Restart required	Required
Changes	• Starting with PostgreSQL 10, the default changed from 0 to 10.

max_wal_senders

Specifies the maximum number of connections to WAL sender processes. The default is 10. The number of WAL sender processes cannot be set higher than max_connections, and it is usually set to the number of standby servers + 1.

ㅤ	Description
Applies to	Main Server
Category	REPLICATION → Sending Servers
Default	10 (0 up to PostgreSQL 9.6)
Restart required	Required
Changes	• Starting with PostgreSQL 10, the default changed from 0 to 10. (0 means replication is disabled.)

primary_conninfo

Sets the connection string used to connect to the main server. It typically specifies the main server's host (IP), port, a user with the replication privilege, and the password.

ㅤ	Description
Applies to	Standby Server
Category	WRITE-AHEAD LOG → Standby Servers
syntax	primary_conninfo = ‘host=10.10.45.230 port=5432 user=replicauser password=mypass’
Restart required	Not required
Changes	• This parameter was introduced in PostgreSQL 12. • Up to PostgreSQL 11, it was set in recovery.conf.

primary_slot_name

Specifies the name of the replication slot used on the main server. If you do not use replication slots, you do not need to set it.

ㅤ	Description
Applies to	Standby Server
Category	WRITE-AHEAD LOG → Standby Servers
Restart required	Not required
Changes	• This parameter was introduced in PostgreSQL 12. • Up to PostgreSQL 11, it was set in recovery.conf.

restore_command

Specifies the shell command used to restore the database from WAL archive files.

ㅤ	Description
Applies to	Standby Server
Category	WRITE-AHEAD LOG → Archive Recovery
Restart required	Not required
Changes	• This parameter was introduced in PostgreSQL 12.• Up to PostgreSQL 11, it was set in recovery.conf.
Placeholders	• `%p` : absolute path to copy the WAL file to • `%f` : archive file name

wal_keep_size

Specifies the total size of WAL files that can be kept in the pg_wal directory, where WAL files are stored. It is set in megabytes (MB). A single WAL file is normally 16MB, so you can calculate the number of WAL files from wal_keep_size. Adjust the number of WAL files to prevent replication from stopping because WAL files were deleted before the standby server replicated them.

ㅤ	Description
Applies to	Main Server
Category	REPLICATION → Sending Servers
Default	0
Restart required	Not required
Changes	• This parameter was introduced in PostgreSQL 13. • Up to PostgreSQL 12, the wal_keep_segments parameter, which specifies the number of WAL files, was used.

wal_level

Introduced in PostgreSQL 9.0, this parameter determines how much information is written to WAL. Possible values are minimal, replica, and logical. Set it to replica to use physical replication, and to logical to use logical replication.

ㅤ	Description
Applies to	Main Server
Location	WRITE-AHEAD LOG → Settings
Default	replica (minimal in PostgreSQL 9.6 and earlier)
Restart required	Required
Values	• `minimal` : writes only the information needed to recover from a crash or immediate shutdown. • `replica` : merges archive and hot_standby starting with PostgreSQL 9.6. - archive : minimal + adds the information needed for WAL archiving. - hot_standby : archive + the information needed to run read-only queries on a standby server • `logical` : adds the information needed to support replica + logical decoding. More data is stored, so WAL grows larger.
Changes	• `logical` was added in PostgreSQL 9.4. • Starting with PostgreSQL 9.6, `replica` replaces archive and hot_standby. • Starting with PostgreSQL 10, the default changed from minimal to `replica`.