6 Utilities

$ ./configure --help | grep ssl
  --with-ssl=LIB          use LIB for SSL/TLS support (openssl)
  --with-openssl          obsolete spelling of --with-ssl=openssl
$

$ ./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
$

$ 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.

$ 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

$ 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"

Many options can be specified for the pg_amcheck command other than the above example.

$ pg_dump --extension=cube
--
-- PostgreSQL database dump
…
--
-- Name: cube; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
…

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)

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)

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)

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

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)

postgres=> \dT int
                           List of data types
   Schema   |  Name   |                   Description
------------+---------+-------------------------------------------------
 pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
(1 row)

$ 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

$ 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
…