PostgreSQL 10 Master-Slave Replication

Im Durchschnitt wird dieses Tutorial PostgreSQL 10 Master-Slave Replication mit 5 bewertet, wobei 1.0 die schlechteste und 5.0 die beste Bewertung ist. Es haben insgesamt 793 Besucher eine Bewertung abgegeben.
793 0

PostgreSQL 10 Master-Slave Replication

Datenbank PostgreSQL Ubuntu

In this tutorial I will show you how to set up an asynchronous replication with the database management system PostgreSQL 10.4. PostgreSQL is an open source database server with more than 15 years of active development time. It is a powerful database with a wide range of functions, which even large loads don’t cause any problems. Some of the new features you will find in PostgreSQL 10 are:

  • Logical replication
  • Quorum Commit for Synchronous Replication
  • SCRAM-SHA-256 authentication
  • Full text search for JSON and JSONB & more

The goal of replication is to increase the availability and data security of your database. The setup will be done on Ubuntu 16.04 hosts and within a private network. For databases it is generally best practice to run them within private networks and not to make them directly accessible over the Internet. Please also keep in mind that this is an asynchronous replication. Under certain circumstances, asynchronous replication may result in data loss. As a rule, asynchronous replication is used to add an extra layer of security to the data. If you need to protect your data at all costs, you should use a synchronous configuration. With synchronous replication, the transaction must have been executed on at least two servers before the master reports a success to the client.

In your gridscale panel you can rebuild the server setup in a short time with our templates even without much experience. Just test gridscale and see the Features and the simplicity of the gridscale panel for yourself.


Preparations

For the following tutorial you need 2 Ubuntu 16.04 servers.

Master IP: 1.0.0.1 – read/write
Slave IP: 1.0.0.2 – read only

If you follow the gridscale tutorial, first create both servers with Ubuntu Template. To install Postgres you can first assign a public IP to both servers and then move the servers to a private network.

More about installing Postgres 10 on Ubuntu can be found here:

Install PostgreSQL 10 on Ubuntu 16.04 / 14.04

More information about PaaS at gridscale and how PaaS works can be found in our
Getting started with PaaS at gridscale
Platform Services from gridscale.

Postgres Master Configuration

After installing Postgres, switch to the default Postgres superuser account and log into the PostgreSQL terminal with it.

Here you create a new role for replication.

CREATE ROLE pgrep WITH REPLICATION LOGIN ;

Then set the password encryption to scram-sha-256.

postgres=# set password_encryption = 'scram-sha-256 ;

And set the password for the role.

\password pgrep

Postgres Replication Log

Customize postgresql.conf

First stop the PostgreSQL service with :

systemctl stop postgresql

In the postgresql.conf file, adjust the Postgres Master Server for replication. Choose an editor of your choice for editing (vim, nano etc.). The file is located under /etc/postgresql/10/main.

The following lines must be commented out within the file and provided with the appropriate values.

Make sure the PostgreSQL server is listening on your interface by entering the internal IP of the master host.

listen_addresses = '10.0.0.1'

After that the Write-Ahead Log settings have to be adjusted.

wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p postgres@standbyhost:/var/lib/postgresql/10/main/archive/%f'

The archive_command variable is used to archive the XLOG from the database instance to a specific location. The advantage here is that you can use any shell script you like. Options are e.g. to send the data to an FTP server, to a cloud storage provider or simply to another server. A popular method is to use rsync (as in the example above).

Hint: For the rsync method to work in the above example, you need to adjust the IP of the standby host and SSH access for rsync.

Then we continue with the settings for replication.

max_wal_senders = 3
wal_keep_segments = 64

With wal_keep_segments you set the minimum number of log segments to be stored on the master. Note that a segment is 16mb in size and in case of emergency you have enough extra memory on the master host.

Then save the file.

Because Archive Mode has been activated, a new directory must now be created for archiving in the PGDATA directory. With the following commands you create the directory, assign the necessary permissions and change the owner to User postgres.

mkdir -p /var/lib/postgresql/10/main/archive/
chmod 700 /var/lib/postgresql/10/main/archive/
chown -R postgres:postgres /var/lib/postgresql/10/main/archive/

 

pg_hba.conf

In the following the PostgreSQL Client Authentication is adjusted, the configuration file is located at /etc/postgresql/10/main/pg_hba.conf.

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5     
# IPv6 local connections:
host    all             all             ::0/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
hostssl   replication     pgrep         10.0.0.2/32             scram-sha-256

In the bottom line the role, IP address for the slave host and the authentication method are defined. Then you save and exit the file.

Postgres Slave Configuration

After the master has been configured, you switch to your slave host.

First stop the PostgreSQL service.

systemctl stop postgresql

Optionally, you can now make the same settings on postgresql.conf and pg_hba.conf on your slave as on the master (so your slave can also act as master if failover has been set up).

Then change the following line in the postgresql.conf as follows:

hot_standby = on

and save the whole thing.

Now change to your PGDATA directory.

cd /var/lib/postgresql/10/

Then rename the existing main/ directory on the slave.

mv main/ main-backup
chmod 700 main/

The next step is to copy the main directory from the master to your slave via pg_basebackup command. After entering the command you will be asked for your password for the user pgrep.

pg_basebackup -h 10.0.0.1 -D /var/lib/postgresql/10/main/ -P -U pgrep --wal-method=stream

Change to your new main/ directory

 cd main/

and create a new file named recovery.conf.

vim recovery.conf

Now copy the following configuration into the file:

standby_mode = 'on'
primary_conninfo = 'host=10.0.0.1 port=5432 user=pgrep password=[your password]'
trigger_file = '/tmp/MasterNow'
#restore_command = '[your-restore-command]''

The first line defines that the slave starts in standby mode. Under “primary_conninfo” the parameters for the connection to the master are defined. And the line “trigger_file” specifies that the slave will step in as master if a trigger file exists (for this Failover-Feature you need additional software like repmgr).

Test the replication

After adjusting the configuration of master and slave, the systems must be restarted.
So reboot to Master and Slave.

reboot now

Last but not least you can start the PostgreSQL service on master and slave.

Postgres Replication - Log File

A look into the log file tells you if everything worked out as it should.

And on the master server you can inspect your pgrep user with the following command (in the psql terminal):

select * from pg_stat_activity where username = 'pgrep' ;

Postgres active Replication User

Summary

In this tutorial you learned how to set up a database replication with Postgres. Postgres is a powerful database system, which you will enjoy for a long time because of its extensive functionalities. Studying the Postgres documentation is therefore inevitable. In the field of database replication, experience values count above all, that’s why you just have to stay tuned until Replication Pro!

If you are more interested in database topics, just have a look at the following tutorials 🙂

Install MongoDB on Ubuntu

Install Apache Cassandra on Ubuntu

Install and configure InfluxDB 1.5 on Ubuntu 16.04

Zurück zur Tutorial Übersicht Back to Tutorial Overview

In this tutorial I will show you how to set up an asynchronous replication with the database management system PostgreSQL 10.4. PostgreSQL is an open source database server with more than 15 years of active development time. It is a powerful database with a wide range of functions, which even large loads don’t cause any […]

Schade, dass dir der Artikel nicht gefallen hat.
Was sollten wir deiner Meinung nach besser machen?

Thank you for your feedback!
We will get back to you as soon as the article is finished.

Übrigens: kennst du schon unser Tutorial zum Thema How To Install Elasticsearch and the ELK Stack on Ubuntu 16.04/18.04?