PDF page 82
Describes the major enhancements of utility commands. #### 3.4.1 Clusterdb {#pg17-3-4-1-clusterdb} The --all and other options can now be used at the same time. [[1b49d56](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1b49d56d358a9c2e310d66c0ac87216d9b932b78)] **Example 86.** Specifying the \-\-all and \-\-table options ```bash $ clusterdb --all --table=data clusterdb: clustering database "demodb" clusterdb: clustering database "postgres" clusterdb: clustering database "template1" ``` #### 3.4.2 Configure {#pg17-3-4-2-configure} The following features have been implemented in the configure command. ##### Output version {#pg17-p82-output-version} The LLVM and OpenSSL versions are now output when executing the configure command. [[5e4dacb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5e4dacb9878cf71e32950e90993afd9450dab4da), [55a428a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=55a428a18d03f332791e15db9ed6ee51156ecd3b)] **Example 87.** Execution log of configure command ```text $ ./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 {#pg17-p82-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d86d20f0ba79451f19782dbb5fbf206746aaffd1)]PDF page 83
#### 3.4.3 Initdb {#pg17-3-4-3-initdb} The following enhancements have been implemented to the initdb command. [[2d819a0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2d819a08a1cbc11364e36f816b02e33e8dcc030b), [f69319f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f69319f2f1fb16eda4b535bcccec90dff3a6795e)] ##### --locale-provider option {#pg17-p83-locale-provider-option} The "builtin" can now be specified as the locale provider. ##### --builtin-locale option {#pg17-p83-builtin-locale-option} Added --builtin-locale option to specify the locale for built-in locale providers. **Example 88.** Specifying the builtin locale provider ```bash $ 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 {#pg17-3-4-4-pg-archivecleanup} The following new features have been added to the pg_archivecleanup command. [[dd7c60f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dd7c60f1935ad3354bc77de09431313d7b0719ef), [3f8c98d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3f8c98d0b631882e3c513954df42891c1c9585c7)] ##### Long name options {#pg17-p83-long-name-options} The long name options have been added to the single-character options. **Table 35.** Add\/change options | -n | --dry-run | Outputs the name of the file to be deleted to standard | | --- | --- | --- | | -x | --strip-extension=EXT | Specify extensions to exclude from file names. | Short option Long option Description -d --debug Outputs debug logs to standard error.PDF page 84
##### Remove history files {#pg17-p84-remove-history-files} The --clean-backup-history option ( shortened option -b) has been added to remove the history files. #### 3.4.5 Pg_combinebackup {#pg17-3-4-5-pg-combinebackup} The new command pg_combinebackup has been added. This command merges base and incremental backups. [[dc21234](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dc212340058b4e7ecfc5a7a81ec50e7a207bf288), [a9577ba](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9577bae6b5c88c6865597aacd33b93d1b17e497)] **Table 36.** Major options | Option name | Short option | Description | | --- | --- | --- | | --debug | -d | Output debug information | | --dry-run | -n | Run test only | | --no-sync | -N | Don't wait for storage sync | | --output | -o | Output directory | | --tablespace-mapping=OLD=NEW | -T | Tablespace mapping | | --manifest-checksums=METHOD | - | Manifest checksum specification | | --no-manifest | - | Don't use manifest | | --sync-method=METHOD | - | Specifying the storage synchronization method | | --clone | - | Using the clone system call | | --copy | - | Perform regular file copy (default) | | --copy-file-range | - | Using the copy_file_range system call | | --help | -? | Output usage information | | --version | -V | Output version information | **Example 89.** Execute pg\_combinebackup command ```bash $ 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 {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d44032d0146306971cd5ccf232fe37269717d6f2), [c671e14](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c671e142bf4b568434eb8559baff34d32eed5b29)] **Table 37.** Major options | --database= DBNAME | -d | Database name for which to create | | --- | --- | --- | | --pgdata=DATADIR | -D | Database cluster for streaming replication to be converted | | --dry-run | -n | Conversion test | | --subscriber-port=PORT | -p | Port number of the subscriber | | --publisher-server=CONN | -P | Connection string to publisher | | --socketdir=DIR | -s | Socket directory to use | | --recovery-timeout=SECS | -t | Seconds to wait for recovery to end | | --subscriber-username=USER | -U | SUBSCRIPTION owner | | --publication=NAME | - | Name of the PUBLICATION | | --replication-slot=NAME | - | Replication slot name | | --subscription=NAME | - | SUBSCRIPTION name | | --verbose | -v | | | --config-file=FILE | - | Configuration file’s path | | --version | -V | Output version information | | --help | -? | Output usage information | Option name Short option Description **Example 90.** Execute pg\_createsubscriber command ```bash $ 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 {#pg17-3-4-7-pg-basebackup} The following new features have been implemented in the pg_basebackup command. ##### --dbname option {#pg17-p86-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a145f424d5248a09d766e8cb503b999290cb3b31)] **Example 91.** Add dbname to primary\_conninfo parameter ```sql $ 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 {#pg17-p86-help-option} The default value of the --checkpoint option is now output in the help message. [[cd02b35](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cd02b35a4609090e6c4e4fc3d8bd055c559dea1b)]PDF page 87
**Example 92.** \-\-help option outputs ```bash $ 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 {#pg17-p87-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 {#pg17-3-4-8-pg-dump} The following options have been added to the pg_dump command. ##### --filter option {#pg17-p87-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a5cf808be55bcc68c3917c380f95122436af1be1)] #### Syntax 22 - Filter file format {#pg17-syntax-22} ```sql include | exclude object_type_name object_name_pattern ``` **Table 38.** Object type | Object type name | Object | Equivalent command options | | --- | --- | --- | | extension | Extension | --extension | | foreign_data | Foreign Table | --include-foreign-data | | table | Table | --table | | table_and_children | Table and Child Table | --table-and-children | | table_data | Table data only | --exclude-table-data | | table_data_and_children | Table and Child Table data | --exclude-table-data-and-children | | schema | Schema | --schema |PDF page 88
**Example 93.** \-\-filter option ```bash $ cat filter.txt include table data* $ pg_dump -d postgres --filter=filter.txt -f postgres.dmp ``` ##### --exclude-extension option {#pg17-p88-exclude-extension-option} The --exclude-extension option has been added to exclude extensions with specified pattern names. [[522ed12](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=522ed12f7c600243870b13d9ff59f8fd5af10978)] #### 3.4.9 Pg_restore {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=959b38d770ba1f8f35edab27ef3ccf8b1d99f5dd)] **Example 94.** Specifying the \-\-transaction\-size option ```bash $ 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 {#pg17-3-4-10-pg-resetwal} The order in which options are displayed in the --help option has been changed. [[b5da1b3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b5da1b3a939a8a7ece408131e6503be7a422e491)]PDF page 89
**Example 95.** \-\-help option output ```sql $ 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 toPDF page 90
##### Preserve subscriber state {#pg17-p90-preserve-subscriber-state} The subscriber (SUBSCRIPTION) state can now be preserved. In previous versions, only metadata was preserved. [[9a17be1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9a17be1e244a45a77de25ed2ada246fd34e4557d)] ##### Migrate logical replication slot {#pg17-p90-migrate-logical-replication-slot} The logical replication slots are now recreated on the destination cluster during upgrade. [[29d0a77](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=29d0a77fa6606f9c01ba17311fc452dabd3f793d)] ##### --copy-file-range option {#pg17-p90-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d93627bcbe5001750e7611f0e637200e2d81dcff)] #### 3.4.12 Pg_walsummary {#pg17-3-4-12-pg-walsummary} The command pg_walsummary has been added. This command parses the contents of the WAL summary file. [[ee1bfd1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ee1bfd168390bc843c6704d16e909692c0a79f27)] **Table 39.** Available options | Option name | Short option | Description | Note | | --- | --- | --- | --- | | --indivisual | -i | Outputs detailed block information | | | --quiet | -q | Perform parsing of files only | | | --help | -? | Output usage | | | --version | -V | Output version information | | **Example 96.** Execute the pg\_walsummary command ```bash $ 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 {#pg17-3-4-13-pgindent} The option name of the pgindent command has been changed. [[387aecc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=387aecc948ed2fa39e04f84d9eea086d32ebd42e)]PDF page 91
**Table 40.** Renamed options | Option Name | Option Name | Note | | --- | --- | --- | | --show-diff | --diff | | | --silent-diff | --check | | #### 3.4.14 Psql {#pg17-3-4-14-psql} The following enhancements have been implemented to the psql command. ##### View of Aggregate Functions {#pg17-p91-view-of-aggregate-functions} The argument names have been added to the view of aggregate functions with multiple arguments. [[b575a26](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b575a26c66e438225f1c0f95ed8c5efc793d3a14)] **Example 97.** View function information ```text 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 {#pg17-p91-sf-ef-sv-ev-meta-command} The semicolons (;) at the end of lines are now ignored. In previous versions, an error occurred. [[390298f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=390298f0806588ceb131dd47760208bd2cd6298f)] **Example 98.** Ignore the semicolon at the end of the line ```text postgres=> \sf func1() ; CREATE OR REPLACE FUNCTION public.func1() RETURNS integer LANGUAGE sql RETURN 10 ```PDF page 92
##### \watch meta-command {#pg17-p92-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f347ec76e2a227e5c5b5065cce7adad16d58d209)] **Example 99.** Specify the minimum number of tuples ```text 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 {#pg17-3-4-15-reindexdb} The following features have been added to the reindexdb command. ##### Combining options {#pg17-p92-combining-options} The --all and other options can now be used at the same time. [[24c928a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=24c928ad9ad801048684569d2b67463c3ec8fdb0)] **Example 100.** Specifying \-\-all and \-\-schema options ```bash $ reindexdb --all --schema=public reindexdb: reindexing database "demodb" reindexdb: reindexing database "postgres" reindexdb: reindexing database "template1" ```PDF page 93
##### Parallelization {#pg17-p93-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=47f99a407de224df6f9c43697d0a9c0a5598b250)] **Example 101.** Specify \-\-index and \-\-jobs options ```bash $ reindexdb --jobs=2 --index=idx_data1 --index=idx_data2 ``` #### 3.4.16 Vacuumdb {#pg17-3-4-16-vacuumdb} The --all and other options can now be used at the same time. [[648928c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=648928c79bed975a89fa2e4ef8bc0bade8e0e0d8)] **Example 102.** Specifying \-\-all and \-\-exclude\-schema options ```bash $ vacuumdb --all --exclude-schema=public vacuumdb: vacuuming database "demodb" vacuumdb: vacuuming database "postgres" vacuumdb: vacuuming database "template1" ``` #### 3.4.17 More than one command {#pg17-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8c16ad3b43299695f203f9157a2b27c22b9ed634), [cccc6cd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cccc6cdeb32f010f1cf777a9e9a85344a4317ab8)] - initdb - pg_basebackup - pg_checksums - pg_dump - pg_rewind - pg_upgrade **Table 41.** Possible values for option | Value for option | Description | Note | | --- | --- | --- | | fsync | Recursively synchronize file-by-file using the fsync system call. | Default | | syncfs | Synchronize the entire file system using the syncfs system call. | Synchronize the entire file system using the syncfs system call. |PDF page 94
**Example 103.** Specify option \-\-sync\-method ```bash $ pg_basebackup -D back.1 --sync-method=fsync $ pg_dump -d postgres -f dump.dat --sync-method=fsync ```