# Change of Utility > Source chapter 3.11 for PostgreSQL 10. --- LLMS index: [llms.txt](/en/llms.txt) ---
Beta 1Source pages: PDF 86-93Original PDF on GitHub

PDF page 86

This section explain the major function enhancement points of utility commands. #### 3.11.1 psql {#pg10-3-11-1-psql} The following functions have been added to the psql command. ##### \d command {#pg10-p86-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. **Example 94.** Display table information \(PostgreSQL 9\.6\) ```text postgres=> \d data1 Table "public.data1" Column | Type | Modifiers --------+-----------------------+----------- c1 | numeric | default 1 c2 | character varying(10) | not null ``` **Example 95.** Display table information \(PostgreSQL 10\) ```text 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 {#pg10-p86-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

**Example 96.** Added output of \\timing command ```text 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 {#pg10-p87-gx-command} The \gx command reruns the most recently executed SQL statement in the extended format. **Example 97.** \\gx command ```text postgres=> SELECT * FROM data1 ; c1 | c2 ----+------ 1 | data (1 row) postgres=> \gx -[ RECORD 1 ] c1 | 1 c2 | data ``` ##### \set command {#pg10-p87-set-command} More parameters have been displayed by the \set command. **Example 98.** \\set command ```text postgres=> \set AUTOCOMMIT = 'on' COMP_KEYWORD_CASE = 'preserve-upper' DBNAME = 'demodb' ECHO = 'none' … ```

PDF page 88

##### \if, \elif, \else, \endif command {#pg10-p88-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. **Example 99.** \\if command ```sql 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 {#pg10-3-11-2-pg-ctl} The following functions have been added to the pg_ctl command. ##### Wait for promotion {#pg10-p88-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 {#pg10-p88-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 {#pg10-p88-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 {#pg10-3-11-3-pg-basebackup} The following changes have been added to the pg_basebackup command.

PDF page 89

##### Change default mode {#pg10-p89-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 {#pg10-p89-discontinue-the-x-option} The -x option (--xlog option) has been deprecated. ##### Change the -X option {#pg10-p89-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 {#pg10-p89-change-the-xlogdir-option} The option name has been changed from "--xlogdir" to "--waldir". ##### -Ft option and -Xstream option combination {#pg10-p89-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. **Example 100.** \-Ft option and \-Xstream option ```bash $ 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 {#pg10-p89-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. **Example 101.** Temporary replication slot creation log\. ```text 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 ``` ```text 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. ``` **Example 102.** Error when there is no margin in the number of replication slots ```bash $ 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 {#pg10-p90-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 {#pg10-p90-verbose-mode-output} More detailed information is displayed when parameter --verbose (or -v) is specified.

PDF page 91

**Example 103.** Output in \-\-verbose mode ```bash $ 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 {#pg10-3-11-4-pg-dump} The following options have been added. ##### -B (--no-blobs) {#pg10-p91-b-no-blobs} Exclude large objects ##### --no-subscriptions {#pg10-p91-no-subscriptions} Exclude SUBSCRIPTION objects used for Logical Replication ##### --no-publications {#pg10-p91-no-publications} Exclude PUBLICATION objects used for Logical Replication ##### --no-sync {#pg10-p91-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 {#pg10-3-11-5-pg-dumpall} The following options have been added. ##### --no-sync {#pg10-p91-no-sync-2} 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 {#pg10-p91-no-role-passwords} Does not dump role's password. ##### --no-subscriptions {#pg10-p91-no-subscriptions-2} Exclude SUBSCRIPTION objects used for Logical Replication ##### --no-publications {#pg10-p91-no-publications-2}

PDF page 92

Exclude PUBLICATION objects used for Logical Replication #### 3.11.6 pg_recvlogical {#pg10-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 {#pg10-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 {#pg10-3-11-8-initdb} "--noclean" and "--nosync" options have been changed to "--no-clean" and "--no-sync" option. #### 3.11.9 pg_receivexlog {#pg10-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 {#pg10-3-11-10-pg-restore} The following options have been added. ##### -N (--exclude-schema) {#pg10-p92-n-exclude-schema} To specify the name of the schema not to be restored has been added. ##### --no-subscriptions {#pg10-p92-no-subscriptions} Exclude SUBSCRIPTION objects used for Logical Replication ##### --no-publications {#pg10-p92-no-publications} Exclude PUBLICATION objects used for Logical Replication #### 3.11.11 pg_upgrade {#pg10-3-11-11-pg-upgrade} Internally it treats tables and sequences as separate objects.

PDF page 93

#### 3.11.12 createuser {#pg10-3-11-12-createuser} "--unencrypted" option (-N option) has been deprecated. #### 3.11.13 createlang / droplang {#pg10-3-11-13-createlang-droplang} The createlang command, droplang command has been deprecated.