【转】:使用pg_basebackup搭建PostgreSQL流复制环境 原作者:Sure 创作时间:2016-01-01 13:30:35+08 |
doudou586 发布于2016-01-01 13:30:35 评论: 6 浏览: 23940 顶: 2914 踩: 3288 |
环境:
[ha@node0 ~]$ uname -a Linux node0 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
postgres=# select version(); version -------------------------------------------------------------------------------------------------------- PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120 313 (Red Hat 4.4.7-16), 64-bit (1 row) postgres=#
[ha@node0 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export PGHOME=/home/ha/pgdb/ export PGDATA=/home/ha/pgdb/data/ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib/ export PATH=$PGHOME/bin:$PATH:$HOME/bin
搭建过程:
listen_addresses = '*' wal_level = hot_standby archive_mode = off max_wal_senders = 3 wal_keep_segments = 16
这里要说明的是参数archive_mode,我在实验过程中有一次发现参数archive_mode和archive_command在不设置的情况下也可以完成流复制,所以我对此进行了调研,发现francs已经对此进行了解释,这里引用francs的结论:
在搭建流复制环境时,并不必须设置 archive_mode 参数为 on ,很多资料在介绍搭建流复制环境时设置这个参数为 on ,可能是出于开启 WAL 归档更安全的原因,因为在主库宕机并且较长时间不能恢复时,从库依然可以读取归档目录的 WAL,从而保证不丢数据; 另一方面,如果主库设置了较大的 wal_keep_segments ,也可以不用开启archive_mode,因为主库保留了足够的 WAL,从而大大降低了因从库所需要的 WAL 被主库覆盖而需要从归档去取 WAL 的情况。所以从这方面说,archive_mode 参数的设置与搭建流复制并没有直接关系。 提示: 对于比较繁忙的库,在搭建流复制从库时,建议主库设置较大的 wal_keep_segments 参数。
host all all 192.168.238.0/24 md5 host replication rep 192.168.238.0/24 trust
这里说明的是,网上有其他对replication设置为md5,并建立.pgpass文件,这种方法也是可行的。
create user rep replication encrypted password 'rep';
[ha@localhost pgdb]$ pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.238.130 -U rep transaction log start point: 0/2000028 on timeline 1 pg_basebackup: starting background WAL receiver 20945/20945 kB (100%), 1/1 tablespace transaction log end point: 0/20000F0 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed [ha@localhost pgdb]$ ll total 20 drwxrwxr-x. 2 ha ha 4096 Dec 26 00:54 bin drwx------. 18 ha ha 4096 Dec 26 12:02 data drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 include drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 lib drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 share [ha@localhost pgdb]$ cd data/ [ha@localhost data]$ ls backup_label pg_hba.conf pg_replslot pg_subtrans postgresql.auto.conf base pg_ident.conf pg_serial pg_tblspc postgresql.conf global pg_logical pg_snapshots pg_twophase pg_clog pg_multixact pg_stat PG_VERSION pg_dynshmem pg_notify pg_stat_tmp pg_xlog
这里需要对目录进行一下说明:data目录可以通过pg_basebackup自动创建,但是其他表空间,需要手动创建并赋权,这里需要注意的是,权限为700。
hot_standby = on
standby_mode = on primary_conninfo = 'host=192.168.238.130 port=5432 user=rep' trigger_file = '/home/ha/pgdb/pg.trigger.file'
[ha@localhost pgdb]$ ps -ef | grep post root 2124 1 0 04:47 ? 00:00:00 /usr/libexec/postfix/master postfix 2147 2124 0 04:47 ? 00:00:00 qmgr -l -t fifo -u postfix 10385 2124 0 11:27 ? 00:00:00 pickup -l -t fifo -u ha 10691 1 0 12:33 pts/3 00:00:00 /home/ha/pgdb/bin/postgres_ora ha 10693 10691 0 12:33 ? 00:00:00 postgres: checkpointer process ha 10694 10691 0 12:33 ? 00:00:00 postgres: writer process ha 10695 10691 0 12:33 ? 00:00:00 postgres: wal writer process ha 10696 10691 0 12:33 ? 00:00:00 postgres: autovacuum launcher process ha 10697 10691 0 12:33 ? 00:00:00 postgres: stats collector process ha 10717 4087 0 12:37 pts/3 00:00:00 grep post
[ha@localhost pgdb]$ ps -ef | grep post root 2124 1 0 04:47 ? 00:00:00 /usr/libexec/postfix/master postfix 2147 2124 0 04:47 ? 00:00:00 qmgr -l -t fifo -u postfix 10385 2124 0 11:27 ? 00:00:00 pickup -l -t fifo -u ha 10691 1 0 12:33 pts/3 00:00:00 /home/ha/pgdb/bin/postgres_ora ha 10693 10691 0 12:33 ? 00:00:00 postgres: checkpointer process ha 10694 10691 0 12:33 ? 00:00:00 postgres: writer process ha 10695 10691 0 12:33 ? 00:00:00 postgres: wal writer process ha 10696 10691 0 12:33 ? 00:00:00 postgres: autovacuum launcher process ha 10697 10691 0 12:33 ? 00:00:00 postgres: stats collector process ha 10718 10691 0 12:37 ? 00:00:00 postgres: wal sender process rep 192.168.238.131(59195) streaming 0/3000060 ha 10720 4087 0 12:37 pts/3 00:00:00 grep post
[ha@localhost data]$ ps -ef | grep post root 2086 1 0 04:48 ? 00:00:00 /usr/libexec/postfix/master postfix 2108 2086 0 04:48 ? 00:00:00 qmgr -l -t fifo -u postfix 9657 2086 0 11:27 ? 00:00:00 pickup -l -t fifo -u ha 9782 1 0 12:36 pts/2 00:00:00 /home/ha/pgdb/bin/postgres ha 9783 9782 0 12:36 ? 00:00:00 postgres: startup process recovering 000000010000000000000003 ha 9784 9782 0 12:36 ? 00:00:00 postgres: checkpointer process ha 9785 9782 0 12:36 ? 00:00:00 postgres: writer process ha 9786 9782 0 12:36 ? 00:00:00 postgres: stats collector process ha 9787 9782 0 12:36 ? 00:00:00 postgres: wal receiver process streaming 0/3000060 ha 9792 3744 0 12:37 pts/2 00:00:00 grep post
[ha@localhost pgdb]$ psql postgres psql (9.4.5) Type "help" for help. postgres=# create table test(id int); CREATE TABLE postgres=# insert into test values (1),(2); INSERT 0 2 postgres=# select * from test ; id ---- 1 2 (2 rows) postgres=#
[ha@localhost data]$ psql postgres psql (9.4.5) Type "help" for help. postgres=# select * from test ; id ---- 1 2 (2 rows)