6 Utilities

$ createuser --member=role1 user1 --echo
SELECT pg_catalog.set_config('search_path', '', false);
CREATE
ROLE
user1 NOSUPERUSER NOCREATEDB
NOCREATEROLE INHERIT
LOGIN
NOREPLICATION NOBYPASSRLS IN ROLE role1;

$ initdb --icu-local=en_US --locale-provider=icu --icu-rules='&a < g' data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Using language tag "en-US" for ICU locale "en_US".
The database cluster will be initialized with this locale configuration:
  provider:    icu
  ICU locale:  en_US
  LC_COLLATE:  en_US.UTF-8
...

$ initdb --set port=5433 --set listen_addresses='*' --locale-provider=libc --
no-locale --encoding=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 locale "C".
The default text search configuration will be set to "english".
Data page checksums are disabled.
…

$ pg_basebackup -D data.zstd --compress=zstd:long --format=tar
$ file data.zstd/*
data.zstd/backup_manifest: ASCII text
data.zstd/base.tar.zst:    Zstandard compressed data (v0.8+), Dictionary ID:
None
data.zstd/pg_wal.tar:      POSIX tar archive

$ pg_bsd_indent -v indent.c
There were 1273 output lines and 223 comments
(Lines with comments)/(Lines with code):  0.548

$ pg_dump --compress=gzip:9 --file=postgres.gz postgres
$ file postgres.gz
postgres.gz: gzip compressed data, max compression, from Unix, original size
11963
$ pg_dump --compress=lz4:12 --file=postgres.lz4 postgres
$ file postgres.lz4
postgres.lz4: LZ4 compressed data (v1.4+)
$ pg_dump --compress=zstd:22 --file=postgres.zstd postgres
$ file postgres.zstd
postgres.zstd: Zstandard compressed data (v0.8+), Dictionary ID: None

$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.
…
Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --large-objects          include large objects in dump
  --blobs                      (same as --large-objects, deprecated)
  -B, --no-large-objects       exclude large objects in dump
  --no-blobs                   (same as --no-large-objects, deprecated)
  -c, --clean                  (drop) database objects before recreating
…

$ pg_basebackup -D back.1 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_120380"
pg_basebackup: write-ahead log end point: 0/5E0001B0
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
$ pg_verifybackup back.1 --progress
887802/887802 kB (100%) verified
backup successfully verified

$ pg_waldump --save-fullpage=$HOME/fullpage
data/pg_wal/000000010000000000000004
rmgr: Heap        len (rec/tot):     65/    65, tx:        740, lsn: …
rmgr: Heap        len (rec/tot):     65/    65, tx:        740, lsn: …
…
$ ls -1 $HOME/fullpage
00000000-00000000-05A26608.1663.5.16388.0_vm
00000000-00000000-05A7D2E8.1663.5.2619.18_main
00000000-00000000-05A7E920.1663.5.2696.1_main

$ pg_waldump 000000010000000000000001  --timeline=0xA
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn:
0/01000028, prev 0/00000000, desc: CHECKPOINT_SHUTDOWN redo 0/1000028; tli 1;
pr
ev tli 1; fpw true; xid 0:3; oid 10000; multi 1; offset 0; oldest xid 3 in DB
1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; old
est running xid 0; shutdown
…

postgres=> \! ls postgresql.conf
postgresql.conf
postgres=> \echo :SHELL_ERROR
false
postgres=> \echo :SHELL_EXIT_CODE
0
postgres=> \! ls postgresql.conf.bad
ls: cannot access 'postgresql.conf.bad': No such file or directory
postgres=> \echo :SHELL_ERROR
true
postgres=> \echo :SHELL_EXIT_CODE
2

postgres=> CREATE FOREIGN TABLE remote1(c1 INT, c2 VARCHAR(10)) SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> CREATE TABLE part1(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> ALTER TABLE part1 ATTACH PARTITION remote1 FOR VALUES FROM (0) TO
(100) ;
ALTER TABLE
postgres=> \d+ part1
                                           Partitioned table "public.part1"
 Column |         Type          | Collation | Nullable | Default | Storage …
--------+-----------------------+-----------+----------+---------+-------- …
c1     | integer               |           |          |         | plain   …
 c2     | character varying(10) |           |          |         | extended…
Partition key: RANGE (c1)
Partitions: remote1 FOR VALUES FROM (0) TO (100), FOREIGN

postgres=> SELECT $1, $2
postgres-> \bind 'value1' 'value2' \g
 ?column? | ?column?
----------+----------
 value1   | value2
(1 row)
postgres=> SELECT * FROM data1 WHERE c1=$1 \bind 100 \g
 c1  |  c2
-----+-------
 100 | data1
(1 row)

postgres=> \x
Expanded display is on.
postgres=> CREATE TABLE data1(c1 char(10), c2 char(20)) ;
CREATE TABLE
postgres=> INSERT INTO data1 VALUES ('1234567890', '12345678901234567890') ;
INSERT 0 1
postgres=> \pset xheader_width
Expanded header width is 'full'.
postgres=> SELECT * FROM data1 ;
-[ RECORD 1 ]------------
c1 | 1234567890
c2 | 12345678901234567890
postgres=> \pset xheader_width column
Expanded header width is 'column'.
postgres=> SELECT * FROM data1 ;
-[ RECORD 1 ]
c1 | 1234567890
c2 | 12345678901234567890

postgres=> SELECT current_time ;
    current_time
--------------------
16:58:33.012005+09
(1 row)
postgres=> \watch i=1 c=2
Sat 16 Sep 2023 04:58:34 PM JST (every 1s)
    current_time
--------------------
16:58:34.850061+09
(1 row)
Sat 16 Sep 2023 04:58:35 PM JST (every 1s)
    current_time
--------------------
 16:58:35.850556+09
(1 row)

postgres=# GRANT pg_checkpoint TO demo1 WITH INHERIT FALSE ;
GRANT ROLE
postgres=# GRANT pg_checkpoint TO demo2 WITH SET FALSE ;
GRANT ROLE
postgres=# \drg
              List of role grants
 Role name |   Member of   | Options | Grantor
-----------+---------------+---------+----------
 demo1     | pg_checkpoint | SET     | postgres
 demo2     | pg_checkpoint | INHERIT | postgres
(2 rows)

$ vacuumdb -d postgres --schema=public
vacuumdb: vacuuming database "postgres"
$ vacuumdb -d postgres --exclude-schema=public
vacuumdb: vacuuming database "postgres"
$ vacuumdb -d postgres --schema=public --exclude-schema=public
vacuumdb: error: cannot vacuum all tables in schema(s) and exclude schema(s) at
the same time

$ vacuumdb --no-process-main --echo postgres
SELECT pg_catalog.set_config('search_path', '', false);
vacuumdb: vacuuming database "postgres"
RESET search_path;
SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
 JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=)
ns.oid
 LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid
 WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
 ORDER BY c.relpages DESC;
SELECT pg_catalog.set_config('search_path', '', false);
VACUUM (PROCESS_MAIN FALSE, SKIP_DATABASE_STATS) pg_catalog.pg_proc;
…

$ vacuumdb --buffer-usage-limit=1MB --echo postgres
SELECT pg_catalog.set_config('search_path', '', false);
vacuumdb: vacuuming database "postgres"
…
SELECT pg_catalog.set_config('search_path', '', false);
VACUUM (SKIP_DATABASE_STATS, BUFFER_USAGE_LIMIT '1MB') public.t1;
…