A major new feature is that you can implement flow Postgresql9.0 copy, it's a bit like ORACLE in the DataGuard (Physial Standby)
However, this method is more secure than Oracle's DataGuard, more efficient, because the database synchronization from the main library in real time, almost no time difference.
And Oracle's DataGuard application received from the library and the main library logs delay, I tested the next, probably a few minutes, the delay depends on the specific business busy main library.
However, this method is more secure than Oracle's DataGuard, more efficient, because the database synchronization from the main library in real time, almost no time difference.
And Oracle's DataGuard application received from the library and the main library logs delay, I tested the next, probably a few minutes, the delay depends on the specific business busy main library.
Here are the detailed steps to replicate the experiment flow
An environmental information
PG version: PostgreSQL 9.0beta3
OS version: Red Hat Enterprise Linux Server release 5.5
Hardware environment: install two virtual machines on WINDOWS XP
Master Information IP: 192.168.1.25
Standby Info IP: 192.168.1.26
PG version: PostgreSQL 9.0beta3
OS version: Red Hat Enterprise Linux Server release 5.5
Hardware environment: install two virtual machines on WINDOWS XP
Master Information IP: 192.168.1.25
Standby Info IP: 192.168.1.26
Step 2 Because the main library has been installed Postgresql, install PG on the main library is not presented here
3 configuration parameters from the library hosts (On standby)
3.1 Setting / etc / sysctl.conf, add the following
kernel.shmmni = 4096
kernel.sem = 501000 6412800000 501000 12800
fs.file-max = 767246
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 0
net.ipv4.ip_conntrack_max = 655360
3.1 Setting / etc / sysctl.conf, add the following
kernel.shmmni = 4096
kernel.sem = 501000 6412800000 501000 12800
fs.file-max = 767246
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 0
net.ipv4.ip_conntrack_max = 655360
sysctl-p into force
3.2 Set / etc / security / limits.conf add the following
* Soft nofile 131072
* Hard nofile 131072
* Soft nproc 131072
* Hard nproc 131072
* Soft core unlimited
* Hard core unlimited
* Soft memlock 50000000
* Hard memlock 50000000
* Soft nofile 131072
* Hard nofile 131072
* Soft nproc 131072
* Hard nproc 131072
* Soft core unlimited
* Hard core unlimited
* Soft memlock 50000000
* Hard memlock 50000000
3.3 Setting / etc / pam.d / login, add the following
session required pam_limits.so
session required pam_limits.so
4 created on the primary database to create the super user (On Master)
4.1 Creating a User
CREATE USER repuser
SUPERUSER
LOGIN
CONNECTION LIMIT 2
ENCRYPTED PASSWORD 'repuser';
4.1 Creating a User
CREATE USER repuser
SUPERUSER
LOGIN
CONNECTION LIMIT 2
ENCRYPTED PASSWORD 'repuser';
4.2 Set master library pg_hba.conf
host replication repuser 192.168.1.26/16 md5
host replication repuser 192.168.1.26/16 md5
Description: Super User repuser library is used to read the main library (Master) of the WAL stream from the library, and in 4 2 set permissions to allow only host 192.168.1.26 (Standby node) in md5 encrypted access.
5 Set the log parameters, recording connection information (Both Master and Standby Library)
log_connections = on
log_connections = on
Description: "log_connections" parameter is used to record the database connection information, open the switch, so that it can be better in the next CSV log observations Master and Standby database library situation.
6 Set the library database postgresql.conf (On Master)
max_wal_senders = 1 - WAL STREAM log the number of the sending process
wal_level = hot_standby - Main Library is set to hot_standby, from the library to open in READ-ONLY mode
archive_mode = on
archive_command = 'cd.'
wal_keep_segments = 64
max_wal_senders = 1 - WAL STREAM log the number of the sending process
wal_level = hot_standby - Main Library is set to hot_standby, from the library to open in READ-ONLY mode
archive_mode = on
archive_command = 'cd.'
wal_keep_segments = 64
Description, key parameters "max_wal_senders" refers to the number of processes wal sent, I am here only one from the library, so set to 1, if more than one from the library, you should be set from a number of libraries, because in the Master database, each Taiwan from the library needs a WAL WAL logs are sent to the sending process log stream from the library.
On the one hand is to introduce this parameter official website.
max_wal_senders (integer)
Specifies the maximum number of concurrent connections from standby servers (ie, the maximum number
of simultaneously running WAL sender processes). The default is zero. This parameter can only be set
at server start. wal_level must be set to archive or hot_standby to allow connections from standby servers.
On the one hand is to introduce this parameter official website.
max_wal_senders (integer)
Specifies the maximum number of concurrent connections from standby servers (ie, the maximum number
of simultaneously running WAL sender processes). The default is zero. This parameter can only be set
at server start. wal_level must be set to archive or hot_standby to allow connections from standby servers.
7 Main Library FULL
7.1 tart the backup (On Master)
select pg_start_backup ('base backup for log streaming');
7.1 tart the backup (On Master)
select pg_start_backup ('base backup for log streaming');
7.2 COPY data files
tar czvf pg_root.tar.gz pg_root - exclude = pg_root / pg_xlog
Since the $ PGDATA / pg_xlog not necessary here to exclude this directory to save time.
tar czvf pg_root.tar.gz pg_root - exclude = pg_root / pg_xlog
Since the $ PGDATA / pg_xlog not necessary here to exclude this directory to save time.
7.3 COPY data file to the standby host and extract
scp pgdata.tar.gz pgb :/ database
scp pgdata.tar.gz pgb :/ database
7.4 Data COPY finished, the end of the backup Stop the backup (On Master)
select pg_stop_backup (), current_timestamp;
select pg_stop_backup (), current_timestamp;
Description: It is recommended unanimously main library and configuration information from the library, including hardware information, directory structure, host configuration.
8 Modify from the library postgresql.conf (On standby)
hot_standby = on - can perform read-only operations from the library
hot_standby = on - can perform read-only operations from the library
9 set from the library recovery.conf (On standby)
standby_mode = 'on' - marked as STANDBY SERVER PG
primary_conninfo = 'host = 192.168.1.25 port = 1921 user = repuser password = repuser'
trigger_file = '/ tmp/postgresql.trigger.1921'
standby_mode = 'on' - marked as STANDBY SERVER PG
primary_conninfo = 'host = 192.168.1.25 port = 1921 user = repuser password = repuser'
trigger_file = '/ tmp/postgresql.trigger.1921'
Description: Key Parameters "primary_conninfo (string)", where the configuration of the hostname, port, username, password,
More explanation on this parameter can refer to the official website, where more information about connection parameters can be configured, there is not explained
http://www.postgresql.org/docs/9.0/static/libpq-connect.html
More explanation on this parameter can refer to the official website, where more information about connection parameters can be configured, there is not explained
http://www.postgresql.org/docs/9.0/static/libpq-connect.html
10 removed from the library and create pg_xlog directory (On standby)
$ Rm-f $ PGDATA / postmaster.pid
$ Mkdir-p $ PGDATA / pg_xlog
$ Rm-f $ PGDATA / postmaster.pid
$ Mkdir-p $ PGDATA / pg_xlog
11 start from the library, and observe csvlog
11.1 observed CSVLOG
2011-01-08 17:22:49.757 CST,,, 24243,, 4d282ce9.5eb3, 2, ,2011-01-08 17:22:49 CST,, 0, LOG, 00000, "entering standby mode",, ,,,,,,, ""
2011-01-08 17:22:49.887 CST,,, 24244,, 4d282ce9.5eb4, 1, ,2011-01-08 17:22:49 CST,, 0, LOG, 00000, "streaming replication successfully connected to primary ",,,,,,,,," "
2011-01-08 17:22:52.677 CST,,, 24243,, 4d282ce9.5eb3, 3, ,2011-01-08 17:22:49 CST, 1/0, 0, LOG, 00000, "redo starts at 1/94000020 ",,,,,,,,," "
2011-01-08 17:22:52.696 CST,,, 24243,, 4d282ce9.5eb3, 4, ,2011-01-08 17:22:49 CST, 1/0, 0, LOG, 00000, "consistent recovery state reached at 1/98000000 ",,,,,,,,," "
2011-01-08 17:22:52.805 CST,,, 24241,, 4d282ce8.5eb1, 4, ,2011-01-08 17:22:48 CST,, 0, LOG, 00000, "database system is ready to accept read only connections ",,,,,,,,," "
Log "streaming replication successfully connected to primary", "database system is ready to accept read only connections"
These illustrate the flow of information has been successfully copied from the library is ready to receive the main library WAL-STREAM.
11.2 Main Library observed WAL-Sender process
[Postgres @ pg1 pg_root] $ ps-ef | grep post
postgres 27225 27166 0 17:22 00:00:05 postgres:? wal sender process repuser 192.168.1.26 (59836) streaming 1/9801E000
11.1 observed CSVLOG
2011-01-08 17:22:49.757 CST,,, 24243,, 4d282ce9.5eb3, 2, ,2011-01-08 17:22:49 CST,, 0, LOG, 00000, "entering standby mode",, ,,,,,,, ""
2011-01-08 17:22:49.887 CST,,, 24244,, 4d282ce9.5eb4, 1, ,2011-01-08 17:22:49 CST,, 0, LOG, 00000, "streaming replication successfully connected to primary ",,,,,,,,," "
2011-01-08 17:22:52.677 CST,,, 24243,, 4d282ce9.5eb3, 3, ,2011-01-08 17:22:49 CST, 1/0, 0, LOG, 00000, "redo starts at 1/94000020 ",,,,,,,,," "
2011-01-08 17:22:52.696 CST,,, 24243,, 4d282ce9.5eb3, 4, ,2011-01-08 17:22:49 CST, 1/0, 0, LOG, 00000, "consistent recovery state reached at 1/98000000 ",,,,,,,,," "
2011-01-08 17:22:52.805 CST,,, 24241,, 4d282ce8.5eb1, 4, ,2011-01-08 17:22:48 CST,, 0, LOG, 00000, "database system is ready to accept read only connections ",,,,,,,,," "
Log "streaming replication successfully connected to primary", "database system is ready to accept read only connections"
These illustrate the flow of information has been successfully copied from the library is ready to receive the main library WAL-STREAM.
11.2 Main Library observed WAL-Sender process
[Postgres @ pg1 pg_root] $ ps-ef | grep post
postgres 27225 27166 0 17:22 00:00:05 postgres:? wal sender process repuser 192.168.1.26 (59836) streaming 1/9801E000
Description: The output section is omitted, you can see "wal sender process repuser" process
11.3 WAL-receiving process observed from the library
[Postgres @ pgb pg_log] $ ps-ef | grep post
postgres 24244 24241 0 17:22 00:00:04 postgres:? wal receiver process streaming 1/9801DF00
Description: Also omitted part of the output results, you can see the "wal receiver process" process.
12 Test
12.1 Creating a user on the main library
postgres = # CREATE ROLE browser LOGIN ENCRYPTED PASSWORD 'browser'
postgres-# nosuperuser noinherit nocreatedb nocreaterole CONNECTION LIMIT 200;
CREATE ROLE
[Postgres @ pgb pg_log] $ ps-ef | grep post
postgres 24244 24241 0 17:22 00:00:04 postgres:? wal receiver process streaming 1/9801DF00
Description: Also omitted part of the output results, you can see the "wal receiver process" process.
12 Test
12.1 Creating a user on the main library
postgres = # CREATE ROLE browser LOGIN ENCRYPTED PASSWORD 'browser'
postgres-# nosuperuser noinherit nocreatedb nocreaterole CONNECTION LIMIT 200;
CREATE ROLE
Verify from the library
postgres = # \ du
List of roles
Role name | Attributes | Member of
----------- + ----------------------------------- + - ---------
browser | No inheritance + | {}
Description: Sure enough, from the library on immediately created a new user 'browser'
postgres = # \ du
List of roles
Role name | Attributes | Member of
----------- + ----------------------------------- + - ---------
browser | No inheritance + | {}
Description: Sure enough, from the library on immediately created a new user 'browser'
Create a table space (On Master) on 12.2 Main Library
mkdir-p / database / pgdata / pg_tbs / tbs_browser
From the library also run mkdir-p / database / pgdata / pg_tbs / tbs_browser (On Sandby)
postgres = # create tablespace tbs_browser owner skytf LOCATION '/ database / pgdata / pg_tbs / tbs_browser';
CREATE TABLESPACE
mkdir-p / database / pgdata / pg_tbs / tbs_browser
From the library also run mkdir-p / database / pgdata / pg_tbs / tbs_browser (On Sandby)
postgres = # create tablespace tbs_browser owner skytf LOCATION '/ database / pgdata / pg_tbs / tbs_browser';
CREATE TABLESPACE
- From the library in the verification
postgres = # \ db
List of tablespaces
Name | Owner | Location
------------- + ---------- + ------------------------- ------------
pg_default | postgres |
pg_global | postgres |
tbs_browser | skytf | / database / pgdata / pg_tbs / tbs_browser
tbs_mydb | skytf | / database / pgdata / pg_tbs / tbs_mydb
Table space "tbs_browser" also came immediately created
postgres = # \ db
List of tablespaces
Name | Owner | Location
------------- + ---------- + ------------------------- ------------
pg_default | postgres |
pg_global | postgres |
tbs_browser | skytf | / database / pgdata / pg_tbs / tbs_browser
tbs_mydb | skytf | / database / pgdata / pg_tbs / tbs_mydb
Table space "tbs_browser" also came immediately created
12.3 Creating a database on the main library
postgres = # CREATE DATABASE browser
postgres-# WITH OWNER = skytf
postgres-# TEMPLATE = template0
postgres-# ENCODING = 'UTF8'
postgres-# TABLESPACE = tbs_browser;
CREATE DATABASE
postgres = # CREATE DATABASE browser
postgres-# WITH OWNER = skytf
postgres-# TEMPLATE = template0
postgres-# ENCODING = 'UTF8'
postgres-# TABLESPACE = tbs_browser;
CREATE DATABASE
- Verify from the library
postgres = # \ l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------- + ---------- + ---------- + ----------- + ---- --- + -----------------------
browser | skytf | UTF8 | C | C |
mydb | skytf | UTF8 | C | C |
postgres | postgres | UTF8 | C | C | = Tc / postgres +
| | | | | Postgres = CTc / postgres
template0 | postgres | UTF8 | C | C | = c / postgres +
| | | | | Postgres = CTc / postgres
template1 | postgres | UTF8 | C | C | = c / postgres +
| | | | | Postgres = CTc / postgres
Database from the library "browser" also once had almost no delay.
postgres = # \ l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------- + ---------- + ---------- + ----------- + ---- --- + -----------------------
browser | skytf | UTF8 | C | C |
mydb | skytf | UTF8 | C | C |
postgres | postgres | UTF8 | C | C | = Tc / postgres +
| | | | | Postgres = CTc / postgres
template0 | postgres | UTF8 | C | C | = c / postgres +
| | | | | Postgres = CTc / postgres
template1 | postgres | UTF8 | C | C | = c / postgres +
| | | | | Postgres = CTc / postgres
Database from the library "browser" also once had almost no delay.
12.4 while observing the CSV logs from the log view, basically no delay
2011-01-08 17:28:59.335 CST, "postgres", "postgres", 24274, "[local]", 4d282e5b.5ed2, 2, "authentication" ,2011-01-08 17:28:59 CST, 2/3, 0, LOG, 00000, "connection authorized: user = postgres database = postgres",,,,,,,,, ""
2011-01-08 17:28:59.335 CST, "postgres", "postgres", 24274, "[local]", 4d282e5b.5ed2, 2, "authentication" ,2011-01-08 17:28:59 CST, 2/3, 0, LOG, 00000, "connection authorized: user = postgres database = postgres",,,,,,,,, ""
12.5 build tables from the database on
mydb => create table table3 (id integer);
ERROR: cannot execute CREATE TABLE in a read-only transaction
Description: From the library is open read-only and can only perform a read operation, you can not write.
13 monitoring streaming
CREATE OR REPLACE VIEW pg_stat_replication AS
SELECT
S.procpid,
S.usesysid,
U.rolname AS usename,
S.application_name,
S.client_addr,
S.client_port,
S.backend_start
FROM pg_stat_get_activity (NULL) AS S, pg_authid U
WHERE S.usesysid = U.oid AND S.datid = 0;
postgres = # select * from pg_stat_replication;
procpid | usesysid | usename | application_name | client_addr | client_port | backend_start
--------- + ---------- + --------- + ------------------ + -------------- + ------------- + --------------------- ----------
27225 | 64949 | repuser | | 192.168.1.26 | 59836 | 2011-01-08 17:22:05.480584 +08
(1 row)
mydb => create table table3 (id integer);
ERROR: cannot execute CREATE TABLE in a read-only transaction
Description: From the library is open read-only and can only perform a read operation, you can not write.
13 monitoring streaming
CREATE OR REPLACE VIEW pg_stat_replication AS
SELECT
S.procpid,
S.usesysid,
U.rolname AS usename,
S.application_name,
S.client_addr,
S.client_port,
S.backend_start
FROM pg_stat_get_activity (NULL) AS S, pg_authid U
WHERE S.usesysid = U.oid AND S.datid = 0;
postgres = # select * from pg_stat_replication;
procpid | usesysid | usename | application_name | client_addr | client_port | backend_start
--------- + ---------- + --------- + ------------------ + -------------- + ------------- + --------------------- ----------
27225 | 64949 | repuser | | 192.168.1.26 | 59836 | 2011-01-08 17:22:05.480584 +08
(1 row)
14 summary
These are set up streaming (also known as Hot Standby) detailed process, which is an exciting learning process,
PG's HOT STANDBY because the data provided by the timeliness and reliability of ORACLE DataGuard no better than less, on the contrary,
I also think that in this respect than to force more than Oracle, thank you so good people with open-source database.
PG's HOT STANDBY because the data provided by the timeliness and reliability of ORACLE DataGuard no better than less, on the contrary,
I also think that in this respect than to force more than Oracle, thank you so good people with open-source database.
No comments:
Post a Comment