Skip to content

6 Utilities

Changes to configure, initdb, backup, restore, upgrade, psql, and maintenance tools.
GASource pages: PDF 59-69Original PDF on GitHub

PDF page 59

Describes the major enhancements of utility commands.

3.4.1 dropdb

The –force option (or -f option) has been added. Forces database deletion even if a connection session exists. The connected session will be closed.

Example 59 dropdb --force option
$ dropdb --force --echo demodb
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE demodb WITH (FORCE);

3.4.2 pg_basebackup

The following enhancements have been implemented in the pg_basebackup command.

–manifest-checksums option

Specify the algorithm for creating the checksum. The options that can be specified are CRC32C, SHA224, SHA256, SHA384, SHA512, or NONE. The default value is CRC32C. If NONE is specified, the manifest will not include the checksum.

–manifest-force-encode option

Outputs the file name to be written to the manifest in hexadecimal. By default, only non-UTF-8 file names are represented in hexadecimal.

–no-estimate-size option

The pg_basebackup command has been changed to estimate the data size without specifying the -progress option. Specify the option –no-estimate-size when not calculating the data amount estimate. This option cannot be used with –progress.

PDF page 60

Example 60 pg_basebackup ---no-estimate-size option
$ pg_basebackup --verbose --no-estimate-size -D back
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1/44000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3822"
pg_basebackup: write-ahead log end point: 1/44000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
–no-manifest option

Disables generation of a backup manifest.

–no-verify-checksums option

Do not verify checksums.

3.4.3 pg_dump

The following enhancements have been implemented in the pg_dump command.

–include-foreign-data option

Added –include-foreign-data option to dump FOREIGN SERVER data. This option specifies the FOREIGN SERVER name pattern. This option can be specified more than once.

PDF page 61

Example 61 pg_dump --include-foreign-data option
$ pg_dump -d demodb --include-foreign-data=svr1
--
-- PostgreSQL database dump
…
CREATE FOREIGN TABLE public.foreign1 (
    c1 numeric,
    c2 character varying(10)
)
SERVER svr1;
…
--
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner:
demo
--
COPY public.foreign1 (c1, c2) FROM stdin;
100     data1
\.
…
Output ALTER DEPEND ON EXTENSION statement

ALTER object DEPENDS ON EXTENSION statement is now output.

Example 62 ALTER DEPENDS ON EXTENSION statement
$ pg_dump -d demodb | grep EXTENSION
-- Name: cube; Type: EXTENSION; Schema: -; Owner: -
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
-- Name: EXTENSION cube; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION cube IS 'data type for multidimensional
cubes';
ALTER FUNCTION public.func1() DEPENDS ON EXTENSION cube;
$

PDF page 62

3.4.4 pg_rewind

The following three options have been added to the pg_rewind command.

–no-ensure-shutdown option

The pg_rewind will start in single-user mode if it detects a database cluster that has not shut down properly. Specify the –no-ensure-shutdown command and do not perform startup processing.

–write-recovery-conf option

The –write-recovery-conf option (or -R option) configures replication-related settings. This option must be specified with the –source-server option.

–restore-target-wal option

The –restore-target-wal option (or -c option) gets the restore_command parameter value from the target cluster and restores the required WAL segments from the archive.

PDF page 63

Example 63 pg_rewind command
$ pg_rewind --help
pg_rewind resynchronizes a PostgreSQL cluster with another copy of the
cluster.
Usage:
  pg_rewind [OPTION]...
Options:
  -c, --restore-target-wal       use restore_command in target
configuration to
                                 retrieve WAL files from archives
  -D, --target-pgdata=DIRECTORY  existing data directory to modify
      --source-pgdata=DIRECTORY  source data directory to synchronize with
      --source-server=CONNSTR    source server to synchronize with
  -n, --dry-run                  stop before modifying anything
  -N, --no-sync                  do not wait for changes to be written
                                 safely to disk
  -P, --progress                 write progress messages
  -R, --write-recovery-conf      write configuration for replication
                                 (requires --source-server)
      --debug                    write a lot of debug messages
      --no-ensure-shutdown       do not automatically fix unclean shutdown
  -V, --version                  output version information, then exit
  -?, --help                     show this help, then exit
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

3.4.5 pg_verifybackup

The pg_verifybackup command checks consistency by comparing the contents of the backup and the manifest. This command performs the following checks:

  • Manifest file version

  • The checksum of the manifest file itself

  • File size

PDF page 64

  • File checksum

  • WAL file integrity Syntax

pg_verifybackup [options] directory
Long OptionShort OptionDescription
–exit-on-error-eExit immediately on error.
–ignore=PATH-iIgnore the indicated path.
–manifest-path=PATH-mUse a specified path for the manifest.
–no-parse-wal-nDo not try to parse WAL files.
–quiet-qDo not print any output, except for errors.
–skip-checksums-sSkip checksum verification.
–wal-directory=PATH-wUse the specified path for WAL files.
–version-VOutput version information.
–help-?Output usage information.
Table 26 pg_verifybackup command option
Example 64 pg_verifybackup command
$ pg_basebackup -D back
$ pg_verifybackup back
backup successfully verified
$ echo $?
0
Validation error

The pg_verifybackup command outputs an error message if the size or content of the file has changed.

PDF page 65

Example 65 pg_verifybackup command with error
$ pg_basebackup -D back
$ vi back/postgresql.conf
$ vi back/pg_hba.conf
$ pg_verifybackup back
pg_verifybackup: error: "pg_hba.conf" has size 4761 on disk but
size 4760 in the manifest
pg_verifybackup:
error:
checksum
mismatch
for
file
"postgresql.conf"
$
Execution error

An error occurs when executing a backup for which a manifest has not been created or a backup created in tar format.

Example 66 pg_verifybackup command with error
$ pg_basebackup -D back --no-manifest
$ pg_verifybackup back
pg_verifybackup:
fatal:
could
not
open
file
"back/backup_manifest": No such file or directory
$ rm -r back
$ pg_basebackup -D back -Ft
$ pg_verifybackup back
pg_verifybackup: error: "base.tar" is present on disk but not in
the manifest
pg_verifybackup: error: "pg_wal.tar" is present on disk but not in
the manifest
pg_verifybackup: error: "base/13577/3433" is present in the
manifest but not on disk
…
Try "pg_waldump --help" for more information.
pg_verifybackup: error: WAL parsing failed for timeline 1
$

PDF page 66

3.4.6 pg_waldump

The –quiet option (or -q option) has been added to the pg_waldump command. This option suppresses non-error screen output.

3.4.7 psql

The following features have been added to the psql command.

\dAc, \dAf, \dAo, \dAp commands

Commands for outputting operator information have been added.

CommandOutput contentsNote
\dAcAM, Input type, Storage type, Operator class, Default?.
\aAfAM, Operator family, Applicable types
\dAoAM, Operator family, Operator, Strategy, Purpose
\dApAM, Operator family, Registered left type, Registered right type, Number, Function
Table 27 Added command
Example 67 \dAc command
postgres=> \dAc
                                List of operator classes
   AM   |         Input type          | Storage type |     Operator class     | Default?
--------+-----------------------------+--------------+------------------------+------
 brin   | "char"                      |              | char_minmax_ops        | yes
 brin   | anyrange                    |              | range_inclusion_ops    | yes
 brin   | bigint                      |              | int8_minmax_ops        | yes
 brin   | bit                         |              | bit_minmax_ops         | yes
 brin   | bit varying                 |              | varbit_minmax_ops      | yes
 brin   | box                         |              | box_inclusion_ops      | yes
 brin   | bytea                       |              | bytea_minmax_ops       | yes
 brin   | character                   |              | bpchar_minmax_ops      | yes
 brin   | date                        |              | date_minmax_ops        | yes
 brin   | double precision            |              | float8_minmax_ops      | yes
…

PDF page 67

\g, \gx commands

The feature has been added to the \g and \gx commands to temporarily change variables specified by the \pset command.

Example 68 \g command
postgres=> SELECT * FROM data1 WHERE c1=10 ;
 c1 |  c2
----+-------
 10 | data1
 10 | data1
10 | data1
(3 rows)
postgres=> \g (format=csv)
c1,c2
10,data1
10,data1
10,data1
\warn command

The \warn command that outputs a message to standard error has been added.

Example 69 \warn command
postgres=> \warn `date`
Thu Sep 24 21:10:09 JST 2020
\e command

After closing the editor, the edited SQL statement is now displayed.

Example 70 \e command
postgres=> \e
postgres=> SELECT * FROM data1
postgres->

PDF page 68

\d+ command

The table type is now output as a ‘Persistence’ column.

Example 71 \d command
postgres=> \d+
                                     List of relations
  Schema   |        Name        | Type  |  Owner   | Persistence |    Size    | Description
-----------+--------------------+-------+----------+-------------+------------+------
 pg_temp_3 | temp1              | table | demo     | temporary   | 8192 bytes |
 public    | data1              | table | demo     | permanent   | 42 MB      |
public    | unlogged1          | table | demo     | unlogged    | 16 kB      |
(3 rows)
PROMPT2 value

Variable PROMPT2 now a %w can be specified. If this value is specified, it will be converted to a space of the same length as PROMPT1.

Example 72 PROMPT2 variable value
postgres=> \set PROMPT2 '>%w'
postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10))
>           TABLESPACE pg_default ;
CREATE TABLE
I/O error detection

Errors when outputting files can now be detected.

The default value of PROMPT1/PROMPT2
The default prompt now includes %x to indicate transaction status.6。

3.4.8 reindexdb

The –jobs option to execute parallel processing has been added to the reindexdb command. The default value is 1 and no parallel processing is performed.

6 Refer 2.5.7 Psql default prompt psql

PDF page 69

Example 73 reindexdb --jobs option
$ reindexdb --concurrently --jobs 2 postgres
reindexdb: warning: cannot reindex system catalogs concurrently,
skipping all

3.4.9 vacuumdb

The –parallel option (or -P option) has been added. Specify the degree of parallelism (0 to 1024) in the option. This option cannot be specified together with the –full or –analyze-only option.

Example 74 vacuumdb --parallel option
$ vacuumdb --parallel=4 postgres
vacuumdb: vacuuming database "postgres"

3.4.10 Other

The URL to www.postgresql.org has been added to the output of the –help option for many commands.

Example 75 --help option
$ pg_controldata --help
pg_controldata displays control information of a PostgreSQL database cluster.
Usage:
  pg_controldata [OPTION] [DATADIR]
Options:
 [-D, --pgdata=]DATADIR  data directory
  -V, --version          output version information, then exit
  -?, --help             show this help, then exit
If no data directory (DATADIR) is specified, the environment variable PGDATA
is used.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>