PDF page 56
Describes the major enhancements of utility commands. #### 3.4.1 Configure {#pg15-3-4-1-configure} Added --with-zstd option to support Zstandard compression method. Zstandard 1.4.0 and above are supported. **Example 67.** For Zstandard compression ```text $ ./configure --help | grep -i zstd --with-zstd build with ZSTD support ZSTD_CFLAGS C compiler flags for ZSTD, overriding pkg-config ZSTD_LIBS linker flags for ZSTD, overriding pkg-config ``` #### 3.4.2 Psql {#pg15-3-4-2-psql} The following enhancements have been implemented in the psql command. ##### Environment variable PSQL_WATCH_PAGER {#pg15-p56-environment-variable-psql-watch-pager} The environment variable PSQL_WATCH_PAGER, which specifies the pager to be displayed when the \watch command is executed, is now available. ##### \Dconfig command {#pg15-p56-dconfig-command} The \dconfig command can display the parameter settings of the instance. If no option is specified, a list of parameters that have changed from the default values is displayed. If a parameter name is specified, the specific parameter setting value can be checked. Wildcards can be used for parameter names. Syntax ```text \dconfig [parameter_name] ```PDF page 57
**Example 68.** Execute the \\dconfig command ```text postgres=> \dconfig work_mem List of configuration parameters Parameter | Value -----------+------- work_mem | 4MB (1 row) postgres=> \dconfig log_*connect* List of configuration parameters Parameter | Value --------------------+------- log_connections | off log_disconnections | off (2 rows) postgres=> \dconfig+ port List of configuration parameters Parameter | Value | Type | Context | Access privileges -----------+-------+---------+------------+------------------- port | 5432 | integer | postmaster | (1 row) ``` ##### \Getenv command {#pg15-p57-getenv-command} Added the \getenv command to get environment variables. Specify the psql variable name and environment variable name to store the variable value. Specifying an environment variable name that does not exist does not cause an error. Syntax ```text \getenv psql_variable_name Environment_variable_name ```PDF page 58
**Example 69.** Execute the \\getenv command ```text postgres=> \getenv home postgres=> \echo :home /home/postgres postgres=> \getenv val BADENV postgres=> \echo :val :val ``` ##### Comments in SQL statements {#pg15-p58-comments-in-sql-statements} Comments with two hyphen (-) in SQL statements are now sent to the backend. Previously it was removed before sending to the backend. ##### Large Object output {#pg15-p58-large-object-output} Access privileges are now output to the \dl+ command and \lo_list+ command. **Example 70.** Output the large object access privilege ```text postgres=> \lo_import 'test.dat' lo_import 16409 postgres=> GRANT ALL ON LARGE OBJECT 16409 TO PUBLIC ; GRANT postgres=> \dl+ Large objects ID | Owner | Access privileges | Description -------+-------+-------------------+------------- 16409 | demo | demo=rw/demo +| | | =rw/demo | (1 row) postgres=> \lo_list+ Large objects ID | Owner | Access privileges | Description -------+-------+-------------------+------------- 16409 | demo | demo=rw/demo +| | | =rw/demo | (1 row) ```PDF page 59
##### Specifying a database name {#pg15-p59-specifying-a-database-name} When specifying a table name with the \d command, etc., use "database_name.schema_name.table_name" format is allowed. However, the "database_name" part is valid only for the currently connected database. If other database names are specified, an error message will be displayed. **Example 71.** Database name qualification ```text postgres=> CREATE TABLE public.data1(c1 INT, c2 VARCHAR(10)) ; CREATE TABLE postgres=> \d postgres.public.data1 Table "public.data1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | | c2 | character varying(10) | | | postgres=> \d demodb.public.data1 cross-database references are not implemented: demodb.public.data1 postgres=> ``` ##### Output of multiple SQL {#pg15-p59-output-of-multiple-sql} PostgreSQL 15 displays all results if the server returns multiple result sets. This behavior can be changed with the "\set SHOW_ALL_RESULTS off" command. #### 3.4.3 Pg_amcheck {#pg15-3-4-3-pg-amcheck} The --install-missing option has been added. If this option is specified, the amcheck extension will be installed automatically if it is not installed. #### 3.4.4 Pg_basebackup {#pg15-3-4-4-pg-basebackup} The following options have been added / extended to the pg_basebackup command. ##### --Target option {#pg15-p59-target-option} The --target option to specify the output destination has been added. This option uses a new data transfer protocol (BASE_BACKUP TARGET). The following formats can be specified SpecifyingPDF page 60
server as the output destination allows base backups to be output to a server running a PostgreSQL instance. Server-side backups can be performed by a user with the SUPERUSER or pg_write_server_files roles. **Table 21.** \-\-Target option settings | blackhole | Nothing is output. Test options | | --- | --- | | server:/path | Output to the specified directory on the server. Currently, the output format is tar (output to /path in the example on the left). | | shell[:DETAIL] | Execute a shell command. | Target Description The --wal-method option must be set to "fetch" or "none" to make this specification. When "shell" is specified in the --target option, it is necessary to set the Contrib module basebackup_to_shell. ##### --Compress option {#pg15-p60-compress-option} The --compress option can now specify the compression method and compression level, whereas the previous version specified the compression level. The compression level is specified by specifying "level=number" after a colon (:); for Zstandard compression, the "workers=number" option for parallel processing can also be set. The same notation as in previous versions is also available for compatibility. If server-side compression (server-gzip) is specified for plain format (-Fp), only the transferred data will be compressed and decompressed and stored on the client side. This setting is useful when network bandwidth is small. **Table 22.** \-\-Compress option settings | Setting | Description | Note | | --- | --- | --- | | none | Do not compress | | | gzip | Gzip compression on the client if not targeted | | | lz4 | LZ4 compression on the client if not targeted | | | zstd | Zstandard compression on the client if not targeted | | | client-gzip | Gzip compression on the client side | | | server-gzip | Gzip compression on the server side | | | client-lz4 | LZ4 compression on the client side | | | server-lz4 | LZ4 compression on the server side | | | client-zstd | Zstandard compression on the client side | | | server-zstd | Zstandard compression on the server side | |PDF page 61
**Example 72.** Specify the compression method ```bash $ pg_basebackup --compress=none --format=tar -D back.1 $ pg_basebackup --compress=zstd:level=9 --format=tar -D back.2 ``` #### 3.4.5 Pg_dump {#pg15-3-4-5-pg-dump} The following enhancements have been implemented for the pg_dump command. ##### --No-table-access-method option {#pg15-p61-no-table-access-method-option} The --no-table-access-method option can now be specified to remove table access methods from the output DDL. This option is ignored if the dump file is output in non-text format. The same option can also be specified for the pg_dumpall and pg_restore commands. ##### Consideration of TOAST data volume {#pg15-p61-consideration-of-toast-data-volume} The pg_dump command takes into account the table size when running in parallel mode; PostgreSQL 15 now also takes into account the amount of TOAST data. #### 3.4.6 Pg_recvlogical {#pg15-3-4-6-pg-recvlogical} The --two-phase (or -t) option has been added to the pg_recvlogical command to support the addition of the TWO_PHASE option to the CREATE_REPILICATION_SLOT command for the replication protocol. **Example 73.** Specify \-\-two\-phase option ```text $ pg_recvlogical --create-slot --slot=slot1 --two-phase --dbname=postgres $ psql postgres=# SELECT two_phase FROM pg_replication_slots WHERE slot_name='slot1' ; two_phase ----------- t ``` #### 3.4.7 Pg_receivewal {#pg15-3-4-7-pg-receivewal} The --compress option, which specifies how to compress the logs, has changed. Compression method, level, etc. can be specified.PDF page 62
Syntax ```text pg_receivewal --compress=METHOD[:DETAIL] METHOD = {'gzip', 'none', 'lz4'} DETAIL = level=[1-9] ``` Valid values for compression are gzip, lz4 or none. An optional compression level (level) can be specified. The range of compression levels is 1-9. **Example 74.** Specify the \-\-compress option ```bash $ pg_receivewal -D wal --compress=lz4:level=9 ^Cpg_receivewal: not renaming "000000010000000000000028.lz4.partial", segment is not complete $ ls wal 000000010000000000000022.lz4 000000010000000000000026.lz4 000000010000000000000023.lz4 000000010000000000000027.lz4 000000010000000000000024.lz4 000000010000000000000028.lz4.partial 000000010000000000000025.lz4 ``` ```text Must be compiled with the --with-lz4 option to perform LZ4 compression. ``` #### 3.4.8 Pg_resetwal {#pg15-3-4-8-pg-resetwal} The --oldest-transaction-id option (short form -u) has been added to specify the oldest transaction ID. #### 3.4.9 Pg_rewind {#pg15-3-4-9-pg-rewind} Added the --config-file option to specify the path to the server configuration file (postgresql.conf). This option is useful when the configuration file is outside the data directory. #### 3.4.10 Pg_upgrade {#pg15-3-4-10-pg-upgrade} The following enhancements have been implemented for the pg_upgrade commandPDF page 63
##### --No-sync option {#pg15-p63-no-sync-option} The --no-sync option (short form -N) has been added. When this option is specified, it does not wait for the completion of file writing. This is not to be used in a production environment. ##### Temporary Directories {#pg15-p63-temporary-directories} Temporary files created during the upgrade will be created in the pg_upgrade_output.d directory of the new database cluster. #### 3.4.11 Pg_waldump {#pg15-3-4-11-pg-waldump} ```text The following enhancements have been added to the pg_waldump command ``` ##### Options {#pg15-p63-options} The following options have been added Multiple --rmgr options can now be specified. **Table 23.** Added options | Option name | Short option | Description | | --- | --- | --- | | --block=N | -B | Show the block of relations specified by the --relation option | | --fork=N | -F | Show only forks with matching numbers | | --relation=tblspc/db/rel | -R | Show only information about the relevant relationship | | --fullpage | -w | Show only Full page write information | ##### Signal {#pg15-p63-signal} When the SIGINT signal is received and the process ends, summary information is output. ##### Addition of output information {#pg15-p63-addition-of-output-information} The remote_apply information of transaction commit and the replication origin information of PREPARE TRANSACTION are added to the command output.PDF page 64
**Example 75.** Command output ```bash $ pg_waldump data/pg_wal/000000010000000000000008 | grep origin rmgr: Transaction len (rec/tot): 117/ 117, tx: 775, lsn: 0/080025F0, prev 0/080025C0, desc: COMMIT 2022-10-14 10:01:46.390500 JST; inval msgs: catcache 57 catcache 56 catcache 66; origin: node 2, lsn 0/0, at 2022-10- 14 10:01:46.382305 JST rmgr: Transaction len (rec/tot): 85/ 85, tx: 776, lsn: 0/08002770, prev 0/08002740, desc: COMMIT 2022-10-14 10:01:46.391231 JST; inval msgs: catcache 66; origin: node 2, lsn 0/0, at 2022-10-14 10:01:46.382305 JST … ```