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.

su -

Download and install the yum rpm PostgreSQL Repository

yum install

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/ to /usr/lib/systemd/system/postgresql-9.6.service.

# systemctl start postgresql-9.6

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


postgresql-9.6 data are in directory


check that everything is OK

su - postgres  
pslq -l

we have to see a list of 3 databases


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        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                ident
host    all         all              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        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
port= 5433
in Centos 7 we have to do another step we have to create the file /etc/sysconfig/pgsql/postgresql-9.4 with
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


# netstat -nlp | grep 5432
netstat -nlp | grep 5432
tcp 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: