6 Utilities

PDF page 75

Describes the major enhancements of utility commands.

3.4.1 Configure

The following new features have been added to the configure command.

Adding the –with-liburing option

The option –with-liburing has been added to use io_uring for asynchronous I/O on Linux. Enabling this option allows specifying io_uring for the parameter io_method. [c325a76]

Addition of –with-libcurl option

The option –with-libcurl option has been added for OAUTHBeare/SASL functionality. [b3f0be7]

Addition of –with-libnuma option

The option –with-libnuma has been added to detect NUMA environments. [65c298f]

3.4.2 Initdb

The following options have been added to the initdb command.

Option –no-data-checksums

Generation of page checksums is now enabled by default in the initdb command. With this specification, the option –no-data-checksums, which does not generate page checksums, has been added. If used at the same time as the –data-checksums option, which generates checksums, the option specified later will take effect. [983a588]

PDF page 76

$ initdb --no-data-checksums 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".
initdb: could not find suitable text search configuration for locale "en_US.UTF-
8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory data ... ok
…

Option –no-sync-data-files

Added new option –no-sync-data-files. This option skips synchronization of directories and files in the database cluster. Unless the –no-sync option is specified, directories such as pg_wal and pg_xact will be written synchronously as in the previous version. [cf131fa]

3.4.3 Pg_combinebackup

The option –link (short form -k) has been added. This option composites backup data using hard links instead of file copies. [99aeb84]

3.4.4 Pg_createsubscriber

The following options have been added to the pg_createsubscriber command.

Option –all

The –all option (short form -a) specifies that subscriptions should be created for all databases on the publisher’s side to which it connects. [fb2ea12]

PDF page 77

Option –enable-two-phase

The option –enable-two_phase has been added to enable the two_phase option for the subscriptions being created (short form -T). [e117cfb]

Option –clean

The –clean option specifies the type of object to be removed from the subscriber. Currently, only ‘publications’ are supported. [e5aeed4, 60dda7b]

$ pg_createsubscriber --help
pg_createsubscriber creates a new logical replica from a standby server.
Usage:
  pg_createsubscriber [OPTION]...
Options:
  -a, --all                       create subscriptions for all databases
…
  --clean=OBJECTTYPE          drop all objects of the specified type from
specified databases on the subscriber; accepts: "publications"
…

3.4.5 Pg_dump

The following options have been added to the pg_dump command. [9c49f0e, bde2fb7, 34eb2a8]

PDF page 78

$ pg_dump --sequence-data | grep -i sequence
-- Name: seq01; Type: SEQUENCE; Schema: public; Owner: demo
CREATE SEQUENCE public.seq01
ALTER SEQUENCE public.seq01 OWNER TO demo;
-- Name: seq01; Type: SEQUENCE SET; Schema: public; Owner: demo
$

3.4.6 pg_dumpall

The following options have been added to the pg_dumpall command.

Option –sequence-data

Added –sequence-data option to dump sequence information. Sequence information is dumped by default, but when used with the –no-data option, the latest sequence values are set. [cea3fc4]

Options for optimizer statistics

Options for dumping statistics have been added. The –statistics option and -statistics-only option dump statistics, the –no-statistics option is the default behavior and does not dump statistics. [34eb2a8]

3.4.7 Pg_dump / pg_dumpall / pg_restore command

The pg_dump, pg_restore, and pg_dumpall commands now have an option to migrate statistics. The following options have been added. [1fd1bd8, cd3c451, acea3fc, 34eb2a8, 6a46089, 71ea0d6]

PDF page 79

$ pg_dump -t data1 --statistics-only
--…
SELECT * FROM pg_catalog.pg_restore_relation_stats(
        'relation', 'public.data1'::regclass,
        'version', '180000'::integer,
        'relpages', '6'::integer,
        'reltuples', '1000'::real,
        'relallvisible', '0'::integer
);
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
        'relation', 'public.data1'::regclass,
        'attname', 'col1'::name,
        'inherited', 'f'::boolean,
        'version', '180000'::integer,
        'null_frac', '0'::float4,
        'avg_width', '4'::integer,
        'n_distinct', '-1'::float4,
        'histogram_bounds',
'{1,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210
…

PDF page 80

3.4.8 Pg_recvlogical

The following features have been implemented to the pg_recvlogical command.

Option –enable-failover

The option –enable-failover has been added. Specifying this option adds a ‘failover’ option to the replication slot. This option is specified at the same time as the –create-slot option. [cf2655a]

Option –enable-two-phase

The option name –two-phase has been changed to –enable-two-phase. The previous option remains available. [6d12d5a]

Option –drop-slot

In previous versions, the –dbname option was required when using the –drop-slot option. In PostgreSQL 18, the –dbname option is no longer required. [c68100a]

3.4.9 Pg_resetwal

The option –char-signedness has been added to the pg_resetwal command. This option changes the sign setting of the char type in the control file. This option is expected to be specified within the pg_upgrade command and is not intended to be specified manually. [30666d1]

3.4.10 Pg_rewind

The –write-recovery-conf option of pg_rewind has been extended to include the database name (dbname) in the primary_conninfo value generated when specified with the –source-server option. This change allows rewound servers to connect to the primary server without having to manually modify the configuration file when sync_replication_slots is enabled. [4ecdd41]

3.4.11 Pg_upgrade

The following new features have been added to the pg_upgrade command.

Option –set-char-signedness

The option –char-signedness has been added to the pg_upgrade command. This option changes the signedness setting of the char type in the control file. [1aab680]

PDF page 81

Statistics migration

The statistics are now migrated in the default configuration. Specify the –no-statistics option to not migrate statistics. [1fd1bd8]

Internally executed command processing

Internally executed pg_dump and pg_dumpall commands are executed with the option –no-sync. Internally executed CREATE DATABASE statement executed with STORATEGY=FILE_COPY clause. [64f34eb, 6e16b1e]

3.4.12 Pg_verifybackup

The pg_verifybackup command can now also verify backups in tar format. The format of the backup target is specified with the –format option (short form -F). When using this option, the –no-parse-wal option must also be specified at the same time. [8dfd312]

$ pg_basebackup -D back.1 --format=tar
$ pg_verifybackup --format=tar --no-parse-wal back.1
backup successfully verified

3.4.13 Psql

The following new features have been implemented in the psql command.

Option –help=variables

The xheader_width description has been added to the output destination of the command option -help=variables. [768dfd8]

$ psql --help=variables | grep -e header -e full
  unicode_header_linestyle
  xheader_width
    set the maximum width of the header for expanded output
    [full, column, page, integer value]

PDF page 82

Prepared statement definition

The following metacommands have been added to manipulate the definition of Prepared statements [d55322b]

postgres=> INSERT INTO data1 VALUES ($1, $2) \parse stmt1
postgres=> SELECT name, statement FROM pg_prepared_statements ;
 name  |             statement
-------+------------------------------------
 stmt1 | INSERT INTO data1 VALUES ($1, $2)
(1 row)
postgres=> \bind_named stmt1 100 'data1' \g
INSERT 0 1
postgres=> SELECT * FROM data1 ;
 col1  |  col2
-------+-------
 100   | data1
(1 row)
postgres=> \close stmt1

Metacommand \dP+

The name of the access method (Access method) for the partition table is now printed. [978f38c]

PDF page 83

postgres=> CREATE TABLE part1(col1 INT PRIMARY KEY, col2 VARCHAR(10))
PARTITION BY RANGE(col1) USING heap ;
CREATE TABLE
postgres=> \dP+
                                   List of partitioned relations
 Schema |    Name    | Owner |       Type        | Table | Access method |…
--------+------------+-------+-------------------+-------+---------------+…
 public | part1      | demo  | partitioned table |       | heap          |…
 public | part1_pkey | demo  | partitioned index | part1 | btree         |…
(2 rows)

Metacommands \df+, \do+, \dAo+, \dC+

The output of each command now includes a “Leakproof?” column indicating whether it is leakproof. [2355e51]

postgres=# \dAo+
                         List of operators of operator families
   AM   |      Operator family      | … | Sort opfamily | Leakproof?
--------+---------------------------+ … +---------------+------------
 brin   | bit_minmax_ops            | … |               | yes
 brin   | bit_minmax_ops            | … |               | yes
 brin   | bit_minmax_ops            | … |               | yes
 brin   | bit_minmax_ops            | … |               | yes
…

Pipeline

The following metacommands have been added to support the pipeline. [41625ab, 3ce3575, 17caf66]

PDF page 84

The following variables are available to show the results of executing the above commands.

PDF page 85

postgres=> \startpipeline
postgres=> SELECT * FROM data1 WHERE col1=$1 \bind 10 \sendpipeline
postgres=*> SELECT * FROM data1 WHERE col1=$1 \bind 20 \sendpipeline
postgres=*> \echo :PIPELINE_COMMAND_COUNT
2
postgres=*> \syncpipeline
postgres=*> \echo :PIPELINE_SYNC_COUNT
1
postgres=*> \flushrequest
postgres=*> \echo :PIPELINE_RESULT_COUNT
2
postgres=*> \getresults 2
 col1 |  col2
------+---------
   10 | data1
(1 row)
 col1 |  col2
------+---------
 20 | data2
(1 row)
postgres=> \endpipeline

Output in extended table format mode

By specifying x for some metacommands, the execution results are output in extended table format mode. In addition to the \d* metacommand, it can also be specified with \l, \z, or \lo. [00f4c29]

PDF page 86

postgres=> \df bitand
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | bitand | bit              | bit, bit            | func
(1 row)
postgres=> \dfx bitand
List of functions
-[ RECORD 1 ]-------+-----------
Schema              | pg_catalog
Name                | bitand
Result data type    | bit
Argument data types | bit, bit
Type                | func

Variable SERVICE

The variable SERVICE is now available to indicate the name of the service used for the connection. Specify %s to include the service name in the prompt string. [477728b]

postgres=> \set PROMPT1 '%/[%n][%s]> '
postgres[demo][]> \connect "service=pg18"
You are now connected to database "postgres" as user "demo" on host "localhost"
(address "::1") at port "5432".
postgres[demo][pg18]> \echo :SERVICE
pg18
postgres[demo][pg18]>

Title by object type

The titles of object list metacommands such as \dt and \di now change according to object type. In the previous version, they were all “List of relations”. [a14707d]

PDF page 87

postgres=> \dt
         List of tables
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | data1 | table | demo
(1 row)
postgres=> \di
               List of indexes
 Schema |    Name    | Type  | Owner | Table
--------+------------+-------+-------+-------
 public | data1_pkey | index | demo  | data1
(1 row)
postgres=> \dm
Did not find any materialized views.
postgres=> \ds
Did not find any sequences.

Metacommand \dx

The output of the \dx metacommand now includes the “Default version” entry for the extension. [d696406]

postgres=> \dx
                           List of installed extensions
   Name   | Version | Default version |   Schema   |Description
----------+---------+-----------------+------------+------------------------…
 pgcrypto | 1.4     | 1.4             | public     | cryptographic functions
 plpgsql  | 1.0     | 1.0             | pg_catalog | PL/pgSQL  procedural …
(2 rows)

PDF page 88

Metacommand \conninfo

The output of the conninfo metacommand has been substantially changed. [bba2fbc, be37ac2, 6d64800]

postgres=> \conninfo
            Connection Information
      Parameter       |         Value
----------------------+------------------------
 Database             | postgres
 Client User          | demo
 Host                 | dbsvr1
 Host Address         | 192.168.1.100
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 27761
 SSL Connection       | true
 SSL Library          | OpenSSL
 SSL Protocol         | TLSv1.3
 SSL Key Bits         | 256
 SSL Cipher           | TLS_AES_256_GCM_SHA384
 SSL Compression      | false
 ALPN                 | postgresql
 Superuser            | off
 Hot Standby          | off
(19 rows)

3.4.14 Vacuumdb

The vacuumdb command now has a –missing-stats-only option to analyze only columns and indexes with missing object statistics. This option must be specified in conjunction with the –analyze-only or –analyze-in-stages option. [edba754]

PDF page 89

$ vacuumdb --verbose --missing-stats-only --analyze-only
vacuumdb: vacuuming database "postgres"