Replication PostgreSQL Slavic multi

en:Replication PostgreSQL Slavic multi he:שכפול מרובה PostgreSQL סלאבית ro:Replicare PostgreSQL slave multi ru:Multi PostgreSQL славянских репликации pl:Replikacja wielu słowiańskich PostgreSQL ja:レプリケーション PostgreSQL スラブ マルチ ar:النسخ المتماثل السلافية كيو متعدد zh:复制 PostgreSQL 斯拉夫语多 de:Replikation PostgreSQL slawischen multi nl:Replicatie PostgreSQL Slavische multi it:Replica PostgreSQL slavo multi pt:Replicação PostgreSQL eslavo multi es:Replicación multi de PostgreSQL Slavic fr:Replication PostgreSQL multi slaves

This article has been created by an automatic translation software. You can view the article source here.

Introduction

This page deals with the implementation of an asynchronous PostgreSQL streaming replication with one master and two Slavic . Our Slavic will be configured as hot standby, that is to say that it will be possible to run queries (read only ) on these.

Context of this placement :

We have 3 Debian servers 8 (last updated ) :

Postgres 01 (10.1.1.75) : will be our Master

Postgres02 (10.1.1.90) : will be a slave

Postgres03 (10.1.1.199) : will be a second slave

Specified commands will be to perform as root when they are preceded by "#" and as Postgres system user when they are preceded by "$".

Installation of PostgreSQL and pre-configuration of the three servers

All commands in this part are to be done on each of the servers.

As a first step, it is recommended to have one file /etc/hosts populated with the matches of each server, like this (by adapting with the hostnames and ip addresses of your own servers ) :

# cat /etc/hosts
127.0.0.1       localhost

10.1.1.75       Postgres 01
10.1.1.90       Postgres 02
10.1.1.199      Postgres 03

Add the deposit APT PostgreSQL :

# echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" > /etc/apt/sources.list.d/postgres.list

We import the public key of this deposit :

# gpg --keyserver pgpkeys.mit.edu --recv-key 7FCC7D46ACCC4CF8 && gpg -a --export 7FCC7D46ACCC4CF8 | apt-key add -
gpg: directory `/root/.gnupg' created
gpg: new configuration file `/root/.gnupg/gpg.conf' created
gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
gpg: keyring `/root/.gnupg/secring.gpg' created
gpg: keyring `/root/.gnupg/pubring.gpg' created
gpg: requesting key ACCC4CF8 from hkp server pgpkeys.mit.edu
gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key ACCC4CF8: public key "PostgreSQL Debian Repository" imported
gpg: no ultimately trusted keys found
gpg: Total number processed: 1
gpg:               imported: 1  (RSA: 1)
OK

It updates the cache APT to take into account the deposit :

# apt-get update

Installing package postgresql (install the latest stable version is the 9.5 at the time where these lines are written ) :

# apt-get install postgresql

We define a password (of his choice but secure ) and generates a ssh key pair (without passphrase ) for the system postgres user :

# passwd postgres

# su - postgres

$ ssh-keygen

We copy the user postgres from and between each server public ssh key :

Since the first server (postgres01 in our case ) towards the second (postgres02 for us ) and troisiseme (postgres03) :

root@postgres01:~# su - postgres

postgres@postgres01:~$ ssh-copy-id postgres02

postgres@postgres01:~$ ssh-copy-id postgres03

From the second to the first and third server :

root@postgres02:~# su - postgres

postgres@postgres02:~$ ssh-copy-id postgres01

postgres@postgres02:~$ ssh-copy-id postgres03

From the third to the first and second server :

root@postgres03:~# su - postgres

postgres@postgres03:~$ ssh-copy-id postgres01

postgres@postgres03:~$ ssh-copy-id postgres02

The Master Configuration

Do only on the master (postgres01 in our case ) :

It creates the role /replication user :

# su - postgres

$ psql -c "CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD '<mot de passe repuser>';"

Note : It defines a connection limit of 2 because we have 2 Slavic

Editing the main postgresql configuration file /etc/postgresql/9.5/main/postgresql.conf to configure the following directives :

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -av %p postgres@<ip du premier slave>:/var/lib/postgresql/wal_archive/%f'
max_wal_senders = 2
wal_keep_segments = 256
hot_standby = on

Note: We enable archiving to our second server (Customize your server ip address ) for even more precautions. We also define the parameters hot_standby, well that ignored on a master, where it should be demoted to slave in the future.

Now editing postgresql authentication file /etc/postgresql/9.5/main/pg_hba.conf to add the permission of our replication user to connect from our Slavic or the following line at the end of the file :

hostssl replication     repuser <ip du réseau de vos serveurs>/24     md5

Please adapt this line according to the name of your replication and network user (or respective ip addresses ) of your Slavic

It restarts the postgresql service to take into account our configuration (in root ) :

# systemctl restart postgresql

This ensures that the service is started properly :

root@postgres01:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Thu 2016-06-02 12:06:28 CEST; 22s ago
  Process: 77056 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 77056 (code=exited, status=0/SUCCESS)

root@postgres01:~# ps -u postgres u
USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  77038  0.0  4.4 227300 21836 ?        S    12:06   0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.
postgres  77040  0.0  0.8 227300  4160 ?        Ss   12:06   0:00 postgres: checkpointer process
postgres  77041  0.0  1.0 227300  4968 ?        Ss   12:06   0:00 postgres: writer process
postgres  77042  0.0  1.7 227300  8776 ?        Ss   12:06   0:00 postgres: wal writer process
postgres  77043  0.0  1.3 227728  6708 ?        Ss   12:06   0:00 postgres: autovacuum launcher process
postgres  77044  0.0  0.6  82244  3392 ?        Ss   12:06   0:00 postgres: archiver process
postgres  77045  0.0  0.8  82244  4244 ?        Ss   12:06   0:00 postgres: stats collector process

Configuration of Slavs

On the first slave

We start by stop the postgresql service :

# systemctl stop postgresql

Editing the main postgresql configuration file /etc/postgresql/9.5/main/postgresql.conf to configure the same guidelines as the master, by tailoring just the ip of the first slave by the second slave in the command rsync to archive either :

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -av %p postgres@<ip du second slave>:/var/lib/postgresql/wal_archive/%f'
max_wal_senders = 2
wal_keep_segments = 256
hot_standby = on

Now editing postgresql authentication file /etc/postgresql/9.5/main/pg_hba.conf to add the permission of our replication user to connect from our other servers (This will be used when this slave expected promoted master ) :

hostssl replication     repuser <ip du réseau de vos serveurs>/24     md5

Note : With this configuration similar to our master this will allow to easily promote this slave as master in case of need.

For the operations below, log in as system postgres user :

# su – postgres

We create the destination directory for the wal of the master archives :

$ mkdir /var/lib/postgresql/wal_archive

It removes the postgresql data directory :

$ rm -rf /var/lib/postgresql/9.5/main

It makes the base backup (customize the ip of your master and the name of your replication user ), the password of your replication user you will be asked :

$ pg_basebackup -h <ip de votre master> -D /var/lib/postgresql/9.5/main -U repuser -v -P --xlog

It configures replication by creating the file /var/lib/postgresql/9.5/main/recovery.conf with the following parameters :

standby_mode = on
primary_conninfo = 'host=<ip de votre master> port=5432 user=repuser password=<mot de passe de repuser> sslmode=require application_name=<hostname de votre slave>'
trigger_file = '/var/lib/postgresql/9.5/postgres.trigger'

Note : The trigger file is the file we create when we want his slave stops replication and will begin accepting entries — when you want to promote it as master. Before promoting a slave in case of crash of her master, ensure that the initial master will not rise to avoid corruption.

We return root and it starts the service :

# systemctl start postgresql

This ensures that the service started correctly :

# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Thu 2016-06-02 12:53:42 CEST; 1min 6s ago
  Process: 8894 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 8894 (code=exited, status=0/SUCCESS)

# ps -u postgres u
USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres   8878  0.0  4.4 227308 21892 ?        S    12:53   0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.
postgres   8879  0.0  1.1 227376  5700 ?        Ss   12:53   0:00 postgres: startup process   recovering 00000001000000000000000
postgres   8880  0.0  0.8 227308  4140 ?        Ss   12:53   0:00 postgres: checkpointer process
postgres   8881  0.0  1.0 227308  5236 ?        Ss   12:53   0:00 postgres: writer process
postgres   8882  0.0  0.6  82252  3316 ?        Ss   12:53   0:00 postgres: stats collector process
postgres   8883  0.0  1.7 238064  8520 ?        Ss   12:53   0:00 postgres: wal receiver process   streaming 0/30003E0

If it connects to our master, we can already check our replication between the master and the first slave is functional :

It connects to our master and it connects in system postgres user :

# su - postgres

Then connecting to postgresql :

$ psql

It enables the extended display and we check our replication :

postgres=# \x
Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 78879
usesysid         | 16384
usename          | repuser
application_name | postgres02
client_addr      | 10.1.1.90
client_hostname  |
client_port      | 49009
backend_start    | 2016-06-02 12:53:36.641823+02
backend_xmin     |
state            | streaming
sent_CRElocation    | 0/30004C0
write_location   | 0/30004C0
flush_location   | 0/30004C0
replay_location  | 0/30004C0
sync_priority    | 0
sync_state       | async

Our asynchronous streaming replication with our first slave is well made. This stage has a Master replication /Classic slave (a single slave asynchronous streaming )

On the second slave

We Redid the same what was done on the first slave obviously adapting its hostname in the file recovery.conf and ip address in the command of archiving in the main configuration file if you also want to enable archiving on this second slave.

Verification and testing of our multi master replication Slavic

It connects to the master and it is logged in user postgres :

# su – postgres

Then connecting to postgresql :

$ psql

It enables the extended display and we check our replication :

postgres=# \x
Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 78879
usesysid         | 16384
usename          | repuser
application_name | postgres02
client_addr      | 10.1.1.90
client_hostname  |
client_port      | 49009
backend_start    | 2016-06-02 12:53:36.641823+02
backend_xmin     |
state            | streaming
sent_location    | 0/5000140
write_location   | 0/5000140
flush_location   | 0/5000140
replay_location  | 0/5000140
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+------------------------------
pid              | 82725
usesysid         | 16384
usename          | repuser
application_name | postgres03
client_addr      | 10.1.1.199
client_hostname  |
client_port      | 51754
backend_start    | 2016-06-02 14:31:43.759683+02
backend_xmin     |
state            | streaming
sent_location    | 0/5000140
write_location   | 0/5000140
flush_location   | 0/5000140
replay_location  | 0/5000140
sync_priority    | 0
sync_state       | async

So there well our 2 replication with our two Slavic servers (postgres02 and postgres 03).

Still connected to postgresql on the master, it creates a data base (ex : checkrep ) :

postgres=# CREATE DATABASE checkrep;

It checks that the database is well replicated on our 2 slaves :

root@postgres02:~# su - postgres

postgres@postgres02:~$ psql
psql (9.5.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 checkrep  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

root@postgres03:~# su - postgres
postgres@postgres03:~$ psql
psql (9.5.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 checkrep  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

The database that we created on our master is is so well automatically replicated to our two Slavic.

To delete, since the master :

postgres=# DROP DATABASE checkrep;

Sort by date Sort by score

Enable comment auto-refresher

You are not allowed to post comments.