How to Upgrade PostgreSQL

========================
How to upgrade PostgreSQL
========================

First remove the old PostgreSQL if one has been installed:

psql --version
yum list postgres*
yum remove postgresql

Then exclude CentOS PostgreSQL Packages:

vi /etc/yum.repos.d/CentOS-Base.repo

– in this file add: exclude=postgresql*
– to the end of the [base] and [updates] sections

Then get the url of the latest version for your distro from here:
http://yum.postgresql.org/repopackages.php (right click to get url)
Then wget it (the url below is what right clicking above gets you), install rpm, and list:

wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
rpm -ivh pgdg*
yum list postgres*

Then we install PostgreSQL:

yum install postgresql93-server

Then initialise db and set it up to start automatically:

service postgresql-9.3 initdb
chkconfig postgresql-9.3 on
service postgresql-9.3 start

HOW TO LOG INTO THE POSTGRESQL DATABASE
————————————-

su - postgres
psql
\q
exit

SOURCE: https://www.digitalocean.com/community/articles/how-to-install-and-use-postgresql-on-a-centos-vps

HOW TO UPDATE VIRTUALMIN SO THAT IT HAS DETAILS OF THE CORRECT PG VERSION
————————————————————————-

Webmin > servers > postgres > module config > system config

Path to psql command:
/usr/pgsql-9.3/bin/psql
Command to start PostgreSQL:
if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.3 start; fi
Command to stop PostgreSQL:
if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql-9.3 stop; fi
Command to initialize PostgreSQ:
if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.3 initdb ; /etc/rc.d/init.d/postgresql-9.3 start; fi
Path to postmaster PID file:
/var/run/postmaster-9.3.pid
Paths to host access config file:
/var/lib/pgsql/9.3/data/pg_hba.conf
Path to pg_dump command:
/usr/pgsql-9.3/bin/pg_dump
Path to pg_restore command:
/usr/pgsql-9.3/bin/pg_restore
Default backup repository directory:
/var/lib/pgsql/9.3/backups