Enabling PostgreSQL replication on Jelastic platform in InfoboxCloud cloud

Replication is an important process for any database server. By setting up replication from one server to several additional servers, you can significantly reduce the probability of data loss.

This article is about setting up a PostgreSQL cluster. With Jelastic, you can easily create a cluster with one or more backup servers, ready to take over the basic operations if the main server fails. In this case, the primary server is in archive mode, while the backup server is in recovery mode, reading data from the primary server's WAL (Write-Ahead Logging) transaction log.


')
In the event of a complete failure of the main server, the contents of the WAL will be transferred to the backup servers with minimal delay.

In today's article we will look at the process of creating PostgreSQL database replication to one or more backup servers. The backup database will be used only in read mode. This is the fastest replication method available for WAL data sent from the primary to the backup server.

Creating the environment

The first step is to create two identical environments with a PostgreSQL database: one for the main database and one for the backup.

1. Log into your Jelastic account and click on the "Create Environment" button, select PostgreSQL 9 as the database and the number of used cloudsets. Then specify the name of the environment for this base (main).



After creating the environment in the Jelastic panel, you will see:




2. Now you need to create another environment with PostgreSQL 9 or clone an existing one. This will be a backup database hosted on another node to ensure the safety and reliability of data storage.



So, now we have 2 identical environments with PostgreSQL databases.

Note: If replication is configured for an existing database, you need to copy the data from the wizard to the standby server before enabling replication.

Replication setup

1. On the node of the main database, click "Configuration"



and go to the conf folder. Modify the following parameters in the postgresql.conf file to enable streaming replication:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3



2. Go to the pg_hba.conf file on the main database (in the conf folder) and open access for the backup server:

host replication all {standby_ip_address}/32 trust

Instead of {standby_ip_adress}, you need to insert the address of your backup standby server. You can see it here:



A window will open with an internal IP address:



Here's what we got:



3. Then, open the conf folder on the backup database node, go to postgresql.conf and turn on hot standby . This will ensure a permanent connection to the main server for reading data. In this case, the backup server itself will be in standby / copy mode.

hot_standby = on



4. Create a recovery.conf file in the conf folder of the backup server (in the same directory as postgresql.conf and pg_hba.conf ), containing the following lines:

standby_mode = 'on'
primary_conninfo = 'host={master_ip_address}'
where {master_ip_address} is the master node's ip address.



5. Restart both nodes (primary and backup server) to apply the settings.

Check what happened

1. Log in to the main database via phpPgAdmin (the credentials were sent to you when creating the environment and the database itself) and create a new database.





2. Login to the backup database and check if a copy of the main database is created. If you created a standby node in master cloning mode, you can reset the password to gain access to the node. When the standby mode is on, the backup node will not be able to log in via the web interface. To verify that replication is working, rename recovery.conf to recovery.con . Restart the backup node. Now you can log in via the web interface and make sure that replication is working and habrahabr database has appeared. After that, do not forget to rename recovery.con to recovery.conf back and restart the backup node.



Just a few simple steps will allow you to create a reliable database cluster!

Good luck using the Jelastic platform in InfoboxCloud!