esdc postgresql

PostgreSQL + PgBouncer

PostgreSQL is our database of choice. http://www.postgresql.org/
PgBouncer is a connection pooler. https://pgbouncer.github.io/
It is very important to access the database only via pgbouncer.

Note: This guide is made for a development environment based on CentOS 7. PostgreSQL and PgBouncer are already installed in production setup.

Install

• We will use the official repository and install the newest version:

  yum install http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm
  yum install postgresql95-server postgresql95-devel pgbouncer
  

• Initialize the DB DATADIR /var/lib/pgsql/9.5/data:

  PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --locale=en_US.UTF-8"
  LC_ALL="en_US.UTF-8"
  /usr/pgsql-9.5/bin/postgresql95-setup initdb
  

• Add /usr/pgsql-9.5/bin into $PATH. You can update ~/.bash_profile or ~/.bashrc to make the change persistent:

  PATH="${PATH}:/usr/pgsql-9.5/bin"
  

Configure

PostgreSQL

See postgresql.conf wiki page for more info

• /var/lib/pgsql/9.5/data/postgresql.conf (only changed parameters)

  listen_addresses = 'localhost'
  max_connections = 1000 # think about this
  unix_socket_permissions = 0770 # only pgbouncer will connect here
  

• /var/lib/pgsql/9.5/data/pg_hba.conf
  Please make sure that you either replace your default contents with the settings below, or make sure that you configure pg_hba.conf in such way that that postgres user has all neccessary authentifications to work with DB.

  TYPE  DATABASE        USER            ADDRESS                 METHOD
  local   all             postgres                                ident map=superuser
  local   replication     postgres                                ident map=superuser
  
  local   all             all                                     md5
  host    all             all             127.0.0.1/32            reject
  host    all             all             ::1/128                 reject
  

• /var/lib/pgsql/9.5/data/pg_ident.conf

  # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
   superuser       root                    postgres
   superuser       postgres                postgres
  

• Start/reload the postgresql-9.5.service

• Create user and database:

  /usr/pgsql-9.5/bin/createuser -U postgres -P esdc   # DBPASS=S3cr3tP4ssw0rd
  /usr/pgsql-9.5/bin/createuser -U postgres -P pdns   # DBPASS=S3cr3tP4ssw0rd
  
  # Create databases
  LC_ALL="en_US.UTF-8" /usr/pgsql-9.5/bin/createdb -U postgres --locale=en_US.UTF-8 -E UTF8 -O esdc esdc 
  LC_ALL="en_US.UTF-8" /usr/pgsql-9.5/bin/createdb -U postgres --locale=en_US.UTF-8 -E UTF8 -O esdc pdns
  
  # Optional:
  psql -U postgres
  esdc=# alter role esdc set client_encoding='UTF8';
  esdc=# alter role esdc set default_transaction_isolation='read committed';
  esdc=# alter role esdc set timezone='Europe/Bratislava';
  

PgBouncer

• Adjust permissions:

  touch /etc/pgbouncer/userlist.txt
  chown root:pgbouncer /etc/pgbouncer/userlist.txt
  chmod 640 /etc/pgbouncer/userlist.txt # only pgbouncer should read this
  
  usermod -G postgres pgbouncer # add user pgbouncer to postgres group, so it can connect to the socket
  

• /etc/pgbouncer/pgbouncer.ini (only changed parameters)

  [databases]
  ; all databases (this is all you need in [databases]
  * =
  
  [pgbouncer]
  listen_addr = *
  listen_port = 6432
  
  auth_type = md5
  auth_file = /etc/pgbouncer/userlist.txt
  
  pool_mode = transaction # was session before
  
  max_client_conn = 500 # see max_connections in postgresql.conf
  default_pool_size = 500
  log_connections = 0
  log_disconnections = 0
  

• /etc/pgbouncer/userlist.txt First you need to find out the md5 password of a user:

  psql -U postgres -c "SELECT usename,passwd FROM pg_catalog.pg_shadow;"
  

• Add one line per user in form of:

  "<username>" "<md5password>"
  

• /etc/sysconfig/iptables Open pgbouncer port in iptables. Add following line to /etc/sysconfig/iptables

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

  Restart iptables service

  systemctl restart iptables
  

Run

PostgreSQL

systemctl enable postgresql-9.5
systemctl start postgresql-9.5

PgBouncer

systemctl enable pgbouncer
systemctl start pgbouncer

Use

psql -h 127.0.0.1 -p 6432 -U esdc esdc