Cacher is the code snippet organizer for pro developers

Create user

CREATE USER myuser PASSWORD 'secret' CREATEDB;

Change a database role

ALTER USER myuser CREATEDB;

Create database

CREATE DATABASE mydb WITH encoding=utf8;

Get running queries order by start-time

SELECT datname, pid, client_addr, query_start, state, query
FROM pg_stat_activity
WHERE datname='db_name' and state like '%active%' ORDER BY query_start;

Cancel a backend's current query

SELECT pg_cancel_backend(PID);

Dump & Restore

$ pg_dump -h host -U user -W -d db_name -Fc -f ~/backups/db_name-`date +'%Y%m%d'`.dump
$ pg_restore -h host -U user -W -d db_name -v -Fc ~/backups/db_name-`date +'%Y%m%d'`.dump