# Utilities > Changes to configure, initdb, backup, restore, upgrade, psql, and maintenance tools. --- LLMS index: [llms.txt](/en/llms.txt) ---
Beta 1Source pages: PDF 94-104Original PDF on GitHub

PDF page 94

This section describes the major feature enhancements to the utility commands. #### 3.4.1 Oid2name {#pg19-3-4-1-oid2name} When the --extended option is specified, the "Path" field, which indicates the file path, is now output. [[3c5ec35](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3c5ec35dea254892d75d829b5642fc3732c8fcf9)] **Example 109.** Executing the oid2name command ```bash $ 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 {#pg19-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c05dee191125f2dcbd0ac40534373cfc7266c0da)] **Example 110.** Executing pg\_archivecleanup command with dry\-run mode ```bash $ 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 {#pg19-3-4-3-pg-createsubscriber} The following features have been added to the pg_createsubscriber command.

PDF page 95

##### Log Output Directory {#pg19-p95-log-output-directory} The --logdir option (short form: -l) has been added to the pg_createsubscriber command to specify the log output directory. [[6b5b7ea](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6b5b7eae3ae65572e2abda20ef2b2c908527c28a)] **Example 111.** Specifying the log output directory ```bash $ 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 {#pg19-p95-reuse-of-replication-slots} If a specified replication slot already exists, it will now be reused. [[85ddcc2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=85ddcc2f4cdef490276d151c80459e287bceb782)] ##### Recovery Parameter Configuration File {#pg19-p95-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=639352d904c8a3d233989253f569cccbea0be123)]

PDF page 96

**Example 112.** Parameter file after executing the pg\_createsubscriber command ```bash $ 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 = false recovery_target_action = promote recovery_target_name = '' recovery_target_time = '' recovery_target_xid = '' recovery_target_lsn = '0/05000320' ``` #### 3.4.4 Pg_dump {#pg19-3-4-4-pg-dump} The following features have been added to the pg_dump command. ##### Dump of extended statistics {#pg19-p96-dump-of-extended-statistics} Extended statistics are now exported using the pg_restore_extended_stats function. [[c32fb29](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c32fb29e979db4a7b92adb29007725eeacf91f64)]

PDF page 97

**Example 113.** Dump of extended statistics ```sql $ pg_dump -d postgres -U demo --statistics-only | grep restore SELECT * FROM pg_catalog.pg_restore_relation_stats( SELECT * FROM pg_catalog.pg_restore_attribute_stats( SELECT * FROM pg_catalog.pg_restore_attribute_stats( SELECT * FROM pg_catalog.pg_restore_attribute_stats( ``` ```sql SELECT * FROM pg_catalog.pg_restore_extended_stats( ``` #### 3.4.5 Pg_dumpall {#pg19-3-4-5-pg-dumpall} 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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=763aaa06f03401584d07db71256fc0ab47235cce)] **Table 35.** Format specification | 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 | **Example 114.** Dump in gzip format ```bash $ 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 {#pg19-3-4-6-pg-restore} The following options have been added to the pg_restore command.

PDF page 98

##### Option --no-globals {#pg19-p98-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3c19983cc0816001ad849a9f0bcaf2ea8321b93d)] **Example 115.** Specifying the \-\-no\-globals option ```sql $ pg_restore -C --no-globals --format=d dump.d -d postgres -v pg_restore: skipping restore of global objects because --no-globals was specified pg_restore: found database "template1" (OID: 1) in file "dump.d/map.dat" … pg_restore: executing SELECT pg_catalog.set_config('search_path', '', false); pg_restore: creating TABLE "public.data1" pg_restore: processing data for table "public.data1" pg_restore: creating CONSTRAINT "public.data1 data1_pkey" pg_restore: creating ACL "SCHEMA public" pg_restore: number of restored databases is 2 ``` ##### Option --globals-only {#pg19-p98-option-globals-only} The --globals-only option has been added to the pg_dumpall command to restore only global data. [[763aaa0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=763aaa06f03401584d07db71256fc0ab47235cce)] #### 3.4.7 Pg_upgrade {#pg19-3-4-7-pg-upgrade} The following features have been added to the pg_upgrade command. ##### Migration of In-Place Tablespaces {#pg19-p98-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3bcfcd815e1a2d51772ba27e0d034467f0344f15)] ##### Speed Up Large Object Migration {#pg19-p98-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b33f753612846b20ed54e296a52a6a52a6c793bc)] #### 3.4.8 Pg_verifybackup {#pg19-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3cf461b3cf91c586ab94b4dddd6dcd8ff8fec47), [f8a0cd2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f8a0cd26717063b99d846b693b416187ab56d67b), [b15c151](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b15c1513984e6eafd264bf6e84a08549905621f1)] **Example 116.** The pg\_verifybackup command for tar\-format backups ```bash $ pg_basebackup --format=t --pgdata=back $ pg_verifybackup --format=t --progress back 24984/24984 kB (100%) verified backup successfully verified ``` #### 3.4.9 Pg_waldump {#pg19-3-4-9-pg-waldump} It is now possible to run the pg_waldump command on backups created in the tar format. [[b15c151](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b15c1513984e6eafd264bf6e84a08549905621f1)] **Example 117.** The pg\_waldump command for tar\-format backups ```bash $ 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 {#pg19-3-4-10-psql} The following new features have been implemented in the psql command. ##### SERVICEFILE variable {#pg19-p99-servicefile-variable} The variable SERVICEFILE, which indicates the service file used for the connection, has been added. [[6b1c4d3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6b1c4d326b064bf0eaedccb08a7fcca5db5d9629)]

PDF page 100

**Example 118.** Displaying the SERVICEFILE variable ```text $ psql "servicefile=/tmp/serviceA.conf service=service1" psql (19beta1) Type "help" for help. postgres=# \echo :SERVICEFILE /tmp/serviceA.conf ``` ##### The %S substitution variable {#pg19-p100-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3ce55f413cdf70b1bc4724052fb4eacf9de239a)] **Example 119.** Changing PROMPT1 variables ```text 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 {#pg19-p100-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dddbbc253b923ef27f724c6abb5a6a39e1254d54)] **Table 36.** Setting the \%i substitution variable | In_hot_standby setting | %i placeholder | Note | | --- | --- | --- | | on | primary | | | off | standby | | | - | ? | Versions without in_hot_standby |

PDF page 101

**Example 120.** Changing PROMPT1 variables ```text postgres=> SHOW in_hot_standby ; -[ RECORD 1 ]--+---- in_hot_standby | off postgres=> \set PROMPT1 '[%i]=> ' [primary]=> ``` ##### Outputting BOOLEAN Values {#pg19-p101-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=645cb44c5490f70da4dca57b8ecca6562fb883a7)] **Example 121.** Outputting BOOLEAN values ```text postgres=> \pset display_true 'True' Boolean true display is "True". postgres=> \pset display_false 'False' Boolean false display is "False". postgres=> SELECT true::boolean, false::boolean ; bool | bool ------+------- True | False (1 row) ``` ##### Comment Output {#pg19-p101-comment-output} Comments added to publications, extended statistics, and subscriptions are now included in the output of the \dRp+, \dRs+, and \dX+ metacommands. [[aecc558](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aecc558666ad62fbecb08ff7af1394656811a581)]

PDF page 102

**Example 122.** Comment output ```text 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 {#pg19-p102-publication-information} The table definition output now includes information indicating whether a table is included in a publication or not. [[2e1d4fd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2e1d4fdb10e6636a7ab64f801d880b4d326ab822)]

PDF page 103

**Example 123.** Comment output ```text 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 {#pg19-3-4-11-vacuumdb} The following options have been added to the vacuumdb command. ##### Option --dry-run {#pg19-p103-option-dry-run} The --dry-run option has been added to display SQL statements without executing them. [[d107176](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d107176d27c73ea3589b949dde07b6bc38b8f583)]

PDF page 104

**Example 124.** Specifying the \-\-dry\-run option ```sql $ vacuumdb -d postgres --dry-run --schema=public vacuumdb: Executing in dry-run mode. No commands will be sent to the server. vacuumdb: vacuuming database "postgres" VACUUM (SKIP_DATABASE_STATS) public.data1; VACUUM (SKIP_DATABASE_STATS) public.data2; VACUUM (SKIP_DATABASE_STATS) public.data3; VACUUM (ONLY_DATABASE_STATS); $ ``` ##### Change in the behavior of the --analyze-only option {#pg19-p104-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6429e5b771dbb98aa56441677b79cdb58f6e1675)]