PostgreSQL stream Replication Cluster build, this is the old file of aging
Host:
PPAS1 PPAS2
1
Installing PPAs 9.0
[Email protected] ~]# Setenforce 0
[[email protected] ~]# ppasmeta-9.0.4.14-linux-x64.run--all installed
[Email protected] ~]# VIM/ETC/BASHRC
Join:
Path= $PATH:/opt/postgresplus/9.0as/binexport PATH
--logout and re-login the system
Installing PPAs 9.0
[Email protected] ~]# Setenforce 0
[[email protected] ~]# ppasmeta-9.0.4.14-linux-x64.run--all installed
[Email protected] ~]# VIM/ETC/BASHRC
Join:
Path= $PATH:/opt/postgresplus/9.0as/binexport PATH
--logout and re-login the system
2
Create archive Directory
[Email protected] ~]# Mkdir/opt/ppas_arch
[Email protected] ~]# chown Enterprisedb.enterprisedb/opt/ppas_arch
Create archive Directory
[Email protected] ~]# Mkdir/opt/ppas_arch
[Email protected] ~]# chown Enterprisedb.enterprisedb/opt/ppas_arch
3
[Email protected] ~]# MKDIR/OPT/POSTGRESPLUS/9.0/.SSH
[Email protected] ~]# chown enterprisedb.enterprisedb/opt/postgresplus/9.0/.ssh/
[[email protected] ~]# ssh [email protected] "mkdir. SSH"
[Email protected] ~]# su-enterprisedbbash-4.1$ ssh-keygen-t rsabash-4.1$ SCP ~/.ssh/id_rsa.pub [email protected]:.ssh/
Id_rsa_ppas1.pub
4
[[email protected] ~]# touch/root/.ssh/authorized_keys[[email protected] ~]# cat/root/.ssh/id_rsa_ppas1.pub >>/ root/.ssh/
Authorized_keys
5
bash-4.1$ ssh [email protected] Login:mon June 22:27:09 from 192.168.122.2
[Email protected] ~]# logoutconnection to 192.168.122.12 closed.
bash-4.1$ Logout[[email protected] ~]#
6
[Email protected] ~]# mkdir/opt/postgresplus/9.0/.ssh[[email protected] ~]# chown enterprisedb.enterprisedb/opt/ postgresplus/9.0/.ssh/
[[email protected] ~]# ssh [email protected] "mkdir. SSH"
[Email protected] ~]# su-enterprisedbbash-4.1$ ssh-keygen-t rsabash-4.1$ SCP ~/.ssh/id_rsa.pub [email protected]:.ssh/
Id_rsa_ppas2.pub
7
[[email protected] ~]# touch/root/.ssh/authorized_keys[[email protected] ~]# cat/root/.ssh/id_rsa_ppas2.pub >>/ root/.ssh/
Authorized_keys
8
bash-4.1$ ssh [email protected] Login:mon June 22:31:14 from 192.168.122.1[[email protected] ~]# logoutconnection To 192.168.122.11 closed.
bash-4.1$ Logout[[email protected] ~]#
9
Configure the PPAs stream Replication
[Email protected] ~]# vim/opt/postgresplus/9.0as/data/postgresql.conf
Wal_level = Hot_standby
Archive_mode = On
Archive_command = ' Cp-i%p/opt/ppas_arch/%f </dev/null '
Max_wal_senders = 1
Hot_standby = On
Log_statement = ' All ' #只用于测试
[Email protected] ~]# echo "Host replication Enterprisedb192.168.122.11/32 Trust" >>/opt/postgresplus/9.0as/ Data/pg_hba.conf[[email protected] ~]# echo "Host replication Enterprisedb192.168.122.12/32 Trust" >>/opt/ Postgresplus/9.0as/data/pg_hba.conf[[email protected] ~]# echo "host all All 192.168.122.11/32 trust" >>/opt/ Postgresplus/9.0as/data/pg_hba.conf
[[email protected] ~]# echo "host all 192.168.122.12/32 Trust" >>/opt/postgresplus/9.0as/data/pg_hba.conf
10
[[email protected] ~]#/etc/init.d/ppas-9.0 stop
11
PPAS1 data is fully prepared to PPAS2
[[email protected] ~]# edb-psql-u enterprisedb edb-c "selectpg_start_backup (' label ', true);"
[Email protected] ~]# rsync-a/opt/postgresplus/9.0as/data/
[Email protected]:/opt/postgresplus/9.0as/data/--excludepostmaster.pid
[Email protected] ~]# edb-psql-u enterprisedb edb-c "Selectpg_stop_backup ();
12
[[email protected] ~]# Vim/opt/postgresplus/9.0as/data/recovery.confstandby_mode = ' on '
Primary_conninfo = ' host=192.168.122.11 port=5444
User=enterprisedb ' trigger_file = '/opt/postgresplus/9.0as/data/recovery_trigger ' Restore_command = ' SCP-CP [email Protected]:/opt/ppas_arch/%f "%p"
[Email protected] ~]# chown enterprisedb.enterprisedb/opt/postgresplus/
9.0as/data/recovery.conf[[email protected] ~]#/etc/init.d/ppas-9.0 start
13
Stream Replication for both servers has been completed
＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃
Keep the following as a note
14
[Email protected] ~]# Mkdir/var/run/pgpool
15
[Email protected] ~]# cp/opt/postgresplus/9.0as/etc/pgpool.conf.samplestream/opt/postgresplus/9.0as/etc/ Pgpool.conf
[Email protected] ~]# vim/opt/postgresplus/9.0as/etc/pgpool.conf
Modify:
BACKEND_HOSTNAME0 = ' 192.168.122.11 '
Backend_port0 = 5444
Backend_weight0 = 1
backend_data_directory0 = '/opt/postgresplus/9.0as/data '
backend_hostname1 = ' 192.168.122.12 '
Backend_port1 = 5444
BACKEND_WEIGHT1 = 1
Backend_data_directory1 = '/opt/postgresplus/9.0as/data '
16
[Email protected] ~]# edb-psql-u enterprisedb edb-c "Select MD5 (' 1q2w3e4r ');"
Md5
---------------------------------
5416d7cd6ef195a0f7622a9c56b55e84
(1 row)
[Email protected] ~]# echo "enterprisedb:5416d7cd6ef195a0f7622a9c56b55e84" >>/opt/postgresplus/9.0as/etc/ Pcp.conf
17
Start Pgpool
[Email protected] ~]#/opt/postgresplus/9.0as/bin/pgpool-f/opt/postgresplus/9.0as/etc/pgpool.conf-f/opt/ postgresplus/9.0as/etc/
Pcp.conf
[Email protected] ~]# Netstat-natulp | grep 9999
TCP 0 0 127.0.0.1:9999 0.0.0.0:* LISTEN 2434/pgpool
[Email protected] ~]#
18
[Email protected] ~]# edb-psql-p 9999-u enterprisedb edbedb-psql (9.0.4.10)
Type ' help ' for help.
edb=# CREATE TABLE A (id int);
CREATE TABLE
edb=# INSERT into a values (5), (6);
INSERT 0 2
edb=# select * from A;
Id
---
5
6
(2 rows)
19
[Email protected] ~]#/etc/init.d/ppas-9.0 stopstopping Postgres Plus Advanced Server 9.0:
Waiting for server to shut down ... Doneserver stopped
20
[Email protected] ~]# edb-psql-h 192.168.122.11-p 9999-u enterprisedbedb
Edb-psql:server closed the connection unexpectedlythis probably means the server terminated Abnormallybefore or while pro Cessing the request.
The above connection error is due to the pgpool at this time the route to PPAS1, but at this time the PPAS1 database has been stop.
[Email protected] ~]# edb-psql-h 192.168.122.11-p 9999-u enterprisedbedb
Edb-psql (9.0.4.10)
Type ' help ' for help.
edb=#
Reconnect again, at which point the Pgpool will turn the connection to PPAS2, and the PPAS1 will be faulted within the Pgpool until the PPAS1 database is re-usable.
21st
edb=# INSERT into a values (7);
Error:cannot execute INSERT in a read-only transaction
Because PPAS2 is still in the slave state of stream Replication, write data operations cannot be done
edb=# \q
22
[Email protected] ~]# Touch/opt/postgresplus/9.0as/data/recovery_trigger
[Email protected] ~]# edb-psql-h 192.168.122.11-p 9999-u enterprisedbedb
Edb-psql (9.0.4.10)
Type ' help ' for help.
edb=# INSERT into a values (7);
Error:cannot execute INSERT in a read-only transaction
The operation here is too fast, so the system has not changed to the master state
edb=# INSERT into a values (7);
INSERT 0 1
Operation in 1-2 seconds, write success
edb=#
-----------------
Reprint please specify the source:
Blog.csdn.net/beiigang
POSTGRESQL9 PPAS9 Stream Replication Configuration
