Top New Features in PostgreSQL 16 database

Postgres 16 new features

• Random connection load balancing support using libpq, which will make it easier to scale out read queries in Postgres version 16.
• In PostgreSQL 16, we can grant (only) vacuum and analyze operation privileges to another user, no need to be done only the owner or super users.
postgres=> grant analyze on table1 to user1;
postgres=> grant vacuum on table1 to user1;
postgres=> grant pg_vacuum_all_tables to satya;
postgres=> grant pg_analyze_all_tables to satya;

• Users with CREATEROLE privilege, without ADMIN OPTION, no longer have the ability to hand out membership in any role to anyone. 
• GRANT query to be performed with ADMIN OPTION.
• The speed of hash index builds improved.
• Logical replication from standby servers. Logical replication subscribers to apply large transactions in parallel.
• Regular expression matching on database and role entries in pg_hba.conf.
• Postgres 16 onwards, NUMERIC can process hexadecimal, octal, and binary integers of any size.
• PostgreSQL query planner can now parallize FULL and RIGHT joins, by creating gather node and worker nodes.
• Supports incremental sorts for SELECT DISTINCT.

New System Views in Postgres 16:

• Monitoring of I/O statistics using the new pg_stat_io view.

New Parameters in PostgreSQL 16:

• New server variable enable_presorted_aggregate to aggregates having ORDER BY or DISTINCT to use pre-sorted data enable/disable.
• Added server variable SYSTEM_USER.
• New server parameter logical_replication_mode to control how logical decoding publishers transfer changes and how subscribers apply them.

Related PostgreSQL Articles: New features of Postgres 15

Top New Features in PostgreSQL 15 database

Postgres 15 new features

PostgreSQL 15 has many improvements and new features.

• MERGE command has been introduced, to do conditional INSERT, UPDATE, DELETE rows.
• Added support for Zstandard (zstd) compression. E.g. allow WAL full-page writes to use LZ4 and Zstandard compression.
• Prevent logical replication of empty transactions.
• UNIQUE NULLS NOT DISTINCT feature introduced for indexes, to have only one NULL value in the column.
• Add SQL functions to monitor the directory contents of logical replication slots, new functions are pg_ls_logicalsnapdir(), pg_ls_logicalmapdir(), and pg_ls_replslotdir().
• Performance improvements, particularly for in-memory and on-disk sorting.
• New pg_database_owner role.
• New wait events are used when calling archive_command, archive_cleanup_command, restore_command and recovery_end_command.
• Add predefined role pg_checkpoint that allows members to run CHECKPOINT.
• New roles like pg_read_all_data & pg_write_all_data has been added.
• Subscribers can stop the application of logical replication changes on error, with the option disable_on_error.
• log_destination parameter has new value - jsonlog - to generate structured log.
• Add support for petabyte units to pg_size_pretty() and pg_size_bytes().
• Stats collector (process) has been removed.
• \dconfig - to inspect/find values of configuration parameters.
• Compression method/level in pg_basebackup commands.
• Roles can be granted to change specific parameters, through ALTER SYSTEM.

New System Views in Postgres 15:

• Add system view pg_ident_file_mappings to report pg_ident.conf information.
• Add system view pg_stat_subscription_stats to report on subscriber activity. And new function pg_stat_reset_subscription_stats() allows resetting these statistics counters.
• pg_backend_memory_contexts

New Parameters in PostgreSQL 15:

• Add server variable recursive_worktable_factor to allow the user to specify the expected size of the working table of a recursive query.
• New server variable log_startup_progress_interval - to generate progress messages in the server log during slow server starts.
• Add server variable shared_memory_size to report the size of allocated shared memory.
• Add server variable shared_memory_size_in_huge_pages to report the number of huge memory pages required.

Removed:

• Python 2.x is no longer supported.

Related PostgreSQL Articles:  New Features in PostgreSQL 14 database   Postgres 13 new features

Postgres pg_upgrade utility

pg_upgrade utility in PostgreSQL

pg_upgrade (formerly pg_migrator)

pg_upgrade upgrades a PostgreSQL cluster to a different major version.

pg_upgrade --help

Usage:

  pg_upgrade [OPTION]...

Options:

  -b, --old-bindir=BINDIR        old cluster executable directory

  -B, --new-bindir=BINDIR        new cluster executable directory (default same directory as pg_upgrade)

  -c, --check                    check clusters only, don't change any data

  -d, --old-datadir=DATADIR     old cluster data directory

  -D, --new-datadir=DATADIR   new cluster data directory

  -j, --jobs=NUM                number of simultaneous processes or threads to use

  -k, --link                    link instead of copying files to new cluster

  -N, --no-sync                  do not wait for changes to be written safely to disk

  -o, --old-options=OPTIONS    old cluster options to pass to the server

  -O, --new-options=OPTIONS  new cluster options to pass to the server

  -p, --old-port=PORT            old cluster port number (default 50432)

  -P, --new-port=PORT            new cluster port number (default 50432)

  -r, --retain                  retain SQL and log files after success

  -s, --socketdir=DIR          socket directory to use (default current dir.)

  -U, --username=NAME           cluster superuser (default "thirumani")

  -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

Before running pg_upgrade you must:

  create a new database cluster (using the new version of initdb)

  shutdown the postmaster servicing the old cluster

  shutdown the postmaster servicing the new cluster

When you run pg_upgrade, you must provide the following information:

  the data directory for the old cluster  (-d DATADIR)

  the data directory for the new cluster  (-D DATADIR)

  the "bin" directory for the old version (-b BINDIR)

  the "bin" directory for the new version (-B BINDIR)

For example:

  pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin

or

  $ export PGDATAOLD=oldCluster/data

  $ export PGDATANEW=newCluster/data

  $ export PGBINOLD=oldCluster/bin

  $ export PGBINNEW=newCluster/bin

  $ pg_upgrade

pg_upgrade --version

pg_upgrade -b oldbindir -B newbindir -d oldconfigdir -D newconfigdir [option...]

/usr/pgsql/12/bin/pg_upgrade --old-bindir=/usr/pgsql/10/bin --new-bindir=/usr/pgsql/12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data --check

pg_upgrade --old-bindir=/usr/pgsql/10/bin --new-bindir=/usr/pgsql/12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data

pg_upgrade -b=/usr/pgsql/11/bin -B=/usr/pgsql/13/bin -d=/var/lib/pgsql/11/data -D=/var/lib/pgsql/13/data --check

pg_upgrade -b=/usr/pgsql/11/bin -B=/usr/pgsql/13/bin -d=/var/lib/pgsql/11/data -D=/var/lib/pgsql/13/data --link

Related Postgres Articles:  Comparison of PostgreSQL Oracle features

Top 40+ (Aurora) PostgreSQL Interview Questions - part 1

40 Best PostgreSQL/Aurora Postgres DBA Interview Questions - Part1

Q1. What is write-ahead logging (WAL)? or What are WAL files / segments?

Answer:

Q2. What is vacuuming?

Answer:

Q3. What is a vacuum full?

Answer:

Q4. What are the vacuum related init database parameters?

Answer:

Q5. What is auto vacuuming (or) what is the difference between normal vacuum and auto-vacuum?

Answer:

Q6. What are the auto vacuum related init database parameters?

Answer:

Q7. How you will confirm whether the auto-vacuum is completed or not?

Answer:

Q8. What is vacuum freeze?

Answer:

Q9. What are Transaction ID wraparound failures and how to avoid them?

Answer:

Q10. What is Frozen Transaction ID (or) what is the difference between normal Transaction ID and Frozen Transaction ID?

Answer:

Q11. How to reclaim space in Postgres?

Answer:

Q12. What is Multi-Version Concurrency Control (MVCC) in PostgreSQL?

Answer:

Q13. What are shared buffers (what is the use of shared_buffer parameter)?

Answer:

Q14. Why would Postgres not free up storage space even after the deletion of data?

Answer:

Q15. What is the use of pg_repack extension? How to remove bloating tables/indexes from (Aurora) Postgres databases?

Answer:

Q16. What is a checkpoint? how often checkpoint will happen?

Answer:

Q17. What is bgwriter? how often background writer should invoke?

Answer:

Q18. What are the toast tables?

Answer:

Q19. What is the meaning of table bloating? how to reduce table/index bloating?

Answer:

Q20. What is Host Based Access (HBA) control? How to allow/restrict a few IP ranges to connect to your Postgres database?

Answer:

Related PostgreSQL Articles:  vacuumdb utility commands in PostgreSQL   Aurora PostgreSQL Database Admin Interview Questions Part2

Top New Features in PostgreSQL 14 database

Postgres 14 new features

PostgreSQL 14 has more than 200 improvements and new features that help developers and administrators.

• Postgres 14 onwards, stored procedures can return data via OUT parameters.
• Security change is moving to SCRAM as the default authentication after SCRAM-SHA-256 authentication. Changed the default of the password_encryption server parameter to scram-sha-256.
• Simplifies the process of assigning write-only and read-only capabilities to users on tables, views, and schemas by introducing two predefined roles, pg_write_all_data, and pg_read_all_data.
• PostgreSQL 14 added support for LZ4 compression for TOAST.
• Performance improvements have been made for partitioned tables, logical replication, and vacuuming.
• Add command-line utility pg_amcheck to simplify running contrib/amcheck tests on many relations
• Allow column names in the WHERE clause of ON CONFLICT to be table-qualified.
• VACUUM automatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition.
• Added a predefined role pg_database_owner that contains only the current database's owner.
• Allows pg_hba.conf and pg_ident.conf records to span multiple lines. A backslash at the end of a line allows record contents to be continued on the next line.
• Add an optional timeout parameter to pg_terminate_backend().
• Add OR REPLACE option for CREATE TRIGGER, to allow pre-existing triggers to be conditionally replaced.
• Allow partitions to be detached in a non-blocking manner.  ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, and FINALIZE.
• Allow REFRESH MATERIALIZED VIEW to use parallelism.
• Allow publications to be more easily added to and removed from a subscription. ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION. This avoids having to specify all publications to add/remove entries.
• Added string_to_table() function to split a string on delimiters.

New Server Parameters in Postgres 14

• Added server parameter idle_session_timeout to close idle sessions.
• New server parameter client_connection_check_interval allows control over whether loss of connection is checked for intra-query, and long-running queries are canceled if the client disconnects.
• Added new read-only server parameter in_hot_standby, to allow clients to easily detect whether they are connected to a hot standby server.
• Added server parameter huge_page_size to control the size of huge pages used on Linux.
• Added server parameter log_recovery_conflict_waits to report long recovery conflict wait times.
• Added server parameter debug_discard_caches to control cache flushing for test purposes.
• New server parameter compute_query_id, with default value auto, will automatically enable query id computation when this extension is loaded.

New System Views in PostgreSQL 14

• pg_stat_progress_copy to report COPY progress.
• pg_stat_wal to report WAL activity.
• pg_stat_statements_info system view to show pg_stat_statements activity.
• pg_stat_replication_slots to report replication slot activity.
• pg_backend_memory_contexts to report session memory usage.

Deprecated/Removed

• Removed the ! operator in Postgres14

Related PostgreSQL Articles: pg_dumpall tool in Postgres   New Features in PostgreSQL 15 database

PostgreSQL pg_dump utility

pg_dump utility in PostgreSQL

pg_dump # to take backup of single postgres database

pg_dump --help

pg_dump -V

pg_dump proddb > /opt/backups/prod_backup 

pg_dump -U postgres -d devdb > /home/pgsql/backups/devdb_bkp

pg_dump -U postgres testdb -Fc > testdb.dump

pg_dump -p 5301 testdb

pg_dump -p 5313 -f export.sql testdb

pg_dump -p 5435 -v testdb > export.sql

pg_dump -p 5333 testdb -F p -h localhost

pg_dump -p 5348 testdb -F c > export.dmp

pg_dump -Fc

pg_dump -U username -W -F t database_name > c:\backup_file.tar

pg_dump -Fc mydb > db.dump

pg_dump -Ft mydb > backup.tar

pg_dump -Fd mydb > db_backup

pg_dump devdb | gzip /opt/backups/devdb.gz

pg_dump | split -b 1m - /usr/backups/database_backup

pg_dump -p 5303 testdb -f export.sql -s                              # schema only

pg_dump -t products testdb --schema-only > test.sql         # get DDL statements

pg_dump -h postgres-aurora-demo.cluster-czjdkvo2yd1y.us-west-2.rds.amazonaws.com -p1526 -U dbadmin -d scott -f apg_west_export.sql --schema-only

pg_dump -p 5432 testdb -f export.sql -a                      # data only

pg_dump -p 5436 testdb -f user_export.sql -n scott

pg_dump -p 5432 testdb -f emp_export.sql -t emp -v

pg_dump -h localhost -p 5454 U edb -f inserts_store.sql --inserts estore

pg_dump -Fc --no-acl -h localhost -U USER_NAME DB_NAME > backup.dump

pg_dump --schema-only --section=pre-data > pre-schema-ddl.sql

pg_dump --schema-only --section=post-data > post-schema-ddl.sql # with indexes and FKs etc.

pg_dump -h aurora-demo-db.cluster-czjdkvo2yd1y.us-west-2.rds.amazonaws.com -p1526 -U dbadmin -d scott -f scott.employee_old_export.sql --table scott.employee_old

pg_dump -a -t my_table my_db | psql target_db # copy table data from one db to another

pg_dump -a -t employee -h aurora-demo-db.cluster-czjdkvo2yd1y.us-west-2.rds.amazonaws.com -p1526 -U dbadmin -d scott | psql -h aurora-demo-db.cluster-czjdkvo2yd1y.us-west-2.rds.amazonaws.com -p1526 -U dbadmin -d scott

nohup pg_dump -a -t employee_part1 -h aurora-demo-db.cluster-czjdkvo2yd1y.us-west-2.rds.amazonaws.com -p1526 -U dbadmin -d scott | psql -h aurora-demo-db.cluster-czjdkvo2yd1y.us-west-2.rds.amazonaws.com -p1526 -U dbadmin -d scott &

pg_dump -h publisher_server_ip -p 5432 -d percona -Fc -s -U postgres | pg_restore -d percona -h subscriber_node_ip -p 5432 -U postgres

pg_dump --help

Usage:

  pg_dump [OPTION]... [DBNAME]

General options:

  -f, --file=FILENAME          output file or directory name

  -F, --format=c|d|t|p         output file format (custom, directory, tar, plain text (default))

  -j, --jobs=NUM               use this many parallel jobs to dump

  -v, --verbose                verbose mode

  -V, --version                output version information, then exit

  -Z, --compress=0-9           compression level for compressed formats

  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock

  --no-sync                    do not wait for changes to be written safely to disk

  -?, --help                   show this help, then exit

Options controlling the output content:

  -a, --data-only              dump only the data, not the schema

  -b, --blobs                  include large objects in dump

  -B, --no-blobs               exclude large objects in dump

  -c, --clean                  clean (drop) database objects before recreating

  -C, --create                 include commands to create database in dump

  -e, --extension=PATTERN      dump the specified extension(s) only

  -E, --encoding=ENCODING      dump the data in encoding ENCODING

  -n, --schema=PATTERN         dump the specified schema(s) only

  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)

  -O, --no-owner               skip restoration of object ownership in plain-text format

  -s, --schema-only            dump only the schema, no data

  -S, --superuser=NAME         superuser user name to use in plain-text format

  -t, --table=PATTERN          dump the specified table(s) only

  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)

  -x, --no-privileges          do not dump privileges (grant/revoke)

  --binary-upgrade             for use by upgrade utilities only

  --column-inserts             dump data as INSERT commands with column names

  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting

  --disable-triggers           disable triggers during data-only restore

  --enable-row-security        enable row security (dump only content user has access to)

  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)

  --extra-float-digits=NUM     override default setting for extra_float_digits

  --if-exists                  use IF EXISTS when dropping objects

  --include-foreign-data=PATTERN include data of foreign tables on foreign servers matching PATTERN

  --inserts                    dump data as INSERT commands, rather than COPY

  --load-via-partition-root    load partitions via the root table

  --no-comments                do not dump comments

  --no-publications            do not dump publications

  --no-security-labels         do not dump security label assignments

  --no-subscriptions           do not dump subscriptions

  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs

  --no-tablespaces             do not dump tablespace assignments

  --no-toast-compression       do not dump TOAST compression methods

  --no-unlogged-table-data     do not dump unlogged table data

  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands

  --quote-all-identifiers      quote all identifiers, even if not key words

  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts

  --section=SECTION            dump named section (pre-data, data, or post-data)

  --serializable-deferrable    wait until the dump can run without anomalies

  --snapshot=SNAPSHOT          use given snapshot for the dump

  --strict-names               require table and/or schema include patterns to match at least one entity each

  --use-set-session-authorization  use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership

Connection options:

  -d, --dbname=DBNAME      database to dump

  -h, --host=HOSTNAME      database server host or socket directory

  -p, --port=PORT          database server port number

  -U, --username=NAME      connect as specified database user

  -w, --no-password        never prompt for password

  -W, --password           force password prompt (should happen automatically)

  --role=ROLENAME          do SET ROLE before dump

Related PostgreSQL Articles: psql utility   PostgreSQL/Aurora Postgres DBA Interview Questions Part2

Comparison of Oracle PostgreSQL features

Features/Commands comparison between Oracle and PostgreSQL

Oracle Postgres

init.ora postgres.conf

SGA shared_buffers

PGA work_mem

Online Redo log         WAL (Write Ahead Log)

Undo MVCC

Row-chaining         TOAST

Table/Index Relation

Row Tuple

Column Attribute

Data Block Page (on the disk)

Page Buffer (in memory)

Autocommit Off Autocommit On

shutdown abort stop immediate

shut immediate stop fast

shutdown normal         stop smart

Related Oracle Articles: 

pg_basebackup PostgreSQL tool

PostgreSQL pg_basebackup utility

pg_basebackup # used to take base backup of running PostgreSQL database cluster

pg_basebackup --help

pg_basebackup --version

pg_basebackup -h localhost -p 5454 -D /data/postgres/12/5454

pg_basebackup --host localhost --pgdata /usr/postgres/backups -U postgres

pg_basebackup -Fp -D - | gzip > ~/backup.tar.gz

pg_basebackup -Ft -D /data/postgres/9.6/5311

pg_basebackup -U postgres -D /data/backup/12 -Ft -z -P -Xs

pg_basebackup --help

Usage:

  pg_basebackup [OPTION]...

Options controlling the output:

  -D, --pgdata=DIRECTORY receive base backup into directory

  -F, --format=p|t        output format (plain (default), tar)

  -r, --max-rate=RATE    maximum transfer rate to transfer data directory (in kB/s, or use suffix "k" or "M")

  -R, --write-recovery-conf write configuration for replication

  -t, --target=TARGET[:DETAIL]    backup target (if other than client)

  -T, --tablespace-mapping=OLDDIR=NEWDIR  relocate tablespace in OLDDIR to NEWDIR

      --waldir=WALDIR    location for the write-ahead log directory

  -X, --wal-method=none|fetch|stream include required WAL files with specified method

  -z, --gzip              compress tar output

  -Z, --compress=[{client|server}-]METHOD[:DETAIL] compress on client or server as specified

  -Z, --compress=none    do not compress tar output

General options:

  -c, --checkpoint=fast|spread set fast or spread checkpointing

  -C, --create-slot      create replication slot

  -l, --label=LABEL      set backup label

  -n, --no-clean          do not clean up after errors

  -N, --no-sync          do not wait for changes to be written safely to disk

  -P, --progress          show progress information

  -S, --slot=SLOTNAME    replication slot to use

  -v, --verbose          output verbose messages

  -V, --version          output version information, then exit

      --manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE use algorithm for manifest checksums

      --manifest-force-encode hex encode all file names in manifest

      --no-estimate-size do not estimate backup size in server side

      --no-manifest      suppress generation of backup manifest

      --no-slot          prevent creation of temporary replication slot

      --no-verify-checksums do not verify checksums

  -?, --help              show this help, then exit

Connection options:

  -d, --dbname=CONNSTR    connection string

  -h, --host=HOSTNAME    database server host or socket directory

  -p, --port=PORT        database server port number

  -s, --status-interval=INTERVAL time between status packets sent to server (in seconds)

  -U, --username=NAME    connect as specified database user

  -w, --no-password      never prompt for password

  -W, --password          force password prompt (should happen automatically)

Related Postgres Articles: PostgreSQL pg_dump utility

pg_dumpall utility in PostgreSQL

pg_dumpall - PostgreSQL command line tool

pg_dumpall # to take backup of all databases in PostgreSQL cluster

pg_dumpall --help

pg_dumpall -V

pg_dumpall > /home/postgres/backup

pg_dumpall -U postgres > c:\pgbackup\all.sql

pg_dumpall -p 5454 -U postgres -f full_dump_data.sql

pg_dumpall --schema-only > c:\pgdump\definition_only.sql

pg_dumpall --s > /opt/pgdump/definition_only.sql

pg_dumpall --a > /opt/pgdump/data_only.sql

pg_dumpall --roles-only --verbose > c:\pgdump\all_roles.sql

pg_dumpall --tablespaces-only > c:\pgdump\all_roles.sql

pg_dumpall | gzip /opt/backups/all_databases_backup.gz

pg_dumpall | split -b 1m - /usr/backups/cluster_backup

pg_dumpall -g --no-role-password > global_objects.sql

pg_dumpall --help

Usage:

  pg_dumpall [OPTION]...

General options:

  -f, --file=FILENAME          output file name

  -v, --verbose                verbose mode

  -V, --version                output version information, then exit

  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock

  -?, --help                    show this help, then exit

Options controlling the output content:

  -a, --data-only              dump only the data, not the schema

  -c, --clean                  clean (drop) databases before recreating

  -E, --encoding=ENCODING    dump the data in encoding ENCODING

  -g, --globals-only            dump only global objects, no databases

  -O, --no-owner                skip restoration of object ownership

  -r, --roles-only              dump only roles, no databases or tablespaces

  -s, --schema-only            dump only the schema, no data

  -S, --superuser=NAME          superuser user name to use in the dump

  -t, --tablespaces-only        dump only tablespaces, no databases or roles

  -x, --no-privileges          do not dump privileges (grant/revoke)

  --binary-upgrade              for use by upgrade utilities only

  --column-inserts              dump data as INSERT commands with column names

  --disable-dollar-quoting      disable dollar quoting, use SQL standard quoting

  --disable-triggers            disable triggers during data-only restore

  --exclude-database=PATTERN   exclude databases whose name matches PATTERN

  --extra-float-digits=NUM      override default setting for extra_float_digits

  --if-exists                  use IF EXISTS when dropping objects

  --inserts                    dump data as INSERT commands, rather than COPY

  --load-via-partition-root    load partitions via the root table

  --no-comments                do not dump comments

  --no-publications            do not dump publications

  --no-role-passwords          do not dump passwords for roles

  --no-security-labels          do not dump security label assignments

  --no-subscriptions            do not dump subscriptions

  --no-sync                    do not wait for changes to be written safely to disk

  --no-table-access-method     do not dump table access methods

  --no-tablespaces              do not dump tablespace assignments

  --no-toast-compression        do not dump TOAST compression methods

  --no-unlogged-table-data      do not dump unlogged table data

  --on-conflict-do-nothing      add ON CONFLICT DO NOTHING to INSERT commands

  --quote-all-identifiers      quote all identifiers, even if not key words

  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts

  --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership

Connection options:

  -d, --dbname=CONNSTR      connect using connection string

  -h, --host=HOSTNAME      database server host or socket directory

  -l, --database=DBNAME    alternative default database

  -p, --port=PORT          database server port number

  -U, --username=NAME      connect as specified database user

  -w, --no-password        never prompt for password

  -W, --password            force password prompt (should happen automatically)

  --role=ROLENAME          do SET ROLE before dump

Related PostgreSQL Articles: PostgreSQL pg_dump tool    vacuumdb utility commands

How to start and stop PostgreSQL server - pg_ctl tool

Postgres pg_ctl commands

pg_ctl -- initialize, start, stop, or control a PostgreSQL server

pg_ctl [ status | start | stop | restart | reload | init ] [-U username] [-P password] [--help]

pg_ctl --version

pg_ctl --help

Usage:

  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]

  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s] [-o OPTIONS] [-p PATH] [-c]

  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]

  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s] [-o OPTIONS] [-c]

  pg_ctl reload     [-D DATADIR] [-s]

  pg_ctl status     [-D DATADIR]

  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]

  pg_ctl logrotate  [-D DATADIR] [-s]

  pg_ctl kill       SIGNALNAME PID

Common options:

  -D, --pgdata=DATADIR   location of the database storage area

  -s, --silent            only print errors, no informational messages

  -t, --timeout=SECS  seconds to wait when using -w option

  -V, --version          output version information, then exit

  -w, --wait              wait until operation completes (default)

  -W, --no-wait          do not wait until operation completes

  -?, --help              show this help, then exit

If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:

  -c, --core-files        allow postgres to produce core files

  -l, --log=FILENAME      write (or append) server log to FILENAME

  -o, --options=OPTIONS  command line options to pass to postgres (PostgreSQL server executable) or initdb

  -p PATH-TO-POSTGRES    normally not necessary

Allowed signal names for kill:

  ABRT HUP INT KILL QUIT TERM USR1 USR2

pg_ctl init[db] [-s] [-D datadir] [-o initdb-options]
pg_ctl -D /usr/local/pgsql/data initdb

pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]
pg_ctl start
pg_ctl -w start
pg_ctl -D /data/postgres/13.6/5304 start
pg_ctl start -l /data/logs
pg_ctl -o "-F -p 5433" start

pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
pg_ctl stop

pg_ctl stop -D /Library/PostgreSQL/14.5/data

pg_ctl --pgdata /usr/local/var/postgres stop

pg_ctl stop -m fast
pg_ctl stop -m smart
pg_ctl stop -m immediate
pg_ctl stop -w -l /home/pg/logs
pg_ctl -D /usr/local/pgsql/data stop -m fast

pg_ctl status [-D datadir]
pg_ctl status
pg_ctl -D /data/postgres/12.7/5301 status
pg_ctl -D /opt/data/pg9-9.5.0/data/5361 status 

pg_ctl reload [-s] [-D datadir]
pg_ctl reload

pg_ctl -D /u01/data/pg/15.3/5310 reload

pg_ctl promote [-s] [-D datadir]
pg_ctl -D /data/postgresql/14/5308 promote                --Failover to standby

pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]
pg_ctl restart
pg_ctl -w restart
pg_ctl -o "-F -p 5433" restart
pg_ctl -D /usr/local/pgsql/data restart

pg_ctl kill signal_name process_id
pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options]
pg_ctl unregister [-N servicename]


Related PostgreSQL Articles:  PostgreSQL psql utility  PostgreSQL DBA Interview Questions and answers