centos6.5下postgresql9.4.3同步流复制的standby安装与配置

ゝ一世哀愁。 2022-10-02 09:46 127阅读 0赞

**一、同步流复制的架构**

PostgreSQL从9.1版本之后提供了同步复制的架构。同步复制要求在数据写入Standby数据库后，事务的commit才返回,所以Standby库出现问题时，会导致主库被hang住。解决这个问题的方法是启动两个Standby数据库，这两个standby数据库只要有一个是正常的，就不会让主库hang住。所以在实际应用中，同步流复制，总是有1个主库和2个以上的Standby库。

**二、同步复制的配置**

实现同步复制功能主要是在主库上配置参数"synchronous\_standby\_names",这个参数指定多个Standby的名称,各个名称通过逗号分隔，而Standby名称是在Standby连接到主库时，由连接参数"application\_name"指定的。要使用同步复制，在Standby数据库中,recovery.conf

里的primary\_conninfo一定要指定连接参数"application\_name"。recovery.conf的示例如下:

standby\_mode = 'on'

primary\_conninfo = 'application\_name=standby01 user=repluesr password=XXXXXX host=172.16.0.111 port=5432 sslmode=disable sslcompression=1'

**三、系统环境**

系统平台：centos6.5

PostgreSQL版本：postgresql9.4.3

防火墙关闭/iptables

selinux设置成

SELINUX=disabled

<table> 
 <tbody> 
 <tr> 
 <td style="border-bottom:#000000 1px solid;border-left:#000000 1px solid;border-top:#000000 1px solid;border-right:#000000 1px solid;">主机名</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#000000 1px solid;border-right:#000000 1px solid;">IP地址</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#000000 1px solid;border-right:#000000 1px solid;">角色</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#000000 1px solid;border-right:#000000 1px solid;">数据目录</td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#000000 1px solid;border-left:#000000 1px solid;border-top:#f0f0f0;border-right:#000000 1px solid;">postgresql01</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">172.16.0.111</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">主库</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">/home/postgres/pgsql/data</td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#000000 1px solid;border-left:#000000 1px solid;border-top:#f0f0f0;border-right:#000000 1px solid;">postgresql02</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">172.16.0.112</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">Standby</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">/home/postgres/pgsql/data</td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#000000 1px solid;border-left:#000000 1px solid;border-top:#f0f0f0;border-right:#000000 1px solid;">postgresql03</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">172.16.0.113</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">Standby</td> 
 <td style="border-bottom:#000000 1px solid;border-left:#f0f0f0;border-top:#f0f0f0;border-right:#000000 1px solid;">/home/postgres/pgsql/data</td> 
 </tr> 
 </tbody> 
</table>

**四、源码包安装**

1、在三台安装依赖包

yum -y install gcc\*

yum -y install readline-devel

2、在三台增加用户

\# adduser postgres

\# passwd postgres

Changing password for user postgres.

New password:

BAD PASSWORD: it is too simplistic/systematic

BAD PASSWORD: is too simple

Retype new password:

passwd: all authentication tokens updated successfully.

注意：主库安装与配置好后，在把全部的文件拷备到standby中。

以下是主库操作

3. 下载PostgreSQL 源码包

\# wget http://ftp.postgresql.org/pub/source/v9.4.3/postgresql-9.4.3.tar.bz2

4. 解压源码包

\# tar xjf postgresql-9.4.3.tar.bz2

5. 进入解压后的目录

\# cd postgresql-9.4.3

6.开始编译安装PostgreSQL 数据库。

\[root@postgresql01 postgresql-9.4.3\]\# ./configure --prefix=/home/postgres/pgsql

\[root@postgresql01 postgresql-9.4.3\]\#gmake

\[root@postgresql01 postgresql-9.4.3\]\# gmake install

7.设置环境

\[root@postgresql01 postgresql-9.4.3\]\# cd /home/postgres/

\[root@postgresql01 postgres\]\# ls

pgsql

\[root@postgresql01 postgres\]\# vi .bash\_profile

把 PATH=$PATH:$HOME/bin

改成 PATH=$PATH:$HOME/bin:/home/postgres/pgsql/bin

保存退出。

让环境变量生效

\[root@postgresql01 postgres\]\# source .bash\_profile

8.初始化数据库

8.1新建数据目录

\[root@postgresql01 postgres\]\# mkdir /home/postgres/pgsql/data

8.2更改权限

\[root@postgresql01 postgres\]\# chown postgres:postgres /home/postgres/pgsql/data

8.3切换到postgres用户

\[root@postgresql01 postgres\]\# su - postgres

8.4init db

\[postgres@postgresql01 ~\]$ /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data

到这里数据的初始化就完成

9.系统服务

9.1回到root用户

\[postgres@postgresql01 ~\]$ exit

9.2复制安装目录下的linux文件/etc/init.d/

进入postgresql的安装目录

\[root@postgresql01 postgres\]\# cd /root/postgresql-9.4.3/

\[root@postgresql01 postgresql-9.4.3\]\# cp contrib/start-scripts/linux /etc/init.d/postgresql

9.3修改/etc/init.d/postgresql 注意：红色是修改部分

\[root@postgresql postgresql-9.4.3\]\# vi /etc/init.d/postgresql

\#! /bin/sh

\# chkconfig: 2345 98 02

\# description: PostgreSQL RDBMS

\# This is an example of a start/stop script for SysV-style init, such

\# as is used on Linux systems. You should edit some of the variables

\# and maybe the 'echo' commands.

\# Place this file at /etc/init.d/postgresql (or

\# /etc/rc.d/init.d/postgresql) and make symlinks to

\# /etc/rc.d/rc0.d/K02postgresql

\# /etc/rc.d/rc1.d/K02postgresql

\# /etc/rc.d/rc2.d/K02postgresql

\# /etc/rc.d/rc3.d/S98postgresql

\# /etc/rc.d/rc4.d/S98postgresql

\# /etc/rc.d/rc5.d/S98postgresql

\# Or, if you have chkconfig, simply:

\# chkconfig --add postgresql

\# Proper init scripts on Linux systems normally require setting lock

\# and pid files under /var/run as well as reacting to network

\# settings, so you should treat this with care.

\# Original author: Ryan Kirkpatrick <pgsql@rkirkpat.net>

\# contrib/start-scripts/linux

\#\# EDIT FROM HERE

\# Installation prefix

prefix=/home/postgres /pgsql

\# Data directory

PGDATA="/home/postgres/pgsql/data"

\# Who to run the postmaster as, usually "postgres". (NOT "root")

PGUSER=postgres

\# Where to keep a log file

PGLOG="$PGDATA/serverlog"

\# It's often a good idea to protect the postmaster from being killed by the

\# OOM killer (which will tend to preferentially kill the postmaster because

\# of the way it accounts for shared memory). Setting the OOM\_SCORE\_ADJ value

\# to -1000 will disable OOM kill altogether. If you enable this, you probably

\# want to compile PostgreSQL with "-DLINUX\_OOM\_SCORE\_ADJ=0", so that

\# individual backends can still be killed by the OOM killer.

\#OOM\_SCORE\_ADJ=-1000

\# Older Linux kernels may not have /proc/self/oom\_score\_adj, but instead

\# /proc/self/oom\_adj, which works similarly except the disable value is -17.

\# For such a system, enable this and compile with "-DLINUX\_OOM\_ADJ=0".

\#OOM\_ADJ=-17

\#\# STOP EDITING HERE

\# The path that is to be used for the script

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

\# What to use to start up the postmaster. (If you want the script to wait

\# until the server has started, you could use "pg\_ctl start -w" here.

\# But without -w, pg\_ctl adds no value.)

DAEMON="$prefix/bin/postmaster"

\# What to use to shut down the postmaster

PGCTL="$prefix/bin/pg\_ctl"

set -e

\# Only start if we can find the postmaster.

test -x $DAEMON ||

echo "$DAEMON not found"

if \[ "$1" = "stop" \]

then exit 0

else exit 5

\# Parse command line parameters.

case $1 in

start)

echo -n "Starting PostgreSQL: "

test x"$OOM\_SCORE\_ADJ" != x && echo "$OOM\_SCORE\_ADJ" > /proc/self/oom\_score\_adj

test x"$OOM\_ADJ" != x && echo "$OOM\_ADJ" > /proc/self/oom\_adj

su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1

echo "ok"

;;

stop)

echo -n "Stopping PostgreSQL: "

su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"

echo "ok"

;;

restart)

echo -n "Restarting PostgreSQL: "

su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"

test x"$OOM\_SCORE\_ADJ" != x && echo "$OOM\_SCORE\_ADJ" > /proc/self/oom\_score\_adj

test x"$OOM\_ADJ" != x && echo "$OOM\_ADJ" > /proc/self/oom\_adj

su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1

echo "ok"

;;

reload)

echo -n "Reload PostgreSQL: "

su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"

echo "ok"

;;

status)

su - $PGUSER -c "$PGCTL status -D '$PGDATA'"

;;

\*)

\# Print help

echo "Usage: $0 \{start|stop|restart|reload|status\}" 1>&2

exit 1

;;

esac

exit 0

9.4启动数据库

\[root@postgresql01 postgresql-9.4.3\]\# /etc/init.d/postgresql start

9.5让数据库开机启动

\[root@postgresql01 postgresql-9.4.3\]\# chkconfig --add postgresql

\[root@postgresql01 postgresql-9.4.3\]\# chkconfig postgresql on

9.6创建数据操作历史记录文件

\[root@postgresql01 postgresql-9.4.3\]\# touch /home/postgres/pgsql/.pgsql\_history

\[root@postgresql01 postgresql-9.4.3\]\# chown postgres:postgres /home/postgres/pgsql/.pgsql\_history

10.测试使用

\[postgres@postgresql01 ~\]$ createdb test

\[postgres@postgresql01 ~\]$ psql test

psql (9.4.3)

Type "help" for help.

test=\#

源码编译安装成功

五、配置同步流复制

配置如下：

1.在主库增加同步的用户名与密码

\[postgres@postgresql01 ~\]$ psql -d postgres

psql (9.4.3)

Type "help" for help.

postgres=\# CREATE ROLE repluser REPLICATION LOGIN PASSWORD 'zhf\#2015';

CREATE ROLE

postgres=\#

2.在主库postgresql01上进行配置

修改以下参数

在主库的/home/postgres/pgsql/data/pg\_hba.conf中做如下配置：

\[postgres@postgresql01 ~\]$ vi /home/postgres/pgsql/data/pg\_hba.conf

增加以下：

host replication repluser 172.16.0.0/24 md5

2.在主库postgreso1的/home/postgres/pgsql/data/postgresql.conf中设置如下三个参数

\[postgres@postgresql01 ~\]$ vi /home/postgres/pgsql/data/postgresql.conf

isten\_addresses = '\*'

max\_wal\_senders = 5

wal\_level = hot\_standby

3.在主数据上指定同步复制的Standby名称，在postgresql01的/home/postgres/pgsql/data/postgresql.conf增加如下一行：

synchronous\_standby\_names = 'standby01,standby02'

4.停止主库postgresql

\[root@postgresql01 ~\]\# /etc/init.d/postgresql stop

5.把主库安装文件拷备到两台standby中

\[root@postgresql01 ~\]\# scp -r /home/postgres/pgsql 172.16.0.112:/home/postgres/

\[root@postgresql01 ~\]\# scp -r /home/postgres/pgsql 172.16.0.113:/home/postgres/

\[root@postgresql01 ~\]\# scp -r /etc/init.d/postgresql 172.16.0.112:/etc/init.d/

\[root@postgresql01 ~\]\# scp -r /etc/init.d/postgresql 172.16.0.113:/etc/init.d/

6.在两台standby中设置权限两台都操作如下：

\[root@postgresql02 ~\]\# cd /home/postgres/pgsql/

\[root@postgresql02 pgsql\]\# chown -R postgres:postgres data

\[root@postgresql02 pgsql\]\# chmod +x /etc/init.d/postgresql

\[root@postgresql02 postgres\]\# chkconfig --add postgresql

\[root@postgresql02 postgres\]\# chkconfig postgresql on

\[root@postgresql03 ~\]\# cd /home/postgres/pgsql/

\[root@postgresql03 pgsql\]\# chown -R postgres:postgres data

\[root@postgresql03 pgsql\]\# chmod +x /etc/init.d/postgresql

\[root@postgresql03 postgres\]\# chkconfig --add postgresql

\[root@postgresql03 postgres\]\# chkconfig postgresql on

7. 在两台standby中设置环境变量两台都操作如下：

\[root@postgresql02 pgsql\]\# cd /home/postgres/

把 PATH=$PATH:$HOME/bin

改成 PATH=$PATH:$HOME/bin:/home/postgres/pgsql/bin

保存退出。

让环境变量生效：

\[root@postgresql02 postgres\]\# source .bash\_profile

\[root@postgresql03 pgsql\]\# cd /home/postgres/

把 PATH=$PATH:$HOME/bin

改成 PATH=$PATH:$HOME/bin:/home/postgres/pgsql/bin

保存退出。

让环境变量生效：

\[root@postgresql03 postgres\]\# source .bash\_profile

8.在备库postgresql02上进行配置

把配置文件拷过来

\[root@postgresql02 postgres\]\# su - postgres

\[postgres@postgresql02 ~\]$ cd pgsql/data/

\[postgres@postgresql02 data\]$ cp /home/postgres/pgsql/share/recovery.conf.sample ./recovery.conf

在postgresql02的/home/postgres/pgsql/data/recovery.conf中的配置“primary\_conninfo”里增加 连接参数“appliction\_name”,如下：

standby\_mode = on

primary\_conninfo = 'application\_name=standby01 user=repluser password=zhf\#2015 host=172.16.0.111 port=5432 sslmode=disable sslcompression=1'

在postgresql02的/home/postgres/pgsql/data/postgresql.conf中的配置参数

hot\_standby = on

完成配置后，启动数据库，如下：

\[root@postgresql02 postgres\]\# /etc/init.d/postgresql start

9.在备库postgresql03上进行配置

把配置文件拷过来

\[root@postgresql03 postgres\]\# su - postgres

\[postgres@postgresql03 ~\]$ cd pgsql/data/

\[postgres@postgresql03 data\]$ cp /home/postgres/pgsql/share/recovery.conf.sample ./recovery.conf

在postgresql03的/home/postgres/pgsql/data/recovery.conf中的配置“primary\_conninfo”里增加 连接参数“appliction\_name”,如下：

standby\_mode = on

primary\_conninfo = 'application\_name=standby02 user=repluser password=zhf\#2015 host=172.16.0.111 port=5432 sslmode=disable sslcompression=1'

在postgresql03的/home/postgres/pgsql/data/postgresql.conf中的配置参数

hot\_standby = on

完成配置后，启动数据库，如下：

\[root@postgresql03 postgres\]\# /etc/init.d/postgresql start

10.启动主库 postgresql01

11.测试在主库中

\[postgres@postgresql01 data\]$ psql test

psql (9.4.3)

Type "help" for help.

test=\# create table test01(id int primary key,note text);

CREATE TABLE

test=\# insert into test01 values(1,'1111111');

INSERT 0 1

test=\# insert into test01 values(2,'2222222');

INSERT 0 1

在备库查询数据

在备库postgresql02中查看

\[postgres@postgresql02 ~\]$ psql test

psql (9.4.3)

Type "help" for help.

test=\# select \* from test01;

id | note

\----+---------

1 | 1111111

2 | 2222222

(2 rows)

test=\#

在备库postgresql03中查看

\[root@postgresql03 postgres\]\# su - postgres

\[postgres@postgresql03 ~\]$ psql test

psql (9.4.3)

Type "help" for help.

test=\# select \* from test01;

id | note

\----+---------

1 | 1111111

2 | 2222222

(2 rows)

test=\#

转载于:https://blog.51cto.com/jxzhfei/1678372

centos6.5下postgresql9.4.3同步流复制的standby安装与配置

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 CentOS7 下安装 PostgreSQL10

相关【PostgreSQL】——主从流复制

相关 centos6.5下postgresql9.4.3同步流复制的standby安装与配置

相关 CentOS下安装postgresql 9.4

相关 Postgresql 同步流复制

相关 Postgresql 异步流复制

相关 PostgreSQL 9.5.5主从实现之异步流复制（Hot Standby）

相关 PostgreSQL 9.5.5主从实现之异步流复制（Hot Standby）

相关【PostgreSQL】Centos下安装PostgreSQL

相关 CentOS下安装PostgreSql