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

ゝ一世哀愁。 2022-10-02 09:46 147阅读 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安装与配置

发表评论取消回复

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

相关阅读

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

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

相关 centos6.3下安装Postgresql安装与配置

相关 CentOS下安装postgresql 9.4

相关 Postgresql 同步流复制

相关 Postgresql 异步流复制

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

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

相关【PostgreSQL】Centos下安装PostgreSQL

相关 CentOS下安装PostgreSql