PostgreSQL9 PPAS9 stream replication Configuration

PostgreSQL9 PPAS9 stream replication Configuration

Set up a stream replication cluster for PostgreSQL.

Host:

PPAS1 PPAS2

1
Install PPAS 9.0
[Root @ ppas1 ~] # Setenforce 0
[Root @ ppas1 ~] # Ppasmeta-9.0.4.14-linux-x64.run-All installation

[Root @ ppas1 ~] # Vim/etc/bashrc
Join:
PATH = $ PATH:/opt/PostgresPlus/9.0AS/binexport PATH
-- Logout and log on to the system again
Install PPAS 9.0
[Root @ ppas2 ~] # Setenforce 0
[Root @ ppas2 ~] # Ppasmeta-9.0.4.14-linux-x64.run-All installation
[Root @ ppas2 ~] # Vim/etc/bashrc
Join:
PATH = $ PATH:/opt/PostgresPlus/9.0AS/binexport PATH
-- Logout and log on to the system again
2
Create an archive directory
[Root @ ppas1 ~] # Mkdir/opt/ppas_arch
[Root @ ppas1 ~] # Chown login isedb. enterprisedb/opt/ppas_arch
Create an archive directory
[Root @ ppas2 ~] # Mkdir/opt/ppas_arch
[Root @ ppas2 ~] # Chown login isedb. enterprisedb/opt/ppas_arch
3
[Root @ ppas1 ~] # Mkdir/opt/PostgresPlus/9.0/. ssh
[Root @ ppas1 ~] # Chown unzip isedb. Unzip isedb/opt/PostgresPlus/9.0/. ssh/
[Root @ ppas1 ~] # Ssh root@192.168.122.12 "mkdir. ssh"
[Root @ ppas1 ~] # Su-enterprisedbbash-4.1 $ ssh-keygen-t rsabash-4.1 $ scp ~ /. Ssh/id_rsa.pub root@192.168.122.12:. ssh/
Id_rsa_ppas1.pub
4
[Root @ ppas2 ~] # Touch/root/. ssh/authorized_keys [root @ ppas2 ~] # Cat/root/. ssh/id_rsa_ppas1.pub>/root/. ssh/
Authorized_keys
5
Bash-4.1 $ ssh root@192.168.122.12Last login: Mon Jun 27 22:27:09 2011 from 192.168.122.2
[Root @ ppas1 ~] # LogoutConnection to 192.168.122.12 closed.
Bash-4.1 $ logout [root @ ppas1 ~] #
6
[Root @ ppas2 ~] # Mkdir/opt/strongsplus/9.0/. ssh [root @ ppas2 ~] # Chown unzip isedb. Unzip isedb/opt/PostgresPlus/9.0/. ssh/
[Root @ ppas2 ~] # Ssh root@192.168.122.11 "mkdir. ssh"
[Root @ ppas2 ~] # Su-enterprisedbbash-4.1 $ ssh-keygen-t rsabash-4.1 $ scp ~ /. Ssh/id_rsa.pub root@192.168.122.11:. ssh/
Id_rsa_ppas2.pub
7
[Root @ ppas1 ~] # Touch/root/. ssh/authorized_keys [root @ ppas1 ~] # Cat/root/. ssh/id_rsa_ppas2.pub>/root/. ssh/
Authorized_keys
8
Bash-4.1 $ ssh root@192.168.122.11Last login: Mon Jun 27 22:31:14 2011 from 192.168.122.1 [root @ ppas1 ~] # LogoutConnection to 192.168.122.11 closed.
Bash-4.1 $ logout [root @ ppas2 ~] #
9
Configure Stream Replication for PPAS
[Root @ ppas1 ~] # 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' # used for testing only
[Root @ ppas1 ~] # Echo "host replication enterprisedb192.168.122.11/32 trust">/opt/PostgresPlus/9.0AS/data/pg_assist.conf [root @ ppas1 ~] # Echo "host replication enterprisedb192.168.122.12/32 trust">/opt/PostgresPlus/9.0AS/data/pg_assist.conf [root @ ppas1 ~] # Echo "host all 192.168.122.11/32 trust">/opt/PostgresPlus/9.0AS/data/pg_hba.conf
[Root @ ppas1 ~] # Echo "host all 192.168.122.12/32 trust">/opt/PostgresPlus/9.0AS/data/pg_hba.conf
10
[Root @ ppas2 ~] #/Etc/init. d/ppas-9.0 stop
11
Full backup of PPAS1 data to PPAS2
[Root @ ppas1 ~] # Edb-psql-U enterprisedb edb-c "SELECTpg_start_backup ('label', true );"
[Root @ ppas1 ~] # Rsync-a/opt/PostgresPlus/9.0AS/data/
Root@192.168.122.12:/opt/PostgresPlus/9.0AS/data/-- excludepostmaster. pid
[Root @ ppas1 ~] # Edb-psql-U enterprisedb edb-c "SELECTpg_stop_backup ();
12
[Root @ ppas2 ~] # Vim/opt/PostgresPlus/9.0AS/data/recovery. confstandby_mode = 'on'
Primary_conninfo = 'host = 192.168.122.11 port = 5444
User = incluisedb 'trigger _ file = '/opt/PostgresPlus/9.0AS/data/recovery_trigger' restore _ command = 'scp-Cp root@192.168.122.11: /opt/ppas_arch/% f "% p "'
[Root @ ppas2 ~] # Chown enterprisedb. enterprisedb/opt/PostgresPlus/
9.0AS/data/recovery. conf [root @ ppas2 ~] #/Etc/init. d/ppas-9.0 start
13

The Stream Replication of the two servers has been completed.

############################

Keep the following as remarks

14
[Root @ ppas1 ~] # Mkdir/var/run/pgpool
15
[Root @ ppas1 ~] # Cp/opt/PostgresPlus/9.0AS/etc/pgpool. conf. samplestream/opt/PostgresPlus/9.0AS/etc/pgpool. conf
[Root @ ppas1 ~] # 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
[Root @ ppas1 ~] # Edb-psql-U enterprisedb edb-c "select md5 ('1q2w3e4r ');"
Md5
---------------------------------
5416d7cd6ef195a0f7622a9c56b55e84
(1 row)
[Root @ ppas1 ~] # Echo "enterprisedb: 5416d7cd6ef195a0f7622a9c56b55e84">/opt/PostgresPlus/9.0AS/etc/pcp. conf
17
Start PGPool
[Root @ ppas1 ~] #/Opt/PostgresPlus/9.0AS/bin/pgpool-f/opt/PostgresPlus/9.0AS/etc/pgpool. conf-F/opt/PostgresPlus/9.0AS/etc/
Pcp. conf
[Root @ ppas1 ~] # Netstat-natulp | grep 9999
Tcp 0 0 127.0.0.1: 9999 0.0.0.0: * LISTEN 2434/pgpool
[Root @ ppas1 ~] #

18
[Root @ ppas1 ~] # 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;
Id
---
5
6
(2 rows)

19
[Root @ ppas1 ~] #/Etc/init. d/ppas-9.0 stopStopping Postgres Plus Advanced Server 9.0:
Waiting for server to shut down... doneserver stopped

20
[Root @ ppas2 ~] # 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 processing the request.
The above connection error occurs because the pgpool routes point to PPAS1 at this time, but the database of PPAS1 has stopped.
[Root @ ppas2 ~] # 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 this time, the pgpool will switch the connection to PPAS2, and set PPAS1 to a fault state within the pgpool until the database of PPAS1 is available again.

21
Edb = # insert into a values (7 );
ERROR: cannot execute INSERT in a read-only transaction
Since PPAS2 is still in the Slave status of Stream Replication, you cannot write data.
Edb = # \ q
22
[Root @ ppas2 ~] # Touch/opt/PostgresPlus/9.0AS/data/recovery_trigger
[Root @ ppas2 ~] # 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 is too fast, so the system has not changed to the Master status.
Edb = # insert into a values (7 );
INSERT 0 1
The write operation will be completed in 1-2 seconds.
Edb = #

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: