PostgreSQL Admin Cheatsheet

psql -U[username] [database];

Start a psql session to a database. You might be asked for a password, depending on how you have cnfigured the server access. You don't need to add the username, if you are using a trusted connection.

\1

Ends psql sessions.

\d

Lists database tables and sequences.

\l

Lists databases.

\du

List of roles

\?

Help

create database [database_name];

Create new database with the specified name.

drop database [database_name];

Drops the specified database

pg_dump -U[username] [database_name] | gzip > backup.gz

Backups the database to the compressed file backup.gz

gunzip -c backup.gz | psql -U[username] [database_name]

Decrompresses the backup.gz file and restores into the specified database.

SELECT * FROM pg_stat_activity;

Checks existing connections to all the databases in the server.

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND procpid <> pg_backend_pid();

Drops all connections to the specified database, except yours. Valid for PostgreSQL versions up to 9.1.

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND pid <> pg_backend_pid();

Same as above for PostgreSQL versions from 9.2 and later.