Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
go to: PostgreSQL nel blog
We want to install postgreSQL 9.1.3 and postgreSQL 8.4.12 on Centos 6.3 Server and we want that they run in parallel.
First of all we install postgreSQL 8.4 that is default postgresql server in Centos 6.3
Install postgresql 8.4
su - yum install postgresql postgresql-server postgresql-libs postgresql-contrib
Than we initialize and start postgresql 8.4.12
service postgresql initdb service postgresql start
check that everything is OK
su - postgresql pslq -l
we have to see a list of 3 databases
Now we configure postgresql 8.4 service to start at boot
su - chkconfig --level 234 postgresql on
Open postgresql port 5432 in Iptables Firewall
echo '-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT' >> /etc/sysconfig/iptables
or with editor:
vi -w /etc/sysconfig/iptables
# add next commit row
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 5432 -j ACCEPT
restart iptables firewall
service iptables restart
Configure psotgresql 8.4
set superuser postgres passsword
# switch to postgres user
su - postgres
# open postgresql cli as postgres and connect to the postgres database
psql postgres
# set the password of user postgres using alter
ALTER USER postgres WITH PASSWORD 'your-postgres-password';
Setting pg_hba.conf
pg_hba.conf say postgresql which users can access which databases and how they can do. ( centos/redhat location /var/lib/pgsql/data/pg_hab.conf)
so we change METHOD to md5 for IPv4 and IPv5 local connections
then we can add other ipv4 connection as
host all all 192.168.0.0/24 md5
at the end we can have something like this
# TYPE DATABSE USER CIDR-ADDRESS METHOD # local for domain socket only local all postgres ident # IPv4 connections host all postgres md5 host all all 127.0.0.1/32 md5 host all all 192.168.0.0/24 md5 # IPv6 host all all ::1/128 md5
In order for the change to take effect we have to
reload pg_hba.conf
from the postgresql cli
postgres=# select pg_reload_conf();
from the shell as postgres user ( su – postgres )
pg_ctl reload
Setting postgresq.conf
In this file we can configure remote access to postgresql (centos/redhat location: /var/lib/pgsql/data/postgresql.conf – )
look for listen_address in connections and authentication section.
to enable remote connections uncomment and change listen_address that by default is ‘localhost’ so access is limited to local machine.
So at the and listen_addresses look like
listen_addresses = '*'
It is possible to set the listen_address to specific IPs using a comma separate list.
Now if you want you can change post value
port= 5432
If port change you have to change iptables firewall setting and restart postgresql.
we can check changes in postgesql cli :
# show listen_addresses; # show port;
we can also do another check as root with
netstat -nxl | grep PGSQL
Install postgresql 9.1.4
First of all stop postgresq 8.4.3 server
su - service postgresql stop
Download and install the yum rpm PostgreSQL Repository
wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm rpm -i pgdg-centos91-9.1-4.noarch.rpm
We can use yum list to check the package now available.
yum list postgresql*
you have to see many postgresql91 packages
As done in postgresql 8.4 we install, inizialize and start postgresql 9.1.
yum install postgresql91 postgresql91-server postgresql91-libs postgresql91-contrib service postgresql-9.1 initdb service postgresql-9.1 start
check that everything is OK
su - postgresql pslq -l
now we configure postgresql 9.1 to start at boot
chkconfig postgresql on
Set postgresql 9.1 environment
the default home directory for the user postgres is at /var/lib/pgsql.
We change the bash_profile for the user postgres to add path for executable/binary directory. Default file has only path for data directory and look like this:
[ -f /etc/profile ] && source /etc/profile PGDATA=/var/lib/pgsql/9.1/data export PGDATA
after we add path for binary it look like this
[ -f /etc/profile ] && source /etc/profile PGDATA=/var/lib/pgsql/9.1/data export PGDATA PATH=/usr/pgsql-9.1/bin:$PATH:$HOME/bin: export PATH
As we want run postgresql 9.1 on 5433 port
Open postgresql port 5433 in Iptables Firewall
echo '-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5433 -j ACCEPT' >> /etc/sysconfig/iptables
or with editor:
vi -w /etc/sysconfig/iptables
# add next commit row
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 5433 -j ACCEPT
restart iptables firewall
service iptables restart
Configure psotgresql 9.1 server
set superuser postgres passsword
su - postgres psql postgres ALTER USER postgres WITH PASSWORD 'your-postgres-password';
Setting postgresq.conf
(centos/redhat location: /var/lib/pgsql/9.1/data/postgresql.conf – )
to enable remote connections uncomment and change listen_address.
listen_addresses = '*'
Usually to change port value (ex:5433 ) we have to uncomment and to modify
# port= 5432
to
port= 5433
in Centos 6.3 we have to do another step we have to create the file /etc/sysconfig/pgsql/postgresql-9.1 with
PGPORT=5433
export PGPORT
touch /etc/sysconfig/pgsql/postgresql-9.1 echo 'PGPORT=5433' >> /etc/sysconfig/pgsql/postgresql-9.2 echo 'export PGPORT' >> /etc/sysconfig/pgsql/postgresql-9.2
Setting pg_hba.conf
we copy the pg_hba.conf from postgresql 8.4 ( before we save orginal conf )
su - postgres mv /var/lib/pgsql/9.1/data/pg_hba.conf /var/lib/pgsql/9.1/data/pg_hba.conf.base cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/9.1/data/pg_hba.conf
We arrive at the end so we can restart the postgresql servers
postgresql 9.1 server on 5433 port
service postgresql-9.1 restart
postgresql 8.4 server on 5432 port
service postgresql-9.1 start
Now we have binaries 9.1 as default postgresql binaries. Usually for me it can be a good choice but if is necessary to use specific 8.4 binary we can use binary full path. It would be possible to use “alternatives” to switch version.
Ex to run psql:
postgresql 8.4 psql
/usr/bin/psql
while psql postgres 9.1 is
/usr/pgsql-9.1/bin/psql
Postgres blog’s post:
- Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
- Upgrade postgresql 8.4.12 To postgresql 9.1.4 Debian
- Downgrade postgresql 9.1.4 to postgresql 8.4.12 – Centos 5
- Upgrade postgresql 8.4 to postgresql 9.1 Centos 5
- Upgrade postgresql server 8.4.3 8.4.12 – Centos 5
- Installare postgreSQL 8.4 in Centos 5
- Upgrade postgresql 8.1 to postgresql 8.4 – Centos 5
