Postgres主从

官方地址

https://www.postgresql.org/download/linux/redhat/

1.安装

1. Rpm安装

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql12-server
#更换目录的话请使用下面的初始化
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

使用自己规划好的数据目录

#创建软连接
ln -s /data/pgsql/   pgsql

2.开启远程访问·

vim /var/lib/pgsql/12/data/postgresql.conf
修改#listen_addresses = 'localhost'  为  listen_addresses='*'
当然，此处‘*’也可以改为任何你想开放的服务器IP

3.信任远程连接

vim /var/lib/pgsql/12/data/pg_hba.conf
修改如下内容，信任全部服务器连接并设置密码校验
md5为允许密码验证,trust为免密
用32表示该IP被固定，用24表示前3位固定
# IPv4 local connections:
host    all            all      127.0.0.1/32            ident
host    all            all        0.0.0.0/0             md5

4. 修改用户密码

su - postgres  #切换用户，执行后提示符会变为 '-bash-4.2$'
psql -U postgres #登录数据库，执行后提示符变为 'postgres=#'
ALTER USER postgres WITH PASSWORD '6KqEvSMnwy';  
#设置postgres用户密码为
#\q  退出数据库
#重启数据库
systemctl restart postgresql-12

5. postgres常用命令

#登录
psql -U [userName] -d [DBName] -p -h
#查看所有的数据库
\l
#切换数据库
\c database
#查看所有的表
\d
#查看表的结构
\d [tableName]
#查看索引
\di
#退出登录
\q

数据库操作

# 创建数据库
create database testdb;

# 删除数据库
drop database testdb;

# 重命名数据库(该数据库必须没有活动的连接)
alter database testdb rename to newname;

# 以其他数据库为模板创建数据库（表结构、数据都会复制）
create database newdb template testdb;

# 将查询结果写入文件
\o /tmp/test.txt
select * from test;

# 列状显示
\w

# 再一次\o关闭写入，否则是连续写入的
\o

数据库用户创建和授权

# 建立新的数据库用户
create user durant with password '123456';

# 为新用户建立数据库
create database testdb owner durant;

# 把新建的数据库权限赋予新用户
grant all privileges on database testdb to durant;

2. 配置主从

1.主节点

#创建用户提供从节点复制流操作(postgres=#)
CREATE ROLE replica login replication encrypted password 'syYJCR2g22B';
#编辑 pg_hba.conf 追加内容允许从服务器使用replica用户来复制
#TYPE      DATABASE      USER            ADDRESS             METHOD
host    replication     replica         172.16.83.6/24          md5

#编辑 postgresql.conf 新增或修改下列属性设置

archive_mode = on     # 开启归档
archive_command = 'test ! -f /var/lib/pgsql/12/pg_archive/%f && cp %p /var/lib/pgsql/12/pg_archive/%f'
wal_level = replica
wal_keep_segments = 15
wal_sender_timeout = 60s
max_connections = 1000     # 最大连接数，从机需要大于或等于该值

#建立归档文件夹
mkdir /var/lib/pgsql/12/pg_archive

#重启 postgres

systemctl restart postgresql-12

在从节点上验证是否可以访问主节点

psql -h 172.16.83.5 -U postgres

2.从节点

#停止从节点的 postgres
systemctl stop postgresql-12

#清空数据文件夹
rm -rf /var/lib/pgsql/12/data

#从主节点拷贝数据
pg_basebackup -h 172.16.83.5 -D /var/lib/pgsql/12/data -p 5432 -U replica -Fp -Xs -Pv -R
syEQzB

#自定义目录需要设置软连接
#注意修改文件所有者是postgres

#编辑 standby.signal 文件 添加如下

standby_mode = 'on'

#编辑 postgresql.conf 新增或修改下列属性设置

primary_conninfo = 'host=172.16.83.5 port=5432 user=replica password=syEQg22B'
recovery_target_timeline = latest
max_connections = 150     #大于主节点
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

重新启动从节点

systemctl restart postgresql-12

登录主节点数据库执行如下命令

su postgres
#查看节点
select client_addr,sync_state from pg_stat_replication;
#查看节点状态
select * from pg_stat_replication ;

https://www.freesion.com/images/63/cb7b573c074d4d83e5a9012c6b80ed8f.png

登录从节点查看主库状态

select * from pg_stat_wal_receiver ;

测试

#主库
create table test01(id int primary key,note text);
insert into test01 values(1,'1111');
insert into test01 values(2,'1111');

#从库
select * from test01;