posgres.md

Postgres 9.2 - Install, Setup and Quick Reference

Set the default template to UTF8

$ su postgres
$ psql -U postgres
psql (9.2.4)
Type "help" for help.

Quick Reference

$ su - postgres
$ psql
postgres=# CREATE USER develop WITH PASSWORD 'newpassword';
postgres=# CREATE DATABASE development WITH OWNER develop ENCODING 'UTF8';
postgres=# CREATE DATABASE staging WITH OWNER develop ENCODING 'UTF8';
postgres=# CREATE DATABASE production WITH OWNER develop ENCODING 'UTF8';
postgres=# \q

$ psql -d development -U develop
development=# \q

List all databases
postgres=# \list

Drop Database
postgres=# DROP DATABASE databasename;

Hacks

edit your /etc/postgresql/9.4/main/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     md5

Reference

PostgreSQL 9.2.4 Documentation
Debian Postgers wiki

postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';
UPDATE 1
postgres=# \c template0
You are now connected to database "template0".
template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
UPDATE 1
template0=# drop database template1;
DROP DATABASE
template0=# create database template1 with template = template0 encoding = 'UTF8';
CREATE DATABASE
template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
UPDATE 1
template0=# \c template1
You are now connected to database "template1".
template1=# update pg_database set datallowconn = FALSE where datname = 'template0';
UPDATE 1