centos-7_x-postgresql-9 .6

centos-7_x-postgresql-9.6.md

Stage 1 - System update and common setup procedure

Update system

# yum update

Set system time to UTC timezone

# timedatectl set-timezone UTC

yum install -y nano wget

Stage 2 - Install PostgreSQL Cluster 9.6 from official RPMs, and enable system service

Get official packages

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

Install PostgreSQL packages

yum install -y postgresql96 \
postgresql96-libs \
postgresql96-server \
postgresql96-contrib \
postgresql96-docs \
postgresql96-devel \
postgresql96-plperl \
postgresql96-plpython \
postgresql96-pltcl

Enable XFS drive (optional - if you require extra drive for data)

yum install -y xfsprogs
cfdisk /dev/sdb
mkfs.xfs -f /dev/sdb1
mount -t xfs /dev/sdb1 /var/lib/pgsql/9.6/data
df -Th /var/lib/pgsql/9.6/data
chown postgres:postgres /var/lib/pgsql/9.6/data

Add to /etc/fstab

/dev/sdb1                                 /var/lib/pgsql/9.6/data xfs     defaults        0 0

Init initial database

/usr/pgsql-9.6/bin/postgresql96-setup initdb

Start service and enable on boot

systemctl start postgresql-9.6
systemctl enable postgresql-9.6
systemctl status postgresql-9.6

Stage 3 - Configure network

Add server port to firewall and restart firewall

firewall-cmd --permanent --zone=public --add-service=postgresql

OR by port

firewall-cmd --permanent --zone=public --add-port=5432/tcp

Restart firewall

systemctl restart firewalld.service

Stage 4 - Login to server and create admin user and database (for remote access)

su - postgres
psql
createuser -W -d -s pgadmin

Create UTF8 database for admin user

createdb -T template0 -l en_US.UTF-8 -E UTF8 -O pgadmin pgadmin

Allow remote user to connect, edit hosts file:

vi /var/lib/pgsql/9.6/data/pg_hba.conf

Configuration for 4 CPU / 26GB RAM

shared_buffers = 6656MB
temp_buffers = 32MB
work_mem = 16MB
maintenance_work_mem = 512MB

Add following entry:

host        all        pgadmin      <user_ip_address>/32        trust

If you need edit confiuration

You might want to change listen_addresses = '*' or something else ...

/var/lib/pgsql/9.6/data/postgresql.conf

Restart server

systemctl restart postgresql-9.6

Remote user test

psql -h <ip> -U pgadmin -W

Change defaults of template1 to UTF8 by default

psql

postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';
postgres=# \c template0
template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
template0=# drop database template1;
template0=# create database template1 with template = template0 encoding = 'UTF8';
template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
template0=# \c template1
template1=# update pg_database set datallowconn = FALSE where datname = 'template0';