How to run PostgreSQL on Docker ?

Docker is a Open source tool/platform where we can create/deploy/run application by using containers.Docker is similar to VM but in VM - one need to create virtual operating system but in case of Docker  -it will allow applications to use system kernel where it is running on.

PostgreSQL community supports PG (v9,4,v9,5,v9.6,v10,v11 e.t.c) Images
The difference between images and container is -
Image is nothing but a template of instructions and  an instance of an image is called Container.

so here is what is what -
================
Docker- is a tool/platform.
Image- is an application which we want to create/deploy  e.g In this case we want to deploy  PG v11.5(latest released as on 28th/Aug/2019)  Image.
Container - Running instance of an image (template of instructions) .

Steps by Steps guide to deploy PostgreSQL image on Docker 
============================================
0. Centos 7 64 bit machine
1.Go to  https://hub.docker.com/
2.Create a login account (Sign up)

After Pressing continue -it will ask you - your  first name/last name /your profile  and  some other crap questions.  - Press Continue - it will further ask you to check your inbox and click on "Confirm your email with Docker "  , Once you done that -
Congrats - You are registered on the site and your DockerID is created.

Sign up time -

First thing ,We need to install docker on machine , right now it is not installed locally on my Centos 7

3.To install Docker Enterprise(EE) , Please follow this link - 

https://docs.docker.com/install/linux/docker-ee/centos/#find-your-docker-ee-repo-url

Otherwise , just  install Docker CE(Community edition) by using yum command 

Open a command line terminal and start the docker daemon service -

4.Go to https://hub.docker.com and write 'postgres' in search box 

All the official PG server images can be found that and on the right hand side, there is a command to pull the image i.e "docker pull postgres" , copy it .

5.Open a command line terminal and paste command - "docker pull postgres"

OR

if we want PG v11.5 then we need to specify the tag explicitly i.e 

Currently latest is 11.5 only so both are same.

6.Run "docker run" command which is used to launch container 

7.Run "docker ps" command which shows status of container and all required information 

8.Run "docker exec" command which is used to login to the container

Here, if we see - We are not only able to login to the container but also login to PG v11.5 server 

9.Remove the  container once you are done !

Hope this helps!

I would like to  thanks my colleague Mr.Jitendra Wadle for all his help and assistance.  

Where is my recovery.conf file in PostgreSQL v12 ?

Well the short answer is - it has gone.
From PostgreSQL v12 - 'recovery.conf' is no more valid and even if someone create a recovery.conf file manually and keep under  data  directory -Server is not going to start - will throw this below error

Parameter  'standby_mode =on' is also removed from PG v12 which used to be #1 parameter of recovery.conf file also trigger_file parameter name is changed to promote_trigger_file.

Other parameters which we usually  mention in the recovery.conf is valid and can be write in the postgresql.conf file of SLAVE cluster.  

It is actually make more sense if all the  required information is mentioned in one file i.e  postgresql.conf rather than creating/managing separate files.

standby.signal which is a empty file has replace recovery.conf file and presence of this file will signal the cluster to run in standby mode.

Step by Step guide to setup SR in PG v12 and Failover
=======================================
1)PG v12 server is up and running -  only modified 2 parameters in the postgresql.conf file of master cluster

archive_mode  = Whether to send  WAL files for archive storage or not
archive_command = Where to send i.e location

2)Create Standby/Slave using pg_basebackup  with option -R 

pg_basebackup utility  is for to take the backup online.There is an another method as well where we can fire - select pg_start_backup('My backup..'); in master cluster to start the online backup and then using cp command take the backup and later fire - select pg_stop_backup(); to signal the master server that online backup is finish but again manual intervention is required i.e remove postmaster.pid /create recovery.conf file (or from PG v12 - create standby.signal) in slave directory  so better to use pg_basebackup only which take care of everything.

-R option will create a empty file with the name- standby.signal .

3)Old recovery.conf file ( taken from PG v10)

   

                     

if we just copy all these above parameters in the postgresql.conf file of slave cluster . Server is going to throw this below error - 

As standby_mode parameter is no more supported and trigger_file is rename to promote_trigger_file -server is failed to start . just Correct both these - Server will start. 

4)Verify SR setup is properly working

Perform - Failover - 

There are multiple ways -

a)shut down the master and promote standby.

b)Shut down the master and touch the file which we mentioned in postgresql.conf in promote_trigger_file parameter.

In this case - using option b) 

standby.signal file has gone from slave/ data directory. Slave is now new Master and able to perform DDL operations.

Why pg_regress is important and How to Create/Add a testcase file part of schedule ?

pg_regress is a utility to run the regression testcases. This utility is available from the PG source code repository  - reside under regress/ folder . From the Development point of view - Regression testing plays a very important role .No developer would like to see  adding 1 feature - breaks 10 others things or fixing 1 bug -introducing 10 more bugs.

How it works -One thing which is mandatory before we run pg_regress  i.e  sql file(which contain all sql queries).
Lets assume we have a feature to test whether arithmetic operator - lets say  "+" is working fine with simple select query or not .

First thing  -We need to do is testing .These below are the sql statements which we would  like to add in the testcase file in regression testsuite  to make sure - Adding new other feature  or changing the existing code later should not hamper the functionality of the feature for which the testcases has been added in the testcase suite. if it is so then testcase will start failing and we can catch the regression easily before complaint  comes in  from end user after release the software.

                                     

Assuming all the above results is valid . To make this as part of regression testsuite so that it can be run via pg_regress everyday/at the development phase  - We can  add  sql statements in either any pre-existing sql files or create a new sql file -whichever is more appropriate . 

Here creating a new sql file with the name - test.sql . SQL file extension should be ".sql"

Now, We can generate corresponding expected  file by either running  pg_regress or psql  against this sql file . Using psql to generate the o/p file . Output file extension should be ".out" 

pg_regress can be run against a running PG Server or it can initialise cluster its own  before executing the sql file.  Like Football - Placement is very important of sql/out file so make sure - test.sql file location is as same as test.out file location  - the only difference is -.sql file will go under sql/ directory whereas .out file will go under expected/ directory otherwise pg_regress will throw an error i.e file not found. 

In this case - Creating a directory called -mydir under sql/ directory and same under expected/ directory and copying test.sql and test.out file  there. 

Case 1 - Run pg_regress  against running PG Server and without schedule file

Case 2:- Run pg_regress using option --temp-instance (which will initialise temporary instance its  own)  -thats mean - no pre-running server is needed before executing test cases. 

                      

Now, We need to add the testcase in schedule file so that it can be part of regression testsuite , We can either create a new schedule file OR add the testcase file name in pre-existing schedule file i.e serial_schedule OR parallel_schedule which we are having under regress/ folder 

In this case- Adding the testcase file in serial_schedule file

  

  

  

Run the schedule file using pg_regress

Testcase is passing and also part of regression testsuite(locally i.e in local repo).

Hope this was helpful !