PostgreSQL 高可用性和读写分离

基本概念

一个操作系统可以安装多个 PostgreSQL 实例，每个实例都有自己的配置文件、数据目录和端口号。每个实例中可以创建多个数据库，每个数据库中可以创建多个表。使用 Docker 可以方便的创建多个 PostgreSQL 实例。

安装主从服务器

docker run --name postgres1 -e POSTGRES_PASSWORD=postgres -d -p 5431:5432 -v ${pwd}/postgres1/data:/var/lib/postgresql/data postgres
docker run --name postgres2 -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v ${pwd}/postgres2/data:/var/lib/postgresql/data postgres

逻辑复制

逻辑复制是根据复制标识（通常是主键）复制数据对象及其更改的一种方法。 我们使用术语逻辑与物理复制相比，逻辑复制使用发布和订阅模型， 其中一个或多个订阅者订阅发布者节点上的一个或多个发布。 订阅者从他们订阅的发布中提取数据， 并可能随后重新发布数据以允许级联复制或更复杂的配置。

创建主数据库和从数据库

在 postgres1 中创建数据库 mydb1 数据库。

CREATE DATABASE mydb1;

在 postgres2 中创建数据库 mydb2 数据库。

CREATE DATABASE mydb2;

在 mydb1 和 mydb2 数据库中创建结构相同的表 mytable。

CREATE TABLE mytable (id int PRIMARY KEY, name text NOT NULL);

将服务器日志级别设置为逻辑

在 postgres1 的 mydb1 数据库中设置服务器日志级别为逻辑。

ALTER SYSTEM SET wal_level = logical;

在 postgres2 的 mydb2 数据库中设置服务器日志级别为逻辑。

ALTER SYSTEM SET wal_level = logical;

主库创建发布者

在 postgres1 的 mydb1 主库中创建发布者。

为所有表创建发布者。

CREATE PUBLICATION my_pub FOR ALL TABLES;

为特定表创建发布者。

CREATE PUBLICATION my_pub FOR TABLE mytable;
CREATE PUBLICATION my_pub FOR TABLE mytable1, mytable2;

从库创建订阅者

在 postgres2 的 mydb2 从库中创建订阅者。

CREATE SUBSCRIPTION my_sub CONNECTION 'dbname=mydb1 host=postgres1_ip_address port=5431 user=postgres password=postgres' PUBLICATION my_pub

测试逻辑复制

分别在 postgres1 主库 mdb1 数据库和 postgres2 从库 mydb2 数据库中创建表 mytable。

在 postgres1 主库 mydb1 中插入数据。

INSERT INTO mytable VALUES (1,'hello');

在 postgres2 从库 mydb2 中查询数据。

SELECT * FROM mytable;

注意事项

对于新创建的表，需要重新创建发布者和订阅者，如果使用 FOR ALL TABLES 创建发布者，新创建的表会自动加入发布者，但是订阅者需要重新创建。

另外可以使用 pgAdmin 等图形化工具来创建发布者和订阅者。

流复制

流复制是一种物理复制方法，它通过将 WAL 记录从一个服务器传输到另一个服务器来复制数据。 流复制是 PostgreSQL 的内置功能，它可以在不同的服务器之间复制数据。

主服务器配置

在 postgres1 中创建复制用户，以免使用超级用户导致安全问题，创建一个 testuser 用户， 并且只允许该用户登录和复制功能，密码为 testpwd。

CREATE ROLE testuser LOGIN REPLICATION ENCRYPTED PASSWORD 'testpwd';

在 postgres1 中修改 pg_hba.conf 文件，允许 testuser 用户连接到主服务器来。

host replication testuser all scram-sha-256

从服务器配置

export PGPASSWORD='testpwd' && pg_basebackup -h postgres1_ip_address -p 5431 -D /var/lib/postgresql/data/my_data_backup -U testuser -P -X stream -R

以上命令会将 postgres1 的数据备份到 /var/lib/postgresql/data/my_data_backup 目录中，-X stream 表示使用流复制，-R 表示备份完成后自动启动从服务器，后续 postgres1 的数据会自动同步到 postgres2，无需手动操作。

以上命令会在 my_data_backup 目录中生成一个名为 standby.signal 的文件，表示从服务器已经启动，同时会在 postgresql.auto.conf 文件中添加 primary_conninfo 配置，表示主服务器的连接信息。

替换掉 postgres2 的数据目录，将备份的数据目录移动到 postgres2 的数据目录中。

rm -rf /var/lib/postgresql/data
mv /var/lib/postgresql/data/my_data_backup /var/lib/postgresql/data

异步和同步流复制的区别

异步复制是指主服务器将 WAL 记录发送到从服务器，但是不等待从服务器确认，主服务器会继续处理其他事务，这样可以提高主服务器的性能，但是可能会丢失数据。

同步复制是指主服务器将 WAL 记录发送到从服务器，等待从服务器确认，只有从服务器确认后，主服务器才会继续处理其他事务，这样可以保证数据的一致性，但是会降低主服务器的性能。

主服务器配置同步复制

在 postgres1 执行以下命令。

ALTER SYSTEM SET synchronous_standby_names = 'standby2';

从服务器配置

在 postgres2 的数据目录中修改 postgresql.auto.conf 文件，添加以下配置。

primary_conninfo = 'application_name=standby2'

https://www.cnblogs.com/haha029/p/16721007.html