Install latest PostgreSQL on Linux Mint

PostgreSQL

Installation

create /etc/apt/sources.list.d/pgdg.list containing:

deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main

Import the repository signing key, and update the package lists

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \

sudo apt-key add -

Update repository indexes

$ sudo apt-get update

Install postgreSQL (run all these cmds as root)

# apt-get install postgresql-9.2

# apt-get install postgresql-contrib-9.2

# apt-get install pgadmin3

Set OS password for user 'postgres'

$ sudo passwd postgres

Set DB password for user 'postgres'

$ psql

psql> ALTER USER postgres WITH PASSWORD 'jessie123';

psql>\q

(note: the new password above is enclosed in double quotes)

Create a .bashrc in home dir of user 'postgres' containing:

PATH=$PATH:/usr/lib/postgresql/9.2/bin

export PGDATA='/var/lib/postgresql/9.2/main/'

export PGCONF='/etc/postgresql/9.2/main'

psql commands

\q	Quit out of psql
select name, setting from pg_settings where category = 'File Locations';	Show settings & file locations

Key directories

/etc/postgresql/9.2/main	Configuration files
/var/lib/postgresql/9.2/main	Data dir (env var $PGDATA)
/var/lib/postgresql	Home dir for user postgres

Key files

/etc/postgresql/9.2/main/pg_ident.conf	Mappings of OS logins to postgreSQL user
/etc/postgresql/9.2/main/postgresql.conf	General settings, where data is stored, where logs are etc
/etc/postgresql/9.2/main/pg_hba.conf	Host based authentication, security and authentication settings
/var/log/postgresql/postgresql-9.2-mail.log	Log file for postgreSQL server

Environment Variables

PGDATA	Location of postgres data
PGCONG	Location of postgres config files (gcb use only)

Server Control

Start the service	/etc/init.d/postgresql start pg_ctl start
Stop the service	/etc/init.d/postgresql stop pg_ctl stop
Know the status	/etc/init.d/postgresql status pg_ctl status
Restart the service	/etc/init.d/postgresql restart pg_ctl restart
Reload config without restarting server	pg_ctl_reload