5 Configuration Parameters

PDF page 57

In PostgreSQL 12 the following parameters have been changed.

3.3.1 Added parameters

The following parameters have been added.

PDF page 58

Primary_conninfo parameter

Specifies the connection string for the primary instance. Previously this was specified in the recovery.conf file. The default value for the application_name entry has traditionally been “walreceiver”, but when the cluster_name parameter is specified, the value for cluster_name has been changed to the default value. The value of the application_name column in the pg_stat_replication catalog changes.

Ssl_library parameter

This parameter indicates the name of the library that provides the SSL feature. The parameter value is “OpenSSL” when –with-openssl is specified when executing configure command in Red Hat Enterprise Linux environment.

postgres=# SHOW ssl_library ;
 ssl_library
-------------
 OpenSSL
(1 row)

Shared_memory_type parameter

This parameter specifies the type of shared memory (such as shared_buffers).

PDF page 59

The default value for this parameter on Linux is mmap. This is the same behavior as PostgreSQL

9.3 and later. Very small System V shared memory and most shared memory are configured using

memory map file (mmap). Setting this parameter to sysv allows you to revert to the pre-PostgreSQL

9.2 behavior. In this case, configure all shared memory using System V shared memory.

Plancache_mode parameter

This parameter sets the method for caching the execution plan of a prepared statement (created by the PREPARE statement). The default value is auto, which is the same behavior as previous versions. Normally, an execution plan is generated each time the SQL statement created by the PREPARE statement is executed by the EXECUTE statement. In the following example, it can be seen that the execution plan changes depending on the parameters specified in the EXECUTE statement. It indicates that the data stored in column C2 is uneven, there is little plan0 data (does an index search), and there is a lot of plan1 data (a whole table is searched).

PDF page 60

postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1 WHERE c2=$1 ;
PREPARE
postgres=> EXPLAIN EXECUTE sel1('plan0') ;
                               QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx1_plan1 on plan1  (cost=0.42..4.44 rows=1 width=12)
   Index Cond: ((c2)::text = 'plan0'::text)
(2 rows)
postgres=> EXPLAIN EXECUTE sel1('plan1') ;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..17906.01 rows=1000001 width=12)
   Filter: ((c2)::text = 'plan1'::text)
(2 rows)

 If the same SQL statement is executed 5 times or more, the execution plan may be cached, and the
cached execution plan (general execution plan) may be used even if the parameter is changed from
the next time. In the example below, the display in the execution plan changes from a literal value to
$1 at the execution of the sixth EXPLAIN statement.

postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Repeat 5 times
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12)
   Filter: ((c2)::text = 'plan1'::text)
(2 rows)
postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Sixth (execution
plan changed)
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12)
   Filter: ((c2)::text = ($1)::text)
(2 rows)

PDF page 61

The newly added parameter plan_cache_mode changes this behavior. Setting the parameter value to “force_custom_plan” turns off execution plan caching. On the other hand, setting the parameter value to “force_generic_plan” immediately enables execution plan caching.

postgres=> SET plan_cache_mode = force_generic_plan ;
SET
postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1
                 WHERE c2=$1 ;
PREPARE
postgres=> EXPLAIN EXECUTE sel1('plan0') ;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on plan1  (cost=0.00..23312.50 rows=1000120 width=12)
   Filter: ((c2)::text = ($1)::text)
(2 rows)

Data_sync_retry parameter

This parameter determines what happens if the fsync system call issued during a checkpoint fails. In previous versions, the fsync function was re-executed (data_sync_retry = “on”), and the default behavior of the new version (data_sync_retry = “off”) caused an instance stoppage by PANIC if the fsync system call failed. This parameter has been added since PostgreSQL 11.2.

3.3.2 Changed parameters

The setting range and options were changed for the following configuration parameters.

PDF page 62

Parameter name Changes client_min_messages It is no longer possible to set it to a higher level than ERROR.

The default_with_oids parameter cannot be referenced from the pg_settings catalog.

PDF page 63

postgres=> SHOW default_with_oids ;
default_with_oids
-------------------
 off
(1 row)
postgres=> SET default_with_oids = on ;
psql: ERROR:  tables declared WITH OIDS are not supported
postgres=> SELECT COUNT(*) FROM pg_settings WHERE
                 name='default_with_oids' ;
 count
-------
     0
(1 row)

Wal_sender_timeout parameter

This parameter can now be changed by the user on a per session basis. This allows changing parameters on a per-connection basis from slave instances in a streaming replication environment.

$ grep primary_conninfo data/postgresql.conf
primary_conninfo = 'host=svrhost1 port=5432 user=postgres
password=password options=''-c wal_sender_timeout=5000'''

Log_connections parameter

The value of the application_name parameter is added to the log that is output when this parameter is set to ‘on’.

PDF page 64

Connection from psql command

LOG:  connection authorized: user=postgres database=postgres
application_name=psql

Connection from pg_basebackup command

LOG:  replication connection authorized: user=postgres
application_name=pg_baseback

Connection from Streaming Replication slave instance

LOG:  replication connection authorized: user=postgres
application_name=walreceiver

Trace_sort parameter

The output log format has been changed when this parameter is set to ‘on’.

LOG:  -1 switching to external sort with 16 tapes: CPU: user: 0.00
s, system: 0.00 s, elapsed: 0.00 s
LOG:  -1 using 3951 KB of memory for read buffers among 15 input
tapes
LOG:  performsort of -1 done (except 15-way final merge): CPU:
user: 0.15 s, system: 0.01 s, elapsed: 0.16 s

LOG:  performsort of worker -1 starting: CPU: user: 0.00 s,
system: 0.00 s, elapsed: 0.00 s
LOG:  internal sort of worker -1 ended, 25 KB used: CPU: user:
0.00 s, system: 0.00 s, elapsed: 0.00 s

Wal_level parameter

When a replication slot is used, it became to be checked whether the wal_level parameter is an appropriate value during the instance startup. If the required level is not set, instance startup will fail.

PDF page 65

In the following example, wal_level is changed to “replica” and the instance is restarted in the Logical Replication environment.

postgres=# ALTER SYSTEM SET wal_level=minimal ;
ALTER SYSTEM
postgres=# \q
$
$ pg_ctl -D data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-10-04 00:52:19.890 EDT [22682]
FATAL:  WAL archival cannot be enabled when wal_level is "minimal"
 stopped waiting
pg_ctl: could not start server
Examine the log output.
$

3.3.3 Parameters with default values changed

The default values of the following configuration parameters have been changed.