PostgreSQL replication slaves multi
en:PostgreSQL replication slaves multi
he:PostgreSQL שכפול עבדים רב
ro:PostgreSQL replicare sclavi multi
ru:PostgreSQL репликации рабы multi
pl:PostgreSQL replikacji niewolników multi
ja:PostgreSQL のレプリケーション スレーブ マルチ
ar:كيو النسخ المتماثل العبيد متعددة
zh:PostgreSQL 复制奴隶多
de:PostgreSQL Replikation Sklaven multi
nl:PostgreSQL replicatie slaven multi
it:PostgreSQL replica schiavi multi
pt:Multi de escravos de replicação PostgreSQL
es:PostgreSQL replicación esclavos multi
fr:Replication PostgreSQL multi slaves
This article has been created by an automatic translation software. You can view the article source here.
Introduction | _. _. Thi page dealwith the implementation of an aynchronou streaming PostgreSQL replication with one master and two . Our Slavic will be configured as hot standby, that is to say that it will be possible to ecute queries | _. _. read-only | _. _. on these.
Context of this implementation | _. _. We have | _. _. It will do that in case this slave should promoted master | _. _. : _! _ | With this configuration similar to that of our master allows to easily promote this slave as a master in case of need. | _. _. updated | _. _. , you will be asked for the password of your user of replication | _. _. You configure replication by creating the file | _. _. with the following parameters: _! _ | : _! _ | The trigger file is the file that we create when we want his slave stop replication and begins to accept the Scriptures that is when we want to promote it as master. Before promoting a slave crash of his master, please make sure that the initial master come up no to avoid any corruption. | _. _. will be our Master | _. _. Audit and test of our multi master replication Slavs | _. _. It connects to the master and you are logged in user | _. _. will be a slave | _. _. To remove it, since the master | _. _. will be a second slave | _. _. Indicated orders will be to root when they are preceded of | _. _. and as system user when they are preceded of | _. _. Installation of PostgreSQL and pre-configuration of the three | _. _. s (All orders of this part are to be done on each of: _! _ | s.) As a first step, it is recommended to have a file | _. _. populated with matches of each: _! _ | , like this: _! _ | by adapting with the hostnames and ip addresses of your own: _! _ | s
Add filing APT PostgreSQL | _. _. We import the public key of this deposit | _. _. We update the APT cache to take into account the deposit | _. _. We install the ql package | _. _. install the latest stable version is the | _. _. at the time of this writing | _. _. It sets a password | _. _. of his choice but secure | _. _. and generates a ssh key pair | _. _. without passphrase | _. _. for the system user | _. _. We copy the public the user ssh key since and between each | _. _. Since the first | _. _. Postgres | _. _. in our case: _! _ | to the second | _. _. Postgres | _. _. for us | _. _. and troisiseme | _. _. Postgres | _. _. Since the second | _. _. to the first and third | _. _. Since the third | _. _. to the first and second | _. _. Configuration of the Master: _! _ | Do only the master: _! _ | Postgres | _. _. in our case: _! _ | We create the role | _. _. user replication: _! _ | : _! _ | We define a connection limit of | _. _. because we have | _. _. Slavic | _. _. We edit the main configuration file of postgresql | _. _. to set up the following guidelines: _! _ | : _! _ | We activate archiving to our second | _. _. customize the IP by one of your | _. _. for even more precautions, but this is not an obligation. We also define the parameters hot_standby, well that ignored on a master, in which case it should be demoted to slave in the future. :
It now publishes the postgresql authentication file | _. _. to add our user authorization of replication to connect from our Slavic or the following line at the end of the file: _. _ | Please adapt this line according to the name of your replication and network user | _. _. or respective ip addresses: _! _ | of your Slavic | _. _. Restart the postgresql service to take into account our configuration | _. _. in root | _. _. This ensures that the service has started successfully | _. _. Configuration of the Slavic | _. _. On the first slave | _. _. Let's stop the postgresql service | _. _. We edit the main configuration file of postgresql | _. _. to configure the same guidelines as the master, by tailoring just the IP address of the first slave by that of the second slave in the archive rsync command if you want to activate it either | _. _. It now publishes the postgresql authentication file | _. _. to add our replication user to connect from our other authorization | _. _. s 3 Servers Debian 8 (For operations below, log in as user system postgres | _. _. We create the directory of destination of the wal of the master archives | _. _. We remove the postgresql data directory | _. _. We do the basic | _. _. customize the ip of your master and the name of your user of replication) :
Postgres01 (10.1.1.75) : Root, we go back and we start the service: _! _ | This ensures that the service has started successfully | _. _. If we connect to our master, we can already check our replication between the master and the first slave is functional | _. _. It connects to our master and we're connecting in postgres system user | _. _. Then we connect to postgresql | _. _. We activate the extended display and we check our replication | _. _. Our asynchronous streaming replication with our first slave is well-made. At this point we have a Master replication | _. _. Classic slave | _. _. a single slave asynchronous streaming | _. _. On the second slave | _. _. Repeat the same what was done on the first slave obviously adapting its hostname in the file recovery.conf and ip address in the order of check-in in the main configuration file if you also want to enable archiving on this second slave.
Postgres02 (10.1.1.90) : Then we connect to postgresql | _. _. We activate the extended display and we check our replication | _. _. We have so much our | _. _. with our two replications | _. _. Slavic s | _. _. Postgres | _. _. and postgres | _. _. Always connected to postgresql on the master, create a test database | _. _. ex | _. _. | _. _. to confirm that replication is functional | _. _. Check that the database is well replicated on our | _. _. Slavic | _. _. The database that we created on our master's is so well automatically replicated on our two Slavic.
Postgres03 (10.1.1.199) :
"#" "$".
Servers
Servers.
/etc/hosts Server ( Servers) :
# cat /etc/hosts 127.0.0.1 localhost 10.1.1.75 postgres01 10.1.1.90 postgres02 10.1.1.199 postgres03
:
# echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" > /etc/apt/sources.list.d/postgres.list
:
# 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
:
# apt-get update
( 9.5 ) :
# apt-get install postgresql
() () :
# passwd postgres
# su - postgres
$ ssh-keygen
Server :
Server (postgres01 ) (postgres02 ) (postgres03) :
root@postgres01:~# su - postgres
postgres@postgres01:~$ ssh-copy-id postgres02
postgres@postgres01:~$ ssh-copy-id postgres03
Server :
root@postgres02:~# su - postgres
postgres@postgres02:~$ ssh-copy-id postgres01
postgres@postgres02:~$ ssh-copy-id postgres03
Server :
root@postgres03:~# su - postgres
postgres@postgres03:~$ ssh-copy-id postgres01
postgres@postgres03:~$ ssh-copy-id postgres02
(postgres01 ) :
/ :
# su - postgres
$ psql -c "CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD '<mot de passe repuser>';"
Note : 2 2
/etc/postgresql/9.5/main/postgresql.conf :
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
/etc/postgresql/9.5/main/pg_hba.conf :
hostssl replication repuser <ip du réseau de vos serveurs>/24 md5
()
() :
# systemctl restart postgresql
:
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
:
# systemctl stop postgresql
/etc/postgresql/9.5/main/postgresql.conf :
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
/etc/postgresql/9.5/main/pg_hba.conf Servers () :
hostssl replication repuser <ip du réseau de vos serveurs>/24 md5
Note :
:
# su – postgres
:
$ mkdir /var/lib/postgresql/wal_archive
:
$ rm -rf /var/lib/postgresql/9.5/main
backup () :
$ pg_basebackup -h <ip de votre master> -D /var/lib/postgresql/9.5/main -U repuser -v -P --xlog
/var/lib/postgresql/9.5/main/recovery.conf :
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 :
:
# systemctl start postgresql
:
# 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
:
:
# su - postgres
:
$ psql
:
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
/ ()
:
# su – postgres
:
$ psql
:
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
2 Server (postgres02 03).
(ex : checkrep) :
postgres=# CREATE DATABASE checkrep;
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)
:
postgres=# DROP DATABASE checkrep;
Enable comment auto-refresher