10 Utilities

PDF page 66

This section explains the major enhancements of utility commands.

3.8.1 psql command

The following features have been added to the psql command.

\gdesc command

A \gdesc command has been added to display the column name and data type of the most recently executed query.

postgres=> SELECT COUNT(*) AS num_settings FROM pg_settings ;
 num_settings
--------------
          272
(1 row)
postgres=> \gdesc
    Column    |  Type
--------------+--------
 num_settings | bigint
(1 row)

Pager specification

As the environment variable to specify the pager used by the psql command, PSQL_PAGER is the priority than PAGER.

Query status

Variables concerning the execution status of SQL statements are now available. Combined with the \if command, SQL statement execution results can now be handled in SQL scripts. The LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE variables maintain their contents even if another SQL statement succeeds after an error.

PDF page 67

postgres=> SELECT * FROM not_exists ;
ERROR:  relation "not_exists" does not exist
LINE 1: SELECT * FROM not_exists ;
                      ^
postgres=> \echo :ERROR
true
postgres=> \echo :SQLSTATE
42P01
postgres=> UPDATE data1 SET c2='update' ;
UPDATE 10
postgres=> \echo :ROW_COUNT
10
postgres=> \echo :LAST_ERROR_MESSAGE
relation "not_exists" does not exist
postgres=> \echo :LAST_ERROR_SQLSTATE
42P01

exit and quit command

The “exit” and “quit” command can now be used to terminate the psql command. Previously, the only \q was available.

Parameter definition check

The syntax “:{?variable_name}” to check whether variables are defined is now available.

PDF page 68

postgres=> \set TESTVAL 1
postgres=> \echo :{?TESTVAL}
TRUE

3.8.2 ECPG command

The following enhancements were implemented in ECPG.

Oracle mode

 The option "-C ORACLE" to change the behavior when retrieving string type data has been added.

Here, explains the comparison between using the default value and specifying -C ORACLE. First, declare an area for character string output and an indicator as shown below.

EXEC SQL BEGIN DECLARE SECTION ;
  char shortstr[5] ;
  short shstr_ind = 0 ;
EXEC SQL END DECLARE SECTION ;

 Output data of VARCHAR(10) type column to variable "char shortstr[5]".

EXEC SQL FETCH C into :shortstr :shstr_ind ;

 Depending on the length of the original data, check the contents output to the variable "shortstr".

PDF page 69

As in the above example, if “-C ORACLE” is specified, space is added if the variable area is surplus, and NULL is appended to the last of the variable.

DO CONTINUE statement

The DO CONTINUE clause can now be specified in the WHENEVER statement. This causes the flow of control to return to the beginning of loop when the result of statement matches the condition specified in WHENEVER.

main() {
   …
   EXEC SQL WHENEVER SQLERROR DO CONTINUE ;
   while (1) {
        EXEC SQL FETCH c INTO :val ;
   }
   …
}

PDF page 70

3.8.3 initdb command

The following enhancements were implemented to the initdb command.

–wal-segsize option

A “–wal-segsize” option to specify the size of the WAL file has been added. This option can be specified to a multiple of 2, and from 1 to 1024 in megabytes. The default value is 16 MB as before. Along with this, the “–with-wal-segsize” option of the “configure” command executed at the time of installation is obsolete.

$ initdb --wal-segsize=128 data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
…

–allow-group-access option

Added –allow-group-access option (or -g option) to allow group access to database cluster access mode. If this option is specified, the group’s read / execute permission is added to the directory protection mode of the database cluster, and the database cluster parameter data_directory_mode is changed to 0750.

$ initdb --allow-group-access data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
…
    pg_ctl -D data -l logfile start
$ ls -ld data
drwxr-x---. 19 postgres postgres 4096 Oct 22 13:32 data

PDF page 71

3.8.4 pg_dump / pg_dumpall command

The following options have been added to the pg_dump and pg_dumpall commands.

–load-via-partition-root option

The –load-via-partition-root option has been added to the pg_dump and pg_dumpall commands. When this option is specified, data is loaded via the root table of the partition table, not the individual partitions when loading the data.

–encoding option

The –encoding option (or -E parameter) has been added to the pg_dumpall command. As with the pg_dump command, this option specifies the character encoding of the output data.

$ pg_dumpall -E utf8 > dump.sql

–no-comments option

The –no-comments option has been added to the pg_dump command. If this option is specified, COMMENT will not be loaded.

3.8.5 pg_receivewal command

The following options have been added to the pg_receivewal command.

–endpos option

Added –endpos option (or -E option) to specify the LSN where the command will stop has been added. This is the same feature as the –endpos option added to the pg_recvlogical command in PostgreSQL 10.

–no-sync option

The –no-sync option was added. When this option is specified, the sync system call is not executed when writing data.

PDF page 72

3.8.6 pg_ctl command

The feature to send SIGKILL signal from pg_ctl command was implemented.

$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL
server.
Usage:
  pg_ctl init[db] [-D DATADIR] [-s] [-o OPTIONS]
  …
  pg_ctl promote  [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl kill     SIGNALNAME PID
Common options:
 …
Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2

3.8.7 pg_basebackup command

The following enhancements were implemented for the pg_basebackup command.

–no-verify-checksum option

The pg_basebackup command now checks the checksum of the backed-up block. The –no-verify-checksum option skips the checksum verification process. If an error occurs in the checksum, the pg_basebackup command exits with a nonzero value.

–create-slot option

The pg_basebackup command now has a –create-slot option (or -C option) to create a replication slot. This option is used with the –slot option. The created replication slots are maintained even after the pg_basebackup command completes. If a replication slot with the same name already exists, the pg_basebackup command displays an error message and exits.

PDF page 73

$ pg_basebackup --create-slot --slot=test1 -v -D back
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "test1"
pg_basebackup: write-ahead log end point: 0/2000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
$

Operation change in batch mode

When specifying the –progress option to the pg_basebackup command and executing it from batch mode (redirect from a shell script to a file), a line feed code “\n” is output instead of the “\r”.

$ pg_basebackup --progres -D back &> back.out
$ cat back.out
waiting for checkpoint
    0/74635 kB (0%), 0/1 tablespace
74644/74644 kB (100%), 0/1 tablespace
74644/74644 kB (100%), 0/1 tablespace
74644/74644 kB (100%), 1/1 tablespace

Exclude pg_internal.init file

The pg_internal.init file is excluded from the backup target of the pg_basebackup command.

Exclude UNLOGGED table

The UNLOGGED and TEMPORARY tables are excluded from the transfer data.

PDF page 74

3.8.8 pg_resetwal / pg_controldata command

The –wal-segsize option to specify the size of the WAL file has been added to the pg_resetwal command. Also, the pg_resetwal and pg_controldata commands have long name options corresponding to existing short options.

3.8.9 configure command

The following options have been changed in the configure command.

–with-wal-segsize option

The –with-wal-segsize option has been obsoleted. The size of the WAL file can be specified by -wal-segsize option of initdb command.

–with-llvm option

The –with-llvm option to provide JIT compilation feature with LLVM has been added. When specifying this option, llvm-config command and clang command must be included in the command search path (or set the environment variable LLVM_CONFIG and the environment variable CLANG).

PDF page 75

3.8.10 pg_verify_checksums command

Pg_verify_checksums has been added as a command for checksum consistency check from outside the database. This command cannot be executed when the instance is running. In the example below, checksums are confirmed for only certain files, indicating that some blocks do not match checksums.

$ pg_verify_checksums --help
pg_verify_checksums verifies page level checksums in offline PostgreSQL
database cluster.
Usage:
  pg_verify_checksums [OPTION] [DATADIR]
Options:
 [-D, --pgdata=]DATADIR  data directory
  -v, --verbose          output verbose messages
  -r RELFILENODE         check only relation with specified relfilenode
  -V, --version          output version information, then exit
  -?, --help             show this help, then exit
If no data directory (DATADIR) is specified, the environment variable
PGDATA is used.
Report bugs to <pgsql-bugs@postgresql.org>.

$ pg_verify_checksums -D data -r 16410
pg_verify_checksums:
checksum
verification
failed
in
file
"data/base/16385/16410", block 0: calculated checksum 42D6 but expected 84E0
Checksum scan completed
Data checksum version: 1
Files scanned:  1
Blocks scanned: 1
Bad checksums:  1
$