PostgreSQL

System Administration Functions ^ref

List current queries:

SELECT * FROM pg_stat_activity;

Cancel backend's current query:

SELECT pg_cancel_backend(pid);

User Management

Creating superuser:

CREATE ROLE foo PASSWORD 'secret' SUPERUSER CREATEDB INHERIT LOGIN;

Granting privileges on database to specific user:

GRANT ALL PRIVILEGES ON DATABASE dbname TO uname;

Changing database owner:

ALTER DATABASE dbname OWNER TO uname;

Permit user login:

ALTER ROLE uname WITH LOGIN;

Change user password:

ALTER ROLE uname WITH PASSWORD 'secret';

Create group and user with read only access to a database:

-- a group
CREATE ROLE readonly;

-- grant group access to existing tables
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- grant group access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- create a user
CREATE USER foo WITH PASSWORD 'secret';
GRANT readonly TO foo;

Rename user:

ALTER ROLE namea RENAME TO nameb;

Backup & Restore

Create a copy of a database:

CREATE DATABASE copy WITH TEMPLATE orig OWNER user;

Plain text backup & restore (data only):

pg_dump -U user -h host --data-only basename > basename.sql
psql -U user -h host basename < basename.sql

Custom compressed backup & restore (full, as postgres user):

pg_dump -Fc -f basename.dump basename
pg_restore -C -d database_name basename.dump

Client authentication ^ref

Allow connecting from any address:

# TYPE  DATABASE  USER  ADDRESS    METHOD
host    all       all   0.0.0.0/0  scram-sha-256
host    all       all   ::0/0      scram-sha-256

Maintenance

Reindex

Note, that sometimes should be quoted even if it does not contain spaces or other special symbols).

REINDEX (VERBOSE) DATABASE <dbname>;

Vacuum

VACUUM (VERBOSE, ANALYZE);

or compatibility syntax:

VACUUM VERBOSE ANALYZE;

Analyze

ANALYZE (VERBOSE);

or compatibility syntax:

ANALYZE VERBOSE;

Client (psql)

Turn off pager:

\pset pager 0

Windows

Initialize database cluster:

initdb.exe --locale=Lithuanian_Lithuania --encoding=UTF-8 -U $USER -W -D "C:/data"

pg_ctl.exe register -N "PostgreSQL ($VERSION)" -U "NT AUTHORITY\SYSTEM" -D "C:/data" -w

Misc

Export table data to csv file:

COPY (SELECT * FROM <table>) TO '/tmp/out.csv' WITH CSV DELIMITER ',' HEADER

Import table data from csv file:

COPY <table> (<column a>, <column b>) FROM '/tmp/filename.csv' CSV DELIMITER ',' HEADER

List top 10 largest tables:

query

select schemaname as table_schema,
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
      as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
         pg_relation_size(relid) desc
limit 10;

Version specific notes

15

Default public schema permissions ^see

View current permissions: \dn+.

Convert to new public permission scheme (when upgrading from 14-):

ALTER SCHEMA public OWNER TO pg_database_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Revert to old public permission scheme:

ALTER SCHEMA public OWNER TO postgres;
GRANT CREATE ON SCHEMA public TO PUBLIC;

Resources

Official documentation