PostgreSQL – useful stuff

(Everything below has been done on FreeBSD 10.1 with PostgreSQL 9.3)

How to connect to the PostgreSQL database:

su – psql

pgsql -U pgsql -d template1

How to list the PostgreSQL databases

\l #(that is a lowercase L not an i)

How to add a user and grant permissions to a certain PostgreSQL database:

psql -U pgsql -d template1

CREATE USER username WITH PASSWORD ‘password’;

CREATE DATABASE dbname;

GRANT ALL PRIVILEGES ON DATABASE dbname to username;

Remove database and user:

DROP DATABASE dbname;

DROP USER username;

Backup/Dump | Restore a PostgreSQL database:

dump -> pg_dump -U username dbname -f dump.sql

restore -> psql -U username -d dbname -f dump.sql