This section describes the major feature enhancements to the utility commands.
3.4.1 Oid2name
When the –extended option is specified, the “Path” field, which indicates the file path, is now output. [3c5ec35]
Example 109Executing the oid2name command
$ oid2name --table=data1 --extended
From database "postgres":
Filenode Table Name Oid Schema Tablespace Path
---------------------------------------------------------------
16385 data1 16385 public pg_default base/5/16385
3.4.2 Pg_archivecleanup
The log now includes an entry indicating that dry-run mode has been specified. This feature has also been added to the pg_createsubscriber and pg_combinebackup commands. [c05dee1]
Example 110Executing pg_archivecleanup command with dry-run mode
$ pg_archivecleanup --dry-run arch 00000001000000000000000A.00000028.backup
pg_archivecleanup: Executing in dry-run mode.
No files will be removed.
arch/000000010000000000000001
arch/000000010000000000000002
arch/000000010000000000000003
…
3.4.3 Pg_createsubscriber
The following features have been added to the pg_createsubscriber command.
PDF page 95
Log Output Directory
The –logdir option (short form: -l) has been added to the pg_createsubscriber command to specify the log output directory. [6b5b7ea]
Example 111Specifying the log output directory
$ pg_createsubscriber -d postgres -P "dbname=postgres user=postgres port=5432"-l ~/log --publication pub1 --subscription sub1 -D data.s
$ ls ~/log/
20260604T174538.189 20260604T174611.016
$ ls ~/log/20260604T174538.189/pg_createsubscriber_server.log
/home/postgres/log/20260604T174538.189/pg_createsubscriber_server.log
$ cat ~/log/20260604T174538.189/pg_createsubscriber_server.log
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "log".
Reuse of Replication Slots
If a specified replication slot already exists, it will now be reused. [85ddcc2]
Recovery Parameter Configuration File
The recovery parameters that are temporarily set when the pg_createsubscriber command is executed are now configured in pg_createsubscriber.conf. In previous versions, these were appended to the postgresql.auto.conf file. To enable the pg_createsubscriber.conf file, “include_if_exists ‘pg_createsubscriber.conf’” is automatically added to the postgresql.auto.conf file. After the recovery process is complete, the filename of the pg_createsubscriber.conf file is changed to pg_createsubscriber.conf.disabled. [639352d]
PDF page 96
Example 112Parameter file after executing the pg_createsubscriber command
$ pg_createsubscriber -d postgres -p 5433 -P "host=dbsvr1 port=5432
dbname=postgres user=postgres" -D data.s --publication=pub1
--subscription=sub1
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "log".
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "log".
$ tail -1 data.s/postgresql.auto.conf
include_if_exists 'pg_createsubscriber.conf'$ cat data.s/pg_createsubscriber.conf.disabled
primary_conninfo='user=postgres
passfile=''/home/postgres/.pgpass''
channel_binding=prefer
port=5432
sslmode=prefer
sslnegotiation=postgres
sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2
gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any
load_balance_hosts=disable'recovery_target=''recovery_target_timeline='latest'recovery_target_inclusive=falserecovery_target_action= promote
recovery_target_name=''recovery_target_time=''recovery_target_xid=''recovery_target_lsn='0/05000320'
3.4.4 Pg_dump
The following features have been added to the pg_dump command.
Dump of extended statistics
Extended statistics are now exported using the pg_restore_extended_stats function. [c32fb29]
The pg_dumpall command now supports output formats other than text. The output format is specified using the –format option (short form: -F). The default value remains text (–format=p). The following values can be specified for the –format option. For output formats other than text, a directory name must be specified for the output destination (–file option). When restoring data dumped by the pg_dumpall command, use the pg_restore command unless text format was specified. [763aaa0]
Format value
Description
Note
ptcd
Text format Custom dump format (file extension: .dmp) tar format (file extension: .tar) gzip format by table (file extension: .gz)
Default value
Table 35Format specification
Example 114Dump in gzip format
$ pg_dumpall -format=d --file=dump.d
$ ls -l dump.d/databases/5/
total 2316-rw-r--r--. 1 postgres postgres 2362652 Feb 27 14:46 4395.dat.gz
-rw-r--r--. 1 postgres postgres 25 Feb 27 14:46 4396.dat.gz
-rw-r--r--. 1 postgres postgres 2568 Feb 27 14:46 toc.dat
$
3.4.6 Pg_restore
The following options have been added to the pg_restore command.
PDF page 98
Option –no-globals
The –no-globals option has been added to prevent the restoration of global objects (roles and tablespaces). This requires the pg_dumpall command output file (in a format other than text) as backup data. [3c19983]
The –globals-only option has been added to the pg_dumpall command to restore only global data. [763aaa0]
3.4.7 Pg_upgrade
The following features have been added to the pg_upgrade command.
Migration of In-Place Tablespaces
In-place tablespaces can now be migrated to the new version of the database cluster. In previous versions, in-place tablespaces were not copied. To use this feature, set the allow_in_place_tablespaces parameter to “on” in the destination database before migration. The presence of in-place tablespaces is not verified by the pg_upgrade –check command. [3bcfcd8]
Speed Up Large Object Migration
The pg_upgrade command performs a bulk migration of data by executing the COPY FROM stdin statement when migrating the pg_largeobject_metadata and pg_shdepend catalogs. In previous
PDF page 99
versions, it executed the lo_create function, ALTER LARGE OBJECT statements, and GRANT statements for each large object. [b33f753]
3.4.8 Pg_verifybackup
The pg_verifybackup command can now be executed on backups created in the tar format. In previous versions, the –no-parse-wal option was required to skip parsing WAL files. [b3cf461, f8a0cd2, b15c151]
Example 116The pg_verifybackup command for tar-format backups
It is now possible to run the pg_waldump command on backups created in the tar format. [b15c151]
Example 117The pg_waldump command for tar-format backups
$ pg_basebackup --format=t -D back
$ pg_waldump back/pg_wal.tar --start=0/08000028 --end=0/08000088
rmgr: XLOG len (rec/tot): 34/ 34, tx: 0, lsn: 0/08000…
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/080000…
3.4.10 Psql
The following new features have been implemented in the psql command.
SERVICEFILE variable
The variable SERVICEFILE, which indicates the service file used for the connection, has been added. [6b1c4d3]
PDF page 100
Example 118Displaying the SERVICEFILE variable
$ psql "servicefile=/tmp/serviceA.conf service=service1"
psql (19beta1)
Type "help" for help.
postgres=# \echo :SERVICEFILE
/tmp/serviceA.conf
The %S substitution variable
It is now possible to specify the %S substitution variable in PROMPT1, PROMPT2, and PROMPT3, which define the input prompt for the psql command. This variable is substituted with the value of the search_path parameter. [b3ce55f]
Example 119Changing PROMPT1 variables
postgres=> SHOW search_path ;
-[ RECORD 1 ]----------------
search_path | "$user", public
postgres=> \set PROMPT1 '[%S]=> '
["$user", public]=> SET search_path = 'public' ;
SET
[public]=>
The %i substitution variable
It is now possible to specify the substitution variable %i in PROMPT1, PROMPT2, and PROMPT3, which define the input prompts for the psql command. This variable is substituted based on the in_hot_standby parameter. [dddbbc2]
In_hot_standby setting
%i placeholder
Note
on
primary
off
standby
-
?
Versions without in_hot_standby
Table 36Setting the %i substitution variable
PDF page 101
Example 120Changing PROMPT1 variables
postgres=> SHOW in_hot_standby ;
-[ RECORD 1 ]--+----
in_hot_standby | off
postgres=> \set PROMPT1 '[%i]=> '
[primary]=>
Outputting BOOLEAN Values
It is now possible to specify the output text for BOOLEAN values using the \pset command. Change the variables display_true and display_false. The default values for these variables are “f” (for false) and “t” (for true). [645cb44]
Comments added to publications, extended statistics, and subscriptions are now included in the output of the \dRp+, \dRs+, and \dX+ metacommands. [aecc558]
PDF page 102
Example 122Comment output
postgres=> CREATE PUBLICATION pub1 FOR TABLE data1 ;
CREATE PUBLICATION
postgres=> COMMENT ON PUBLICATION pub1 IS 'comment for pub1' ;
COMMENT
postgres=> \dRp+
Publication pub1
Owner | All tables | All sequences | Inserts |…| Via root | Description
-------+------------+---------------+---------+…+----------+---------------
demo | f | f | t |…| f | comment for pub1
Tables:
"public.data1"
Publication Information
The table definition output now includes information indicating whether a table is included in a publication or not. [2e1d4fd]
PDF page 103
Example 123Comment output
postgres=# CREATE TABLE data1(col1 INT PRIMARY KEY, col2 VARCHAR(10)) ;
CREATE TABLE
postgres=# CREATE PUBLICATION pub1 FOR TABLE data1 ;
CREATE PUBLICATION
postgres=# CREATE PUBLICATION pub2 FOR ALL TABLES EXCEPT (TABLE data1) ;
CREATE PUBLICATION
postgres=# \d data1
Table "public.data1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
col1 | integer | | not null |
col2 | character varying(10) | | |
Indexes:
"data1_pkey" PRIMARY KEY, btree (col1)
Included in publications:
"pub1"
Excluded from publications:
"pub2"
3.4.11 Vacuumdb
The following options have been added to the vacuumdb command.
Option –dry-run
The –dry-run option has been added to display SQL statements without executing them. [d107176]
Change in the behavior of the –analyze-only option
When the –analyze-only option is specified with the vacuumdb command, statistics are now collected for partition tables as well. In previous versions, partition tables were excluded from statistics collection. [6429e5b]