最早的c2c网站,资阳网站设计,百度竞价排名危机事件,360免费做网站电话本文面向想要练习 PostgreSQL 中数据库复制基础知识但可能无法访问远程服务器的初学者。我认为学习新技术时#xff0c;在自己的机器上运行示例以巩固概念是至关重要的。对于副本来说#xff0c;这可能很困难#xff0c;因为许多可用的资源假设用户具有一定的 PostgreSQL 经…本文面向想要练习 PostgreSQL 中数据库复制基础知识但可能无法访问远程服务器的初学者。我认为学习新技术时在自己的机器上运行示例以巩固概念是至关重要的。对于副本来说这可能很困难因为许多可用的资源假设用户具有一定的 PostgreSQL 经验并且可以访问另一台运行副本的服务器。我们不会在这里做任何假设唯一的先决条件是您已经安装了 Postgres 并且可以登录到 shell。
准备工作
对于这些示例我们使用 Ubuntu 22.10 来运行 PostgreSQL 16开发版本。
$HOME/pg/data将$HOME/pg/data 作为我们的数据目录。第一个示例将介绍使用流式物理复制然后是使用 日志传输 (shipping)进行物理复制的简短示例然后是逻辑复制的示例最后总结不同的复制类型及其功能。
流式物理复制
首先我们以超级用户身份登录主服务器通常这是默认用户“postgres”。
psql -U postgres进入 shell 后为复制创建一个新角色在主服务器中创建一个表向该表中插入一些数据然后退出。
postgres# CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD rep_pass;
postgres# create table t1(a int, b int);
postgres# insert into t1 values (1,2);
postgres# \q打开 $HOME/pg/data/postgres.conf 并确保
listen_addresses localhost然后将以下内容附加到 $HOME/pg/data/pg_hba.conf 的末尾
host replication rep_user localhost md5运行以下命令创建主服务器的备份然后将其用作副本服务器的数据目录位于 $HOME/pg/rep。
$ pg_basebackup -h localhost -U rep_user -X stream -C -S replica_1 -v -R -W -D $HOME/pg/rep创建此目录后打开 $HOME/pg/rep/postgres.conf 并将端口号设置为 PostgreSQL 默认 5432 以外的其他值。
port 5433然后在 postgres.conf 中编辑以primary_conninfo开始的 行使其如下所示
primary_conninfo dbnamepostgres userpostgres hostlocalgost port5432 sslmodedisable在启动服务器之前我们必须做的最后一件事是在副本服务器的数据文件夹中创建一个 空的standby.signal文件。在命令行中输入以下内容
$ touch $HOME/pg/rep/standby.signal打开第二个终端因为我们将在主机上启动两个独立的 PostgreSQL 实例每个实例位于不同的端口上。
在第一个终端主服务器中输入
pg_ctl -D $HOME/pg/data start在第二个终端副本服务器中输入
pg_ctl -D $HOME/pg/rep start每个都应该给出一个输出表明服务器已启动。我们可以通过输入以下内容来确认这一点
$ ps -aux | grep postgres
tristen 29088 0.0 0.1 175596 18580 ? Ss 15:42 0:00 /home/tristen/disk/pgapp/bin/postgres -D /home/tristen/pg/data
tristen 29089 0.0 0.0 175728 2388 ? Ss 15:42 0:00 postgres: checkpointer
tristen 29090 0.0 0.0 175752 2364 ? Ss 15:42 0:00 postgres: background writer
tristen 29092 0.0 0.0 175596 7424 ? Ss 15:42 0:00 postgres: walwriter
tristen 29093 0.0 0.0 177196 4892 ? Ss 15:42 0:00 postgres: autovacuum launcher
tristen 29094 0.0 0.0 177176 4984 ? Ss 15:42 0:00 postgres: logical replication launcher
tristen 29112 0.0 0.1 175596 18456 ? Ss 15:42 0:00 /home/tristen/disk/pgapp/bin/postgres -D /home/tristen/pg/rep
tristen 29113 0.0 0.0 175728 2564 ? Ss 15:42 0:00 postgres: checkpointer
tristen 29114 0.0 0.0 175596 2564 ? Ss 15:42 0:00 postgres: background writer
tristen 29115 0.0 0.0 176348 4488 ? Ss 15:42 0:00 postgres: startup recovering 000000010000000000000014
tristen 29116 0.0 0.0 176136 3664 ? Ss 15:42 0:00 postgres: walreceiver streaming 0/14000110
tristen 29117 0.0 0.0 177336 7060 ? Ss 15:42 0:00 postgres: walsender rep_user 127.0.0.1(39650) streaming 0/14000110
tristen 29126 0.0 0.0 17580 2252 pts/2 S 15:42 0:00 grep --colorauto postgres可以看到有两个进程 $HOME/disk/pgapp/bin/postgres 在不同的两个目录中运行。 现在我们将连接到两个实例中的每一个。首先是第一个终端上的主服务器
psql -U postgres -p 5432然后是第二个终端上的副本服务器
psql -U postgres -p 5433-p 标志用于指定端口。在主服务器上使用它是多余的因为它使用默认端口。然而这很好地表明我们确实连接到两个不同的端口从而连接到两个不同的 PostgreSQL 实例。
如果我们在 Postgres shell 中输入 \d可以获得数据库中的表的列表。在副本服务器相对应的第二个终端中可以看到表 t1 确实被复制了。
postgres# \dList of relationsSchema | Name | Type | Owner
-------------------------------public | t1 | table | postgres
(1 row)现在让在主服务器上创建一个新表以表明它将自动复制到副本服务器。
在第一个终端上输入
postgres# create table t2(c int, d text);
CREATE TABLE
postgres# insert into t2 values (3, hello);
INSERT 0 1在第二个终端中输入
postgres# select * from t2;c | d
----------3 | hello
(1 row)可以看到不仅新表是从主服务器复制过来的而且刚刚插入的数据也是如此。
值得注意的是这种复制关系只是单向的。也就是说副本服务器只能从主服务器的复制。如果尝试在副本服务器中创建表则会收到一条错误消息
ERROR: cannot execute CREATE TABLE in a read-only transaction从逻辑上讲这是因为我们希望副本是只读的以使来自 主服务器的数据更容易可用。
日志传输物理复制
日志传输(Shipping)物理复制 与 流式物理复制 有类似的设置主要只是更改一些配置文件。在主服务器上编辑 postgresql.conf
wal_level replica
archive_mode on
archive_command cp %p /path/to/archive/%f如果服务器正在运行请重新启动服务器并创建主服务器的备份
$ pg_basebackup -h localhost -U rep_user -X fetch -v -R -W -D $HOME/pg/rep打开 $HOME/pg/rep/postgresql.conf 并编辑两行。注释掉以 archive_command 开头的行并取消注释以 Restore_command 开头的行添加在 之后进行恢复时要使用的命令。这些行应如下所示
#archive_command cp %p /path/to/archive/%frestore_command cp /path/to/archive/%f %p像 流式物理复制 示例中一样启动两台服务器应该可以看到副本正在复制主服务器。请注意日志传输更改(shipping changes)可能不会像在流式物理复制中那样实时。日志传输复制具有较高的延迟并且仅在 WAL 文件填充到副本时才发送更改。
逻辑复制
逻辑复制与物理复制具有与服务器相同的初始设置。它涉及一些不同的配置还涉及一些手动编辑以使表正常工作。
在主服务器上编辑 postgresql.conf
wal_level logical确保主服务器上的 pga_hba.conf 包含复制用户的正确连接权限。
host all rep_user localhost md5一旦所有配置准备就绪要启动两台服务器并登录 shell。回想一下在我们的示例中每个服务器都使用不同的端口号因此请确保您没有两次使用同一服务器在每个服务器各自的 shell 中我们需要在每个服务器中创建一个具有 相同名称和结构 的表。如果表完全不同副本将无法找到它将收到错误。也就是说在两个终端中输入以下内容
postgres# create table t1(id int, val text);现在我们在两个数据库中都有相同的空表继续以下命令复制该表。在主服务器上输入以下命令为表 t1 创建发布
postgres# CREATE PUBLICATION pub_t1 FOR TABLE t1;这将为表 t1 创建一个发布这意味着表 t1 将被复制到副本服务器。可以复制任意数量的表作为发布的一部分。
创建发布后将副本数据库中的表 t1 订阅到主数据库中的表 t1。为此必须在副本数据库中创建订阅如下所示
postgres# CREATE SUBSCRIPTION sub_t1 CONNECTION dbnamepostgres hostlocalhost port5432 userpostgres PUBLICATION pub_t1;注意在本示例中我们同时使用了默认用户 postgres 和默认数据库 postgres但这不是必需的。
现在已经创建了订阅将看到主服务器表 t1 中的所有更新都出现在副本服务器的表 t1 中。主服务器上没有做发布的任何其他表都不会复制到副本。现在做一个测试在主数据库终端中向表 t1 中插入一些数据
postgres# insert into t1 values (1, hello);
INSERT 0 1
postgres# insert into t1 values (2, world);
INSERT 0 1现在让我们看看该表是否实际上已复制到副本服务器中
postgres# select * from t1;id | val
-----------1 | hello2 | world
(2 rows)现在数据已成功复制到副本服务器。但我们怎么区分这是不是物理复制呢好吧让我们向主数据库添加一个新表并插入一些数据
postgres# create table t2(c int, d int);
CREATE TABLE
postgres# insert into t2 values(3,4);
INSERT 0 1
postgres# insert into t2 values(5,6);
INSERT 0 1再检查一下副本
postgres# select * from t2;
ERROR: relation t2 does not exist
LINE 1: select * from t2;可以看到表 t2 没有被复制因为它没有订阅因此不存在于副本中。这允许将某些表从中央数据库公开给远程数据库同时保持其他表的私有性。
但是对 表结构 的 修改 在副本数据库中 结果会如何好吧让我们测试一下。在主数据库终端中更改表结构并添加一些数据
postgres# alter table t1 add column x int;
ALTER TABLE
postgres# insert into t1 values (3, foo, 42);
INSERT 0 1
postgres# insert into t1 values (4, bar, 10);
INSERT 0 1
postgres# select * from t1;id | val | x
---------------1 | hello | 2 | world | 3 | foo | 424 | bar | 10
(4 rows)现在检查一下副本服务器
postgres# select * from t1;id | val
-----------1 | hello2 | world
(2 rows)可以看到副本服务器不再复制主服务器。即使将同一架构中的新数据添加到主数据库中它也不会显示在副本中
postgres# insert into t1 values (15, test);
INSERT 0 1postgres# select * from t1;id | val
-----------1 | hello2 | world
(2 rows)由于我们更改了 t1 的 架构副本不再从中复制数据因为它现在实际上是一个不同的表。如果您更改了表的架构并将副本运行在其他地方记住这一点很重要。
如果不在副本中创建与主服务器中的表 相匹配的表会发生什么情况
postgres# CREATE PUBLICATION pub_t2 FOR TABLE t2;
CREATE PUBLICATIONpostgres# CREATE SUBSCRIPTION sub_t2 CONNECTION dbnamepostgres hostlocalhost port5432 userpostgres PUBLICATION pub_t2;
ERROR: relation public.t2 does not exist这样收到一条错误消息指出我们想要订阅的 关系 不存在。这可能会非常令人困惑因为我们给了它所有连接信息并且我们知道表 t2 存在于该位置。但是此错误是指它在我们的副本上找不到表 t2因此无法创建订阅。因此请务必记住我们需要两个数据库包含具有相同架构的相同表以便逻辑复制正常工作。
对比
以下是我们从高层次角度讨论的所有主题的摘要。如果您仍在尝试决定要使用哪种复制方法那么此摘要可能有助于做出决定。
逻辑复制与物理复制
Logical 逻辑复制Physical 物理复制在行级别工作将对各个数据库行的更改从主服务器复制到副本服务器。通过逻辑复制我们可以选择要复制哪些表、模式甚至列从而提供更多粒度。在磁盘块级别工作将数据从主服务器复制到副本。这使得它在时间和空间上都更加高效。因为逻辑在行级别工作所以我们可以在不同的 PostgreSQL 版本之间甚至在不同操作系统上运行的 PostgreSQL 实例之间进行备份。由于复制了整个数据库集群因此我们无法获得逻辑复制的粒度。逻辑复制支持双向或多主复制设置。我们还需要在同一操作系统上运行相同的 PostgreSQL 版本来制作副本。逻辑复制的效率低于物理复制物理复制有两种方式传输 和 流复制
逻辑复制与物理复制的比较
日志传输 与 流式复制
Streaming 流式复制Shipping 日志传输当发生更改时将更改从主服务器发送到副本。这会降低延迟并确保副本与主服务器保持同步接近实时不会在发生更改时发送更改而是在 WAL 文件填满或达到可配置的超时后发送它们。相对来说延迟较高。持续将 WAL 记录发送到副本副本会重放这些记录以保持同步在文件级别工作WAL 文件在主服务器上存档并复制到副本服务器上。这是通过手动传输或脚本完成的然后重播 WAL 文件以与主服务器保持同步。更高效、更少资源占用不需要复制或归档 WAL 文件效率较低且资源密集需要持久连接不需要持久连接适合连接不稳定或高延迟的环境。副本是只读的。对于负载平衡查询、高可用性和备份很有用但不适合写入。副本可以配置为只读或读写。
流式物理复制与日志传输物理复制的比较
异步流与同步流 复制
Asynchronous 异步Synchronous 同步如果主服务器比较繁忙则副本可以落后于主服务器。如果主服务器崩溃我们就会丢失未复制的数据。性能较高主服务器在收到副本已收到事务的确认之前不会提交。如果主数据库崩溃我们永远不会丢失数据但是如果副本出现问题此方法可能会减慢主数据库的速度甚至停止它。此外由于网络延迟也会对性能产生影响。
异步流与同步流的比较
总结
关于使用 PostgreSQL 设置服务器复制以实现各种复制方法的文章到此结束。首先我们回顾了设置和运行每种方法的实际步骤然后查看了每种方法功能的高级摘要。
References 参考
27.2. log-shipping standby servers. PostgreSQL Documentation. (2023, February 9). Retrieved March 29, 2023, from https://www.postgresql.org/docs/current/warm-standby.html
31.11. quick setup. PostgreSQL Documentation. (2023, February 9). Retrieved March 29, 2023, from https://www.postgresql.org/docs/current/logical-replication-quick-setup.html
B, A. (2022, April 8). [web log]. Retrieved March 29, 2023, from https://scalegrid.io/blog/comparing-logical-streaming-replication-postgresql/.
Levinas, M. (2022, October 10). [web log]. Retrieved March 29, 2023, from https://www.cherryservers.com/blog/how-to-set-up-postgresql-database-replication.
Ravoof, S. (2023, February 17). [web log]. Retrieved March 29, 2023, from https://kinsta.com/blog/postgresql-replication/. 原文地址Setting Up a PostgreSQL Replica Server Locally - Highgo Software Inc.
