5 Changes of Parameters

Source chapter 3.3 for PostgreSQL 9.5.

Alpha 2Source pages: PDF 27-30Original PDF on GitHub

PDF page 27

The following parameters have been changed in PostgreSQL 9.5.

3.3.1 Added Parameters

The following parameters have been added.

Parameter Name	Description	Default Value
cluster_name	The name that is included in process titles	''
gin_pending_list_limit	Maximum size of the GIN pending list	4MB
row_security	Enable Row Level Security feature. available value: on, off and force	on
track_commit_timestamp	Output the last commit time of the transaction to WAL	off
wal_compression	WAL compression at the full page write	off
log_replication_commands	Output log about replication command	off
max_wal_size	WAL size to start the checkpoint; the range of values: 2 - 2147483647	1GB
min_wal_size	WAL size to start the recycling the WAL files; the range of values: 2 - 2147483647	80MB
operator_precedence_warning	Output a warning to the SQL that has a possibility of changing that the result due to the change in priorities	off
wal_retrieve_retry_interval	The re-acquisition interval of the WAL data in milliseconds	5s

Table 20 Added Parameters

wal_retrieve_retry_interval parameter

Wal_retrieve_retry_interval has been parameterized from the fixed value setting in the previous version. The default value of 5 seconds is the same behavior as the previous version.

3.3.2 Changed Parameters

The following parameters are changed in the range of setting or the options.

PDF page 28

Parameter Name	Modification
log_autovacuum_min_duration	Can be specified for each table
archive_mode	“always” can be specified
trace_sort	The output of the additional information
debug_assertions	Became read-only
local_preload_libraries	Allow set by ALTER ROLE SET statement

Table 21 Changed Parameters

log_autovacuum_min_duration

Parameter log_autovacuum_min_duration can now be specified for each table.

Example 16 Setting parameter for each table

postgres=> ALTER TABLE vacuum1 SET (log_autovacuum_min_duration = 1000) ;
ALTER TABLE
postgres=> \d+ vacuum1
                               Table "public.vacuum1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+--------
 c1     | numeric               |         | main     |         |
 c2     | character varying(10)     |         | extended |          |
Options: log_autovacuum_min_duration=1000

archive_mode

“always” has been added to the choice of the parameter archive_mode. There is no difference between “on” and “always” except the slave instance in the replication environment. If this parameter is specified to “always” in the slave instance, archiver process is started, and the archive log is output.

PDF page 29

3.3.3 Parameters changed the default value

The default values of the following parameters have been changed.

Parameter Name	PostgreSQL 9.4	PostgreSQL 9.5
server_version 9.4.4	server_version 9.4.4	9.5alpha2
server_version_num 90404	server_version_num 90404	90500
search_path	“$user”,public	“$user”, public

Table 22 Parameters the default value of which is changed

3.3.4 Obsoleted Parameter

The following parameters are obsoleted.

Obsoleted checkpoint_segments

Parameter checkpoint_segments, which determine the occurrence time of the checkpoint, is obsolete and has been divided into parameter max_wal_size and min_wal_size.

Parameter Name	Description	Default Value
max_wal_size	Maximum size to let the WAL grow to between automatic WAL checkpoints.	1GB
min_wal_size	As long as WAL disk usage stays below this setting.	80MB

Table 23 Alternative parameters for checkpoint_segments

In previous version, parameters checkpoint_segments, was used for both starting the checkpoint and recycling the WAL file. Therefore, when the interval of the checkpoint was extended, a lot of WAL file was created. This feature has been changed so as to control the two functions by separate parameters in the PostgreSQL 9.5.

Obsoleted ssl_renegotiation_limit

Parameter ssl_renegotiation_limit is obsoleted.

3.3.5 Changed parameters in recovery.conf File

The following parameters of the recovery.conf file has been changed.

PDF page 30

Added recovery_target_action parameter

Parameter recovery_target_action has been added. This parameter has extended the pause_at_recovery_target parameter of the previous version more generally. The operation in case it reaches the recovery target can be specified. You can specify the following values.

pause (default value)

Wait in a state where instance has reached the target.

promote

Complete the recovery, and accept the user’s connection.

shutdown

Complete the recovery, and stop the instance. Renaming recovery.conf file will not be performed.

Obsoleted parameter

Parameter pause_at_recovery_target has been removed. As an alternative parameter, abovementioned recovery_target_action is used. When the parameter pause_at_recovery_target is specified in recovery.conf file, the following message is logged and instance cannot be started.

Example 17 Error by pause_at_recovery_target setting

$ cat data/recovery.conf
restore_command = 'cp /usr/local/pgsql/arch/%f %p'
pause_at_recovery_target = on
$
$ pg_ctl -D data -w start
waiting for server to start....LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
.... stopped waiting
pg_ctl: could not start server
Examine the log output
$
$ cat data/pg_log/postgresql-2015-08-07_111751.log
LOG:  database system was shut down at 2015-08-07 11:17:20 JST
FATAL:  unrecognized recovery parameter "pause_at_recovery_target"
LOG:  startup process (PID 12233) exited with exit code 1
LOG:  aborting startup due to startup process failure