PDF page 66
This section explains the major enhancements of utility commands. #### 3.8.1 psql command {#pg11-3-8-1-psql-command} The following features have been added to the psql command. ##### \gdesc command {#pg11-p66-gdesc-command} A \gdesc command has been added to display the column name and data type of the most recently executed query. **Example 80.** \\gdesc command ```text 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 {#pg11-p66-pager-specification} As the environment variable to specify the pager used by the psql command, PSQL_PAGER is the priority than PAGER. ##### Query status {#pg11-p66-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
**Table 21.** The Variable indicating execution result of SQL statement | Variable name | Description | | --- | --- | | ERROR | True if the most recently executed SQL failed | | LAST_ERROR_MESSAGE | The error message of the last error | | LAST_ERROR_SQLSTATE | The error code of the last error | | ROW_COUNT | Number of tuples processed by the most recently executed SQL statement | | SQLSTATE | statement | **Example 81.** SQL execution result variable ```text 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 {#pg11-p67-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 {#pg11-p67-parameter-definition-check} The syntax ":{?variable_name}" to check whether variables are defined is now available.PDF page 68
**Example 82.** String output declaration ```text postgres=> \set TESTVAL 1 postgres=> \echo :{?TESTVAL} TRUE ``` #### 3.8.2 ECPG command {#pg11-3-8-2-ecpg-command} The following enhancements were implemented in ECPG. ##### Oracle mode {#pg11-p68-oracle-mode} ```text 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. **Example 83.** String output declaration ```text EXEC SQL BEGIN DECLARE SECTION ; char shortstr[5] ; short shstr_ind = 0 ; EXEC SQL END DECLARE SECTION ; ``` ```text Output data of VARCHAR(10) type column to variable "char shortstr[5]". ``` **Example 84.** Fetch the column data ```text EXEC SQL FETCH C into :shortstr :shstr_ind ; ``` ```text Depending on the length of the original data, check the contents output to the variable "shortstr". ```PDF page 69
**Table 22.** Default setting | Tabledata | shortstrvalue | shortstr indvalue _ | Note | | --- | --- | --- | --- | | '' | '' | 0 | shortstr[0] = NULL | | 'AB' | 'AB' | 0 | shortstr[4] = not updated | | 'ABCD' | 'ABCD' | 0 | shortstr[4] = NULL | | 'ABCDE' | 'ABCDE' | 0 | shortstr[4] = 'E' | | 'ABCDEF' | 'ABCDE' | 6 | shortstr[4] = 'E' | | 'ABCDEFGHIJ' 'ABCDE' | 'ABCDEFGHIJ' 'ABCDE' | 10 | shortstr[4] = 'E' | **Table 23.** \-C ORACLE setting | Tabledata | shortstrvalue | shortstr indvalue _ | Note | | --- | --- | --- | --- | | '' | ' ' | -1 | shortstr variable is filled with spaces | | 'AB' | 'AB ' | 0 | shortstr variable is filled with spaces | | 'ABCD' | 'ABCD' | 0 | shortstr[4] = NULL | | 'ABCDE' | 'ABCD' | 5 | shortstr[4] = NULL | | 'ABCDEF' | 'ABCD' | 6 | shortstr[4] = NULL | | 'ABCDEFGHIJ' 'ABCD' | 'ABCDEFGHIJ' 'ABCD' | 10 | shortstr[4] = NULL | 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 {#pg11-p69-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. **Example 85.** Specify DO CONTINUE clause ```text main() { … EXEC SQL WHENEVER SQLERROR DO CONTINUE ; while (1) { EXEC SQL FETCH c INTO :val ; } … } ```PDF page 70
#### 3.8.3 initdb command {#pg11-3-8-3-initdb-command} The following enhancements were implemented to the initdb command. ##### --wal-segsize option {#pg11-p70-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. **Example 86.** initdb \-\-wal\-segsize option ```bash $ 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 {#pg11-p70-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. **Example 87.** initdb \-\-allow\-group\-access option ```bash $ 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 {#pg11-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 {#pg11-p71-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 {#pg11-p71-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. **Example 88.** pg\_dumpall command with specifying the character encoding ```bash $ pg_dumpall -E utf8 > dump.sql ``` ##### --no-comments option {#pg11-p71-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 {#pg11-3-8-5-pg-receivewal-command} The following options have been added to the pg_receivewal command. ##### --endpos option {#pg11-p71-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 {#pg11-p71-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 {#pg11-3-8-6-pg-ctl-command} The feature to send SIGKILL signal from pg_ctl command was implemented. **Example 89.** pg\_ctl command ```bash $ 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 {#pg11-3-8-7-pg-basebackup-command} The following enhancements were implemented for the pg_basebackup command. ##### --no-verify-checksum option {#pg11-p72-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 {#pg11-p72-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
**Example 90.** pg\_basebackup command ```bash $ 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 {#pg11-p73-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". **Example 91.** Batch mode operation ```bash $ 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 {#pg11-p73-exclude-pg-internal-init-file} The pg_internal.init file is excluded from the backup target of the pg_basebackup command. ##### Exclude UNLOGGED table {#pg11-p73-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 {#pg11-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. **Table 24.** Added options for pg\_resetwal command | Short option | Added option | | --- | --- | | -c | --commit-timestamp-ids | | -D | --pgdata | | -e | --epoch | | -f | --force | | -l | --next-wal-file | | -m | --multixact-ids | | -n | --dry-run | | -o | --next-oid | | -O | --multixact-offset | | -x | --next-transaction-id | | None | --wal-segsize | **Table 25.** Option for pg\_controldata command | Short option | Added option | | --- | --- | | -D | --pgdata | #### 3.8.9 configure command {#pg11-3-8-9-configure-command} The following options have been changed in the configure command. ##### --with-wal-segsize option {#pg11-p74-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 {#pg11-p74-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 {#pg11-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. **Example 92.** Usage of pg\_verify\_checksums command ```bash $ 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