Postgresql9 PPAS9 stream replication configuration, postgresql9ppas9
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 = #
-----------------
Please refer to the following source for reprinting:
Blog.csdn.net/beiigang
PostgreSQL921 requires configuration after installation?
You can tune the settings in postgresql. conf, such as the shared memory size and checkpoint segment.
How to copy the table structure and fully copy the table in the postgresql database, such as create table a as select * from B in oracle
Oracle example
SQL> SELECT 2*3 FROM 4 test_main; ID VALUE ---------- 1 ONE 2 TWOSQL> CREATE TABLE test_t AS SELECT * FROM test_main; the TABLE has been created. SQL> select * FROM test_t; ID VALUE ---------- -------- 1 ONE 2 TWO
Postgresql example
Test = # create table test_t AS (SELECT * FROM test_main); SELECT 2 Test = # select * from test_t; id | value ---- + ------- 1 | ONE 2 | TWO (TWO rows of records) I don't know if Postgresql requires brackets. Haha.
