eGov ERP DevOps

Postgres Configuration

Owned by Manjunatha S (Unlicensed)
May 08, 2020
4 min read

PostgreSQL Installation

Get binaries from below location

wget http://get.enterprisedb.com/postgresql/postgresql-9.4.5-1-linux-x64.run

Running binaries as sudo

$ chmod +x ~/Downloads/postgresql-9.4.5-1-linux-x64.run

$ sudo ~/Downloads/postgresql-9.4.5-1-linux-x64.run

$ sudo ~/Downloads/postgresql-9.4.5-1-linux-x64.run --mode text

  • ---------------------------------------------------------------------------

Welcome to the PostgreSQL Setup Wizard.

  • ---------------------------------------------------------------------------

Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.4]:

  • ---------------------------------------------------------------------------

Please select a directory under which to store your data.

Data Directory [/opt/PostgreSQL/9.4/data]:

  • ---------------------------------------------------------------------------

Please provide a password for the database superuser (postgres).

A locked Unix

user account (postgres) will be created if not present.

Password :

Retype password :

  • ---------------------------------------------------------------------------

Please select the port number the server should listen on.

Port [5432]: 6432

  • ---------------------------------------------------------------------------

Advanced Options

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]

[2] C

[3] C.UTF-8

[4] en_AG

.

.

.

[24] en_ZW.utf8

[25] POSIX

Please choose an option [1] : 1

  • ---------------------------------------------------------------------------

Setup is now ready to begin installing PostgreSQL on your computer.

Do you want to continue? [Y/n]: Y

  • ---------------------------------------------------------------------------

Please wait while Setup installs PostgreSQL on your computer.

Installing

0% ______________ 50% ______________ 100%

#########################################

  • ---------------------------------------------------------------------------

Setup has finished installing PostgreSQL on your computer.

Checking service status

$ service postgresql-9.4 status

pg_ctl: server is running (PID: 1158)

/opt/PostgreSQL/9.4/bin/postgres "-D" "/opt/PostgreSQL/9.4/data"

Tuning PostgreSQL Parameters

listen_addresses = '127.0.0.1'

This parameter defines, what all the remote servers are, this postgres server should serve. Here, we are limiting the access to

localhost, and no other remote machine can access this server. All the remote servers are going to connect pgBouncer, which tunnel

its connection to local db server.

If we want make this server to listen all remote IPs, we need to mention this parameter value as '*'.

Port = 6432

By default, postgres runs on 5432, which we configured pgbouncer, which transfer all incoming traffic to 6432.

max_connections = 2550

Further to our communication on Call, in future we may expect 5000 active connections to DB, from the remote working machines.

Hence, we planned to divide 2500 connections as an active, and 2500 connections will be in pooling mode {Waiting}. That means, at

any moment, postgres will accept maximum 2500 + 50(Spare connections to DBAs). If we go beyond 2550, postgres will through

connection exception to application.

Having too many connections on DB server leads to kernel settings. As we all know, postgres runs based on processes {each

connection is a process}, and uses the shared memory with IPC {Inter Process Communication}. To achieve this, we need to configure

kernel level "sem" {Semaphore} setting for higher values. Here, we have 2550, which is a bit higher value than expected.

During our DB configuration, we found the below ERROR message from DB logs.

FATAL: could not create semaphores: No space left on device

HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number

of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to

raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its

max_connections parameter (currently 40). The PostgreSQL documentation contains more information about configuring your system

for PostgreSQL.

As per the PostgreSQL manual, we have configured SEMMNI as

http://www.postgresql.org/docs/9.1/static/kernel-resources.html

FROM Default

kernel.sem=250 32000 32 128

To

kernel.sem=250 32000 32 1024 {we configured SEMMNI, a bit high to support future changes}

ssl = on

Configured ssl on. We have configured ssl as UAT server configured.

ssl_cert_file = /opt/PostgreSQL/9.4/ssl/server.crt

Providing ssl cert file location.

ssl_key_file = /opt/PostgreSQL/9.4/ssl/server.key

Providing key file location.

How We Configured SSL as below.

$ mkdir ssl

$ cd ssl

$ openssl req -new -text -out server.req

$ openssl rsa -in privkey.pem -out server.key

$ openssl req -x509 -in server.req -text -key server.key -out server.crt

shared_buffers = 4GB

PostgreSQL moderate setting for shared buffers, where all our transactional data resides.It holds, all the

SELECT/UPDATE/DELETE/INSERT information, along with some internal cache query plans. We can increase this value upto 8GB

too, but we don't have enought traffic to decide that. Once, we goto live, we need to check this parameter value again.

effective_cache_size = 24GB

This is the setting we considered as 3/4th of existing RAM. This memory is not used by postgres, where this figure will be useful in

generating a proper postgresql plan. We believe, we don't need to go beyond this value, even after we go to live.

work_mem = 100MB

This is the setting value, we assumed to be enough to have for incoming traffic. We have configure a bit high value, which we need to

reduce once we see there is not 100MB is really enough, for any ORDER BY, HASH JOINs and some GROUP operations.

This value is high, respect to max_connections. We need to reduce this value after observing the tempfile generations on DB server.

maintenance_work_mem = 1GB

This is the amount of memory, which will be used by autovacuum deamon processes, and for any maintenance operations like

VACUUM, CREATE INDEX.temp_file_limit = -1

This parameter we configured as "no limit" on temp files generation. We need to re-limit this value, once we observe the load. By

specifying "no limit" may cause DB disk storage full.

We will tune this parameter, once we observe the maximum temp file size we can allow after checking the pgbadger, and pgHawk

reports.

wal_buffers = 32MB

Amount of buffers, for Transactions log files (Xlog files). If the amount of 32MB is filled, it will flush all its content to 16MB files, which

are located in /opt/pg-logger. These pg_xlog files are useful, during the postgres crash recovery, and Online Backup recovery and also

in slave recovery too.

checkpoint_segments = 32

After 32 Xlog (16MB) file generates, postgres issues a CHECKPOINT by flushing all its dirty buffers (Modified Buffers in

shared_buffers), and records it's checkpoint ID. This defines, DB is in a consistant status upto this Checkpoint ID. During

CHECKPOINT time, we may notice a bit I/O load on server, since it's flushing all its dirty buffers from shared_buffers to local disk.

If we have shared_buffers too high, this may increase load significantly.

/*

  • All Future DB parameter changes goes here.

  • Parameter changes like

  • > Log settings for pgbadger

  • > Replication settings

  • > Vacuum/Autovacuum settings

  • > Any other settings, after observing DB in LIVE

  • /

DevOps as a Culture