6 Utilities

$ pg_basebackup -D back -R
$ ls back
backup_label      log           pg_ident.conf  pg_replslot
pg_stat_tmp  PG_VERSION            postgresql.conf
base              pg_commit_ts  pg_logical     pg_serial
pg_subtrans  pg_wal                standby.signal
current_logfiles  pg_dynshmem   pg_multixact   pg_snapshots
pg_tblspc    pg_xact
global            pg_hba.conf   pg_notify      pg_stat
pg_twophase  postgresql.auto.conf
$ 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''
port=5432
sslmode=disable
sslcompression=0 gssencmode=disable target_session_attrs=any'
primary_slot_name = 'slot1'
$

$ pg_checksums --help
pg_checksums enables, disables, or verifies data checksums in a
PostgreSQL database cluster.
Usage:
  pg_checksums [OPTION]... [DATADIR]
Options:
 [-D, --pgdata=]DATADIR    data directory
  -c, --check              check data checksums (default)
  -d, --disable            disable data checksums
  -e, --enable             enable data checksums
  -f, --filenode=FILENODE  check only relation with specified
filenode
  -N, --no-sync            do not wait for changes to be written
safely to disk
  -P, --progress           show progress information
  -v, --verbose            output verbose messages
  -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>.
$

 Specify the --check the option to check the integrity of the block. If the consistency error is confirmed,
the command ends with a return value of 1.

$ pg_checksums -D data --check
Checksum operation completed
Files scanned:  973
Blocks scanned: 11187
Bad checksums:  0
Data checksum version: 1
$ echo $?
0
$

 Specify the --enable option (--disable the option to disable) to enable database cluster checksums.
Even if the checksum is enabled, a consistency check is performed.

$ pg_checksums -D data --enable
Checksum operation completed
Files scanned:  973
Blocks scanned: 11187
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
$

 This command must be run with the instance successfully shut down. If an instance terminates
abnormally or cannot be executed for a database cluster during the instance is up.

$ pg_ctl -D data -m immediate stop
waiting for server to shut down.... done server stopped
$ pg_checksums -D data --check
pg_checksums: cluster must be shut down
$

$ pg_checksums -D data --check --progress
87/87 MB (100%) computed
Checksum operation completed
Files scanned:  973
Blocks scanned: 11187
Bad checksums:  0
Data checksum version: 1

$ pg_ctl -D data logrotate
server signaled to rotate log file
$

$ pg_dump -t data1 --inserts --on-conflict-do-nothing
--
-- PostgreSQL database dump
…
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner: demo
--
INSERT INTO public.data1 VALUES (1, 'data1') ON CONFLICT DO NOTHING;
INSERT INTO public.data1 VALUES (2, 'data1') ON CONFLICT DO NOTHING;
…

$ pg_dump -t data1 --inserts --rows-per-insert=2
--
-- PostgreSQL database dump
…
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner: postgres
--
INSERT INTO public.data1 VALUES
        (1, 'data1'),
        (2, 'data1');
INSERT INTO public.data1 VALUES
        (3, 'data1'),
        (4, 'data1');
…

$ pg_dumpall --exclude-database='demodb[12]' -f alldump.sql

-- User Configurations
-- User Config {User_name}
-- Databases
-- Database {Database_name} dump

$ pg_rewind --help
pg_rewind resynchronizes a PostgreSQL cluster with another copy of the
cluster.
Usage:
  pg_rewind [OPTION]...
Options:
  -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
      --debug                    write a lot of debug messages
…

$ pg_upgrade --help
pg_upgrade upgrades a PostgreSQL cluster to a different major version.
Usage:
  pg_upgrade [OPTION]...
Options:
  -b, --old-bindir=BINDIR       old cluster executable directory
…
  -r, --retain                  retain SQL and log files after success
  -s, --socketdir=DIR           socket directory to use (default CWD)
  -U, --username=NAME           cluster superuser (default "postgres")
  -v, --verbose                 enable verbose internal logging
  -V, --version                 display version information, then exit
  --clone                       clone instead of copying files to new
cluster
  -?, --help                    show this help, then exit
…

postgres=> \pset format csv
Output format is csv.
postgres=> \pset csv_fieldsep
Field separator for CSV is ",".
postgres=> SELECT * FROM data1 ;
c1,c2
<- Title
1,ABC
2,"AB,C"
<- If the data contains column separators.
2,"AB""C"
<- When the data contains double quotations.

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION
                 BY LIST(c1) ;
CERATE TABLE
postgres=> \d part1
                Partitioned table "public.part1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | numeric               |           |          |
 c2     | character varying(10) |           |          |
Partition key: LIST (c1)
Number of partitions: 0

 Tablespace of partitioned indexes is now displayed.

postgres=> CREATE INDEX idx1_part1 ON part1(c2) TABLESPACE ts1 ;
CREATE INDEX
postgres=> \d idx1_part1
       Partitioned index "public.idx1_part1"
 Column |         Type          | Key? | Definition
--------+-----------------------+------+------------
 c2     | character varying(10) | yes  | c2
btree, for table "public.part1"
Tablespace: "ts1"

postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY
LIST (c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES IN (10) ;
CREATE TABLE
postgres=> \z
                                    Access privileges
 Schema |  Name   |       Type        | Access privileges | Column
privileges | Policies
--------+---------+-------------------+-------------------+-------------
------+----------
 public | part1   | partitioned table |                   |                   |
 public | part1v1 | table             |                   |                   |
(2 rows)

$ psql -p 5432 -h pghost1 -d postgres -U demo
Password for user demo: <<PASSWORD>>
psql (12.0)
Type "help" for help.
postgres=> \conninfo
You are connected to database "postgres" as user "demo" on host
"pghost1" (address "192.168.1.101") at port "5432".
postgres=>

postgres=> \set VERBOSITY sqlstate
postgres=> SELECT * FROM not_exists ;
psql: ERROR:  42P01

ostgres=> CREATE TABLE part1(c1 INT, c2 VARCHAR(2)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (1000000) ;
CREATE TABLE
postgres=> \dP
             List of partitioned relations
 Schema | Name  | Owner |       Type        | On table
--------+-------+-------+-------------------+----------
 public | part1 | demo  | partitioned table |
(1 row)
postgres=> \dP+
                          List of partitioned relations
 Schema | Name  | Owner |       Type        | On table | Total size | Description
--------+-------+-------+-------------------+----------+------------+-------------
 public | part1 | demo  | partitioned table |          | 0 bytes    |
(1 row)

postgres=> \h CREATE SEQUENCE
Command:     CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
URL: https://www.postgresql.org/docs/12/sql-createsequence.html

$ vacuumdb -h remhost11 -d postgres -U postgres --skip-locked
vacuumdb: error: cannot use the "skip-locked" option on server
versions older than PostgreSQL 12