当前位置:数据库 > Postgres >>

Postgres 9.2 Replication Configuration on CentOS 5

Postgres 9.2 Replication Configuration on CentOS 5
 
Master server configuration
 
Edit postgresql.conf
Add variables
archive_mode = on  
archive_command = 'cp "%p" /data/pgsql/data/pg_xlog/archive_status/"%f"'  
archive_timeout = 500  
wal_level = 'hot_standby'  
max_wal_senders=1  
wal_keep_segments=50  
 
Edit pg_hba.conf
Add the following line replace this ip with your slave host name
host     replication     postgres       192.168.1.15/32     trust  
 
Next thing we need to do is take data snapshot of data from master and then move that to slave server
[postgres@zwc ~]$ psql -c "select pg_start_backup('replbackup');"  
 pg_start_backup   
-----------------  
 0/64000020  
(1 row)  
[postgres@zwc ~]$ tar -cfP pg_backup.tar data  
[postgres@zwc ~]$ psql -c "select pg_stop_backup();"  
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived  
 pg_stop_backup   
----------------  
 0/640000E0  
(1 row)  
 
Move this data to slave server
[postgres@zwc ~]$ scp pg_backup.tar postgres@192.168.1.15:/tmp  
 
 
Slave server configuration
 
Move the existing data directory to a new folder
[postgres@pgstandby ~]$ pgstop  
[postgres@pgstandby ~]$ mv data data.old  
 
Unzip master server data snapshot file that is copied into this server
[postgres@pgstandby ~]$ tar -xvf /tmp/pg_backup.tar   
 
Remove postmaster.pid so standby server does not see the primary server’s pid as its own
[postgres@pgstandby data]$ rm -f postmaster.pid   
 
Edit postgresql.conf
Add variables
hot_standby = on  
 
Edit recovery.conf file
Add variables
[postgres@pgstandby data]$ cp -rp ../share/recovery.conf.sample recovery.conf  
standby_mode = on  
primary_conninfo = 'host=zwc port=1521'  
 
Start slave database server
To test replication, simple insert into a table on master server and query the same from slave server
[postgres@zwc ~]$ psql -h zwc -U zwc -d testdb  
Password for user zwc:   
psql (9.2.4)  
Type "help" for help.  
  
testdb=> \dt+  
                       List of relations  
 Schema |   Name    | Type  | Owner |    Size    | Description   
--------+-----------+-------+-------+------------+-------------  
 public | orderinfo | table | zwc   | 8192 bytes |   
 public | t01       | table | zwc   | 8192 bytes |   
(2 rows)  
  
testdb=> create table t02(id integer);  
CREATE TABLE  
testdb=> \dt+  
                       List of relations  
 Schema |   Name    | Type  | Owner |    Size    | Description   
--------+-----------+-------+-------+------------+-------------  
 public | orderinfo | table | zwc   | 8192 bytes |   
 public | t01       | table | zwc   | 8192 bytes |   
 public | t02       | table | zwc   | 0 bytes    |   
(3 rows)  
  
testdb=> \q  
[postgres@zwc ~]$   
[postgres@zwc ~]$ psql -h pgstandby -U zwc -d testdb  
Password for user zwc:   
psql (9.2.4)  
Type "help" for help.  
  
testdb=> \dt+  
                       List of relations  
 Schema |   Name    | Type  | Owner |    Size    | Description   
--------+-----------+-------+-------+------------+-------------  
 public | orderinfo | table | zwc   | 8192 bytes |   
 public | t01       | table | zwc   | 8192 bytes |   
 public | t02       | table | zwc   | 0 bytes    |   
(3 rows)  
  
testdb=> insert into t02 values(1),(2);  
ERROR:  cannot execute INSERT in a read-only transaction  
testdb=> \q  
[postgres@zwc ~]$ psql -h zwc -U zwc -d testdb  
Password for user zwc:   
psql (9.2.4)  
Type "help" for help.  
  
testdb=> insert into t02 values(1),(2);  
INSERT 0 2  
testdb=> \q  
[postgres@zwc ~]$ psql -h pgstandby -U zwc -d testdb  
Password for user zwc:   
psql (9.2.4)  
Type "help" for help.  
  
testdb=> select * from t02;  
 id   
----  
  1  
  2  
(2 rows)  
  
testdb=>   
 
Standby database log
STATEMENT:  insert into t02 values(1),(2);  
LOG:  connection received: host=192.168.1.13 port=27595  
LOG:  connection received: host=192.168.1.13 port=27596  
LOG:  connection authorized: user=zwc database=testdb  
LOG:  restartpoint starting: time  
LOG:  restartpoint complete: wrote 21 buffers (0.5%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=2.019 s, sync=0.009 s, total=2.031 s; sync files=17, longest=0.005 s, average=0.000 s  
LOG:  recovery restart point at 0/7C015D50  
DETAIL:  last completed transaction was at log time 2013-09-03 22:03:22.099392+08  
ERROR:  cannot execute DELETE in a read-only transaction  
STATEMENT:  delete from t02 where id=1;  
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,