PDF page 75
Describes the major enhancements of utility commands. #### 3.4.1 Configure {#pg18-3-4-1-configure} The following new features have been added to the configure command. ##### Adding the --with-liburing option {#pg18-p75-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c325a7633fcb33dbd73f46ddbbe91e95ddf3b227)] ##### Addition of --with-libcurl option {#pg18-p75-addition-of-with-libcurl-option} The option --with-libcurl option has been added for OAUTHBeare/SASL functionality. [[b3f0be7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3f0be788afc17d2206e1ae1c731d8aeda1f2f59)] ##### Addition of --with-libnuma option {#pg18-p75-addition-of-with-libnuma-option} The option --with-libnuma has been added to detect NUMA environments. [[65c298f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=65c298f61fc70f2f960437c05649f71b862e2c48)] #### 3.4.2 Initdb {#pg18-3-4-2-initdb} The following options have been added to the initdb command. ##### Option --no-data-checksums {#pg18-p75-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=983a588e0b864d5c016d5902217ba4b11fc82b4f)]PDF page 76
**Example 101.** Specifying the \-\-no\-data\-checksums option ```bash $ 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 {#pg18-p76-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cf131fa942855acd42e8283185837566c7e8c15e)] #### 3.4.3 Pg_combinebackup {#pg18-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=99aeb84703177308c1541e2d11c09fdc59acb724)] #### 3.4.4 Pg_createsubscriber {#pg18-3-4-4-pg-createsubscriber} The following options have been added to the pg_createsubscriber command. ##### Option --all {#pg18-p76-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fb2ea12f42b9453853be043b8ed107e136e1ccb7)]PDF page 77
##### Option --enable-two-phase {#pg18-p77-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e117cfb2f6c67fe4ba46720bc6917da3dbd48c10)] ##### Option --clean {#pg18-p77-option-clean} The --clean option specifies the type of object to be removed from the subscriber. Currently, only 'publications' are supported. [[e5aeed4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e5aeed4b8020424fa882c3d1969d00c2b2d8cf30), [60dda7b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=60dda7bbc45f30e2ba50ecf20dfb9d944a589a38)] **Example 102.** Verifying Options ```sql $ 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 {#pg18-3-4-5-pg-dump} The following options have been added to the pg_dump command. [[9c49f0e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9c49f0e8cd7d59e240f5da88decf2d62d8a4ad0d), [bde2fb7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bde2fb797aaebcbe06bf60f330ba5a068f17dda7), [34eb2a8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=34eb2a80d5a3ea1392e4a9f16ebac54e96d0c6ed)] **Table 24.** Added options | Option name | Description | Note | | --- | --- | --- | | --sequence-data | Output sequence data | | | --with-statistics | Dump statistics data | | | --no-statistics | Do not dump statistics | default |PDF page 78
**Example 103.** Specifying the \-\-sequence\-data option ```sql $ 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 {#pg18-3-4-6-pg-dumpall} The following options have been added to the pg_dumpall command. ##### Option --sequence-data {#pg18-p78-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 {#pg18-p78-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=34eb2a80d5a3ea1392e4a9f16ebac54e96d0c6ed)] #### 3.4.7 Pg_dump / pg_dumpall / pg_restore command {#pg18-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1fd1bd871012732e3c6c482667d2f2c56f1a9395), [cd3c451](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cd3c45125d2d92e86ad7530b162562a23d063c26), [acea3fc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=acea3fc49f006a08e6c540ac0bacc2c2577a5ff9), [34eb2a8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=34eb2a80d5a3ea1392e4a9f16ebac54e96d0c6ed), [6a46089](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6a46089e458f2d700dd3b8c3f6fc782de933529a), [71ea0d6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=71ea0d6795438f95f4ee6e35867058c44b270d51)]PDF page 79
**Table 25.** Added options | Option name | Description | pg_dump / pg_dumpall | pg_restore | | --- | --- | --- | --- | | --no-data | Contains no data | Yes | Yes | | --no-policies | Contains no Row Level Security settings | Yes | Yes | | --no-schema | Contains no schema | Yes | Yes | | --no-statistics | Contains no statistics | Yes | Yes | | --restrict-key=KEY | Restrict meta commands | Yes | Yes | | --sequence-data | Contains sequence data | Yes | No | | --statistics | Contains optimizer statistics data | Yes | No | | --statistics-only | Contains only statistics | Yes | No | **Example 104.** Output statistics by pg\_dump command ```sql $ 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 {#pg18-3-4-8-pg-recvlogical} The following features have been implemented to the pg_recvlogical command. ##### Option --enable-failover {#pg18-p80-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cf2655a9029aff63dd567dbbdcdee15ec969905d)] ##### Option --enable-two-phase {#pg18-p80-option-enable-two-phase} The option name --two-phase has been changed to --enable-two-phase. The previous option remains available. [[6d12d5a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6d12d5a433c9c8cbf92fc19afd2a3465f275564c)] ##### Option --drop-slot {#pg18-p80-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c68100aa4313cf1cef826cf2d50f67164d13ebe4)] #### 3.4.9 Pg_resetwal {#pg18-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=30666d1857d7a785331f5805c20ec19430500466)] #### 3.4.10 Pg_rewind {#pg18-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4ecdd4110d5cbaf107c0c85d16df78dffe0a9574)] #### 3.4.11 Pg_upgrade {#pg18-3-4-11-pg-upgrade} The following new features have been added to the pg_upgrade command. ##### Option --set-char-signedness {#pg18-p80-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1aab6805919b84a2f2477132d96f2147ed36150e)]PDF page 81
##### Statistics migration {#pg18-p81-statistics-migration} The statistics are now migrated in the default configuration. Specify the --no-statistics option to not migrate statistics. [[1fd1bd8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1fd1bd871012732e3c6c482667d2f2c56f1a9395)] ##### Internally executed command processing {#pg18-p81-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=64f34eb2e2ce4bca7351d8c88a6999aeed000c4a), [6e16b1e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6e16b1e42003d811562d30b572ac3972238e2957)] #### 3.4.12 Pg_verifybackup {#pg18-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8dfd3129027969fdd2d9d294220c867d2efd84aa)] **Example 105.** Execute pg\_verifybackup command for tar format backup ```bash $ pg_basebackup -D back.1 --format=tar $ pg_verifybackup --format=tar --no-parse-wal back.1 backup successfully verified ``` #### 3.4.13 Psql {#pg18-3-4-13-psql} The following new features have been implemented in the psql command. ##### Option --help=variables {#pg18-p81-option-help-variables} The xheader_width description has been added to the output destination of the command option -help=variables. [[768dfd8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=768dfd8e6575f752f722d0a0ad69adebf0045a39)] **Example 106.** Output of \-\-help option ```bash $ 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 {#pg18-p82-prepared-statement-definition} The following metacommands have been added to manipulate the definition of Prepared statements [[d55322b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d55322b0da60a8798ffdb8b78ef90db0fb5be18e)] **Table 26.** Added metacommands | Metacommand | Description | Note | | --- | --- | --- | | \\parse | Create a Prepared statement | | | \\bind_named | Bind a value to a parameter | | | \\close | Close a Prepared statement | | **Example 107.** Operation of Prepared statement ```text 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+ {#pg18-p82-metacommand-dp} The name of the access method (Access method) for the partition table is now printed. [[978f38c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=978f38c771fb3a19fdd5cb73cb662441eb9e551c)]PDF page 83
**Example 108.** Output of the \\dP\+ metacommand ```text 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+ {#pg18-p83-metacommands-df-do-dao-dc} The output of each command now includes a “Leakproof?” column indicating whether it is leakproof. [[2355e51](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2355e51110e7c687c125a5958f12a462931de996)] **Example 109.** Output of the \\dAo\+ metacommand ```text 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 {#pg18-p83-pipeline} The following metacommands have been added to support the pipeline. [[41625ab](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=41625ab8ea3d0a2656dd0f067f1f0b61df63af97), [3ce3575](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3ce357584e795762aa6dc45fafc21dccea7e5ec6), [17caf66](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=17caf6644546eccb4a1f6e0137ea1d0f58da8c16)]PDF page 84
**Table 27.** Added metacommand | Metacommand | Description | Note | | --- | --- | --- | | \\startpipeline | Start pipeline | | | \\sendpipeline | Send extended query | | | \\syncpipeline | Synchronize pipeline | | | \\endpipeling | End pipeline | | | \\flushrequest | Request flush | | | \\flush | Send unsent data | | | \\getresults | Get execution results | Allow specifying number of fetches | The following variables are available to show the results of executing the above commands. **Table 28.** Added variables | Variable name | Description | Note | | --- | --- | --- | | PIPELINE_SYNC_COUNT | Number of pipes synchronized | | | PIPELINE_COMMAND_COUNT | Number of commands piped | | | PIPELINE_RESULT_COUNT | Number of results executed | |PDF page 85
**Example 110.** Pipeline Execution ```text 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 {#pg18-p85-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=00f4c2959d631c7851da21a512885d1deab28649)]PDF page 86
**Example 111.** Extended table format mode output ```text 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 {#pg18-p86-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=477728b5d6fa16461b81cd22b0568fec1eab97ac)] **Example 112.** Output service name ```text 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 {#pg18-p86-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a14707da564e8c94bd123f0e3a75e194fd7ef56a)]PDF page 87
**Example 113.** Title according to object type ```text 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 {#pg18-p87-metacommand-dx} The output of the \dx metacommand now includes the "Default version" entry for the extension. [[d696406](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d696406a9b255546bc1716d07199103abd8bb785)] **Example 114.** Output of the \\dx metacommand ```text 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 {#pg18-p88-metacommand-conninfo} The output of the conninfo metacommand has been substantially changed. [[bba2fbc](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bba2fbc6238b2a0a7f348fbbb5c31ffa7623bc39), [be37ac2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=be37ac20fc23511c49b9c56567c7c707b73d10b0), [6d64800](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6d6480066c1a96c7130b97b1139fdada9d484f80)] **Example 115.** Output of the \\conninfo metacommand \(with SSL\) ```text 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 {#pg18-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](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=edba754f052ea0c02287080004aa6051eaa4f597)]PDF page 89
**Example 116.** Specifying the \-\-missing\-stats\-only option ```bash $ vacuumdb --verbose --missing-stats-only --analyze-only vacuumdb: vacuuming database "postgres" ```