PostgreSQL Basics

This guide covers the everyday PostgreSQL administration tasks that map to the MySQL basics guide: connect, create databases and roles, grant access, inspect activity, maintain tables, and make restorable backups.

Official PostgreSQL references:

• Server administration
• psql
• Roles
• Privileges
• Backup and restore

Connect with psql

On a Linux host, the local postgres operating-system account can normally connect through the Unix socket:

sudo -u postgres psql

Connect to a local or remote database as a named role. -W prompts for a password; do not put passwords in shell command lines.

psql -h db.example.com -p 5432 -U app_owner -d app_db -W

Useful psql meta-commands:

\conninfo          Show the current connection
\l+                List databases
\du+               List roles and attributes
\dn+               List schemas
\dt                List tables in the current database
\d+ app.orders     Describe a table
\dp app.orders     Show table privileges
\q                 Quit psql

Create a database and owner role

PostgreSQL uses roles for both users and groups. A role with LOGIN can connect; a role with NOLOGIN is useful for grouping privileges.

CREATE ROLE app_owner LOGIN;
\password app_owner

CREATE DATABASE app_db
  OWNER app_owner
  ENCODING 'UTF8'
  TEMPLATE template0;

The \password command prompts interactively and avoids placing a password in SQL history or shell history. Set an expiry for temporary access:

ALTER ROLE contractor LOGIN VALID UNTIL '2026-12-31 23:59:59+00';
ALTER ROLE contractor VALID UNTIL 'infinity';

Deleting a database is permanent. Connect to another database first and ensure no application still uses it:

DROP DATABASE app_db;

Create application roles and grants

Use an owner role for schema changes and separate login roles for applications, automation, and reporting. Grant roles to users instead of duplicating privileges for every login.

CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE reporting_user LOGIN;
GRANT app_readonly TO reporting_user;

CREATE SCHEMA app AUTHORIZATION app_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Run the following as app_owner after connecting to app_db. Existing tables need explicit grants; default privileges apply only to objects created in the future by the named owner.

GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_readonly;

Create a read-write group when an application must change data. Do not give it ownership or database superuser rights unless that is explicitly required.

CREATE ROLE app_readwrite NOLOGIN;
GRANT USAGE ON SCHEMA app TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;
GRANT app_readwrite TO application_user;

Revoke access when it is no longer needed:

REVOKE app_readonly FROM reporting_user;
REVOKE ALL ON ALL TABLES IN SCHEMA app FROM app_readonly;
DROP ROLE reporting_user;

Inspect databases, roles, and privileges

Run these in psql to see database sizes, roles, and access controls:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles
ORDER BY rolname;

SELECT current_database(), current_user, session_user;

Check a table's grants with either \dp app.orders or the information schema:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app' AND table_name = 'orders'
ORDER BY grantee, privilege_type;

Create and inspect tables

Connect as the owner and create objects in the dedicated application schema:

CREATE TABLE app.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_email text NOT NULL,
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO app.orders (customer_email, total_cents)
VALUES ('[email protected]', 1999);

SELECT * FROM app.orders;

Use a transaction for related changes so they either all succeed or all roll back:

BEGIN;
UPDATE app.orders SET total_cents = 2499 WHERE id = 1;
COMMIT;
-- Use ROLLBACK instead of COMMIT to discard the changes.

TRUNCATE removes all rows quickly and is not a substitute for a filtered DELETE:

TRUNCATE TABLE app.orders;

Monitor connections and long-running queries

pg_stat_activity shows one row per server process. The query text and other-session details can be restricted for non-superusers.

SHOW max_connections;

SELECT count(*) AS connections
FROM pg_stat_activity;

SELECT pid, usename, datname, application_name, client_addr,
       state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

Find sessions waiting on locks:

SELECT pid, usename, datname, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

SELECT locktype, relation::regclass, mode, pid, granted
FROM pg_locks
WHERE NOT granted;

Cancel a running query before terminating its connection. Both actions affect applications, so confirm the PID and owner first.

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Find the largest tables and indexes

SELECT schemaname,
       relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Vacuum, analyze, and reindex

PostgreSQL's autovacuum daemon should remain enabled for normal workloads. Use manual maintenance to address a specific table or after large data changes.

VACUUM (ANALYZE) app.orders;
ANALYZE app.orders;
REINDEX TABLE app.orders;

Avoid VACUUM FULL during normal operation. It requires an ACCESS EXCLUSIVE lock and rewrites the table, blocking other use of it while it runs.

VACUUM FULL app.orders;

Back up and restore

Use the custom dump format for regular single-database backups. It supports selective and parallel restore through pg_restore.

pg_dump -Fc -d app_db -f app_db-$(date +%Y%m%d).dump
createdb -T template0 app_db_restore
pg_restore --clean --if-exists --no-owner -d app_db_restore app_db-20260731.dump

For a plain SQL dump, stop on the first restore error:

pg_dump -d app_db > app_db.sql
createdb -T template0 app_db_restore
psql -X --set ON_ERROR_STOP=on -d app_db_restore < app_db.sql

pg_dump backs up one database and does not include cluster-wide roles or tablespaces. Back up those definitions separately:

pg_dumpall --globals-only > postgres-globals-$(date +%Y%m%d).sql
psql -X -f postgres-globals-20260731.sql postgres

Test restores on another host or an isolated database before trusting any backup process.

Configuration and authentication

Find the active configuration files before changing them:

SHOW config_file;
SHOW hba_file;
SHOW data_directory;
SHOW listen_addresses;

postgresql.conf controls server settings such as connection limits, memory, logging, and replication. pg_hba.conf controls which clients and roles can authenticate and by which method. Prefer scram-sha-256 password authentication for password-based access and restrict network rules to trusted sources.

After a valid configuration change that only needs reload, run:

SELECT pg_reload_conf();

Some settings require a server restart. Check the official configuration reference and test changes outside production first.

Daily administrator checklist

sudo -u postgres psql -c 'SELECT now(), version();'
sudo -u postgres psql -c 'SELECT datname, numbackends FROM pg_stat_database ORDER BY numbackends DESC;'
sudo -u postgres psql -c 'SELECT pid, usename, state, query FROM pg_stat_activity WHERE state <> '\''idle'\'';'
sudo -u postgres pg_dump -Fc -d app_db -f /backups/app_db-$(date +%Y%m%d).dump

Review failed backups, unexpected connection growth, lock waits, autovacuum warnings, and disk usage before they become outages.