PostgreSQL replication settings from a MySQL user's perspective

Hello.
I'm Mandai, the Wild team member in charge of development.

This document summarizes the settings for using synchronous replication with PostgreSQL 9.4.
Since our company primarily uses MySQL, using PostgreSQL
, even though it's the same RDBMS, was a somewhat confusing process.

It worked much more smoothly than I expected, which made me very happy, so
I'd like to conclude by saying that I encourage everyone to try it out.

This time the process is a bit long and complicated, so I will show you the menu

PostgreSQL replication (master DB side work)

1. Create a replication user
2. Addition to pg_hba.conf
3. Editing postgresql.conf
4. Restarting the master DB

Work on the slave DB side

1. Copy the master with pg_basebackup
2. Edit postgresql.conf (slave side)
3. Creating recovery.conf
4. Editing recovery.conf
5. Restarting the slave DB

Verifying Replication

I would like to proceed with the above steps

 

PostgreSQL replication (master DB side work)

setting up replication on a database that was already running smoothly
, we worked in a truly wild situation:
As an aside, I actually get more motivated in situations like this.

As a general task

Complete the master DB configuration and restart

Copy the data from the master DB to the slave DB

Enter the settings on the slave DB side and restart

I think it would be something like this

Assuming that the master DB in the local network has been assigned the IP address 192.168.1.1 and the slave DB has been assigned the IP address 192.168.1.2

 

Create a replication user

Let's start with something simple: create a user called `repl_user` for replication.
After logging into the master database, create the user using the following query.

CREATE USER repl_user REPLICATION PASSWORD '[password]';

 

Addition to pg_hba.conf

Next, we will edit pg_hba.conf.
Here, we will configure the slave database to allow the repl_user user we created earlier to perform operations.

host replication repl_user 192.168.1.2/32 md5

 
The IP address will be changed to that of the slave.
If you are creating multiple slave databases, you will need to adjust the IP mask accordingly, but
if you are only creating one, /32 should be fine.

 

Editing postgresql.conf

Next, we'll move on to editing postgresql.conf.
Here, we'll add and modify settings related to replication.
All the necessary settings are commented out, so you can enable them by uncommenting them.
This is more user-friendly than MySQL.

The settings required for replication are as follows:

 

wal_level

This item sets how much information to include in the wal file

There are three WAL levels: minimal, archive, and hot_standby.
The default is minimal, which is sufficient for normal operation.

To set up replication, you need a level of archive or higher, so in this case we will set it to hot_standby

wal_level = hot_standby

 

synchronous_commit

This setting determines whether to wait for the wal file to be written to completion before returning success to the client after the transaction has been executed

The default is on, which returns a response to the client after the wal file is written

If this setting is turned off, transaction data will be lost if writing to the WAL file fails, resulting in a different outcome than the status notified to the client.
However, from a data perspective, only the data in the transaction will be lost; no inconsistency will occur.

This means that there is a possibility that a success message is returned to the client, but the message is not actually a success

This improves the response speed to the client, but the only problem seems to be that it will occasionally (how occasionally depends on the degree to which the transaction fails) return a lie (or is that the only problem?)

This time we will leave it on

synchronous_commit = on

 

archive_mode

By turning on archive mode, you can store wal files in an area other than the normal wal storage area

If replication delays occur, the slave system will sequentially catch up on the unapplied WAL files.
However, the lifecycle of WAL files in the default storage area is surprisingly short, so it cannot withstand long delays.
In such cases, if the delay becomes too long to recover from, the data will need to be re-entered, which adds to the operational burden.

To solve this, we copy the wal files outside the default storage area, which allows us to handle long replication delays regardless of their lifecycle

The copied wal files won't accumulate indefinitely (however, they will remain if there is a delay, so be careful), so there's no need to use a separate batch command to remove them. We'll
leave it on for now.

archive_mode = on

 

archive_command

If archive_mode = on, set the command to save the wal file. Usually, this is done with the cp command

There are several replacement strings that can be used in the command to indicate the source wal file, so we will use them

• %f ・・・ file name of the wal file
• %p ・・・ Absolute path of the target wal file

archive_command = '[Console command to save wal files]'

 

max_wal_senders

This sets the number of slave databases to connect to.
It's common to set this to the number of slave databases + 1 for connections via the pg_basebackup command.

max_wal_senders = 2 # number of slaves + 1

 

synchronous_standby_names

This lists the names of the databases that will be connected as slaves.
Connections to slaves whose names are not listed here will not be possible.

synchronous_standby_names = "db_slave1" # If you have multiple slaves, add DB names separated by commas

 

 

Restarting the master DB

That completes the settings for the master database.
It is also possible to configure the slave database here (the reason will be explained later), but this would make the postgresql.conf file difficult to understand, so for now we will configure it each time.

At this point, restart PostgreSQL for the changes to take effect

As a side note, if you search for PostgreSQL startup commands, you'll find that people use various methods, but sometimes they don't work due to differences in installation methods.
The command I always use to start PostgreSQL is as follows:

# Via initd /etc/init.d/postgresql-9.4 [start|stop|restart] # Via pg_ctl su postgres -c "/usr/pgsql-9.4/bin/pg_ctl [start|stop|restart] -D /var/lib/pgsql/9.4/data -m fast"

 
Both require you to run as root, but they worked for me with PostgreSQL installed with yum

if you want to start two PostgreSQL processes
you should use pg_ctl

 

Work on the slave DB side

We will now proceed with the slave server configuration.
We will assume that PostgreSQL is installed on the slave DB server,
and that there are no version differences.

 

Copy the master with pg_basebackup

First, we copy the entire master data set using pg_basebackup.
While it's possible to copy the $PGDATA directory to a different location than the default, if you do so, you'll need to change the $PGDATA path in the /etc/init.d/postgresql script when starting via initd.
This time, we'll copy it to the default location, /var/lib/pgsql/9.4/data.
We'll rename the existing data directory and back it up.

The pg_basebackup command to copy the data from the master is as follows:

pg_basebackup -h 192.168.1.1 -U repl_user -D /var/lib/pgsql/9.4/data -P --xlog

 
Depending on the size of the database, the copy will be completed after a short wait.
If you enter the copied data directory, you will notice that pg_hba.conf and postgresql.conf have also been copied.

As mentioned in the section on editing the postgresql.conf file on the master database, the reason why settings for the slave database can also be configured on the master is because the postgresql.conf file on the master is copied.
A database operating as a master database skips the settings required to operate as a slave database, and a database operating as a slave database skips the settings required to operate as a master database, which I think is a clever design.

 

Edit postgresql.conf (slave side)

Configure postgresql.conf for the slave

 

hot_standby

These are the settings for running PostgreSQL in hot standby mode.
When a server is running in hot standby mode, it will not be able to perform any update operations and will become a read-only database.

However, this item alone does not enable replication; the replication function will only be enabled when hot_standby is on and recovery.conf, which will be created later, exists

hot_standby = on

 

 

Creating recovery.conf

You don't need to create this file from scratch; a sample exists, so you can copy it and modify it.
The sample recovery.conf is located under /usr/pgsql-9.4/share, so copy it to /var/lib/pgsql/9.4/data.

cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf

 

 

Editing recovery.conf

There are two places to edit in recovery.conf.
Once you've done that, the replication setup is complete!

 

standby_mode

By turning on standby_mode, even after the last wal file has been received and processing has been completed, the system will continue to operate while waiting for the next wal file

standby_mode = on

 

primary_conninfo

This document contains the information needed to connect to the master database.
A particularly important point to note is whether the `application_name` field is included in the `synchronous_standby_names` list in the master database's `postgresql.conf` file.

Other than that, it's just as I read it

primary_conninfo = 'host=192.168.1.1 port=5432 user=repl_user password=[password] application_name=db_slave1'

 

 

From restarting the slave DB to checking replication

Once the above settings are complete, the slave DB is ready to be restarted.
The restart command is the same as for the master DB.

Once the restart is complete, replication should be taking place, so check the progress of replication from the master DB side

Log in to the master DB and run the following query:

select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 12345 usesysid | 12345 usename | repl_user application_name | db_slave1 client_addr | 192.168.1.2 client_hostname | client_port | 43210 backend_start | 2016-06-12 17:01:07.923879+09 backend_xmin | state | streaming sent_location | 27/B4EED5F8 write_location | 27/B4EED5F8 flush_location | 27/B4EED5F8 replay_location | 27/B4EED5C0 sync_priority | 0 sync_state | async

 
If the state item is streaming, it means that replication is currently being performed

The status of data synchronization can be seen in sent_location, write_location, flush_location, and replay_location

• sent_location is the address that indicates where the WAL files have been sent from the master DB to the slave DB
• write_location is the address that indicates how far the data has been written in the slave buffer area
• flush_location is the address that indicates how far the data has been written to the slave's disk area
• replay_location is the address that indicates how much data has been imported into the slave DB

The four addresses increase with the data, so you can see the progress at a glance

In the example above, the addresses up to sent_location, write_location, and flush_location are the same, indicating that these points are up-to-date.
Replay_location is slightly delayed, but since execution is currently in asynchronous mode, this is not a concern unless the discrepancy is significant.

 
That's all