Home > Centos, Linux, Postgresql - SQL, Server > Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit

Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit

postgresql  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:

Annunci
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: