wal_level PostgreSQL

wal_level - Write Ahead Log Level - это один из параметров, который можно изменить в конфигурационного файла postgresql.conf

Расположение конфигурационного файла postgresql.conf можно получить выполнив

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

vi /var/lib/pgsql/data/postgresql.conf

Описание

wal_level (enum) wal_level determines how much information is written to the WAL. The default value is minimal, which writes only the information needed to recover from a crash or immediate shutdown. replica adds logging required for WAL archiving as well as information required to run read-only queries on a standby server. Finally, logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start. In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include: CREATE TABLE AS CREATE INDEX CLUSTER COPY into tables that were created or truncated in the same transaction But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so replica or higher must be used to enable WAL archiving (archive_mode) and streaming replication. In logical level, the same information is logged as with replica, plus information needed to allow extracting logical change sets from the WAL. Using a level of logical will increase the WAL volume, particularly if many tables are configured for REPLICA IDENTITY FULL and many UPDATE and DELETE statements are executed. In releases prior to 9.6, this parameter also allowed the values archive and hot_standby. These are still accepted but mapped to replica.

runtime-config-wal

Описание для PostgreSQL 9.2

wal_level (enum) wal_level determines how much information is written to the WAL. The default value is minimal, which writes only the information needed to recover from a crash or immediate shutdown. archive adds logging required for WAL archiving, and hot_standby further adds information required to run read-only queries on a standby server. This parameter can only be set at server start. In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include: CREATE TABLE AS CREATE INDEX CLUSTER COPY into tables that were created or truncated in the same transaction But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so either archive or hot_standby level must be used to enable WAL archiving (archive_mode) and streaming replication. In hot_standby level, the same information is logged as with archive, plus information needed to reconstruct the status of running transactions from the WAL. To enable read-only queries on a standby server, wal_level must be set to hot_standby on the primary, and hot_standby must be enabled in the standby. It is thought that there is little measurable difference in performance between using hot_standby and archive levels, so feedback is welcome if any production impacts are noticeable.

runtime-config-wal