postgres

* install
http://tecadmin.net/install-postgresql-server-on-ubuntu/

* server start
/etc/init.d/postgresql start

* checkout postgre version
locate postgres | xargs -i xargs -t '{}' -V

* change account
sudo su - postgres (or sudo -i -u postgres)


* if having problem peer authentication

open the file pg_hba.conf for ubuntu it will be in /etc/postgresql/9.x/main and change the this line:
from "local   all             postgres                                peer"
to   "local   all             postgres                                trust"

sudo service postgresql restart


* list clusters

pg_lsclusters

* choose a specific cluster

psql -U postgres --cluster 9.3/main

* install postgis

sudo apt-get install -y postgis postgresql-9.3-postgis-2.1
sudo -u postgres psql -c "CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;" DATABASE_NAME_HERE

* export

sudo su - postgres
pg_dump -Ft -O -x --cluster 9.3/main -d db -f db.tar

* import

sudo su - postgres
dropdb db
createdb db
pg_restore -Ft -O -x --cluster 9.3/main -d db db.tar 

* if having problem importing, try simple way (I prefer it)

pg_dump --encoding=ISO88591 -d db | gzip -9 > /tmp/db.sql.gz

* if still having problem with encoding try below
pg_dump -U postgres -d db -v | iconv | gzip  > /tmp/db.sql.gz

dropdb db
createdb db
or (createdb -E SQL_ASCII -T template0 --lc-collate=C --lc-ctype=C db)
psql db -U postgres -f db.sql


* clone db

sudo su - postgres
dropdb -h 127.0.0.1 -p 5433 -U postgres -w -i -e foodback_app_live
createdb --template='foodback_app' 'foodback_app_live' -h 127.0.0.1 -p 5433 -U postgres -w

* commands

psql -U postgres
\l : list databases
\c db : use database
\dt : show tables 
\d: desc table

* show process list

select * from pg_stat_activity;

* copy records to file

COPY (select * from table) TO '/tmp/data.tsv'
COPY another_table FROM '/tmp/data.tsv'

* alter password

ALTER USER postgres with password 'YourNewPassword';