PostgreSQL搭建及流复制-DocApp

环境

CentOS Linux release 7.7.1908 (Core)
PostgreSQL 10.9

1. Centos环境下PostgreSQL搭建

系统设置
修改最大用户进程数
vim /etc/security/limits.conf添加
*          soft     nproc          65535
*          hard     nproc          65535
*          soft     nofile         65535
*          hard     nofile         65535

修改文件句柄数
vim /etc/sysctl.conf添加
fs.file-max = 65535

安装PostgreSQL仓库
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装客户端
yum -y install postgresql10

安装服务端
yum -y install postgresql10-server

加入开机启动项
systemctl enable postgresql-10.service

初始化数据库
/usr/pgsql-10/bin/postgresql-10-setup initdb

启动数据库服务
systemctl start postgresql-10

检查运行状态
systemctl status postgresql-10

设置用户密码
{
$ su - postgres
# 默认用户postgres
-bash-4.2$ psql
psql (10.9)
Type "help" for help.

postgres=# \password
# 设置密码为 '******'
Enter new password:
Enter it again:
# 保存退出
postgres=# \q
}

修改监听地址
vim /var/lib/pgsql/10/data/postgresql.conf
listen_addresses = '*'
#listen_addresses = 'localhost'

修改客户端认证方式
vim /var/lib/pgsql/10/data/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             192.168.1.0/24          md5
host    all             all             0.0.0.0/0            md5

重启服务
systemctl restart postgresql-10

连接测试
psql 是 PostgreSQL 的客户端程序，要连接 PostgreSQL 数据库，我们需要指定以下内容：
{
$ psql -h 192.168.1.2 -U postgres
Password for user postgres:
psql (10.9)
Type "help" for help.

postgres=# \q
}

1.5.创建用户并授权

创建一个用户，只允许访问单独某个特定的库，而其他库不允许访问

使用超级用户登录数据库

然后执行

CREATE USER testUser WITH PASSWORD '*****';

这时就创建了用户 testUser

GRANT ALL PRIVILEGES ON DATABASE testDB TO testUser;

将数据库 testDB 权限授权于 testUser 但此时用户还是没有读写权限，需要继续授权表

--进入需要操作的数据库后执行
GRANT ALL PRIVILEGES ON all tables in schema public TO testUser;

这一句是将当前数据库下 public schema 的表都授权于 testUser 如果要单独一个权限以及单独一个表，则：

GRANT SELECT ON TABLE mytable TO testUser;

这一句就是将 mytable 这张表的查询权限授予 testUser

2. PostgreSQL异步流复制热备

隐藏内容

此处内容需要权限查看

普通2.9元

3. PostgreSQL同步复制

同步与异步区别
PostgreSQL流复制默认是异步的。如果主服务器崩溃，那么某些已被提交的事务可能还没有被复制到从服务器，会导致数据丢失。数据的丢失量与故障转移时的复制延迟成比例。

同步复制能够保证一个事务的所有修改都能被传送到同步的从服务器。提高了事务提交标准的持久化级别，这种保护级别被称为2-safe复制。

同步复制时，写事务的每次提交将一直等待，直到该提交在主服务器和从服务器上都已被写入到磁盘上的预写日志中。数据会被丢失的唯一可能性是主服务器和后备服务器在同一时间都崩溃。

一旦流复制已经被配置，配置同步复制就只需要一个额外的配置步骤，将synchronous_standby_names设置为一个非空值。 synchronous_commit也必须被设置为on，但由于这是默认值，通常不需要改变。同步模式比异步模式增加了响应时间，用性能换取了安全。

3.1 同步复制配置

设置application_name
更新备库配置文件 recovery.conf 中的 primary_conninfo 参数，默认值为 walreceiver ，需要指定该实例的 application_name 。

更新前
primary_conninfo = 'host=192.168.1.2 port=5432 user=repuser password=repuser'

主库查询复制状态为
select application_name, client_addr, sync_state from pg_stat_replication;
 application_name |  client_addr  | sync_state
------------------+---------------+------------
 walreceiver      | 192.168.1.3 | async
(1 row)

更新后
primary_conninfo = 'host=192.168.1.2 port=5432 user=repuser password=repuser application_name=standby003'

重启从库服务，主库查询复制状态为
select application_name, client_addr, sync_state from pg_stat_replication;
 application_name |  client_addr  | sync_state
------------------+---------------+------------
 standby003       | 192.168.1.3 | async
(1 row)
根据 application_name 参数，可以看出实例名更改已经生效为 standby003

设置synchronous_standby_names
更新主服务器的postgresql.conf文件
synchronous_standby_names = 'standby003'

重启主库服务，主库查询复制状态为
select application_name, client_addr, sync_state from pg_stat_replication;
 application_name |  client_addr  | sync_state
------------------+---------------+------------
 standby003       | 192.168.1.3 | sync
(1 row)
根据sync_state参数，可以看出复制模式是同步状态。

synchronous_standby_names
同步复制支持一个或多个同步从服务器，提交的事务会一直等待，直到所有同步从服务器都确认收到了数据为止。该参数支持三种配置方式。
[FIRST] num_sync ( standby_name [, ...] )

关键字FIRST加上num_sync，指定基于优先级的同步复制。 例如，FIRST 3 (s1, s2, s3, s4) 名称出现在列表前面3个的从服务器被赋予更高的优先级，并将被视为同步复制。在列表后面出现的其它从服务器为潜在的同步从服务器。如果当前的某些同步从服务器断开连接，列表后面的从服务器，优先级将被提高，从而转变为同步从服务器，其中关键字FIRST是可选的。
ANY num_sync ( standby_name [, ...] )

关键字ANY加上num_sync，指定基于数量的同步复制，例如，ANY 3 (s1, s2, s3, s4) 每个提交至少被s1、s2、 s3 和s4中的任意三个从服务器同步复制。
standby_name [, ...]

与第一个使用FIRST和num_sync等于1的语法相同。例如，FIRST 1 (s1, s2)和s1, s2具有相同的含义：选择s1或s2作为同步服务器。特殊项*匹配任何备用服务器名称