13 Change of Utility

PDF page 86

This section explain the major function enhancement points of utility commands.

3.11.1 psql

The following functions have been added to the psql command.

\d command

The format of table information outputted by \d command has been changed. “Modifier” column that was conventionally used has been divided into Collation, Nullable, and Default.

postgres=> \d data1
            Table "public.data1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 c1     | numeric               | default 1
 c2     | character varying(10) | not null

postgres=> \d data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          | 1
 c2     | character varying(10) |           | not null |

Additional information of the \timing command

The \timing command controls the output of the execution time of the SQL statement. In the new version, time format that is easy to understand has been added to the execution time output. When the SQL execution time is less than 1 second, it is outputted in the same format as the old version.

PDF page 87

postgres=> \timing
Timing is on.
postgres=> INSERT INTO data1 values (generate_series(1, 10000000)) ;
INSERT 0 10000000
Time: 61086.012 ms (01:01.086)

\gx command

The \gx command reruns the most recently executed SQL statement in the extended format.

postgres=> SELECT * FROM data1 ;
 c1 |  c2
----+------
  1 | data
(1 row)
postgres=> \gx
-[ RECORD 1 ]
c1 | 1
c2 | data

\set command

More parameters have been displayed by the \set command.

postgres=> \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'demodb'
ECHO = 'none'
…

PDF page 88

\if, \elif, \else, \endif command

It is now possible to perform conditional branching within the psql command. Conditional branching can be performed between \if, \else, and \endif, and the commands there between are treated as a block. For \If command and \elif command, the parameters that can determined True or False must be specified. It is also possible to nest conditional statements.

SELECT
EXISTS(SELECT 1 FROM customer WHERE customer_id=123) AS is_customer,
EXISTS(SELECT 1 FROM employee WHERE employee_id=456) AS is_employee ;
\gset
\if :is_customer
SELECT * FROM customer WHERE customer_id = 123 ;
\elif :is_employee
SELECT * FROM employee WHERE employee_id = 456 ;
\endif

3.11.2 pg_ctl

The following functions have been added to the pg_ctl command.

Wait for promotion

The pg_ctl command can specify an option ‘-w’ to wait for the standby instance to be promoted. In the past, it was necessary to refer to the trigger file to confirm completion of promotion.

Added aliases for options

“–wait” and “–no-wait " can be used as aliases for option “-w” and “-W “. Also, “–options” can be used for “-o” to specify options.

Wait for startup (-w) to default

By default, all operations have become to wait for operation completion (–wait). In the past, the default behavior of instance startup and promotion processing did not wait for operation completion.

3.11.3 pg_basebackup

The following changes have been added to the pg_basebackup command.

PDF page 89

Change default mode

The default WAL transfer mode is now Stream. For this reason, connections to multiple wal sender processes are used by default.

Discontinue the -x option

The -x option (–xlog option) has been deprecated.

Change the -X option

For -X option, value “none” which means that transaction log does not included in the backup can be specified now. Also, the long option name has been changed from “–xlog-method” to “–wal-method”.

Change the –xlogdir option

The option name has been changed from “–xlogdir” to “–waldir”.

-Ft option and -Xstream option combination

The option to output backup data to tar file -Ft and the -Xstream option can now be used at the same time. In this case, the pg_wal.tar file in which transaction logs are stored in the directory specified by the -D option is output.

$ pg_basebackup -D back1 -v -Ft -Xstream
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
…
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
$ ls back1/
base.tar  pg_wal.tar
$ tar tvf back1/pg_wal.tar
-rw------- postgres/postgres 16777216 2017-05-20 16:36 0000001000000000000002F
-rw------- postgres/postgres        0 2017-05-20 16:36
archive_status/00000001000000000000002F.done
$

Using temporary replication slots

When the slot name (-S) is not specified (and –no-slot is not specified), temporary replication slot is

PDF page 90

used. Below is the log when log_replication_commands parameter is set to “on”. Temporary slots with names starting with pg_basebackup_ have been created.

LOG:  received replication command: IDENTIFY_SYSTEM
LOG:  received replication command: BASE_BACKUP LABEL 'pg_basebackup
base backup'    NOWAIT
LOG:  received replication command: IDENTIFY_SYSTEM
LOG:  received replication command: CREATE_REPLICATION_SLOT
"pg_basebackup_12889" TEMPORARY PHYSICAL RESERVE_WAL
LOG:  received replication command: START_REPLICATION SLOT
"pg_basebackup_12889" 0/49000000 TIMELINE 1

If the replication slot is full, creating replication slot fails so that pg_basebackup command fails.
Please check that the parameter max_replication_slots has free space.

$ pg_basebackup -D back
pg_basebackup: could not connect to server: FATAL:  number of requested
standby connections exceeds max_wal_senders (currently 0)
pg_basebackup: removing contents of data directory "back"
$ echo $?
1

Cleanup on error

When an error occurs during the pg_basebackup command or when a signal received, the file in the directory specified by the -D parameter will be deleted. If you do not want the delete operation, you can specify the parameter –no-clean (or -n).

–verbose mode output

More detailed information is displayed when parameter –verbose (or -v) is specified.

PDF page 91

$ pg_basebackup  -D back --verbose
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/35000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/35000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
$

3.11.4 pg_dump

The following options have been added.

-B (–no-blobs)

Exclude large objects

–no-subscriptions

Exclude SUBSCRIPTION objects used for Logical Replication

–no-publications

Exclude PUBLICATION objects used for Logical Replication

–no-sync

Does not execute sync system call after writing file. By default, the sync call is executed to ensure a reliable write operation.

3.11.5 pg_dumpall

The following options have been added.

–no-sync

Does not execute sync system call after writing file. By default, the sync call is executed to ensure a reliable write operation.

–no-role-passwords

Does not dump role’s password.

–no-subscriptions

Exclude SUBSCRIPTION objects used for Logical Replication

–no-publications

PDF page 92

Exclude PUBLICATION objects used for Logical Replication

3.11.6 pg_recvlogical

The -E option (–endpos option) to terminate the program after receiving the specified LSN has been added

3.11.7 pgbench

“–log-prefix” parameter to change the prefix string of the log file has been added. The default value is “pgbench_log” as in the previous version. In addition to the above, some new functions were provided to the pgbench command, but no verification has been done.

3.11.8 initdb

“–noclean” and “–nosync” options have been changed to “–no-clean” and “–no-sync” option.

3.11.9 pg_receivexlog

The name of the command was changed to pg_receivewal. The –compress parameter can now be specified to compress the output WAL file. Compression ratio can be specified from 0 to 9. In order to use this function it is necessary to build in the environment where the libz library is installed.

3.11.10 pg_restore

The following options have been added.

-N (–exclude-schema)

To specify the name of the schema not to be restored has been added.

–no-subscriptions

Exclude SUBSCRIPTION objects used for Logical Replication

–no-publications

Exclude PUBLICATION objects used for Logical Replication

3.11.11 pg_upgrade

Internally it treats tables and sequences as separate objects.

PDF page 93

3.11.12 createuser

“–unencrypted” option (-N option) has been deprecated.

3.11.13 createlang / droplang

The createlang command, droplang command has been deprecated.