Postgresql master-slave 切换测试

本文详细介绍了在Ubuntu 12.04环境下,如何进行PostgreSQL的主从复制配置,包括创建用户、修改配置文件、数据同步、启动从库以及主备切换的步骤。在主从切换过程中,通过触发器和监控日志确保切换顺利,同时提供了遇到问题时的解决参考链接。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

[table]
|系统:|
||
| ubuntu 1204|
||
|IP:|
||
| slave: 10.4.2.101 |
||
| master:10.4.2.110|
[/table]


安装详见:[url]http://2057.iteye.com/blog/1616620[/url]

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、设置从库recovery.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);



主备切换:(参考:[url]http://francs3.blog.163.com/blog/static/405767272011724103133766/[/url] )


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;


目前整理到此
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值