4 Utilities

PDF page 16

The following sections describe the major enhancements of the utility commands.

3.2.1 pg_rewind

Pg_rewind command has been added in PostgreSQL 9.5.

Overview

Pg_rewind command is a tool to build replication environment. Unlike pg_basebackup command, this command can synchronize to an existing database cluster. It will be used to resynchronization of the promoted slave instance and the old master instance.

Command Parameters

Following parameters can be specified to pg_rewind command.

Conditions for executing

To run the pg_rewind command, there are some conditions. pg_rewind command is to check the conditions to run from the contents of the pg_control file of source and target. It is necessary either to specify the parameters wal_log_hints of PostgreSQL instance to “on” (the default value: “off”), or to enable the function of the data checksum. The parameters full_page_writes is also need to be set to “on” (the default value: “on”).

PDF page 17

$ pg_rewind --source-server='host=remhost1 port=5432 user=postgres'
 --target-pgdata=data.p -P
connected to remote server
target server need to use either data checksums or "wal_log_hints = on"
Failure, exiting
The instance of the target database cluster must be stopped normally.

$ pg_rewind --source-server='host=remhost1 port=5432 user=postgres'
 --target-pgdata=data.p -P
target server must be shut down cleanly
Failure, exiting
Data copy procedure, as well as pg_basebackup command, use the connection to the wal sender
process. It is necessary to set the max_wal_senders parameter and pg_hba.conf file of connection
destination (data provider) instance.

Execution procedure

Pg_rewind should be executed according to the procedure listed below. In the following example, the old master instance is set to be the new slave instance using the promotion of the slave instance. 1. Setup the parameters of the master instance Setup the parameters in pg_hba.conf and postgresql.conf file. Reload the parameter file, if necessary. 2. Stop the target instance Stop the old master instance. 3. Execute pg_rewind command Run the pg_rewind command on the host of the old master instance. First specify with the -n parameter, and after the test, run without the -n parameter.

PDF page 18

$ pg_rewind --source-server='host=remhost1 port=5432 user=postgres'
 --target-pgdata=data.p
connected to server
The servers diverged at WAL position 0/9000060 on timeline 1.
Rewinding from last common checkpoint at 0/8000060 on timeline 1
reading source file list
reading target file list
reading WAL in target
need to copy 53 MB (total source directory size is 76 MB)
54294/54294 kB (100%) copied
creating backup label and updating control file
Done!
4. Create the recovery.conf
Pg_rewind command does not create recovery.conf file in the target database cluster. Therefore
you should create recovery.conf file in the new slave instance.
5. Modify the postgresql.conf
By the execution of pg_rewind command, postgresql.conf file has been overwritten and copied
from the remote host. Edit the parameters if necessary.
6. Start the new slave instance
Start the new slave instance. And check the pg_stat_replication catalog on the new master
instance.

Exit status

Pg_rewind command returns 0 when the process is completed successfully. If it fails then exits with 1.

3.2.2 pg_ctl

The default value for the instance stop mode (-m option) has been changed to 'fast' from 'smart'.

PDF page 19

3.2.3 vacuumdb

The –jobs parameter has been added to use multiple cores actively. You can specify the number of jobs of parallel processing for the –jobs parameter. The range of the number of the jobs is more than one, less than “macro FD_SETSIZE - 1” (in Red Hat Enterprise Linux 7 1,023 or less).

$ vacuumdb --jobs=-1
vacuumdb: number of parallel "jobs" must be at least 1
$ vacuumdb --jobs=1025
vacuumdb: too many parallel jobs requested (maximum: 1023)

Relationship between –jobs parameter and the number of session

When a number is specified to –jobs parameter, the same number of sessions as the number specified in the parameter are created. In case that VACUUM is performed for all database (–all specified), this command processes each database in parallel. In case that VACUUM is performed on a single database, it processes each table in parallel. The default value for this parameter is 1; it leads the same behavior as previous versions. In the following example, 10 postgres processes have started because ‘-jobs=10’ is specified.

PDF page 20

$ vacuumdb --jobs=10 -d demodb &
vacuumdb: vacuuming database "demodb"
$ ps -ef | grep postgres
postgres 14539     1  0 10:59 pts/2  00:00:00 /usr/local/pgsql/bin/postgres -D data
postgres 14540 14539  0 10:59 ?      00:00:00 postgres: logger process
postgres 14542 14539  0 10:59 ?      00:00:00 postgres: checkpointer process
postgres 14543 14539  0 10:59 ?      00:00:00 postgres: writer process
postgres 14544 14539  0 10:59 ?      00:00:00 postgres: wal writer process
postgres 14545 14539  0 10:59 ?      00:00:00 postgres: stats collector process
postgres 14569 14539  6 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14570 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14571 14539  5 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14572 14539  7 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14573 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14574 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14575 14539  9 11:00 ?      00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14576 14539  5 11:00        00:00:00 postgres: postgres demodb [local] VACUUM
postgres 14577 14539  0 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
postgres 14578 14539  1 11:00 ?      00:00:00 postgres: postgres demodb [local] idle
It the value specified in the --jobs parameter is larger than the number of --table parameters, the upper
limit of the degree of parallelism is the number of tables. In addition, since the PostgreSQL
max_connections parameter is not considered in the calculation of the number of sessions, in case that
the excess of the number of sessions is detected, "FATAL: sorry, too many clients already" error will
occur.

PDF page 21

3.2.4 pg_dump

The following enhancements have been implemented.

Add –enable-row-security parameter

At default, pg_dump command gets a dump by specifying the row_security parameter to “off”. Data acquisition will result in an error in case of connecting with user privileges that cannot bypass the Row Level Security. By specifying the –enable-row-security parameter, the dump file contains only the data that has the authority contained. Error in Alpha 2 will occur.

Add –snapshot parameter

Specify the snapshot ID created with pg_export_snapshot function. You can get a dump using the specified snapshot ID.

Enhance output at the –verbose parameter

Schema name is included in the log that is output when you specify the –verbose parameter.

Obsolete –ignore-version parameter

–ignore-version parameter was made obsolete. pg_dumpall command, pg_restore command has also been modified as well.

3.2.5 psql

The following features have been added to the psql command.

pager_min_lines parameter

Pager_min_lines parameter has been added. If this parameter is specified, pager is not used when the number of rows of output is less than the specified value. The default value is 0, and pager works in accordance with the number of rows of the terminal. This setting did not work at the author environment.

postgres=> \pset pager_min_lines 20
Pager won't be used for less than 20 lines

PDF page 22

\set ECHO errors

If you specify a \set ECHO errors, statement which you are trying to run is displayed in case of an error.

postgres=> SELECT * FROM notexists1 ;
ERROR:  relation "notexists1" does not exist
LINE 1: SELECT * FROM notexists1;
                      ^
postgres=> \set ECHO errors
postgres=> SELECT * FROM notexists1 ;
ERROR:  relation "notexists1" does not exist
LINE 1: SELECT * FROM notexists1;
                      ^
STATEMENT:  SELECT * FROM notexists1 ;

Display schema name error

A message is added to indicate the error occurrence location in the case a schema name which does not exist is specified.

postgres=> CREATE TABLE badschema.table1(c1 NUMERIC, c2 VARCHAR(10)) ;
ERROR:  schema "badschema" does not exist
LINE 1: CREATE TABLE badschema.table1(c1 NUMERIC, c2 VARCHAR(10)) ;

\watch output \timing information

The output of the \watch command, are now added to the results of \timing command.

PDF page 23

postgres=> \timing
Timing is on.
demodb=> \watch 1
Watch every 1s  Fri Aug  7 11:56:58 2015
              now
-------------------------------
 2015-08-07 11:56:59.931996+09
(1 row)
Time: 0.458 ms

Backslash Command

The following enhancements have been implemented. Require superuser privilege for \db+ command. In previous version, general user can execute \db+ command.

Command Modification Description

3.2.6 pgbench

Some new features have been provided for pgbench command.

–latency-limit parameter

Parameter –latency-limit (-L) can now be specified in the parameters of the pgbench command. If you specify a value in milliseconds for this parameter, the transaction rate of which the duration is shorter than the specified time is displayed.

PDF page 24

$ pgbench -c 10 -d pgbench -U pgbench -L 10
pghost:  pgport:  nclients: 10 nxacts: 10 dbName: pgbench
starting vacuum...end.
……………………………………………..
number of transactions actually processed: 100/100
number of transactions above the 10.0 ms latency limit: 64 (64.000 %)
latency average: 17.065 ms
latency stddev: 12.816 ms
tps = 479.927051 (including connections establishing)
tps = 546.603406 (excluding connections establishing)

Behavior change at the specified -f parameter

Behavior of the command in case of using a custom script (-f option), and not specifying the -n option, has been changed. In previous versions, VACUUM is performed to each table which pgbench created, and ended the command if the table does not exist. In PostgreSQL 9.5, it has been changed to continue the processing the custom scripts even if VACUUM process fails.

$ pgbench -f bench.sql -U user1 postgres
starting vacuum...ERROR:  relation "pgbench_branches" does not exist
(ignoring this error and continuing anyway)
ERROR:  relation "pgbench_tellers" does not exist
(ignoring this error and continuing anyway)
ERROR:  relation "pgbench_history" does not exist
(ignoring this error and continuing anyway)
end.
transaction type: Custom query
…………………………………………………………
Some of the new features other than the above has been implemented to pgbench command.

PDF page 25

3.2.7 pg_receivexlog

To the pg_receivexlog command, parameters for controlling the replication slot was added. Slot name is specified using –slot parameters as in the previous version.

–create-slot

Create a slot with the name specified in -slot parameter, and continues to receive WAL data. If there is no margin in the parameter max_replication_slots of the PostgreSQL instance, or if the slot having the same name already exists, then an error occurs.

–drop-slot

Remove the specified slot, and exit the command.

–synchronous

Flush the WAL data to disk immediately after it has been received

3.2.8 reindexdb

“-v” “-verbose” option has been added to output a detailed information. “REINDEX (VERBOSE) DATABASE database_name” statement is executed internally.

PDF page 26

$ reindexdb -v demodb
INFO:  index "pg_class_oid_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_relname_nsp_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_class_tblspc_relfilenode_index" was reindexed
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
………………………………..