postgresql学习配置主从同步和自动备份_postgresql %f
赞
踩
最近刚接手一个维护项目，一个生产环境出了故障，所有数据都损毁了。数据第一啊，原先在做方案的时候厂家已经建议客户做冗余什么的，但是始终因为资金的原因没有落实，但也得想办法提高数据安全性开拔，优先保存数据库的内容吧。痛定思痛，就考虑学习一下数据库的自动同步，理论上自动同步有好个好处，当主服务器出现故障后，可以将从服务器切换为主服提供服务。每天自动备份数据库数据库，然后放到其他服务器上（不在一个机房），也算是异地容灾了。聊胜于无，思路我感觉是没错的。先试试吧。
注：本人技术生疏，没法原创文章，只是根据网上的教程做实际实验，然后实际应用。所以感谢能够原创文章的大神，你们是我们学习的榜样。
一、准备环境。
1、两台centos7的主机，安装好pg数据库。
pgbfz:10.0.0.60
pgbfc:10.0.0.61
二、实践
参考postgres部署主从同步(流复制) - slnngk - 博客园
有些漏项需要补一下。
1、设置pg服务可以开机启动。
我是用的把postgresql文件夹里的linux拷贝到/etc/init.d/pgsql
然手加执行权限，然后用
chkconfig --add pgsql
chkconfig pgsql on
2、设置防火墙端口可以远程访问。
firewall-cmd --zone=public --add-port=5432/tcp --permanent
最终在WIN下测试通二。
三、自动备份
参考https://www.cnblogs.com/telwanggs/p/11547370.html
#!/bin/bash
cur_time=$(date '+%Y-%m-%d')
sevendays_time=$(date -d -7days '+%Y-%m-%d')
export PGPASSWORD=oracle
echo "Starting Backup PostgreSQL ..."
rm -rf pgsql-backup.$sevendays_time.tar.gz
su -c postgres pg_dumpall -h localhost -U postgres -p 5432>"/bf/pgsql-backup.$cur_time.dmp"
tar zcvf "/bf/pgsql-backup.$cur_time.tar.gz" *.dmp
echo "Remove temp file ..."
rm -rf pgsql-backup.*.dmp
echo "Finish Backup ..."
按我的实际情况稍稍改动了一下
添加定时任务
crontab -e
0 2 * * * /bf/bf.sh
其中bf文件夹是我挂载的局域网备份NAS的盘。
备份过程中可能会用到输入密码
可以参照
31.15. 口令文件
用户家目录中的.pgpass或者PGPASSFILE 引用的文件是一个可以包含口令的文件。如果连接要求口令（并且没有用其它方法声明口令）， 那么可以用它。 在 Microsoft Windows 上，文件名字是%APPDATA%\postgresql\pgpass.conf （%APPDATA%指用户配置里的 Application Data 子目录）。
这个文件应该有下面这样的格式行：
_hostname_:_port_:_database_:_username_:_password_
你可以通过复制上面的行并且在前面添加#用于添加提醒注释到文件， 头四个字段每个都可以是一个文本值，或者*，它匹配所有的东西。 第一个匹配当前连接参数的口令行的口令域将得以使用。（因此，如果你使用了通配符， 那么应该把最具体的记录放在前面。）如果记录包含:或者\， 应该用\逃逸。一个localhost的主机名匹配来自本机的 TCP（主机名localhost）和Unix域套接字（pghost为空或缺省的套接字目录）连接。 在备用服务器中，一个名字为replication的数据库被匹配到主服务器的流复制连接。 database字段用处有限，因为对同一集群中所有数据库用户有相同密码。
在linux系统中，.pgpass的权限必须不允许任何全局或者同组的用户访问； 我们可以用命令chmod 0600 ~/.pgpass实现这个目的。如果权限比这个松， 这个文件将被忽略。在Microsoft Windows上，假定该文件存储在一个安全的目录中，所以没有做特殊的权限检查。
做一个文件，然后将
export PGPASSWORD=oracle
这一行改为
export PGPASSFILE= 你存储密码文件的路径
就可以不用输入密码备份了。
大神写的很详细了。
拷贝过来做一下文章备份
1.2 主库安装
1.2.1 安装介质准备
下载地址: https://www.postgresql.org/ftp/source/
我这里下载的是11.6版本,介质如下:
postgresql-11.6.tar.gz
这里下载的源码,所以下面的步骤是源码安装
1.2.2 安装依赖包
yum install readline
yum install gcc
yum -y install -y readline-devel
yum install zlib-devel
1.2.3 编译安装
[root@localhost soft]# tar -xvf postgresql-11.6.tar.gz
[root@localhost soft]#mkdir -p /opt/postgresql-11.6
[root@localhost soft]# cd postgresql-11.6
[root@localhost soft]#./configure --prefix=/opt/postgresql-11.6
[root@localhost soft]#make
[root@localhost soft]#make install
1.2.4 创建相应的用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres
1.2.5 创建数据及日志目录，并做相应授权
[root@localhost soft]#mkdir -p /opt/postgresql-11.6/{data,log}
[root@localhost soft]#chown -R postgres:postgres /opt/postgresql-11.6
1.2.6 初始化数据库
#su - postgres
[postgres@localhost bin]$ cd /opt/postgresql-11.6/bin
[postgres@localhost bin]$ ./initdb -D /opt/postgresql-11.6/data/
1.2.7 启动数据库
[postgres@localhost bin]$ cd /opt/postgresql-11.6/bin
[postgres@localhost bin]$./pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start
1.2.8 修改环境变量
[postgres@localhost ~]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/postgresql-11.6/bin
export PATH
1.2.9 登陆使用
[postgres@localhost bin]$cd /opt/postgresql-11.6/bin
[postgres@localhost bin]$ ./psql
psql (11.6)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
1.2.10 修改postgres用户的访问密码并测试建库建表
PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为postgres.
su - postgres
psql
# ALTER USER postgres WITH PASSWORD 'postgres';
# select * from pg_shadow ;
# create database hxl;
# \c hxl
project=# create table person(id integer, name text);
project=# insert into person values (1, 'hxl');
project=# select * from person;
1.2.11 配置postgresql允许远程访问
只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件:
pg_hba.conf：配置对数据库的访问权限;
postgresql.conf：配置PostgreSQL数据库服务器的相应的参数
vim /opt/postgresql-11.6/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
重新加载配置文件
su - postgres
pg_ctl -D /opt/postgresql-11.6/data reload
修改postgresql.conf
vim /opt/postgresql-11.6/data/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
修改该改参数需要重启动
pg_ctl -D /opt/postgresql-11.6/data -l /opt/postgresql-11.6/log/postgres.log stop
pg_ctl -D /opt/postgresql-11.6/data -l /opt/postgresql-11.6/log/postgres.log start
到这里主库已经按照好了,下面进行主库的配置
1.2.12 主从配置
1.2.12.1 创建同步账号
[postgres@localhost data]$ psql
psql (11.6)
Type "help" for help.
postgres=# CREATE ROLE repl login replication encrypted password 'repl';
CREATE ROLE
1.2.12.2 修改配置文件(pg_hba.conf)
在该文件最后添加如下两行:
host replication repl 192.168.1.0/24 md5
host all repl 192.168.1.0/24 trust
1.2.12.3 修改配置文件(postgresql.conf)
找到相应的参数进行如下配置修改
wal_level = replica
archive_mode = on
archive_command = 'cp %p /opt/postgresql-11.6/data/pg_archive/%f'
##%p = path of file to archive
##%f = file name only
max_wal_senders = 6
wal_keep_segments = 10240
wal_sender_timeout = 60s
1.2.12.4 创建归档日志目录
mkdir -p /opt/postgresql-11.6/data/pg_archive
1.2.12.5 重启主库
pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log restart
1.3 从库安装
1.3.1 安装
从库的安装跟主库安装步骤一致,需要启动数据库
1.3.2 停掉从库
若从库的数据库已经在运行的话,事先将其停掉
[postgres@localhost data]$ pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log stop
waiting for server to shut down.... done
server stopped
1.3.3 准备data目录
从库安装完成后，不初始化，若已经初始化，删除其data目录
若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录
su - postgres
[postgres@localhost postgresql-11.6]$ cd /opt/postgresql-11.6
[postgres@localhost postgresql-11.6]$ mv data bakdata
[postgres@localhost postgresql-11.6]$ mkdir data
root用户下修改权限
chown -R postgres:postgres /opt/postgresql-11.6
chmod 0700 /opt/postgresql-11.6/data
1.3.4 同步主库的数据文件
[postgres@localhost postgresql-11.6]$ pg_basebackup -Fp --progress -D /opt/postgresql-11.6/data -h 192.168.1.130 -p 5432 -U repl --password
Password:
113625/113625 kB (100%), 1/1 tablespace
可以看到data目录下的所有文件都同步过来了
[postgres@localhost data]$ pwd
/opt/postgresql-11.6/data
[postgres@localhost data]$ ls -al
total 56
drwxrwxr-x 20 postgres postgres 4096 Jan 14 11:11 .
drwxr-xr-x 9 postgres postgres 94 Jan 14 11:08 ..
-rw------- 1 postgres postgres 224 Jan 14 11:11 backup_label
drwx------ 6 postgres postgres 54 Jan 14 11:11 base
drwx------ 2 postgres postgres 4096 Jan 14 11:11 global
drwxrwxr-x 2 postgres postgres 166 Jan 14 11:11 pg_archive
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_commit_ts
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_dynshmem
-rw------- 1 postgres postgres 4719 Jan 14 11:11 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jan 14 11:11 pg_ident.conf
drwx------ 4 postgres postgres 68 Jan 14 11:11 pg_logical
drwx------ 4 postgres postgres 36 Jan 14 11:11 pg_multixact
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_notify
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_replslot
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_serial
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_snapshots
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_stat
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_stat_tmp
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_subtrans
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_tblspc
drwx------ 2 postgres postgres 6 Jan 14 11:11 pg_twophase
-rw------- 1 postgres postgres 3 Jan 14 11:11 PG_VERSION
drwx------ 3 postgres postgres 60 Jan 14 11:11 pg_wal
drwx------ 2 postgres postgres 18 Jan 14 11:11 pg_xact
-rw------- 1 postgres postgres 88 Jan 14 11:11 postgresql.auto.conf
-rw------- 1 postgres postgres 24073 Jan 14 11:11 postgresql.conf
1.3.4 创建recovery.conf文件
从模板文件拷贝到data目录
[postgres@localhost share]$ cp /opt/postgresql-11.6/share/recovery.conf.sample /opt/postgresql-11.6/data/recovery.conf
对其进行修改，参数如下:
在data目录下创建recovery.conf文件,内容如下
standby_mode = on # 这个说明这台机器为从库
primary_conninfo = 'host=192.168.1.130 port=5432 user=repl password=repl' # 这个说明这台机器对应主库的信息
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据
1.3.5 修改从库postgresql.conf文件
修改如下内容项:
max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 1s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on #如果有错误的数据复制，是否向主进行反馈
1.3.6 启动从库
pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start
1.3.7 验证
1.3.7.1 查看同步情况
[postgres@localhost pg_wal]$ psql
postgres=# \x on
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 28450
usesysid | 16411
usename | repl
application_name | walreceiver
client_addr | 192.168.1.131
client_hostname |
client_port | 42388
backend_start | 2020-01-14 11:30:41.314367+08
backend_xmin | 600
state | streaming
sent_lsn | 0/8000140
write_lsn | 0/8000140
flush_lsn | 0/8000140
replay_lsn | 0/8000140
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
1.3.7.2 主库尝试创建对象看是否同步到从库
psql -h localhost -U uhxl -d hxl
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');
查看从库是否同步
1.4 主从切换(promote方式主从切换)
1.4.1 主备库进程查看
主库
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state: in production
[postgres@localhost data]$
备库
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state: in archive recovery
[postgres@localhost data]$
1.4.2 停掉主库
pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log stop
查看状态已经处于stop状态
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state: shut down
1.4.3 提升从库为主库
这个时候从库保持运行状态,不需要停掉
[postgres@localhost data]$ pg_ctl promote -D /opt/postgresql-11.6/data/
waiting for server to promote.... done
server promoted
查看状态
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state: in production
1.4.4 验证
这个时候从库的recovery.conf文件会自动命名为recovery.done
尝试在原来的从库写入数据
insert into tb_hxl01 values(20,'name6');
insert into tb_hxl01 values(21,'name7');
insert into tb_hxl01 values(22,'name8');
insert into tb_hxl01 values(23,'name9');
insert into tb_hxl01 values(24,'name10');
insert into tb_hxl01 values(25,'name10');
写入新增数据,重库启动后,模拟差异数据是否同步到从库
1.4.5 将原来的主库部署成为重库
1.4.5.1 创建recovery.conf文件
在原来主库的data目录下创建recovery.conf文件
1.4.5.2 启动
[postgres@localhost data]$ pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start
查看状态
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state: in archive recovery
这个时候发现数据库无法连接
[postgres@localhost log]$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
查看错误日志postgres.log
2020-01-19 15:07:55.297 CST [31189] FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 1000)
解决办法,修改当前数据库的max_connections与主库保持一致
1.4.5.3 验证刚才从库写入的数据是否同步过来
查看新备库中数据是否与现主库内容相同，9.6以后的版本应该会自动同步差异
