Andrew Mercer
on this page

PostgreSQL Administration

Day-to-day admin commands. See Overview for the concepts referenced below (roles vs. users, pg_hba.conf match ordering).

Granting Access

Connect as the superuser (on most distro packages, the postgres OS user maps to the postgres PostgreSQL role via peer auth):

sudo -u postgres psql

Create a database, a role, and grant privileges:

CREATE DATABASE [ database_name ];
CREATE USER [ user_name ] WITH ENCRYPTED PASSWORD '[ encrypted_password ]';
GRANT ALL PRIVILEGES ON DATABASE [ database_name ] TO [ user_name ];

Note that GRANT ALL PRIVILEGES ON DATABASE only covers database-level privileges (connect, create schema, temp tables) — it does not grant access to tables within the database. For that you also need, connected to the target database:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [ user_name ];
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO [ user_name ];

And for objects created after the grant, set a default privilege so new tables aren't silently locked out:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO [ user_name ];

Local trust auth (development only)

# /var/lib/pgsql/[ pgsql_version ]/data/pg_hba.conf
host    all             all             127.0.0.1/32            trust

trust means no password check at all for connections matching this rule — fine for a throwaway local dev instance, a real liability anywhere reachable by anyone else. Prefer scram-sha-256 (or md5 on older versions) even for local connections outside a pure dev box.

systemctl restart postgresql.service

Create / Delete / Access Databases

createdb database_name
dropdb database_name
psql database_name

database_name=# \q

Switch Databases

postgres=# \connect <database_name>

or the short form:

postgres=# \c <database_name>

Remember this opens a new connection — you're not just changing context within the same session, so anything set with SET on the old connection doesn't carry over.

psql Meta-Command Cheat Sheet

\dt          list tables in the current database
\d+ <table>  show table schema, indexes, and constraints (MySQL 'desc <table>' equivalent)
\dn          list schemas
\du          list roles
\l           list databases
\dv          list views
\di          list indexes
\x           toggle expanded output (one column per line — useful for wide rows)
\timing      toggle query timing display
\q           quit

Example, listing tables then inspecting one:

postgres=# \dt
postgres=# \d+ spaces

Querying Tables

Ordinary SQL — nothing psql-specific here:

SELECT * FROM spaces WHERE spacename = 'OutReach Experience Manager';

Backup and Restore

pg_dump database_name > dump.sql

psql database_name < dump.sql

For anything beyond a small ad-hoc dump, prefer the custom format (-Fc) over plain SQL — it's compressed, supports parallel restore, and lets you select individual tables/schemas at restore time without re-running the whole dump:

pg_dump -Fc database_name > dump.custom
pg_restore -d database_name --clean --if-exists dump.custom

For a whole cluster (all databases, roles, tablespaces), use pg_dumpall instead — pg_dump never captures roles or cluster-wide objects on its own:

pg_dumpall > cluster_dump.sql

A logical dump is not the same thing as the physical base backups used for replication and point-in-time recovery — see Replication: Base Backups if you need PITR rather than a point-in-time logical snapshot.

Allow Remote Connections

Configure listen_addresses

# /var/lib/pgsql/data/postgresql.conf
listen_addresses = '0.0.0.0'

0.0.0.0 listens on every interface. Prefer binding to a specific address if the host has more than one NIC and you don't actually want it reachable from all of them.

Configure pg_hba.conf

# /var/lib/pgsql/data/pg_hba.conf
host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

0.0.0.0/0 and ::/0 mean "anyone" — scope this to your actual subnet in anything beyond a lab/test box (e.g. 10.0.0.0/24 instead of 0.0.0.0/0). Also worth switching md5 to scram-sha-256 if your client library supports it; md5 auth is a weaker hash and being phased out as the recommended default.

Restart postgresql.service

systemctl restart postgresql.service

Open firewall port

firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --complete-reload
  • Replication — pg_hba.conf also needs a replication line for standbys, separate from normal client access
  • HA with Citus — coordinator/worker nodes need the same remote-access + firewall treatment as above, applied to each container/host