Postgres commands

Only for absolute n00bs (like me).

su - postgres
psql
\l # lists all dbs
\c dbname # connects to dbname
\dt # show tables

Create a new db:

CREATE DATABASE synapse
 ENCODING 'UTF8'
 LC_COLLATE='C'
 LC_CTYPE='C'
 template=template0
 OWNER synapse_user;

Dump a db to file:

pg_dump -U postgres -W -F t mydb > /tmp/mydb.tar

W asks for password, F output file format which is 't' here, for tar

Dump all databases to file:

pg_dumpall -U postgres > /tmp/alldbs.sql

Show running processes

SELECT * FROM pg_stat_activity WHERE state = 'active';

Show vacuum progress:

select * from pg_stat_progress_vacuum;

Show size of a certain db:

SELECT
pg_size_pretty (
    pg_database_size ('mydatabase')
);

Kill process gracefully

SELECT pg_cancel_backend(\<pid of the process\>)