Installing PostgreSql 9.6 into CentOs

1. Introduction

This post gathers information about install PostgreSql into Centos 7.

2. Step-by-Step

2.1. Configure YUM repository

# vim /etc/yum.repos.d/CentOS-Base.repo
    :
[base]
    :
exclude=postgresql*

[update]
    :
exclude=postgresql*

# yum list postgresql*
# yum install postgresql96-server

2.2. Get RPM file and run

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

2.3. Install PostgreSql

# yum -y install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs

2.4. Configure PostgreSql auto estart

# systemctl enable postgresql-9.6

2.5. Initialize database

# service postgresql-9.6 initdb

2.6. Start PostgreSql and check

# systemctl start postgresql-9.6

2.7. Connect to PostgreSql

# su - postgres
Last login: Sun Mar 12 21:29:21 -03 2017 on pts/1
-bash-4.2$ psql
psql (9.6.2)
Type "help" for help.

postgres=#

2.8. List databases and query system catalog

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres
(3 rows)

postgres=#  select schemaname, count(*) from pg_tables group by schemaname;
     schemaname     | count
--------------------+-------
 information_schema |     7
 pg_catalog         |    55
(2 rows)

2.9. Locating PostgreSql configuration file "pg_hba"

# su - postgres
# env | grep "PGDATA"
PGDATA=/var/lib/pgsql/9.6/data
# ls -l /var/lib/pgsql/9.6/data/pg*.conf
-rw-------. 1 postgres postgres 4224 Mar 12 21:27 /var/lib/pgsql/9.6/data/pg_hba.conf

2.10. Configure PostgreSql remote access

• Change "ident" for "md5"

# vi /var/lib/pgsql/9.6/data/pg_hba.conf
  :
#host    all             all             127.0.0.1/32            ident
host    all             all             127.0.0.1/32            md5
  :

3. References

• http://nervinformatica.com.br/blog/index.php/2016/12/31/instalacao-postgresql-9-6-em-centos-7/
• https://wiki.postgresql.org/wiki/YUM_Installation
• https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7