Install postgres.
sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main' > /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib libpq-dev
Change to posgres user who is the default user created by postgres
sudo -i -u postgres
Enter postgres console
psql
Change postgres' password
\password postgres
Create a new password-protected role to use with your app. Remove any priviledges you don't want.
CREATE ROLE someappname WITH CREATEDB CREATEROLE SUPERUSER INHERIT REPLICATION LOGIN ENCRYPTED PASSWORD 'some-super-strong-password';
Check roles list to make sure your role is created successfully.
\du
Quit console
\q
Logout and change back to your normal user
exit
Some useful commands for data copying
pg_dump -h your_db_host -U db_user -C db_name | psql -h localhost -d local_db_name -U local_db_user
pg_dump -U remote_user -h remote_server -t table_to_copy source_db | psql target_db
psql -h <host> -p <port> -U <username> <database>
\copy categories TO 'categories.csv' CSV DELIMITER '|'