PostgreSQL Database Design and implementation Standard

PostgreSQL Database Design & implementation Standard:

its been a while  that I am working on PostgreSQL and would like to create standard for DB cluster installation.

 Installation:

install the postgres software under the below directory and user postgres:

Postgres Binary tablespace----> /postgres/<App_name>/<postgres/version>

pre-work 

1.we  need to create separate OS user to install & run the PostgrSQL cluster ,e,g postgres OS User.

2.create separate File system for Data,Temp,Postgres Binary and WAL(write Ahead Log),Backup

3.folder sturcture should be like below

Data tablespace----> /postgres/<App_name>/Data/--->used for Data

Temp tablespace---->/postgres/<App_name>/Temp/---->used for sorting, Temp

Postgres Binary tablespace----> /postgres/<App_name>/<postgres/version>

WAL tablespace---->/postgres/<App_name>/WAL/--->To store WAL files

Backup tablespace----> /postgres/<App_name>/Backup/--->to store backup files

3.we can have profile  for Postgres OS user.

we can Have :PG_HOME,PG_DATA,PG_DATABASE,PG_BACKUP,PG_HOSTNAME

4.Port Number ---configure Non-Default port number  5432

Memory configuration 

As per the Industry standard ,Please allocate the Memory for Each Layer in Postgres like below

shared_buffers - Non Prod==>20-30% of total, Non Prod==>RAM 20% of total RAM---->Key Memory component for PostgreSQL cluster

temp_buffers - Non Prod==>10-15% of total RAM,Non Prod==> 10% of total RAM----> Memory component for Temp Buffer

work_mem - Non Prod==>5-10% of total RAM ,5% of total RAM-

maintenance_work_mem - 10-15% of total RAM  10% of total RAM-

DB creation

1.create separate tablespace for each DB and park the DB data on that tablespace,it will be easy to recover during server crash.

2.Add the DB parameter accoridng to  below for Prod & Non Prod systems

max_connections - For Dev systems, 100-200. For Production, 500-1000 (Depending on application requirements)

Authentication/Security

Do not leave the defult setting and change all connection thru encrypted and password session on hba file

pg_hba.conf entries (Allow specific IP)

host     all             all            172.17.0.0/32           md5

Monitoring Tools:

I believe Community version needs to buy separate license for the below PostgreSQL monitoring tools,But Enterprise DB license must include PEM monitoring license.

PEM(Postgres Enterprise Manager) just we need to install the agent on the DB server and GUI based tool to monitor Postgres service.

ZABBIX

NAGIOS

PEM

Backup Policy:

Prod we can enable the WAL  to have get the Point in time recovery ,for Non Prod ,No need  to enable the WAL

backup types :

we use the pg_dump backup utility for Community version and Enterprise DB we can use BART tools.

we can schedule the backup backup jobs under cron

1.pg_dump

2.BART tool(EDB)

Maintenance Jobs needed:

1.Vacuum job 

2.Backup Job

3.stats gathering