技术交流QQ群:①185473046   ②190706903   ③203744115   网站地图
登录

下次自动登录
现在的位置: 首页PostgreSQL>正文
Linux下PostgreSQL数据库主从同步配置
2022年06月26日 PostgreSQL 暂无评论 ⁄ 被围观 511次+

操作系统:CentOS-7.6

主节点:192.168.21.100

从节点:192.168.21.101

PostgreSQL版本:postgresql-11.13.tar.gz

下载地址:https://ftp.postgresql.org/pub/source/v11.13/postgresql-11.13.tar.gz

PostgreSQL数据库的主从同步是一种高可用解决方案,可以实现读写分离。

一、基础配置

在主从服务器上都进行操作

1、防火墙配置

CentOS 7.x 8.x 默认使用的是firewall作为防火墙,这里改为iptables防火墙。

1.1、关闭firewall:

systemctl stop firewalld.service #停止firewall

systemctl disable firewalld.service #禁止firewall开机启动

systemctl mask firewalld

systemctl stop firewalld

yum remove firewalld

1.2、安装iptables防火墙

yum install iptables-services #安装

vi /etc/sysconfig/iptables #编辑防火墙配置文件

# sample configuration for iptables service

# you can edit this manually or use system-config-firewall

# please do not ask us to add additional ports/services to this default configuration

*filter

:INPUT ACCEPT [0:0]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [0:0]

-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

-A INPUT -p icmp -j ACCEPT

-A INPUT -i lo -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited

-A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT

:wq! #保存退出

systemctl restart iptables.service #最后重启防火墙使配置生效

systemctl enable iptables.service #设置防火墙开机启动

/usr/libexec/iptables/iptables.init restart #重启防火墙

防火墙端口说明:

PostgreSQL默认使用tcp 5432端口

2、关闭SELINUX

vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

3、修改主机名

#设置主机名为pgsql-master

hostname pgsql-master

hostnamectl set-hostname pgsql-master

vi /etc/hostname #编辑配置文件

pgsql-master #修改localhost.localdomain为pgsql-master

:wq! #保存退出

vi /etc/hosts #编辑配置文件

127.0.0.1 localhost pgsql-master #修改localhost.localdomain为pgsql-master

#从服务器执行相同的操作,把主机名称分别修改为服务器角色对应的名称pgsql-slave

4、下载软件包

PostgreSQL版本:postgresql-11.13.tar.gz

下载地址:https://ftp.postgresql.org/pub/source/v11.13/postgresql-11.13.tar.gz

Pgpool版本:pgpool-II-4.2.4.tar.gz

下载地址:https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.2.4.tar.gz

5、安装编译工具包

yum install tcl tcl-devel uuid-devel perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake gcc* readline-devel

6、编译安装postgresql

#创建目录

mkdir -p /usr/local/pgsql #创建安装目录

mkdir -p /usr/local/pgsql/data #创建数据库存放目录

#编译PostgreSQL

cd /usr/local/src

tar zxvf postgresql-11.13.tar.gz #解压

cd postgresql-11.13

./configure --prefix=/usr/local/pgsql --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap

gmake world #gmake包括第三方插件全部编译

gmake install-world #包括第三方插件全部安装

二、配置PostgreSQL主数据库

在主节点服务器上进行操作

1、创建运行用户

#创建PostgreSQL运行用户,PostgreSQL不允许使用root用户运行服务

#创建用户群组postgres

groupadd postgres

#创建用户postgres,并加入postgres组

useradd -g postgres postgres

2、设置目录权限

chown postgres.postgres -R /usr/local/pgsql

chown postgres.postgres -R /usr/local/pgsql/data

3、把postgresql加入系统环境变量

vi /etc/profile #添加如下3行内容

export PGHOME=/usr/local/pgsql

export PGDATA=/usr/local/pgsql/data

export PATH=$PATH:$PGHOME/bin

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

:wq! #保存退出

source /etc/profile

4、修改数据库配置文件

vi /usr/local/pgsql/data/postgresql.conf

port = 5432

max_connections = 1000

listen_addresses = '*' #监听本机所有ip,也可以按需设置

wal_log_hints = on

data_checksums = on

full_page_writes = on

#日志配置

log_destination = 'csvlog' #日志格式,值为stderr,csvlog,syslog,and eventlog之一

logging_collector = on #开启日志功能,默认是off不启用日志

log_directory = 'log' #日志路径,默认是PGDATA的相对路径,即{PGDATA}/log,可以使用自定义目录

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #日志文件命名形式,使用默认即可

log_rotation_age = 1d #单个日志文件的生存期,默认1天,在日志文件大小没有达到log_rotation_size时,一天只生成一个日志文件

log_rotation_size = 1024MB #单个日志文件的大小,如果时间没有超过log_rotation_age,一个日志文件最大只能到1024M,否则将新生成一个日志文件

log_truncate_on_rotation = on  #当日志文件已存在时,该配置如果为off,新生成的日志将在文件尾部追加,如果为on,则会覆盖原来的日志

log_lock_waits = off #控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。在判断一个锁等待是否会影响性能时是有用的,缺省是off

log_statement = 'none' # none, ddl, mod, all 控制记录哪些SQL语句,none不记录;ddl记录所有数据定义命令,比如CREATE,ALTER,和DROP 语句;mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等;all记录所有执行的语句,将此配置设置为all可跟踪整个数据库执行的SQL语句

log_duration = on #记录每条SQL语句执行完成消耗的时间,将此配置设置为on,用于统计哪些SQL语句耗时较长

log_min_duration_statement = 0 #-1表示不可用,0将记录所有SQL语句和它们的耗时,>0只记录那些耗时超过(或等于)这个值(ms)的SQL语句,log_min_duration_statement会将SQL语句和耗时在同一行记录

log_connections = off #不记录连接日志

log_disconnections = off #不记录连接断开日志

log_line_prefix = '%m [%p] %u %d %r' #日志输出格式,根据需要设置(能够记录时间,用户名称,数据库名称,客户端IP和端口等信息)

log_timezone = 'Asia/Shanghai' #日志时区,和服务器设置同一个时区

:wq! #保存退出

5、初始化数据库

#切换到postgres用户

su - postgres

#初始化数据库

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8

6、启动数据库

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start #启动

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop #停止

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart #重启

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status #查看状态

7、设置PostgreSQL开机启动

7.1使用init启动(适合CentOS 7.x)

#切换到root

su - root

#拷贝启动文件

cp /usr/local/src/postgresql-11.13/contrib/start-scripts/linux  /etc/init.d/postgresql

#设置运行权限

chmod +x /etc/init.d/postgresql

#编辑修改

vi /etc/init.d/postgresql

prefix=/usr/local/pgsql #安装目录

PGDATA="/usr/local/pgsql/data" #数据库存放目录

PGUSER=postgres #运行用户

:wq! #保存退出

service postgresql start

service postgresql restart

service postgresql stop

chkconfig postgresql on

7.2使用Systemd启动(适合CentOS 7.x 8.x )

vi /usr/lib/systemd/system/postgresql.service

[Unit]

Description=The PostgreSQL Database Server

After=syslog.target

After=network.target

[Service]

Type=forking

User=postgres

Group=postgres

ExecStart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

ExecStop=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop

ExecRestart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart

ExecReload=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data reload

ExecStatus=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status

TimeoutSec=300

[Install]

WantedBy=multi-user.target

:wq! #保存退出

systemctl daemon-reload

systemctl start postgresql

systemctl enable postgresql

systemctl restart postgresql

systemctl status postgresql

7.3使用启动脚本(适合CentOS 7.x 8.x)

#切换到su - postgres用户下操作

#创建编辑启动脚本

vi /usr/local/pgsql/pgsql.sh

#!/bin/bash

#数据库程序安装目录

prefix=/usr/local/pgsql

#数据库数据存储目录

PGDATA=/usr/local/pgsql/data

#启动日志路径

PGLOG=$PGDATA/serverlog

#定义postmaster启动方式

DAEMON=$prefix/bin/postmaster

PGCTL=$prefix/bin/pg_ctl

#数据库运行用户

#PGUSER=postgres

#提示输入参数

usage() {

echo "Usage: ./pgsql.sh [start|stop|restart|reload|status]"

exit 1

}

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

fi

}

# If we want to tell child processes to adjust their OOM scores, set up the

# necessary environment variables. Can't just export them through the "su".

if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]

then

DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"

fi

# Parse command line parameters.

case $1 in

start)

echo -n "Starting PostgreSQL: "

test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

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

$DAEMON_ENV $DAEMON -D "$PGDATA" >>$PGLOG 2>&1 &

echo "ok"

;;

stop)

echo -n "Stopping PostgreSQL: "

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

$PGCTL stop -D "$PGDATA" -s

echo "ok"

;;

restart)

echo -n "Restarting PostgreSQL: "

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

$PGCTL stop -D "$PGDATA" -s

test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

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

$DAEMON_ENV $DAEMON -D "$PGDATA" >>$PGLOG 2>&1 &

echo "ok"

;;

reload)

echo -n "Reload PostgreSQL: "

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

$PGCTL reload -D "$PGDATA" -s

echo "ok"

;;

status)

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

$PGCTL status -D "$PGDATA"

;;

*)

# Print help

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

exit 1

;;

esac

exit 0

:wq! #保存退出

#添加脚本执行权限

chmod +x /usr/local/pgsql/pgsql.sh

sh /usr/local/pgsql/pgsql.sh start|stop|restart|status

#添加开机启动

#切换到root用户下执行

vi /etc/rc.d/rc.local

su - postgres -c "/bin/sh /usr/local/pgsql/pgsql.sh start "

:wq! #保存退出

#默认/etc/rc.local没有执行权限,需要手动添加执行权限

chmod +x /etc/rc.d/rc.local

8、进入postgresql控制台,设置postgres用户登录密码

#初始化数据库后,默认会生成一个名为postgres的数据库和一个名为postgres的数据库用户,没有密码。

#postgres用户作为数据库的管理员,用来创建其他新的数据库和数据库用户。

#切换到postgres用户

su - postgres

#进入控制台

psql -U postgres -d postgres -h 127.0.0.1 -p 5432

参数:-U指定用户,-d指定数据库,-h指定服务器,-p指定端口。

#命令简写,当前的Linux系统用户postgres,同时也是PostgreSQL的数据库用户postgres,同时PostgreSQL内部有与当前系统用户同名的数据库postgres,直接使用psql命令即可登录控制台

#修改数据库用户postgres的密码为postgres

ALTER USER postgres WITH PASSWORD 'postgres';

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

ALTER ROLE

postgres=#\q #退出控制台

9、设置数据库访问权限

vi /usr/local/pgsql/data/pg_hba.conf #添加修改

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:

host all all 127.0.0.1/32 trust  #允许所有用户从本机免密访问所有数据库

host all postgres 0.0.0.0/0 md5   #允许用户postgres从任何ip以密码访问所有数据库

:wq! #保存退出

#所有数据库(all)、所有用户(all)、从本机(127.0.0.1/32)均可免密访问(trust)

#从任何ip(0.0.0.0/0 )都需要输入正确的密码(md5)才能访问,ip可按需设置

#重启服务,由于当前是在postgres用户下,会提示输入root用户的密码才能重启

systemctl restart postgresql

#注意

1、在postgres用户下,从本机登录postgres数据库,pg_hba.conf文件里面设置的是trust,使用psql -U postgres -d postgres -h 127.0.0.1 -p 5432或者psql命令登录,均免密访问。

2、在postgres用户下,从本机登录postgres数据库,pg_hba.conf文件里面设置的是md5,使用psql -U postgres -d postgres -h 127.0.0.1 -p 5432命令登录需要输入密码,使用psql命令免密登录。

3、在其他用户(root)下,使用psql -U postgres -d postgres -h 127.0.0.1 -p 5432命令从本机登录postgres数据库,pg_hba.conf文件里面设置的是trust,免密访问。

4、在其他用户(root)下,使用psql -U postgres -d postgres -h 127.0.0.1 -p 5432命令从本机登录postgres数据库,pg_hba.conf文件里面设置的是md5,则需要输入数据库用户postgres的密码才能登录。

5、在其他用户(root)下,不能使用psql命令登录,只能使用psql -U postgres -d postgres -h 127.0.0.1 -p 5432命令登录。

10、创建测试数据库

#进入控制台

psql -U postgres -d postgres -h 127.0.0.1 -p 5432

#创建数据库用户dbuser并设置密码为123456

CREATE USER dbuser WITH PASSWORD '123456';

#创建数据库testdb,指定所有者为dbuser

CREATE DATABASE testdb OWNER dbuser;

#设置用户权限,把testdb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限

GRANT ALL PRIVILEGES ON DATABASE testdb to dbuser;

#列出数据库

\l

#进入数据库

\c testdb

#退出控制台

\q

#使用dbuser用户进入

psql -U dbuser -d testdb -h 127.0.0.1 -p 5432

#创建数据表

create table testdb(id integer, name text);

#插入数据

insert into testdb values (1, 'testdb');

#查询数据库信息

select * from testdb;

#退出控制台

\q

11、PostgreSQL数据库主从同步配置

继续在主库服务器操作

11.1、创建主从同步账号repl

#进入控制台,创建数据库同步用户repl,密码为repl

psql -U postgres -d postgres -h 127.0.0.1 -p 5432

CREATE ROLE repl login replication encrypted password 'repl';

postgres=# \q

#设置数据库主从同步用户权限信息

vi /usr/local/pgsql/data/pg_hba.conf   #在最后添加下面两行

host replication repl 192.168.21.0/24 md5

host all repl 192.168.21.0/24 trust

:wq! #保存退出

11.2、修改数据库配置文件信息

vi /usr/local/pgsql/data/postgresql.conf

wal_level = replica

archive_mode = on

archive_command = 'cp %p /usr/local/pgsql/data/pg_archive/%f'

wal_keep_segments = 10240

wal_sender_timeout = 60s

:wq! #保存退出

archive_command = 'gzip < %p > /usr/local/pgsql/data/pg_archive/%f.gz'

11.3、创建归档日志目录

mkdir -p /usr/local/pgsql/data/pg_archive

systemctl restart postgresql   #最后重启数据库

三、配置PostgreSQL从数据库

在从节点服务器上进行操作

1、创建运行用户

#创建PostgreSQL运行用户,PostgreSQL不允许使用root用户运行服务

#创建用户群组postgres

groupadd postgres

#创建用户postgres,并加入postgres组

useradd -g postgres postgres

2、设置目录权限

chown postgres.postgres -R /usr/local/pgsql

chown postgres.postgres -R /usr/local/pgsql/data

chmod 0700 /usr/local/pgsql/data

3、把postgresql加入系统环境变量

vi /etc/profile #添加如下3行内容

export PGHOME=/usr/local/pgsql

export PGDATA=/usr/local/pgsql/data

export PATH=$PATH:$PGHOME/bin

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

:wq! #保存退出

source /etc/profile

4、同步主库的数据文件

#切换到postgres用户

su - postgres

/usr/local/pgsql/bin/pg_basebackup -Fp --progress -D /usr/local/pgsql/data -R -h 192.168.21.100 -p 5432 -U repl --password

#输入之前设置的密码repl

#可以看到data目录下的所有文件都同步过来了

[postgres@pgsql-slave ~]$ /usr/local/pgsql/bin/pg_basebackup -Fp --progress -D /usr/local/pgsql/data -R -h 192.168.21.100 -p 5432 -U repl --password

Password:

47876/47876 kB (100%), 1/1 tablespace

[postgres@pgsql-slave ~]$

5、创建recovery.conf文件

从模板文件拷贝到data目录

cp /usr/local/pgsql/share/recovery.conf.sample  /usr/local/pgsql/data/recovery.conf

vi /usr/local/pgsql/data/recovery.conf

standby_mode = on #on为从库

primary_conninfo = 'host=192.168.21.100 port=5432 user=repl password=repl' #对应主库信息

recovery_target_timeline = 'latest' #流复制同步最新数据

:wq! #保存退出

6、修改从库postgresql.conf文件

vi /usr/local/pgsql/data/postgresql.conf

修改如下内容项:

max_connections = 1000 #最大连接数

hot_standby = on #说明这台机器不仅仅是用于数据归档,也用于数据查询

max_standby_streaming_delay = 30s #数据流备份的最大延迟时间

wal_receiver_status_interval = 10s #间隔时间

hot_standby_feedback = on #如果有错误的数据复制,是否向主进行反馈

#日志配置

log_destination = 'csvlog' #日志格式,值为stderr,csvlog,syslog,and eventlog之一

logging_collector = on #开启日志功能,默认是off不启用日志

log_directory = 'log' #日志路径,默认是PGDATA的相对路径,即{PGDATA}/log,可以使用自定义目录

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #日志文件命名形式,使用默认即可

log_rotation_age = 1d #单个日志文件的生存期,默认1天,在日志文件大小没有达到log_rotation_size时,一天只生成一个日志文件

log_rotation_size = 1024MB #单个日志文件的大小,如果时间没有超过log_rotation_age,一个日志文件最大只能到1024M,否则将新生成一个日志文件

log_truncate_on_rotation = on #当日志文件已存在时,该配置如果为off,新生成的日志将在文件尾部追加,如果为on,则会覆盖原来的日志

log_lock_waits = off #控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。在判断一个锁等待是否会影响性能时是有用的,缺省是off

log_statement = 'none' # none, ddl, mod, all 控制记录哪些SQL语句,none不记录;ddl记录所有数据定义命令,比如CREATE,ALTER,和DROP 语句;mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等;all记录所有执行的语句,将此配置设置为all可跟踪整个数据库执行的SQL语句

log_duration = on #记录每条SQL语句执行完成消耗的时间,将此配置设置为on,用于统计哪些SQL语句耗时较长

log_min_duration_statement = 0 #-1表示不可用,0将记录所有SQL语句和它们的耗时,>0只记录那些耗时超过(或等于)这个值(ms)的SQL语句,log_min_duration_statement会将SQL语句和耗时在同一行记录

log_connections = off #不记录连接日志

log_disconnections = off #不记录连接断开日志

log_line_prefix = '%m [%p] %u %d %r' #日志输出格式,根据需要设置(能够记录时间,用户名称,数据库名称,客户端IP和端口等信息)

log_timezone = 'Asia/Shanghai' #日志时区,和服务器设置同一个时区

:wq! #保存退出

7、启动从数据库

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start #启动

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop #停止

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart #重启

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status #查看状态

8、设置从库PostgreSQL开机启动

8.1使用init启动(适合CentOS 7.x)

#切换到root

su - root

#拷贝启动文件

cp /usr/local/src/postgresql-11.13/contrib/start-scripts/linux /etc/init.d/postgresql

#设置运行权限

chmod +x /etc/init.d/postgresql

#编辑修改

vi /etc/init.d/postgresql

prefix=/usr/local/pgsql #安装目录

PGDATA="/usr/local/pgsql/data" #数据库存放目录

PGUSER=postgres #运行用户

:wq! #保存退出

service postgresql start

service postgresql restart

service postgresql stop

chkconfig postgresql on

8.2使用Systemd启动(适合CentOS 7.x 8.x )

vi /usr/lib/systemd/system/postgresql.service

[Unit]

Description=The PostgreSQL Database Server

After=syslog.target

After=network.target

[Service]

Type=forking

User=postgres

Group=postgres

ExecStart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

ExecStop=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop

ExecRestart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart

ExecReload=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data reload

ExecStatus=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status

TimeoutSec=300

[Install]

WantedBy=multi-user.target

:wq! #保存退出

systemctl daemon-reload

systemctl start postgresql

systemctl enable postgresql

systemctl restart postgresql

systemctl status postgresql

8.3使用启动脚本(适合CentOS 7.x 8.x)

#切换到su - postgres用户下操作

#创建编辑启动脚本

vi /usr/local/pgsql/pgsql.sh

#!/bin/bash

#数据库程序安装目录

prefix=/usr/local/pgsql

#数据库数据存储目录

PGDATA=/usr/local/pgsql/data

#启动日志路径

PGLOG=$PGDATA/serverlog

#定义postmaster启动方式

DAEMON=$prefix/bin/postmaster

PGCTL=$prefix/bin/pg_ctl

#数据库运行用户

#PGUSER=postgres

#提示输入参数

usage() {

echo "Usage: ./pgsql.sh [start|stop|restart|reload|status]"

exit 1

}

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

fi

}

# If we want to tell child processes to adjust their OOM scores, set up the

# necessary environment variables. Can't just export them through the "su".

if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]

then

DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"

fi

# Parse command line parameters.

case $1 in

start)

echo -n "Starting PostgreSQL: "

test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

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

$DAEMON_ENV $DAEMON -D "$PGDATA" >>$PGLOG 2>&1 &

echo "ok"

;;

stop)

echo -n "Stopping PostgreSQL: "

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

$PGCTL stop -D "$PGDATA" -s

echo "ok"

;;

restart)

echo -n "Restarting PostgreSQL: "

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

$PGCTL stop -D "$PGDATA" -s

test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"

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

$DAEMON_ENV $DAEMON -D "$PGDATA" >>$PGLOG 2>&1 &

echo "ok"

;;

reload)

echo -n "Reload PostgreSQL: "

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

$PGCTL reload -D "$PGDATA" -s

echo "ok"

;;

status)

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

$PGCTL status -D "$PGDATA"

;;

*)

# Print help

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

exit 1

;;

esac

exit 0

:wq! #保存退出

#添加脚本执行权限

chmod +x /usr/local/pgsql/pgsql.sh

sh /usr/local/pgsql/pgsql.sh start|stop|restart|status

#添加开机启动

#切换到root用户下执行

vi /etc/rc.d/rc.local

su - postgres -c "/bin/sh /usr/local/pgsql/pgsql.sh start "

:wq! #保存退出

#默认/etc/rc.local没有执行权限,需要手动添加执行权限

chmod +x /etc/rc.d/rc.local

四、验证主从配置

1、进入主库,执行以下操作

select client_addr,sync_state from pg_stat_replication;

[postgres@pgsql-master ~]$ psql -U postgres -d postgres -h 127.0.0.1 -p 5432

psql (11.13)

Type "help" for help.

postgres=# select client_addr,sync_state from pg_stat_replication;

client_addr | sync_state

----------------+------------

192.168.21.101 | async

(1 row)

postgres=# \x on

Expanded display is on.

postgres=# select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid | 16393

usesysid | 16395

usename | repl

application_name | walreceiver

client_addr | 192.168.21.101

client_hostname |

client_port | 55014

backend_start | 2022-06-26 12:13:47.617224-04

backend_xmin | 577

state | streaming

sent_lsn | 0/3000140

write_lsn | 0/3000140

flush_lsn | 0/3000140

replay_lsn | 0/3000140

write_lag |

flush_lag |

replay_lag |

sync_priority | 0

sync_state | async

postgres=#\q

#从服务器正在接收流信息,而且是异步流复制,主从配置成功。

2、在主库创建数据看是否同步到从库

#创建数据表

psql -U dbuser -d testdb -h 127.0.0.1 -p 5432

create table tb_test

(

id bigserial primary key not null,

name varchar(64)

);

#插入数据

insert into tb_test(name) values('name1');

insert into tb_test(name) values('name2');

insert into tb_test(name) values('name3');

insert into tb_test(name) values('name4');

insert into tb_test(name) values('name5');

insert into tb_test(name) values('name6');

3、进入从库查看是否同步

psql -U dbuser -d testdb -h 127.0.0.1 -p 5432

testdb=> select * from tb_test;

id | name

----+-------

1 | name1

2 | name2

3 | name3

4 | name4

5 | name5

6 | name6

(6 rows)

testdb=>\q

#可以看到数据同步成功

3、主从库进程查看

#主库

[postgres@pgsql-master ~]$ /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/| grep 'Database cluster state'

Database cluster state: in production

#从库

[postgres@pgsql-slave ~]# /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/| grep 'Database cluster state'

Database cluster state: in archive recovery

五、数据库主从切换

配置数据库主从是为了实现高可用,当主库发生故障,我们可以切换从库为主库,继续提供服务

#停止主库,模拟主库故障,在主库服务器操作

su - postgres

systemctl stop postgresql #停止主库

/usr/local/pgsql/bin/pg_ctl stop -m fast #停止主库

ps -ef|grep postgres #查看数据库进程,已经看不到了

#把从库提升为新主库,对外提供服务,在从库服务器操作

su - postgres

ps -ef|grep postgres #查看进程,数据库正常

/usr/local/pgsql/bin/pg_ctl status #查看数据库状态正常

/usr/local/pgsql/bin/pg_ctl promote #提升从库为新主库

#提升从库为主库之后,可以看到后台进程中没有startup recovering进程了,多了postgres: walwriter 写进程。

#之前的/usr/local/pgsql/data/recovery.conf文件自动更改为/usr/local/pgsql/data/recovery.done 这是告诉PostgreSQL,我现在不再是从库了,我的身份是主库。

#验证新主库是否可以写入数据

psql -U dbuser -d testdb -h 127.0.0.1 -p 5432

#创建数据表

create table new_test

(

id bigserial primary key not null,

name varchar(64)

);

#插入数据

insert into new_test(name) values('new_name1');

insert into new_test(name) values('new_name1');

insert into new_test(name) values('new_name1');

#查询数据信息

select * from new_test;

testdb=> select * from new_test;

id | name

----+-----------

1 | new_name1

2 | new_name1

3 | new_name1

(3 rows)

#可以看到已经能正常写入数据了

#在新主库上设置主从同步用户权限信息

允许新从库(原主库192.168.1.100)可以通过replica用户访问数据库

vi /usr/local/pgsql/data/pg_hba.conf #在最后添加下面两行

host replication repl 192.168.21.0/24 md5

host all repl 192.168.21.0/24 trust

:wq! #保存退出

#在新从库上创建recovery.conf文件

如果不配置该文件的话,那么原来的主库一旦重新启动话,就将成为了1个新的独立主库,脱离了主从数据库环境。

从模板文件拷贝到data目录

cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf

vi /usr/local/pgsql/data/recovery.conf

standby_mode = on #on为从库

primary_conninfo = 'host=192.168.21.101 port=5432 user=repl password=repl' #对应主库信息

recovery_target_timeline = 'latest' #流复制同步最新数据

:wq! #保存退出

chmod 0700 /usr/local/pgsql/data #设置权限,重要,否则无法启动

systemctl start postgresql #启动新从库(原主库)数据库

#验证数据库是否同步

#登录新从库(原主库)数据库

psql -U dbuser -d testdb -h 127.0.0.1 -p 5432

select * from new_test; #查看数据库信息

testdb=> select * from new_test;

id | name

----+-----------

1 | new_name1

2 | new_name1

3 | new_name1

(3 rows)

注意:

1、从库提升为主库的命令:/usr/local/pgsql/bin/pg_ctl promote

2、新主库(原从库)的pg_hba.conf文件,允许新从库(原主库192.168.1.100)可以通过replica用户访问数据库

3、原主库配置为新从库的时候,必须要创建/usr/local/pgsql/data/recovery.conf文件

4、原主库配置为新从库的时候,必须要设置权限chmod 0700 /usr/local/pgsql/data  否则无法启动

5、原主库会自动发现时间线的差异,并拷贝过来

至此,Linux下PostgreSQL数据库主从同步配置完成。

     

  系统运维技术交流QQ群:①185473046 系统运维技术交流□Ⅰ ②190706903 系统运维技术交流™Ⅱ ③203744115 系统运维技术交流™Ⅲ

给我留言

您必须 [ 登录 ] 才能发表留言!



Copyright© 2011-2022 系统运维 All rights reserved
版权声明:本站所有文章均为作者原创内容,如需转载,请注明出处及原文链接
陕ICP备11001040号-3