Postgresql master-slave切替テスト

9524 ワード

システム:
          ubuntu 1204
IP:
          slave:  10.4.2.101
          master:10.4.2.110
インストールの詳細は、次のとおりです.
http://2057.iteye.com/blog/1616620
1、ユーザーの作成
create user msuser superuser login connection limit 2 encrypted password 'msuser';

2、masterライブラリpg_の構成hba.conf
次のように構成を追加します.
     host replication msuser 10.4.2.101/16 md5

3、master's postgresql.conf(hotstandby構成詳細:http://2057.iteye.com/blog/1616620 )
   
 
     listen_addresses = '*' 
     max_connections = 100

     shared_buffers = 24MB

     wal_level = hot_standby
     checkpoint_segments = 128

     archive_mode = on
     archive_command = 'cp -i %p /usr/local/pgsql/archivedir/%f </dev/null'
     archive_timeout = 600

     max_wal_sends = 1
     wal_keep_segments = 64

     logging_collector = on 
     log_directory = 'pg_log'
     log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

     log_min_messages = debug5

     debug_print_parse = off
     debug_print_rewritten = off
     debug_print_plan = off     
     debug_pretty_print = on
     log_checkpoints = on
     log_connections = on

     log_disconnections = on
     
     track_activity_query_size = 1024

4、メインライブラリ全備(restart pg)
     pgsql -c "select pg_start_backup('standbybackup',true)";

5、データのコピー
   
   tar czvf pg_master_data.tar.gz /usr/local/pgsql/data --exclude=/usr/local/pgsql/data/pg_xlog

   scp pg_master_data.tar.gz pgslave

   select pg_stop_backup(), current_timestamp;

6、slave postgresqlを修正する.conf
     
     
     listen_addresses = '*' 
     max_connections = 100

     shared_buffers = 24MB

     wal_level = hot_standby
     checkpoint_segments = 128

     hot_standby = on     

     logging_collector = on 
     log_directory = 'pg_log'
     log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

     log_min_messages = debug5

     debug_print_parse = off
     debug_print_rewritten = off
     debug_print_plan = off     
     debug_pretty_print = on
     log_checkpoints = on
     log_connections = on
     log_disconnections = on

     track_activity_query_size = 1024

7.スレーブライブラリを設定する.conf
     
     standby_mode = 'on'
     primary_conninfo = 'host=10.4.2.110 port=5432 user=msuser password=msuser'
     trigger_file = '/data/pgsql/trigger_activestb'

   
8.ライブラリファイルを削除し、pg_を作成するxlogディレクトリ
     
     rm -rf postmaster.pid
     mkdir -p pg_xlog

slave postgresqlの起動
レプリケーションが開始されました
ps -aef|grep post
postgres 1633 1527 0 15:23 pts/0 00:00:00 su postgres
postgres 1640 1633 0 15:23 pts/0 00:00:00 bash
postgres 2106 1 1 16:46 pts/0 00:00:00/usr/local/pgsql/bin/postgres -D/usr/local/pgsql/data
postgres 2107 2106 0 16:46 ? 00:00:00 postgres: logger process                             
postgres 2109 2106 0 16:46 ? 00:00:00 postgres: writer process                             
postgres 2110 2106 0 16:46 ? 00:00:00 postgres: wal writer process                         
postgres 2111 2106 0 16:46 ? 00:00:00 postgres: autovacuum launcher process                
postgres 2112 2106 0 16:46 ? 00:00:00 postgres: archiver process                           
postgres 2113 2106 0 16:46 ? 00:00:00 postgres: stats collector process                    
postgres 2114 2106 0 16:46 ? 00:00:00 postgres: wal sender process repuser 10.4.2.101(34140) streaming 0/7000078
postgres 2117 1640 0 16:47 pts/0 00:00:00 ps -aef
postgres 2118 1640 0 16:47 pts/0 00:00:00 grep --color=auto post
master:

/usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/

pg_control version number: 903

Catalog version number: 201105231

Database system identifier: 5846925142468689917

Database cluster state: in production

pg_control last modified: Wed 20 Feb 2013 05:26:35 PM CST

Latest checkpoint location: 0/A0251F8

Prior checkpoint location: 0/A0008E8

Latest checkpoint's REDO location: 0/A0251C0

Latest checkpoint's TimeLineID: 1

Latest checkpoint's NextXID: 0/959

Latest checkpoint's NextOID: 32769

Latest checkpoint's NextMultiXactId: 1

Latest checkpoint's NextMultiOffset: 0

Latest checkpoint's oldestXID: 670

Latest checkpoint's oldestXID's DB: 1

Latest checkpoint's oldestActiveXID: 959

Time of latest checkpoint: Wed 20 Feb 2013 05:26:30 PM CST

Minimum recovery ending location: 0/0

Backup start location: 0/0

Current wal_level setting: hot_standby

Current max_connections setting: 100

Current max_prepared_xacts setting: 0

Current max_locks_per_xact setting: 64

Maximum data alignment: 8

Database block size: 8192

Blocks per segment of large relation: 131072

WAL block size: 8192

Bytes per WAL segment: 16777216

Maximum length of identifiers: 64

Maximum columns in an index: 32

Maximum size of a TOAST chunk: 1996

Date/time type storage: 64-bit integers

Float4 argument passing: by value

Float8 argument passing: by value

slave:

/usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/

pg_control version number: 903

Catalog version number: 201105231

Database system identifier: 5846925142468689917

Database cluster state: in archive recovery

pg_control last modified: Wed 20 Feb 2013 05:27:07 PM CST

Latest checkpoint location: 0/A0251F8

Prior checkpoint location: 0/A0008E8

Latest checkpoint's REDO location: 0/A0251C0

Latest checkpoint's TimeLineID: 1

Latest checkpoint's NextXID: 0/959

Latest checkpoint's NextOID: 32769

Latest checkpoint's NextMultiXactId: 1

Latest checkpoint's NextMultiOffset: 0

Latest checkpoint's oldestXID: 670

Latest checkpoint's oldestXID's DB: 1

Latest checkpoint's oldestActiveXID: 959

Time of latest checkpoint: Wed 20 Feb 2013 05:26:30 PM CST

Minimum recovery ending location: 0/B000000

Backup start location: 0/0

Current wal_level setting: hot_standby

Current max_connections setting: 100

Current max_prepared_xacts setting: 0

Current max_locks_per_xact setting: 64

Maximum data alignment: 8

Database block size: 8192

Blocks per segment of large relation: 131072

WAL block size: 8192

Bytes per WAL segment: 16777216

Maximum length of identifiers: 64

Maximum columns in an index: 32

Maximum size of a TOAST chunk: 1996

Date/time type storage: 64-bit integers

Float4 argument passing: by value

Float8 argument passing: by value

テストデータの挿入:

create table test(id int,crt_time timestamp default clock_timestamp());
insert into test (id) select generate_series(1,1000000);

プライマリ・スタンバイ・スイッチ:(参照:
http://francs3.blog.163.com/blog/static/405767272011724103133766/ )
slave triggerの作成
touch/data/pgsql/trigger_activestb
recovery.confはrecoveryになります.doneは、バックアップがアクティブになっていることを示します.
切り替えの際はマスタの構成を交換する
元のプライマリ・ライブラリの現在のバックアップを作成するrecovery.conf

recovery_target_timeline = 'latest'
standby_mdoe = 'on'
primary_conninfo ='host=ip port=5432 user=msuser password=msuser'
trigger_file = '/usr/local/pgsql/trigger_activestb.5432'

メインライブラリpg_hba.confにライブラリからのアクセスを許可する構成を追加
host replication msuser ip/16 md5

マスターライブラリの起動、スレーブライブラリの起動
問題が発生した場合は、リンクscpに欠落しているファイルを参照してください.
成功すると
LOG: streaming replication successfully connected to primary
コメント:
rsync -av --progress/usr/local/pgsql/data/10.4.2.110:/usr/local/pgsql/data --exclude 'pg_log/*' --exclude 'pg_xlog/*' --exclude postmaster.pid --exclude pg_hba.conf --exclude postgresql.conf;
アーカイブの削除:
メインライブラリの準備をしてから、他のアーカイブファイルを削除します.

psql -c "select pg_start_backup('standbybackup',true)";

select pg_stop_backup(), current_timestamp;

ここまでに整理する