PDF page 66
Describes the major enhancements of utility commands. #### 3.4.1 Configure {#pg14-3-4-1-configure} The following options have been added to the 'configure' command. ##### Specify SSL library {#pg14-p66-specify-ssl-library} Added "--with-ssl={library name}" option to specify the SSL library. Also, conventional "--withopenssl" option has been left for compatibility. **Example 76.** Configure command for SSL ```text $ ./configure --help | grep ssl --with-ssl=LIB use LIB for SSL/TLS support (openssl) --with-openssl obsolete spelling of --with-ssl=openssl $ ``` ##### Column compression method {#pg14-p66-column-compression-method} The --with-lz4 option has been added to take advantage of the LZ4 column compression feature. The compiler flag LZ4_CFLAGS and the linker flag LZ4_LIBS have been added as environment variables. **Example 77.** Configure command for LZ4 ```text $ ./configure --help | grep -i lz4 --with-lz4 build with LZ4 support LZ4_CFLAGS C compiler flags for LZ4, overriding pkg-config LZ4_LIBS linker flags for LZ4, overriding pkg-config $ ``` #### 3.4.2 Initdb {#pg14-3-4-2-initdb} The following options have been added to the initdb command. ##### --no-instructions option {#pg14-p66-no-instructions-option} When this option is specified, a message indicating the instance start-up method will not be output.PDF page 67
**Example 78.** \-\-no\-instructions option ```bash $ initdb -D data --no-instructions The files belonging to this database system will be owned by user "postgres". This user must also own the server process. … initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option - A, or --auth-local and --auth-host, the next time you run initdb. ``` ##### --data-checksums option {#pg14-p67-data-checksums-option} In the message output when the --help option is specified, the --data-checksums option that specifies the checksum has been promoted from "Less commonly used options:" to the commonly used option "Options:". **Example 79.** Checksum specification option ```bash $ initdb --help initdb initializes a PostgreSQL database cluster. Usage: initdb [OPTION]... [DATADIR] Options: -A, --auth=METHOD default authentication method for local connections --auth-host=METHOD default authentication method for local TCP/IP connections --auth-local=METHOD default authentication method for local-socket connections [-D, --pgdata=]DATADIR location for this database cluster -E, --encoding=ENCODING set default encoding for new databases -g, --allow-group-access allow group read/execute on data directory -k, --data-checksums use data page checksums --locale=LOCALE set default locale for new databases ``` ##### --clobber-cache option {#pg14-p67-clobber-cache-option} ```text … ```PDF page 68
##### --discard-cache option {#pg14-p68-discard-cache-option} Run the bootstrap backend with the debug_discard_caches=1 option. This takes a very long time and is only of use for debugging. #### 3.4.3 Pg_amcheck {#pg14-3-4-3-pg-amcheck} The pg_amcheck command has been added. This command makes it easy to check the structure of tables and indexes against a database with the Contrib module amcheck installed. In the following example, it can be seen that the damaged part of data2 table. **Example 80.** Pg\_amcheck command ```bash $ pg_amcheck --database=postgres --table=data2 --verbose pg_amcheck: including database: "postgres" pg_amcheck: in database "postgres": using amcheck version "1.3" in schema "public" pg_amcheck: checking heap table "postgres"."public"."data2" heap table "postgres"."public"."data2", block 0, offset 17: xmin 3236212 equals or exceeds next valid transaction ID 0:546 heap table "postgres"."public"."data2", block 0, offset 48: line pointer to page offset 6282 is not maximally aligned pg_amcheck: checking btree index "postgres"."pg_toast"."pg_toast_16399_index" pg_amcheck: checking heap table "postgres"."pg_toast"."pg_toast_16399" ``` ```text Many options can be specified for the pg_amcheck command other than the above example. ```PDF page 69
**Table 27.** Major options | Option name | Description | | --- | --- | | --all | Check all databases. | | --index=PATTERN | Check matching index(es). | | --schema=PATTERN | Check matching schema(s). | | --on-error-stop | Stop checking at end of first corrupt page. | | --parent-check | Check index parent/child relationships. | | --startblock=BLOCK | Begin checking table(s) at the given block number. | | --endblock=BLOCK | Check table(s) only up to the given block number. | | --jobs=NUM | Use this many concurrent connections to the server. | | --progress | Show progress information. | #### 3.4.4 Pg_dump {#pg14-3-4-4-pg-dump} The pg_dump command has been following enhancements are implemented. ##### Restore partitions {#pg14-p69-restore-partitions} It is now possible to restore a single partition from the partitioned table as a table. ##### Dumping Extension settings {#pg14-p69-dumping-extension-settings} The --extension option has been added to limit the extension definitions to be dumped. Specify the pattern for the extension name. **Example 81.** \-\-extension option ```sql $ pg_dump --extension=cube -- -- PostgreSQL database dump … -- -- Name: cube; Type: EXTENSION; Schema: -; Owner: - -- CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public; … ```PDF page 70
##### Multiple --verbose option {#pg14-p70-multiple-verbose-option} In the pg_dump command, pg_dumpall command, pg_restore command, and pg_rewind command, specifying the -v option multiple times will now increase the output level of the log. #### 3.4.5 Pg_dumpall {#pg14-3-4-5-pg-dumpall} The pg_dumpall command now has an option --no-toast-compression that does not dump the compression settings for TOAST data. #### 3.4.6 Pg_rewind {#pg14-3-4-6-pg-rewind} It is now possible to specify a standby instance of streaming replication as the connection destination. #### 3.4.7 Psql {#pg14-3-4-7-psql} The following features have been added to the psql command. ##### Displaying access methods {#pg14-p70-displaying-access-methods} The Access Method is now output to the output of the \di+, \dm+, \dt+, and \dtS+ commands. **Example 82.** Display access method ```text postgres=> \di+ List of relations Schema | Name | Type | Owner | Table | Persistence | Access Method | … --------+------------+-------+-------+-------+-------------+-------------+ … public | idx1_data1 | index | demo | data1 | permanent | btree | … public | idx1_data2 | index | demo | data2 | permanent | btree | … (2 rows) postgres=> \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access Method | … --------+---------+-------------------+-------+-------------+-----------+ …- public | data1 | table | demo | permanent | heap | … public | data2 | table | demo | permanent | heap | … (2 rows) ```PDF page 71
##### \dt, \di command {#pg14-p71-dt-di-command} It is now possible to specify for TOAST table and TOAST index. **Example 83.** Specifying the TOAST table ```text postgres=> \dt pg_toast.pg_toast_16384 List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16384 | TOAST table | postgres (1 row) postgres=> \di pg_toast.pg_toast_1213_index List of relations Schema | Name | Type | Owner | Table ----------+---------------------+-------+----------+------------- pg_toast | pg_toast_1213_index | index | postgres | pg_toast_1213 (1 row) ``` ##### \dX command {#pg14-p71-dx-command} Outputs a list of extended statistics. **Example 84.** List of extended statistics ```text postgres=> CREATE STATISTICS stat1_data1 ON c1, c2 FROM data1 ; CREATE STATISTICS postgres=> ANALYZE data1 ; ANALYZE postgres=> \dX List of extended statistics Schema | Name | Definition | Ndistinct | Dependencies | MCV --------+-------------+-------------------+-----------+--------------+------ public | stat1_data1 | c1, c2 FROM data1 | defined | defined | defined (1 row) ```PDF page 72
##### \d+ command {#pg14-p72-d-command} When the table name is specified in the \d+ command, a compression setting column (Compression) is output between the Storage and Stats target columns. This column can be suppressed by setting the HIDE_TOAST_COMPRESSION variable to 'on'. **Example 85.** \\d\+ command ```text postgres=> \d+ compress1 Table "public.compress1" Column | Type | Collation | Nullable | Default | Storage | Compression | … --------+---------+-----------+----------+---------+----------+------------+ … c1 | integer | | | | plain | | … c2 | text | | | | extended | lz4 | … Access method: heap postgres=> \set HIDE_TOAST_COMPRESSION on postgres=> \d+ compress1 Table "public.compress1" Column | Type | Collation | Nullable | Default | Storage | Stats target | … --------+---------+-----------+----------+---------+----------+--------------+ … c1 | integer | | | | plain | | … c2 | text | | | | extended | | … Access method: heap ``` ##### \df, \do command {#pg14-p72-df-do-command} These commands can now add specify the data type.PDF page 73
**Example 86.** \\df\, \\do command ```text postgres=> \do - pg_catalog.int4 List of operators Schema | Name | Left arg type | Right arg type | Result type | Description ------------+------+---------------+----------------+-------------+----------- pg_catalog | - | | integer | integer | negate (1 row) postgres=> \df sum (numeric) List of functions Schema | Name | Result data type | Argument data types | Type ------------+------+------------------+---------------------+------ pg_catalog | sum | numeric | numeric | agg (1 row) ``` ##### \dT command {#pg14-p73-dt-command} The \dT command now accepts alias names for data types. **Example 87.** \\dT command with alias ```text postgres=> \dT int List of data types Schema | Name | Description ------------+---------+------------------------------------------------- pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage (1 row) ``` ##### Behavior when the editor is closed {#pg14-p73-behavior-when-the-editor-is-closed} The \e command (also \ef and \ev commands) launches the editor and no longer re-executes SQL statements in the file if the file is not modified. #### 3.4.8 Reindexdb {#pg14-3-4-8-reindexdb} The --tablespace option has been added to the reindexdb command. It is now possible to specify the tablespace in which to store the indexes to be recreated.PDF page 74
**Example 88.** REINDEXDB \-\-tablespace option ```bash $ reindexdb --table=data1 --tablespace=ts1 --verbose postgres INFO: index "idx1_data1" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "pg_toast_16385_index" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s ``` #### 3.4.9 Vacuumdb {#pg14-3-4-9-vacuumdb} The following options have been added to the vacuumdb command. ##### --force-index-cleanup option {#pg14-p74-force-index-cleanup-option} ```text Always remove index entries pointing to dead tuples. ``` ##### --no-index-cleanup option {#pg14-p74-no-index-cleanup-option} Do not delete unnecessary tuples in the index. ##### --no-truncate option {#pg14-p74-no-truncate-option} Do not truncate the free pages at the end of the table. ##### --no-process-toast option {#pg14-p74-no-process-toast-option} Do not perform VACUUM processing on the TOAST table.PDF page 75
**Example 89.** Vacuumdb command options ```sql $ vacuumdb --help vacuumdb cleans and analyzes a PostgreSQL database. Usage: vacuumdb [OPTION]... [DBNAME] Options: -a, --all vacuum all databases -d, --dbname=DBNAME database to vacuum --disable-page-skipping disable all page-skipping behavior -e, --echo show the commands being sent to the server -f, --full do full vacuuming -F, --freeze freeze row transaction information --force-index-cleanup always remove index entries that point to dead tuples -j, --jobs=NUM use this many concurrent connections to vacuum --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum --no-index-cleanup don't remove index entries that point to dead tuples --no-process-toast skip the TOAST table associated with the table to vacuum --no-truncate don't truncate empty pages at the end of the table -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available … ```