installare Postgresql 9.6 su Centos 7.4 64 bit

postgresql  go to:  PostgreSQL nel blog 

Default postgresql server in Centos 7 x86 64 is version 9.2.x

to install postgresql 9.6.x use   Postgresl yum repository.

https://www.postgresql.org/download/linux/redhat/

su -

Download and install the yum rpm PostgreSQL Repository

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

We can use yum list to check the package now available.

yum list postgresql*

you have to see many postgresql96 packages

Install, inizialize and start postgresql 9.6.

install the client

yum install postgresql96

Install th server postgresql 9.6 with contrib package.

yum install postgresql96-server postgresql94-contrib

Than we initialize and start postgresql-9.6

#/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
# systemctl enable postgresql-9.6
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.

# systemctl start postgresql-9.6

postgresql-9.6 /bin /lib /doc /share are in directory

/usr/pgsql-9.6

postgresql-9.6 data are in directory

/var/lib/pgsql/9.6

check that everything is OK

su - postgres  
pslq -l

we have to see a list of 3 databases

Firewall

Open postgresql port 5432 in Iptables Firewall

with editord vi, cmedit nano edit file /etc/sysconfig/iptables  :
vi -w /etc/sysconfig/iptables

# add next commit row

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

restart iptables firewall

Configure postgres set superuser postgres password

# 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/9.4/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.1.0/24        md5

at the end we can have something like this

# TYPE  DATABASE    USER        CIDR-ADDRESS               METHOD                    
                                                                                
# "local" is for Unix domain socket connections only                            
local   all         all                                      peer    
# IPv4 local connections:                                                       
host    all         all           127.0.0.1/32               ident
host    all         all           192.168.1.0/24             md5      
# IPv6 local connections:                                                       
host    all         all           ::1/128                    ident                     
# Allow replication connections from localhost, by a user with the              
# replication privilege.                                                        
#local   replication     postgres                            peer           
#host    replication     postgres        127.0.0.1/32        ident          
#host    replication     postgres        ::1/128             ident  

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/9.4/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.

Restart postgresql con

servicectl  postgresql restart

Now if you want you can change post value

port=  5432
Change port value 
Default port value for postgresql is 5432
Usually to change port value (ex:5433 ) we have to uncomment and to modify
# port= 5432
to
port= 5433
in Centos 7 we have to do another step we have to create the file /etc/sysconfig/pgsql/postgresql-9.4 with
PGPORT=5433
export PGPORT
touch /etc/sysconfig/pgsql/postgresql-9.6
echo ‘PGPORT=5433’ >> /etc/sysconfig/pgsql/postgresql-9.6
echo ‘export PGPORT’ >> /etc/sysconfig/pgsql/postgresql-9.6

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
netstat -nlp | grep PGSQL
unix 2 [ ACC ] STREAM LISTENING 263035 /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 263037 /tmp/.s.PGSQL.5432

and

# netstat -nlp | grep 5432
netstat -nlp | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 28083/postmaster 
tcp6 0 0 :::5432 :::* LISTEN 28083/postmaster 
unix 2 [ ACC ] STREAM LISTENING 263035 28083/postmaster /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 263037 28083/postmaster /tmp/.s.PGSQL.5432

Postgres blog’s post:

Annunci

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...