使用pg_basebackup搭建PostgreSQL流复制环境

使用pg_basebackup搭建PostgreSQL流复制环境,第1张

概述环境:     OS: [ha@node0 ~]$ uname -aLinux 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     内存:1G     CPU:1核     数据库: postgres=# select version();

环境:
OS:

[ha@node0 ~]$ uname -alinux 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
内存:1G
cpu:1核
数据库:
postgres=# select version();                                                    version                                                    --------------------------------------------------------------------------------------------------------------- Postgresql 9.4.5 on x86_64-unkNown-linux-gnu,compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16),64-bit(1 row)postgres=#
.bash_profile
[ha@node0 ~]$ cat .bash_profile # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then	. ~/.bashrcfi# User specific environment and startup programsexport 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

搭建过程:
1.主库配置
a.postgresql.conf:

Listen_addresses = '*'wal_level = hot_standbyarchive_mode = offmax_wal_senders = 3wal_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 参数。
b.pg_hba.conf
host    all             all             192.168.238.0/24        md5host    replication     rep             192.168.238.0/24        trust
这里说明的是,网上有其他对replication设置为md5,并建立.pgpass文件,这种方法也是可行的。

c.新建用户rep

create user rep replication encrypted password 'rep';

2.使用pg_basebackup进行从库备份

[ha@localhost pgdb]$ pg_basebackup -D $PGDATA -F p -X stream -v -P -h 192.168.238.130 -U reptransaction log start point: 0/2000028 on timeline 1pg_basebackup: starting background WAL receiver20945/20945 kB (100%),1/1 tablespace                                         transaction log end point: 0/20000F0pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup completed[ha@localhost pgdb]$ lltotal 20drwxrwxr-x.  2 ha ha 4096 Dec 26 00:54 bindrwx------. 18 ha ha 4096 Dec 26 12:02 datadrwxrwxr-x.  4 ha ha 4096 Dec 26 00:54 includedrwxrwxr-x.  4 ha ha 4096 Dec 26 00:54 libdrwxrwxr-x.  4 ha ha 4096 Dec 26 00:54 share[ha@localhost pgdb]$ cd data/[ha@localhost data]$ lsbackup_label  pg_hba.conf    pg_replslot   pg_subtrans  postgresql.auto.confbase          pg_IDent.conf  pg_serial     pg_tblspc    postgresql.confglobal        pg_logical     pg_snapshots  pg_twophasepg_clog       pg_multixact   pg_stat       PG_VERSIONpg_dynshmem   pg_notify      pg_stat_tmp   pg_xlog
这里需要对目录进行一下说明:data目录可以通过pg_basebackup自动创建,但是其他表空间,需要手动创建并赋权,这里需要注意的是,权限为700。

3.从库配置
a.postgresql.conf

hot_standby = on
b.recovery.conf
standby_mode = onprimary_conninfo = 'host=192.168.238.130 port=5432 user=rep'trigger_file = '/home/ha/pgdb/pg.trigger.file'
4.备库启动前,主库进程
[ha@localhost pgdb]$ ps -ef | grep postroot       2124      1  0 04:47 ?        00:00:00 /usr/libexec/postfix/masterpostfix    2147   2124  0 04:47 ?        00:00:00 qmgr -l -t fifo -upostfix   10385   2124  0 11:27 ?        00:00:00 pickup -l -t fifo -uha        10691      1  0 12:33 pts/3    00:00:00 /home/ha/pgdb/bin/postgres_oraha        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
5.备库启动后
a.主库进程
[ha@localhost pgdb]$ ps -ef | grep postroot       2124      1  0 04:47 ?        00:00:00 /usr/libexec/postfix/masterpostfix    2147   2124  0 04:47 ?        00:00:00 qmgr -l -t fifo -upostfix   10385   2124  0 11:27 ?        00:00:00 pickup -l -t fifo -uha        10691      1  0 12:33 pts/3    00:00:00 /home/ha/pgdb/bin/postgres_oraha        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/3000060ha        10720   4087  0 12:37 pts/3    00:00:00 grep post
b.备库进程
[ha@localhost data]$ ps -ef | grep postroot       2086      1  0 04:48 ?        00:00:00 /usr/libexec/postfix/masterpostfix    2108   2086  0 04:48 ?        00:00:00 qmgr -l -t fifo -upostfix    9657   2086  0 11:27 ?        00:00:00 pickup -l -t fifo -uha         9782      1  0 12:36 pts/2    00:00:00 /home/ha/pgdb/bin/postgresha         9783   9782  0 12:36 ?        00:00:00 postgres: startup process   recovering 000000010000000000000003ha         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/3000060ha         9792   3744  0 12:37 pts/2    00:00:00 grep post
6.实验效果
a.主库
[ha@localhost pgdb]$ psql postgrespsql (9.4.5)Type "help" for help.postgres=# create table test(ID int);CREATE tablepostgres=# insert into test values (1),(2);INSERT 0 2postgres=# select * from test ; ID ----  1  2(2 rows)postgres=#
b.备库
[ha@localhost data]$ psql postgrespsql (9.4.5)Type "help" for help.postgres=# select * from test ; ID ----  1  2(2 rows)
总结

以上是内存溢出为你收集整理的使用pg_basebackup搭建PostgreSQL流复制环境全部内容,希望文章能够帮你解决使用pg_basebackup搭建PostgreSQL流复制环境所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1174909.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-02
下一篇 2022-06-02

发表评论

登录后才能评论

评论列表(0条)

保存