https://www.postgresql.org/download/linux/ubuntu/

The installation above will create a default cluster for you.
If you wish to create a new one run the following command as postgres …

pg_createcluster -d /var/lib/postgresql/14/main 14 main

Binaries:        /usr/lib/postgresql/<version>/bin
Data Directory:  /var/lib/postgresql/<version>/main
Config Files:    /etc/postgresql/<version>/main

Symbolic links for some binaries will be placed in /usr/bin.
These link to a script “/usr/share/postgresql-common/pg_wrapper

CREATE ROLE ian LOGIN PASSWORD '<password>';
CREATE ROLE ian SUPERUSER PASSWORD '<password>';
ALTER ROLE ian PASSWORD '<password>';

CREATE ROLE doob LOGIN PASSWORD '<password>';
REASSIGN OWNED BY ian TO doob;
DROP OWNED BY ian;
DROP ROLE ian;

Options: S = Show system objects, + = Show additional details

psql -U <username> -d <database> -h <hostname> -p <port> -c <command> -f <sql_file>

\d  <name>	 Describe Object
\dt		 List Tables in public schema
\dt cron.*       List Tables in cron schema
\dv		 List Views
\ds		 List Sequences
\di		 List Indexes
\dm              List Materialised Views
\dn+             List Schemas and Access Privileges
\dp		 List Object Access Privileges
\db 		 List Tablespaces
\det             List Foreign Tables
\da              List Aggregates
\dRp             List Publishers (Logical Replication)
\dRs             List Subscribers (Logical Replication)
\l		 List Databases

\e		 Edit the query buffer
\p		 Display contents of the query buffer
\r		 Reset the query buffer
\s		 Display command-line history
\w  FILE	 Write query buffer to file
\i  FILE	 Execute commands from file
\o  FILE	 Send all query results to file
\q		 Quit psql
\g  FILE	 Execute query and send results to file
\watch 30        Automatically execute query every 30 seconds
\timing          Display time SQL query takes to execute

\c		 Connect to new database (default is postgres)
\cd DIR		 Change working directory
\conninfo        Information about current database connection
\!  COMMAND	 Execute command in shell

\a		 Toggle between aligned (default) and unaligned mode
\x  ON/OFF/AUTO	 Toggle expanded output (default off)

-- User Data
SELECT USENAME, USESUPER FROM PG_USER;
-- User Sessions
SELECT USENAME, DATNAME, PID, QUERY FROM PG_STAT_ACTIVITY;
-- Config Settings (Same as SHOW ALL)
SELECT NAME, SETTING, SHORT_DESC FROM PG_SETTINGS;
-- Database Information
SELECT OID, DATNAME, DATCTYPE, DATTABLESPACE FROM PG_DATABASE;
-- PostgreSQL Server Version
SELECT VERSION();
-- Show Current User
SELECT CURRENT_USER;
-- Show Current Database
SELECT CURRENT_DATABASE;

SHOW HBA_FILE;
SHOW CONFIG_FILE;
SHOW DATA_DIRECTORY;

SELECT * FROM PG_FILE_SETTINGS;
SELECT * FROM PG_HBA_FILE_RULES;

PGDATA          Cluster Data Directory
PGDATABASE	Database Name Parameter
PGUSER		User Connection Parameter
PGPASSWORD	User Password Parameter

-- Table Sizes
SELECT PG_SIZE_PRETTY (PG_TABLE_SIZE('WORKPIECE_HISTORY'));
SELECT PG_SIZE_PRETTY (PG_INDEXES_SIZE('WORKPIECE_HISTORY'));
SELECT PG_SIZE_PRETTY (PG_TOTAL_RELATION_SIZE('WORKPIECE_HISTORY'));
SELECT PG_SIZE_PRETTY (PG_DATABASE_SIZE('IC1'));
 
-- Partitioned / Inherited Tables
SELECT pi.inhparent::regclass AS parent_table_name, 
       pg_size_pretty(SUM(pg_total_relation_size(psu.relid))) AS total,
       pg_size_pretty(SUM(pg_relation_size(psu.relid))) AS internal,
       pg_size_pretty(SUM(pg_table_size(psu.relid) - pg_relation_size(psu.relid))) AS external, -- toast
       pg_size_pretty(SUM(pg_indexes_size(psu.relid))) AS indexes
  FROM pg_catalog.pg_statio_user_tables psu
  JOIN pg_class pc ON psu.relname = pc.relname
  JOIN pg_database pd ON pc.relowner = pd.datdba
  JOIN pg_inherits pi ON pi.inhrelid = pc.oid
 WHERE pd.datname = 'postgres'
GROUP BY pi.inhparent
ORDER BY SUM(pg_total_relation_size(psu.relid)) DESC;
 
SELECT RELNAME, PG_SIZE_PRETTY (PG_TABLE_SIZE(RELID)) AS TABLE_SIZE
  FROM PG_STATIO_USER_TABLES
ORDER BY PG_TABLE_SIZE(RELID) DESC;
 
-- User Sessions
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE pid <> pg_backend_pid()
  AND state != 'idle';
 
-- User Permissions
SELECT grantee, table_catalog, table_schema, TABLE_NAME,
       string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE grantee != 'postgres'
GROUP BY grantee, table_catalog, table_schema, TABLE_NAME;
 
-- Database Locks  
SELECT pg_class.relname, pg_locks.transactionid, pg_locks.mode, pg_locks.granted AS "g",
       substr(pg_stat_activity.query,1,30), pg_stat_activity.query_start,
       age(now(),pg_stat_activity.query_start) AS "age", pg_stat_activity.pid
  FROM pg_stat_activity, pg_locks
  LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid=pg_stat_activity.pid
  AND pg_stat_activity.pid <> pg_backend_pid()
ORDER BY query_start;
 
-- Blocking Locks
SELECT pid, usename, 
       pg_blocking_pids(pid) AS blocked_by, 
       query AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
 
-- Blocking Locks showing both queries
SELECT activity.pid, activity.usename, activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
 
-- Terminate User Sessions Idle for longer than an hour
SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
 WHERE pid <> pg_backend_pid()
   AND state = 'idle'
   AND state_change < CURRENT_TIMESTAMP - INTERVAL '60' MINUTE;
 
-- Populate table with random Ids
CREATE TABLE T1 (id INTEGER);
INSERT INTO T1 SELECT * FROM generate_series(1, 500) ORDER BY random();
 
-- Reload postgresql.conf and pg_hba.conf Files
SELECT pg_reload_conf();

There is no command to disable a foreign key constraint.
Instead you need to temporarily disable all triggers on the table.

ALTER TABLE cities DISABLE TRIGGER ALL;
INSERT INTO cities VALUES ...
ALTER TABLE cities ENABLE TRIGGER ALL;

-- For Server
ALTER SYSTEM SET idle_in_transaction_session_timeout = '6h';
-- For Database
ALTER DATABASE doob SET idle_in_transaction_session_timeout = '6h';
-- For User
ALTER ROLE ian SET idle_in_transaction_session_timeout = '6h';

pg_dump doob > doob_db.sql
pg_dump -t t1 doob > t1_table.sql
pg_dump -T t1 doob > doob_db_exc_t1.sql
pg_dump --exclude-table-data t1 doob > doob_db_exc_t1_data.sql
pg_dump --exclude-table-data 'fkpv*' --exclude-table-data 'fkti*' doob > doob_db.sql
pg_dump -s doob > doob_all_schema_only.sql
pg_dump -s -n warehouse doob > doob_warehouse_schema_only.sql

These formats can be used by pg_restore to restore the data.

-- Custom Format
pg_dump -Fc doob > db.dump
-- Directory Format
pg_dump -Fd doob -f dumpdir

SELECT pg_start_backup('Backup_Name', true);
tar cfP /home/postgres/db_file_backup.tar $PGDATA
SELECT pg_stop_backup();

select pg_create_restore_point('Pre_Upgrade');

psql -U username -f backupfile.sql
psql -U username --set ON_ERROR_STOP=on -f backupfile.sql

-- You cannot use pg_restore to restore SQL dump files
pg_restore -U username --create --verbose ic1 /postgres/backup/backupfile.dmp

Log files written to $PGDATA/pg_xlog
mkdir $PGDATA/pg_log_archive

wal_level = 'hot_standby' OR 'replica' from version 9.6
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/pg_log_archive/%f && cp %p /var/lib/pgsql/pg_log_archive/%f'
archive_timeout = 600 (Switch logs every 10 minutes)

select pg_switch_wal(); (Manually create WAL).

Perform File System Backup of $PGDATA (See Above)

service postgresql stop
$PGDATA/recovery.conf
service postgresql start
The server will go into recovery mode and proceed to read through the archived WAL files it needs.
When complete the recovery.conf file will be renamed to recovery.done

systemctl stop postgresql
# If you have more than 1 version of PostgreSQL installed ...
systemctl stop postgresql@13-main

export PGDATA=/postgres/data
pg_ctl start|stop|restart|status
service postgresql status