Postgres Cheat Sheet

sudo apt-get install postgresql postgresql-contrib

sudo -u postgres psql postgres

\password postgres

Control+D

psql -U <username> -h 127.0.0.1 <database-name>

sudo -u postgres createdb mydb

CREATE USER <username> WITH PASSWORD '<password>';

\du

GRANT ALL PRIVILEGES ON DATABASE "<databasename>" to <username>;

sudo -u postgres dropdb mydb

sudo -u postgres psql mydb

\list

\connect <database-name>

CREATE TABLE weather (
  city            varchar(80),
  temp_lo         int,           -- low temperature
  temp_hi         int,           -- high temperature
  prcp            real,          -- precipitation
  date            date
);

DROP TABLE tablename;

\dt

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

psql -U username -h localhost -d database_name < path/to/your/file.sql

# open pg_hba.conf
vi /etc/postgresql/9.1/main/pg_hba.conf

# All you have to do is replace `peer` to `md5`
# Simply replace the line
## local  all      all          peer
# with
## local  all      all          md5

# restart postgres
sudo service postgresql restart

sudo apt-get install php5-pgsql
sudo /etc/init.d/apache2 restart

# open pg_hba.conf with edit permission
sudo vi /etc/postgresql/9.3/main/pg_hba.conf

# Find the line `local   all             postgres                    ident`,
# the last word may be `ident`, `md5` or `all`, whatever change it to to `trust`

# restart postgres
Restart `sudo /etc/init.d/postgres restart`

# Package repo (for apt-get)
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

sudo apt-get update

sudo apt-get install postgresql-9.4 postgresql-server-dev-9.4 postgresql-contrib-9.4

sudo pg_lsclusters

sudo /etc/init.d/postgresql stop

sudo pg_dropcluster --stop 9.3 main

sudo pg_dropcluster 9.3 main` # Optional

sudo pg_dropcluster --stop 9.4 main

sudo pg_createcluster 9.4 main

sudo service postgresql restart 9.4