系统: |
|
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、设置从库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);
主备切换:(参考:
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;
目前整理到此
分享到:
相关推荐
PostgreSQL(postgresql14-devel-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
PostgreSQL(postgresql13-devel-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
PostgreSQL(postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机...
PostgreSQL(postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
官方离线安装包,测试可用。使用rpm -ivh [rpm完整包名] 进行安装
官方离线安装包,测试可用。使用rpm -ivh [rpm完整包名] 进行安装
PostgreSQL(postgresql14-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的...
PostgreSQL(postgresql13-libs-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
PostgreSQL(postgresql14-pltcl-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
PostgreSQL(postgresql14-plperl-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机...
PostgreSQL(postgresql14-llvmjit-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机...
PostgreSQL(postgresql13-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的...
官方离线安装包,测试可用。使用rpm -ivh [rpm完整包名] 进行安装
PostgreSQL(postgresql13-contrib-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机...
postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar
postgresql-10.5-2 postgresql-10.5-2-windows-x64.exe postgresql_10.5
官方离线安装包,测试可用。使用rpm -ivh [rpm完整包名] 进行安装
PostgreSQL(postgresql14-test-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
PostgreSQL(postgresql13-plperl-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机...
PostgreSQL(postgresql-14.2-2-windows-x64.exe),适用于Windows系统:PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象...