6 Utilities

PDF page 56

Describes the major enhancements of utility commands.

3.4.1 Configure

Added –with-zstd option to support Zstandard compression method. Zstandard 1.4.0 and above are supported.

$ ./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

The following enhancements have been implemented in the psql command.

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

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

\dconfig [parameter_name]

PDF page 57

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

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

\getenv psql_variable_name Environment_variable_name

PDF page 58

postgres=> \getenv home
postgres=> \echo :home
/home/postgres
postgres=> \getenv val BADENV
postgres=> \echo :val
:val

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

Access privileges are now output to the \dl+ command and \lo_list+ command.

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

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.

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

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

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

The following options have been added / extended to the pg_basebackup command.

–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 Specifying

PDF 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.

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

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.

PDF page 61

$ 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

The following enhancements have been implemented for the pg_dump command.

–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

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

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.

$ 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

The –compress option, which specifies how to compress the logs, has changed. Compression method, level, etc. can be specified.

PDF page 62

Syntax

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.

$ 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

Must be compiled with the --with-lz4 option to perform LZ4 compression.

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

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

The following enhancements have been implemented for the pg_upgrade command

PDF page 63

–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

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

  The following enhancements have been added to the pg_waldump command

Options

The following options have been added Multiple –rmgr options can now be specified.

Signal

When the SIGINT signal is received and the process ends, summary information is output.

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

$ 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
…