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.
Ends psql sessions.
Lists database tables and sequences.
List of roles
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.