环境:
Os:Centos 7
DB:13.8
主库:192.168.1.134
从库:192.168.1.135
########################################主库:192.168.1.134############################################
1.下载安装介质
下载地址:
源码下载地址: https://www.postgresql.org/ftp/source/
二进制下载地址(新版本的没有二进制下载,只能使用源码安装):https://www.enterprisedb.com/download-postgresql-binaries
我这里下载的介质是:
postgresql-13.8.tar.gz
2.安装依赖包
yum install readline
yum install gcc
yum -y install -y readline-devel
yum install zlib-devel
3.源码安装
[root@host134 soft]# tar -xvf postgresql-13.8.tar.gz
[root@host134 soft]# cd postgresql-13.8
[root@host134 soft]#mkdir -p /opt/pg13 ##这里是指定安装目录
[root@host134 soft]#./configure --prefix=/opt/pg13
[root@host134 soft]#make
[root@host134 soft]#make install
4.创建相应的用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres
5.创建数据及日志目录，并做相应授权
[root@localhost soft]#mkdir -p /opt/pg13/{data,log}
[root@localhost soft]#chown -R postgres:postgres /opt/pg13
6.初始化数据库
#su - postgres
[postgres@host134 /]$ cd /opt/pg13/bin
[postgres@host134 bin]$ ./initdb -D /opt/pg13/data/
7.启动数据库
[postgres@host134 bin]$ cd /opt/pg13/bin
[postgres@localhost bin]$./pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
8.登陆使用
[postgres@host134 bin]$cd /opt/pg13/bin
[postgres@host134 bin]$ ./psql
psql (13.8)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
9.设置环境变量
su - postgres
[postgres@localhost ~]$ vi .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/pg13/bin
export PATH
10.修改postgres用户的访问密码并测试建库建表
PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为postgres.
su - postgres [postgres@host134 ~]$ psql psql (13.8) Type "help" for help. postgres=# ALTER USER postgres WITH PASSWORD 'postgres'; ALTER ROLE postgres=# select * from pg_shadow ; usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig ----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+----------- postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | | (1 row)
创建用户
postgres=# create user hxl with password 'postgres';
CREATE ROLE
创建数据库
create database db_test encoding = 'utf8' owner hxl; -- 创建数据库指定字符集和属主
将数据库得权限,全部赋给某个用户
grant all on database db_test to hxl;
11.配置postgresql允许远程访问
只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件:
pg_hba.conf: 配置对数据库的访问权限;
postgresql.conf: 配置PostgreSQL数据库服务器的相应的参数
su - postgres
vi /opt/pg13/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
[postgres@host134 ~]$ pg_ctl -D /opt/pg13/data reload
server signaled
修改postgresql.conf
vi /opt/pg13/data/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on; 该参数修改需要重启动
max_connections = 1000 ##修改链接数 默认是100
修改该改参数需要重启动
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
然后通过navicate for postgresql 即可进行连接
12.创建表并写入数据
登录提示输入密码
[postgres@host134 ~]$ psql -h 192.168.1.134 -U hxl -d db_test
Password for user hxl:
psql (13.8)
Type "help" for help.
create table tb_test
(
id bigserial primary key not null,
name varchar(20),
createtime timestamp default current_timestamp,
modifytime timestamp default current_timestamp
);
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');
db_test=> select * from tb_test;
id | name | createtime | modifytime
----+-------+----------------------------+----------------------------
1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901
2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863
3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182
4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843
5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502
(5 rows)
13.开启归档模式
查看当前是在归档模式
[postgres@host134 ~]$ psql -h 192.168.1.134 -U postgres Password for user postgres: psql (13.8) Type "help" for help. postgres=# show archive_mode; archive_mode -------------- off (1 row)
可以看到当前没有在归档模式
创建归档目录
su - postgres
mkdir -p /opt/pg13/archivelog
vi /opt/pg13/data/postgresql.conf
wal_level = replica
archive_mode = on
##archive_command = 'cp %p /opt/pg13/archivelog/%f' ##pa_wal目录的归档日志会拷贝到该目录
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f' #按照日期目录归档,wal日志会自动拷贝到该目录
需要重新启动
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
查看归档情况
select pg_switch_wal();
归档切换
select pg_switch_wal();
select pg_switch_wal();
[postgres@host134 archivelog]$ ls
000000010000000000000006 000000010000000000000007 000000010000000000000008 000000010000000000000009 20221018
这个时候会看到自动创建了日志目录,日期目录有相应的归档日志
[postgres@host134 20221018]$ ls
00000001000000000000000A 00000001000000000000000B 00000001000000000000000C
删除归档
su - postgres
[postgres@host134 pg_wal]$ pg_controldata /opt/pg13/data ##可以查看当前使用的wal Latest checkpoint's REDO WAL file: 00000001000000000000000A
[postgres@host134 pg_wal]$ pg_archivecleanup -d /opt/pg13/archivelog 00000001000000000000000A ##删除00000001000000000000000A之前的归档
这里只能删除archivelog目录下的符合条件的归档,若该目录下还有子目录,比如安装日期创建文件夹的目录 ，是需要指定日期目录才能删除的，比如:
pg_archivecleanup -d /opt/pg13/archivelog/20221020 00000001000000000000000A
14.设置开机自启动
请参考:
https://www.cnblogs.com/hxlasky/p/16802748.html
15.参数配置例子
listen_addresses='*' port=5432 max_connections=1000 ####内存相关######### work_mem=10MB maintenance_work_mem=2GB max_locks_per_transaction=1024 max_wal_size=64GB checkpoint_timeout=30min checkpoint_completion_target=0.9 shared_buffers=RAM*0.25GB #RAM指内存,最大设置为64GB effective_cache_size=RAM*0.5GB #RAM指内存(free -g) ##归档部分 wal_level=replica archive_mode=on archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f' ######慢查询部分##################### logging_collector=on log_directory = '/opt/pg13/log' ##指定具体目录,否则会默认在pgdata目录下创建log目录 log_destination='stderr' log_min_duration_statement=1000 ##超过1秒的慢查询都会记录 log_filename = 'postgresql-%Y-%m-%d.log' log_truncate_on_rotation = off ##是否覆盖 log_rotation_age = 1d ##每天生成 log_rotation_size = 10MB ##每个日志大小 #log_statement = all 和 log_min_duration_statement = 5000 #根据需要两者设置其一即可 #如果两个都进行了配置默认所有SQL全部打印，log_min_duration_statement设置失效 ##log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息 log_min_duration_statement = 1000 #milliseconds,记录执行5秒及以上的语句 log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'
wal_log_hints=on
full_page_writes=on
########################################从库:192.168.1.135############################################
从库的安装部署与主库一致
########################################主从部署############################################
1.主库创建流复制的用户
postgres=# CREATE ROLE replica login replication encrypted password 'replica';
2.主库修改pg_hba.conf文件,允许备库IP通过复制用户访问数据库
vi /opt/pg13/data/pg_hba.conf # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host replication replica 192.168.1.0/24 md5 ## 新增的,我这里整个网段开放
需要重新reload,否则报错连接不了
[postgres@host134 ~]$ pg_ctl -D /opt/pg13/data reload
3.停掉从库
su - postgres
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
4.从库准备data目录
从库安装完成后，不初始化，若已经初始化，删除其data目录
若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录
su - postgres
[postgres@host135 ~]$ cd /opt/pg13
[postgres@host135 pg13]$ mv data bakdata
[postgres@host135 pg13]$ mkdir data
注意权限要正确,不对的话需要进行修改,root用户下修改权限
[root@host135 ~]# chown -R postgres:postgres /opt/pg13
[root@host135 ~]# chmod 0700 /opt/pg13/data
5.备库上执行对于主库的基础备份
[postgres@host135 pg13]$pg_basebackup -h 192.168.1.134 -p 5432 -U replica --password -X stream -Fp --progress -D /opt/pg13/data -R
注意,备份选项上带有-R选项.
[postgres@host135 pg13]$ pg_basebackup -h 192.168.1.134 -p 5432 -U replica --password -X stream -Fp --progress -D /opt/pg13/data -R
Password:
pg_basebackup: error: FATAL: no pg_hba.conf entry for replication connection from host "192.168.1.135", user "replica", SSL off
原因是主库修改了pg_hba.conf,没有reload,执行如下reload即可
pg_ctl -D /opt/pg13/data reload
[postgres@host135 pg13]$ pg_basebackup -h 192.168.1.134 -p 5432 -U replica --password -X stream -Fp --progress -D /opt/pg13/data -R
Password:
32247/32247 kB (100%), 1/1 tablespace
执行了pg_basebackup命令,从库会把主库的 postgresql.conf,pg_hba.conf文件也拷贝过来了的
现在这两个文件的内容主从库是一致的.
6.备库就可以执行pg_ctl start启动了
这时,就可以看到备库服务器上自动生成了standby.signal文件,同时,也看到在$PGDATA路径下,数据库自动帮我们配置了关于流复制的主库的信息:
[postgres@host135 data]$ ls -1 backup_label backup_manifest base current_logfiles global log pg_commit_ts pg_dynshmem pg_hba.conf pg_ident.conf pg_logical pg_multixact pg_notify pg_replslot pg_serial pg_snapshots pg_stat pg_stat_tmp pg_subtrans pg_tblspc pg_twophase PG_VERSION pg_wal pg_xact postgresql.auto.conf postgresql.conf standby.signal
也看到在$PGDATA路径下,数据库会复制主库的pg_hba.conf,postgresql.conf这两个文件到从库,这个时候主从库配置文件保持了一致,若需要修改的,也可以修改,比如端口号.
同时postgresql.auto.conf,数据库自动帮我们配置了关于流复制的主库的信息
[postgres@host135 data]$ more postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replica password=replica channel_binding=disable host=192.168.1.134 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
当然了,如果我们没有使用-R来备份主库的话.我们完全可以在备库上手工创建standby.signal文件,然后手工编辑postgresql.auto.conf,并在其内容中配置主库的信息.
7.启动从库
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
报错:
2022-10-19 10:16:25 CST [32043]: [1-1] user=,db=,app=,client=LOG: redirecting log output to logging collector process
2022-10-19 10:16:25 CST [32043]: [2-1] user=,db=,app=,client=HINT: Future log output will appear in directory "/opt/pg13/log".
2022-10-19 10:57:31 CST [3551]: [1-1] user=,db=,app=,client=FATAL: data directory "/opt/pg13/data" has invalid permissions
2022-10-19 10:57:31 CST [3551]: [2-1] user=,db=,app=,client=DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
解决办法:
root用户下修改权限
[root@host135 ~]# chown -R postgres:postgres /opt/pg13
[root@host135 ~]# chmod 0700 /opt/pg13/data
8.主库查看数据库复制信息
[postgres@host134 data]$ psql -xc "select * from pg_stat_replication" -[ RECORD 1 ]----+------------------------------ pid | 21407 usesysid | 16397 usename | replica application_name | walreceiver client_addr | 192.168.1.135 client_hostname | client_port | 50736 backend_start | 2022-10-19 10:59:43.465187+08 backend_xmin | state | streaming sent_lsn | 0/1B000148 write_lsn | 0/1B000148 flush_lsn | 0/1B000148 replay_lsn | 0/1B000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2022-10-19 11:02:13.760907+08
9.进程查看
从库进程
[postgres@host135 data]$ ps -ef|grep postgres postgres 3815 1 0 10:59 ? 00:00:00 /opt/pg13/bin/postgres -D /opt/pg13/data postgres 3816 3815 0 10:59 ? 00:00:00 postgres: logger postgres 3817 3815 0 10:59 ? 00:00:00 postgres: startup recovering 00000001000000000000001B postgres 3818 3815 0 10:59 ? 00:00:00 postgres: checkpointer postgres 3819 3815 0 10:59 ? 00:00:00 postgres: background writer postgres 3820 3815 0 10:59 ? 00:00:00 postgres: stats collector postgres 3821 3815 0 10:59 ? 00:00:00 postgres: walreceiver streaming 0/1B000148 postgres 3864 26618 0 11:00 pts/1 00:00:00 ps -ef postgres 3865 26618 0 11:00 pts/1 00:00:00 grep --color=auto postgres root 26617 25114 0 09:26 pts/1 00:00:00 su - postgres postgres 26618 26617 0 09:26 pts/1 00:00:00 -bash
主库进程
[postgres@host134 data]$ ps -ef|grep postgres postgres 11073 1 0 Oct18 ? 00:00:00 /opt/pg13/bin/postgres -D /opt/pg13/data postgres 11074 11073 0 Oct18 ? 00:00:00 postgres: logger postgres 11077 11073 0 Oct18 ? 00:00:00 postgres: checkpointer postgres 11078 11073 0 Oct18 ? 00:00:00 postgres: background writer postgres 11079 11073 0 Oct18 ? 00:00:00 postgres: walwriter postgres 11080 11073 0 Oct18 ? 00:00:00 postgres: autovacuum launcher postgres 11081 11073 0 Oct18 ? 00:00:00 postgres: archiver last was 00000001000000000000001A.00000028.backup postgres 11082 11073 0 Oct18 ? 00:00:01 postgres: stats collector postgres 11083 11073 0 Oct18 ? 00:00:00 postgres: logical replication launcher postgres 11294 11073 0 Oct18 ? 00:00:00 postgres: postgres postgres 192.168.1.134(40882) idle postgres 21407 11073 0 10:59 ? 00:00:00 postgres: walsender replica 192.168.1.135(50736) streaming 0/1B000148
10.数据验证
登录从库
[postgres@host135 data]$ psql -h 192.168.1.135 -U postgres Password for user postgres: psql (13.8) Type "help" for help. postgres=# \c db_test; You are now connected to database "db_test" as user "postgres". db_test=# select * from tb_test; id | name | createtime | modifytime ----+-------+----------------------------+---------------------------- 1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901 2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863 3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182 4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843 5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502 (5 rows)
主库写入:
[postgres@host134 data]$ psql -h 192.168.1.134 -U postgres
Password for user postgres:
psql (13.8)
Type "help" for help.
postgres=# \c db_test;
You are now connected to database "db_test" as user "postgres".
db_test=# select * from tb_test;
id | name | createtime | modifytime
----+-------+----------------------------+----------------------------
1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901
2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863
3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182
4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843
5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502
(5 rows)
db_test=# insert into tb_test(name) values('name6');
INSERT 0 1
从库查询:
[postgres@host135 data]$ psql -h 192.168.1.135 -U postgres Password for user postgres: psql (13.8) Type "help" for help. postgres=# \c db_test; You are now connected to database "db_test" as user "postgres". db_test=# select * from tb_test; id | name | createtime | modifytime ----+-------+----------------------------+---------------------------- 1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901 2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863 3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182 4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843 5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502 6 | name6 | 2022-10-19 11:04:56.543939 | 2022-10-19 11:04:56.543939 (6 rows)
尝试从库写入数据
db_test=# insert into tb_test(name) values('name7');
ERROR: cannot execute INSERT in a read-only transaction
从库尝试归档
db_test=# select pg_switch_wal();
ERROR: recovery is in progress
HINT: WAL control functions cannot be executed during recovery.
#############################主从切换##########################
1.主库停止,模拟故障
192.168.1.134上执行
##查看状态
[postgres@host134 data]$ pg_ctl -D /opt/pg13/data status
pg_ctl: server is running (PID: 24009)
/opt/pg13/bin/postgres "-D" "/opt/pg13/data"
##停止数据库
[postgres@host134 data]$ pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop -m fast
waiting for server to shut down.... done
server stopped
2.备库提升为新主库,对外提供服务
在备库192.168.1.135上执行
[postgres@host135 data]$ pg_ctl promote -D /opt/pg13/data
waiting for server to promote.... done
server promoted
[postgres@host135 data]$ ps -ef|grep postgres postgres 5225 13298 0 14:18 ? 00:00:00 postgres: walwriter postgres 5227 13298 0 14:18 ? 00:00:00 postgres: autovacuum launcher postgres 5228 13298 0 14:18 ? 00:00:00 postgres: archiver last was 000000010000000000000024 postgres 5230 13298 0 14:18 ? 00:00:00 postgres: logical replication launcher postgres 13298 1 0 09:19 ? 00:00:00 /opt/pg13/bin/postgres -D /opt/pg13/data postgres 13299 13298 0 09:19 ? 00:00:00 postgres: logger postgres 13301 13298 0 09:19 ? 00:00:00 postgres: checkpointer postgres 13302 13298 0 09:19 ? 00:00:00 postgres: background writer postgres 13304 13298 0 09:19 ? 00:00:00 postgres: stats collector
重要1:启动备库为新主库的命令是pg_ctl promote。
提升备库为主库之后,可以看到,后台进程中不再有startup recovering,以及walreceiver streaming进程了.
同时,多了postgres: walwriter 写进程.
重要2:$PGDATA/standby.signal文件自动消失了. 这是告诉PostgreSQL,我现在不再是备库了,我的身份是主库了.
这里将之前同步的信息删除掉,postgresql.auto.conf文件中的primary_conninfo
3.在新主库写入数据
192.168.1.135上执行
[postgres@host135 data]$ psql -h 192.168.1.135 -U hxl -d db_test
insert into tb_test(name) values('name9');
insert into tb_test(name) values('name10');
insert into tb_test(name) values('name11');
insert into tb_test(name) values('name12');
insert into tb_test(name) values('name13');
insert into tb_test(name) values('name14');
insert into tb_test(name) values('name15');
insert into tb_test(name) values('name16');
insert into tb_test(name) values('name17');
insert into tb_test(name) values('name18');
insert into tb_test(name) values('name19');
insert into tb_test(name) values('name20');
db_test=> select * from tb_test;
id | name | createtime | modifytime
----+--------+----------------------------+----------------------------
1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901
2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863
3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182
4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843
5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502
6 | name6 | 2022-10-19 11:04:56.543939 | 2022-10-19 11:04:56.543939
7 | name7 | 2022-10-19 11:25:52.236651 | 2022-10-19 11:25:52.236651
8 | name8 | 2022-10-20 09:21:51.977815 | 2022-10-20 09:21:51.977815
41 | name9 | 2022-10-20 14:22:26.326255 | 2022-10-20 14:22:26.326255
42 | name10 | 2022-10-20 14:22:26.34316 | 2022-10-20 14:22:26.34316
43 | name11 | 2022-10-20 14:22:26.359988 | 2022-10-20 14:22:26.359988
44 | name12 | 2022-10-20 14:22:26.433694 | 2022-10-20 14:22:26.433694
45 | name13 | 2022-10-20 14:22:26.451945 | 2022-10-20 14:22:26.451945
46 | name14 | 2022-10-20 14:22:26.469966 | 2022-10-20 14:22:26.469966
47 | name15 | 2022-10-20 14:22:26.482091 | 2022-10-20 14:22:26.482091
48 | name16 | 2022-10-20 14:22:26.498319 | 2022-10-20 14:22:26.498319
49 | name17 | 2022-10-20 14:22:26.524554 | 2022-10-20 14:22:26.524554
50 | name18 | 2022-10-20 14:22:26.555449 | 2022-10-20 14:22:26.555449
51 | name19 | 2022-10-20 14:22:26.591774 | 2022-10-20 14:22:26.591774
52 | name20 | 2022-10-20 14:22:27.587955 | 2022-10-20 14:22:27.587955
4.新主库修改pg_hba.conf文件
192.168.1.135上操作
修改新主库(原备库192.168.1.135)的$PGDATA/pg_hba.conf文件,在其中添加允许新备库(原主库192.168.1.134)可以通过replica用户访问数据库的条目信息。
host replication all 192.168.1.134/32 md5
若之前就是以网段的方式开通的话,可以不需要修改,如下:
host replication replica 192.168.1.0/24 md5
修改了pg_hba.conf文件不需要重新启动,重新加载即可
pg_ctl -D /opt/pg13/data reload
5.原主库新建$PGDATA/standby.signal文件
192.168.1.134上操作
[postgres@host134 data]$ cd /opt/pg13/data
[postgres@host134 data]$ touch standby.signal
[postgres@host134 data]$ pwd
/opt/pg13/data
[postgres@host134 data]$ ll standby.signal
-rw-rw-r-- 1 postgres postgres 0 Oct 20 14:27 standby.signal
注意:这一步骤非常非常重要,如果不配置该文件的话,那么原来的主库一旦重新启动话,就将成为了1个新的独立主库,脱离了主从数据库环境.
6.原主库修改$PGDATA/postgresql.auto.conf文件
192.168.1.134上操作
[postgres@host134 data]$ more postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. maintenance_work_mem = '262144' primary_conninfo='user=replica password=replica host=192.168.1.135 port=5432'
7.启动原主库,变为新备库
192.168.1.134上操作
[postgres@host134 data]$pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start
8.新备库数据验证
psql -h 192.168.1.134 -U hxl -d db_test
遇到的问题:
问题1:
新备库上操作192.168.1.134
将pg_wal、archive_status 和 归档目录 中的00000002.history删除即可
[postgres@host134 archive_status]$ rm *
[postgres@host134 archive_status]$ pwd
/opt/pg13/data/pg_wal/archive_status
[postgres@host134 archive_status]$ ls
[postgres@host134 pg_wal]$ rm 00000002.history
问题2:
新备库上操作192.168.1.134
2022-10-20 15:10:54 CST [28507]: [11-1] user=,db=,app=,client=LOG: primary server contains no more WAL on requested timeline 1 2022-10-20 15:10:54 CST [26013]: [91-1] user=,db=,app=,client=LOG: new timeline 2 forked off current database system timeline 1 before current recovery point 0/250000A0
解决办法:
pg_rewind -D /opt/pg13/data --source-server='host=192.168.1.135 port=5432 user=postgres dbname=postgres password=postgres'
使用该参数需要配置如下参数
wal_log_hints=on
full_page_writes=on
同时需要(数据库停止状态下操作)
pg_checksums -D /opt/pg13/data --enable
问题3:
[postgres@host134 data]$ pg_rewind -D /opt/pg13/data --source-server='host=192.168.1.135 port=5432 user=postgres dbname=postgres password=postgres' pg_rewind: servers diverged at WAL location 0/25000000 on timeline 1 pg_rewind: error: could not open file "/opt/pg13/data/pg_wal/000000010000000000000024": No such file or directory pg_rewind: fatal: could not find previous WAL record at 0/2401D8D8
需要将新主库上的相应文件拷贝到当前备库
标签：opt,10,00,postgres,部署,pg13,postgresql13,源码,2022 From： https://www.cnblogs.com/hxlasky/p/16810443.html