6 Utilities

PDF page 82

Describes the major enhancements of utility commands.

3.4.1 Clusterdb

The –all and other options can now be used at the same time. [1b49d56]

$ clusterdb --all --table=data
clusterdb: clustering database "demodb"
clusterdb: clustering database "postgres"
clusterdb: clustering database "template1"

3.4.2 Configure

The following features have been implemented in the configure command.

Output version

The LLVM and OpenSSL versions are now output when executing the configure command. [5e4dacb, 55a428a]

$ ./configure --with-llvm --with-ssl=openssl
…
checking for llvm-config... /usr/bin/llvm-config
configure: using llvm 12.0.1
…
checking for openssl... /usr/bin/openssl
configure: using openssl: OpenSSL 1.1.1k  FIPS 25 Mar 2021
…

Injection Points

The Injection points are features that allow developers to execute custom code, assist in testing for complex conflicts, etc. To use this feature, it is necessary to specify –enable-injection-points when executing the configure command. [d86d20f]

PDF page 83

3.4.3 Initdb

The following enhancements have been implemented to the initdb command. [2d819a0, f69319f]

–locale-provider option

The “builtin” can now be specified as the locale provider.

–builtin-locale option

Added –builtin-locale option to specify the locale for built-in locale providers.

$ initdb --locale-provider=builtin --builtin-locale=C.UTF8 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 this locale configuration:
  default collation provider:  builtin
  default collation: C.UTF-8
  LC_COLLATE:  C.UTF-8
…

3.4.4 Pg_archivecleanup

The following new features have been added to the pg_archivecleanup command. [dd7c60f, 3f8c98d]

Long name options

The long name options have been added to the single-character options.

Short option Long option Description -d –debug Outputs debug logs to standard error.

PDF page 84

Remove history files

The –clean-backup-history option ( shortened option -b) has been added to remove the history files.

3.4.5 Pg_combinebackup

The new command pg_combinebackup has been added. This command merges base and incremental backups. [dc21234, a9577ba]

$ pg_combinebackup back.1 back.inc1 --output=data.2
$ ls data.2
PG_VERSION       current_logfiles  pg_dynshmem    pg_multixact  pg_snapshots
pg_tblspc    postgresql.auto.conf
backup_label     global            pg_hba.conf    pg_notify     pg_stat
pg_twophase  postgresql.conf
backup_manifest  log               pg_ident.conf  pg_replslot   pg_stat_tmp
pg_wal
base             pg_commit_ts      pg_logical     pg_serial     pg_subtrans
pg_xact

PDF page 85

3.4.6 Pg_createsubscriber

The new command pg_createsubscriber has been added. This command converts a streaming replication standby server to a logical replication standby server. The major advantage of using this command is to reduce the initial data copy overhead that occurs when setting up a logical replication environment. [d44032d, c671e14]

Option name Short option Description

$ pg_createsubscriber -D data.stby --publisher-server='host=dbsvr1 port=5432
dbname=postgres'
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".

PDF page 86

The following requirements are necessary to execute the pg_createsubscriber command.

• The primary server parameter wal_level must be set to ’logical'.

• The standby instance must be shut down. When the pg_createsubscriber command is executed, PUBLICATION and logical replication slots are created on the primary server with all tables in the specified database (FOR ALL TABLES). The standby server is no longer a standby for streaming replication and the SUBSCRIPTION is created. The default PUBLICATION and replication slot name created on the primary server and the SUBSCRIPTION name created on the standby server is “pg_createsubscriber_{DB OID}_{random integer}”.

3.4.7 Pg_basebackup

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

–dbname option

If a database name is specified in the –dbname option (-d) at the same time as the –write-recoveryconf option (-R), the database name will be output to the primary_conninfo parameter setting specified in the postgresql.auto.conf file. [a145f42]

$ pg_basebackup -D back --dbname="dbname=demodb" -R
$ cat back/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo
=
'user=postgres
passfile=''/home/postgres/.pgpass''
channel_binding=disable
port=5432
sslmode=disable
sslcompression=0
sslcertmode=disable
sslsni=1
ssl_min_protocol_version=TLSv1.2
gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any
load_balance_hosts=disable dbname=demodb'

–help option

The default value of the –checkpoint option is now output in the help message. [cd02b35]

PDF page 87

$ pg_basebackup --help
pg_basebackup takes a base backup of a running PostgreSQL server.
…
General options:
  -c, --checkpoint=fast|spread
                         set fast or spread (default) checkpointing
…

–incremental option

The –incremental option ( shortened option -i) to perform incremental backups has been added. This option specifies the path to the manifest file on which the backup is based.

3.4.8 Pg_dump

The following options have been added to the pg_dump command.

–filter option

The –filter option has been added to the pg_dump / pg_dumpall / pg_restore commands. This option specifies the file that describes the list of objects to be included (or excluded) from the dump file. The format of the file is as follows. [a5cf808]

Syntax 22 - Filter file format

include | exclude object_type_name object_name_pattern

PDF page 88

$ cat filter.txt
include table data*
$ pg_dump -d postgres --filter=filter.txt -f postgres.dmp

–exclude-extension option

The –exclude-extension option has been added to exclude extensions with specified pattern names. [522ed12]

3.4.9 Pg_restore

The pg_restore command now has a –transaction-size option to perform a commit once the specified number of objects have been processed. The default behavior is to commit on a per-SQL basis. This option provides an intermediate behavior between per-SQL commit and single-transaction processing with the –single-transaction option. [959b38d]

$ pg_restore --help | grep transaction
  -1, --single-transaction     restore as a single transaction
  --transaction-size=N         commit after every N objects

3.4.10 Pg_resetwal

The order in which options are displayed in the –help option has been changed. [b5da1b3]

PDF page 89

$ pg_resetwal --help
pg_resetwal resets the PostgreSQL write-ahead log.
Usage:
  pg_resetwal [OPTION]... DATADIR
Options:
 [-D, --pgdata=]DATADIR  data directory
  -f, --force            force update to be done even after unclean shutdown
or
                         if pg_control values had to be guessed
  -n, --dry-run          no update, just show what would be done
  -V, --version          output version information, then exit
  -?, --help             show this help, then exit
Options to override control file values:
  -c, --commit-timestamp-ids=XID,XID
                                   set oldest and newest transactions bearing
                                   commit timestamp (zero means no change)
  -e, --epoch=XIDEPOCH             set next transaction ID epoch
  -l, --next-wal-file=WALFILE      set minimum starting location for new WAL
  -m, --multixact-ids=MXID,MXID    set next and oldest multitransaction ID
  -o, --next-oid=OID               set next OID
  -O, --multixact-offset=OFFSET    set next multitransaction offset
  -u, --oldest-transaction-id=XID  set oldest transaction ID
  -x, --next-transaction-id=XID    set next transaction ID
      --wal-segsize=SIZE           size of WAL segments, in megabytes
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

3.4.11 Pg_upgrade

The following features have been implemented to the pg_upgrade command.

PDF page 90

Preserve subscriber state

The subscriber (SUBSCRIPTION) state can now be preserved. In previous versions, only metadata was preserved. [9a17be1]

Migrate logical replication slot

The logical replication slots are now recreated on the destination cluster during upgrade. [29d0a77]

–copy-file-range option

The option –copy-file-range has been added to copy files using the copy_file_range system call in Linux and FreeBSD environments. [d93627b]

3.4.12 Pg_walsummary

The command pg_walsummary has been added. This command parses the contents of the WAL summary file. [ee1bfd1]

$ pg_walsummary
pg_wal/summaries/0000000100000000050000300000000005A829E8.summary
TS 1663, DB 1, REL 1259, FORK main: block 0
TS 1663, DB 1, REL 1259, FORK main: block 3
TS 1663, DB 1, REL 1259, FORK main: blocks 7..8
…

3.4.13 Pgindent

The option name of the pgindent command has been changed. [387aecc]

PDF page 91

3.4.14 Psql

The following enhancements have been implemented to the psql command.

View of Aggregate Functions

The argument names have been added to the view of aggregate functions with multiple arguments. [b575a26]

postgres=> \df json_object_agg
List of functions
-[ RECORD 1 ]-------+-----------------------
Schema              | pg_catalog
Name                | json_object_agg
Result data type    | json
Argument data types | key "any", value "any"
Type                | agg

\sf, \ef, \sv, \ev meta-command

The semicolons (;) at the end of lines are now ignored. In previous versions, an error occurred. [390298f]

postgres=> \sf func1() ;
CREATE OR REPLACE FUNCTION public.func1()
 RETURNS integer
 LANGUAGE sql
RETURN 10

PDF page 92

\watch meta-command

The min_rows (abbreviated m), which indicates the minimum number of output tuples, has been added to the watch meta-command. The iterative operation terminates when the specified number of tuples are no longer output. In the example below, the \watch command terminates because all tuples have been deleted from the data1 table. [f347ec7]

postgres=> \watch 10 min_rows=1
Fri 27 Sep 2024 09:28:17 PM JST (every 10s)
 c1  |  c2
-----+-------
 100 | data1
 200 | data2
(2 rows)
Fri 27 Sep 2024 09:28:27 PM JST (every 10s)
 c1 | c2
----+----
(0 rows)
postgres=>

3.4.15 Reindexdb

The following features have been added to the reindexdb command.

Combining options

The –all and other options can now be used at the same time. [24c928a]

$ reindexdb --all --schema=public
reindexdb: reindexing database "demodb"
reindexdb: reindexing database "postgres"
reindexdb: reindexing database "template1"

PDF page 93

Parallelization

The –jobs and –index options can now be used at the same time. Multiple indexes on different tables can be processed in parallel. [47f99a4]

$ reindexdb --jobs=2 --index=idx_data1 --index=idx_data2

3.4.16 Vacuumdb

The –all and other options can now be used at the same time. [648928c]

$ vacuumdb --all --exclude-schema=public
vacuumdb: vacuuming database "demodb"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

3.4.17 More than one command

The following commands now have an additional option “–sync-method” to determine the storage synchronization method for files. This option is disabled if the –no-sync option is specified. The utilities for which the –sync-method option has been added are as follows. [8c16ad3, cccc6cd]

• initdb

• pg_basebackup

• pg_checksums

• pg_dump

• pg_rewind

• pg_upgrade

PDF page 94

$ pg_basebackup -D back.1 --sync-method=fsync
$ pg_dump -d postgres -f dump.dat --sync-method=fsync