DB Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== Installation ====

=== Linux ===
[[https://www.postgresql.org/download/linux/debian/]]\\
[[https://www.postgresql.org/download/linux/redhat/]]\\

=== Create Cluster ===
The Debian or Ubuntu installation will create a default instance. \\
Create new instances using the commands below ... \\
<code bash>
# Debian / Ubuntu
pg_createcluster -d /var/lib/postgresql/17/main 17 main

# Redhat / Fedora
# Default Data Directory: /var/lib/pgsql/17/data
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
/usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/main
</code>

=== Instance Details ===
<code>
/usr/lib/postgresql/17/bin/pg_config
/usr/lib/postgresql/17/bin/pg_controldata -D $PGDATA
/usr/lib/postgresql/17/bin/pg_waldump $PGDATA/pg_wal/<WAL_FILE>
</code>

== Default File Locations ==
<code>
Binaries:        /usr/lib/postgresql/<version>/bin
Data Directory:  /var/lib/postgresql/<version>/main
Config Files:    /etc/postgresql/<version>/main
</code>
Symbolic links for some binaries will be placed in /usr/bin.\\
These link to a script "/usr/share/postgresql-common/pg_wrapper\\
==== psql command-line ====

<code>
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;
</code>

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

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

<code>
\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)</code>

==== Meta Data ====
<code SQL>
-- 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;
</code>

==== File Locations / Variables ====
<code>
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
</code>

==== Useful SQL Commands ====
<code SQL>
-- 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();

CREATE TABLE t2 (id integer, description text, updated timestamptz);
INSERT INTO t2
SELECT floor(random()*(100-1+1))+1 AS random_number,
       substr(md5(random()::text), 1, 10) AS random_string,
       now() - INTERVAL '1 year' + random() * (now() - now() - INTERVAL '1 year') AS random_timestamp
  FROM generate_series(1,10);

-- Reload postgresql.conf and pg_hba.conf Files
SELECT pg_reload_conf();
</code>

==== Foreign Keys ====
There is no command to disable a foreign key constraint.\\
Instead you need to temporarily disable all triggers on the table.\\
<code>
ALTER TABLE cities DISABLE TRIGGER ALL;
INSERT INTO cities VALUES ...
ALTER TABLE cities ENABLE TRIGGER ALL;
</code>

==== Parameter Settings ====
<code SQL>
-- 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';
</code>
^ Parameter Name ^ Description ^
| **Connections / Authentication** ||
| listen_addresses | Host Names or IP Addresses on which the server accepts connections |
| port | The port the server listens on |
| max_connections | Maximum number of concurrent connections |
| superuser_reserved_connections | Number of connections reserved for PostgreSQL superusers |
| **Resources** ||
| shared_buffers | Amount of memory database server uses |
| huge_pages | Enable use of huge memory pages |
| work_mem | Amount of memory used by complex sorts. Size is applied to each sort operation |
| maintenance_work_mem | Amount of memory used by maintenance operations such as VACUUM |
| ** Write Ahead Log ** ||
| wal_level | How much information is written to the WAL (minimal, replica, logical) |
| synchronous_commit | How much WAL processing must complete before database returns "success" |
| wal_compression | Enables compressing a full page image written to WAL |
| archive_mode | Enables the creation of external WAL files |
| archive_command | Local shell command that will archive a completed WAL file |
| archive_timeout | Force the server to switch to a new WAL file periodically |

==== Backups ====
=== PG_DUMP ===
== Script ==
<code>
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
</code>

== Other Formats ==
These formats can be used by pg_restore to restore the data.\\
<code SQL>
-- Custom Format
pg_dump -Fc doob > db.dump
-- Directory Format
pg_dump -Fd doob -f dumpdir
</code>

=== File System Backup ===
<code>SELECT pg_start_backup('Backup_Name', true);
tar cfP /home/postgres/db_file_backup.tar $PGDATA
SELECT pg_stop_backup();</code>

==== Restores ====
<code>
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</code>

==== Point in Time Recovery ====
<code>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</code>

==== Startup / Stop / Status ====
<code>
systemctl stop postgresql
# If you have more than 1 version of PostgreSQL installed ...
systemctl stop postgresql@13-main
</code>
<code>export PGDATA=/postgres/data
pg_ctl start|stop|restart|status
service postgresql status</code>