Where is my recovery.conf file in PostgreSQL v12?
The short answer is: it’s gone.
With PostgreSQL v12, “recovery.conf” is no longer valid. Even if someone were to create a recovery.conf file manually and keep it under the data directory, the server is not going to start and will throw the following error:
The parameter “standby_mode =on”, which used to be the #1 parameter of the recovery.conf file has also been removed from PostgreSQL v12. Also, the “trigger_file” parameter name has been changed to “promote_trigger_file.”
Other parameters for recovery.conf are valid and can be written in the “postgresql.conf” file of the slave cluster.
It actually makes more sense if all the required information is mentioned in one file—i.e., postgresql.conf—rather than creating and managing separate files.
“standby.signal”—which is an empty file—has replaced the recovery.conf file and the presence of this file will signal to the cluster to run in standby mode.
Step-by-Step guide to setting up Streaming Replication in PostgreSQL v12 and Failover
Make sure PostgreSQL v12 server is up and running, with only these two parameters modified in the postgresql.conf file of the master cluster:
archive_mode = Whether to send Write-Ahead Logging ( WAL) files for archive storage or not
archive_command = Where to send (i.e., location)
Create a standby/slave using “pg_basebackup” with option -R:
The “pg_basebackup” utility is used to take the backup online.
There is an alternative method as well, where we can fire “select pg_start_backup('My backup..');” in the master cluster to start the online backup, and then using cp command take the backup and later fire “select pg_stop_backup();” to signal to the master server that online backup is finished. But again, manual intervention is required—i.e. you need to remove the “postmaster.pid /create recovery.conf” file (or in PostgreSQL v12, create standby.signal) in slave directory—so it’s better to use only “pg_basebackup,” which takes care of everything.
Option -R will create an empty file with the name “standby.signal.”
Contents of the old “recovery.conf” file (taken from PostgreSQL v10):
If you just copy all the above parameters in the postgresql.conf file of slave cluster, the server is going to throw this error:
As the “standby_mode” parameter is no longer supported and “trigger_file” has been renamed to “promote_trigger_file,” the server failed to start. Just correct both of these and the server will start.
Verify SR setup is properly working:
Perform Failover :
There are multiple ways to do this:
a) Shut down the master and promote standby.
b) Shut down the master and touch the file that we mentioned in the “promote_trigger_file parameter” of postgresql.conf, in step 3 above.
In this case, using option b:
The “standby.signal” file is gone from the slave/data directory. Slave is now the new master and able to perform DDL operations.
... View more
How to run PostgreSQL on Docker?
Docker is an open source platform where we can create, deploy, and run applications by using containers. Docker is similar to virtual machines (VM), but in VM you need to create a virtual operating system, while Docker allows applications to use the system kernel.
PostgreSQL community supports (v9.4,v9.5, v9.6, v10, v11 etc.) Images.
The difference between Images and containers is that Images are simply templates of instructions, and an instance of an Image is called a container.
Here are some helpful definitions
Docker: a tool/platform.
Image: an application that we want to create/deploy. (In this case, we want to deploy the PostgreSQL v. 11.5 (latest release as of August 28, 2019) Image.)
Container: runs an instance of an Image (template of instructions).
Step-by-Step guide to deploying a PostgreSQL Image on Docker
0. Use a CentOS 7 64-bit machine.
Go to https://hub.docker.com.
Create an account
After pressing Continue, it will ask you for your first name, last name, and some related personal questions. Press Continue, and it will then ask you to check your inbox and verify your email address. Once you’ve done that, congrats! You are registered on the site, and your DockerID is created.
3. Sign in to your account:
Before proceeding, you need to install Docker on your machine if it is not installed locally on your Centos
To install Docker Enterprise (EE), please follow this link:
Otherwise, just install Docker Community Edition (CE) by using the yum command:
Open a command line terminal and start the Docker daemon service:
Go to https://hub.docker.com and search for 'postgres’ :
All the PostgreSQL server Official Images can be found there. On the right-hand side of the page, there is a command to pull the Image: "docker pull postgres." Copy it.
Open a command line terminal and paste the command "docker pull postgres":
if you want PostgreSQL v11.5, then you need to specify the tag explicitly:
Currently, 11.5 is the latest version, so both commands will pull the same Image.
Enter the command "docker run," which is used to launch container:
Run the command "docker ps" to show the status of the container and all required information:
Run the command "docker exec" to log in to the container:
Here you are able to log in not only to the container but also to the PostgreSQL v11.5 server.
Remove the container once you are done!
Hope this helps!
I would like to thank my colleague Jitendra Wadle for all his help and assistance.
Article originally posted Thursday, August 29, 2019 at - https://tusharah.blogspot.com/2019/08/how-to-run-postgresql-on-docker.html
... View more