INSTALANDO O POSTGRES 9.4 NO RHEL 7

Recentemente precisei instalar um Postgres DB em uma VM RHEL 7 para testes. Aproveitei para compartilhar os passos seguidos aqui no Blog. Esse passo a passo deve funcionar em qualquer Distro RHEL-like: Fedora, Centos, Scientific Linux, OEL, etc.

A instalação é a mais básica possível, pois o propósito desse DB é apenas para testes e laboratórios. Nada de tuning ou personalização.
Instale os seguintes pacotes do repositório Postgresql.org oficial.

[rsoares@rhel7-server-1 ~]$ sudo yum install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-1.noarch.rpm

[rsoares@rhel7-server-1 ~]$ sudo yum groupinstall "PostgreSQL Database Server 9.4 PGDG"

Inicialize o Postgres DB.

sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb

Habilite e teste o serviço do Postgres.

[rsoares@rhel7-server-1 ~]$ sudo systemctl enable postgresql-9.4.service

[rsoares@rhel7-server-1 ~]$ sudo systemctl start postgresql-9.4.service

[rsoares@rhel7-server-1 ~]$ sudo systemctl stop postgresql-9.4.service

Troque para o usuário de sistema do postgres.

[rsoares@rhel7-server-1 ~]$ sudo su - postgres

Altere o pg_hba.conf (espécie de “firewall” do Postgres) para permitir o acesso externo através da rede da VM.

-bash-4.2$ vim /var/lib/pgsql/9.4/data/pg_hba.conf

# TYPE DATABASE USER 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.122.0/24 md5

Altere o binding do serviço para aceitar conexões em qualquer endereço IP da VM.

-bash-4.2$ vim /var/lib/pgsql/9.4/data/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;

Logout do postgres user.

Ctrl + D no terminal

Reinicie o serviço postgres

[rsoares@rhel7-server-1 ~]$ sudo systemctl start postgresql-9.4.service
[rsoares@rhel7-server-1 ~]$ sudo systemctl status postgresql-9.4.service
postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled)
Active: active (running) since Thu 2014-11-06 17:21:38 BRST; 1s ago
Process: 24832 ExecStop=/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 25292 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
Process: 25286 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 25296 (postgres)
CGroup: /system.slice/postgresql-9.4.service
├─25296 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
├─25297 postgres: logger process
├─25299 postgres: checkpointer process
├─25300 postgres: writer process
├─25301 postgres: wal writer process
├─25302 postgres: autovacuum launcher process
└─25303 postgres: stats collector process

Confira o binding do serviço na porta TCP padrão so Postgres (5432)

[rsoares@rhel7-server-1 ~]$ netstat -tanp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:55970 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN -
tcp 0 0 192.168.122.65:22 192.168.122.1:39634 ESTABLISHED -
tcp6 0 0 ::1:25 :::* LISTEN -
tcp6 0 0 :::47021 :::* LISTEN -
tcp6 0 0 :::111 :::* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -
tcp6 0 0 ::1:631 :::* LISTEN -
tcp6 0 0 :::5432 :::* LISTEN -

Crie um novo DB User

-bash-4.2$ createuser -d -l -P --interactive NEW_DB_USER
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Crie um novo Data Base

-bash-4.2$ createdb -e -O NEW_DB_USER NEW_DB "New DataBase"
CREATE DATABASE "NEW_DB" OWNER "NEW_DB_USER";
COMMENT ON DATABASE "NEW_DB" IS 'New DataBase';