一、PostgreSQL的网络测试
安装PostgreSQL客户端
sudo yum install postgresql
进行网络测试主要是验证客户端是否能够连接到远程的PostgreSQL服务器。以下是使用psql
命令进行网络测试的基本步骤:
连接到数据库:
使用psql
命令连接到远程的PostgreSQL数据库服务器。命令的基本格式如下:psql -h 主机名或IP地址 -p 端口号 -U 用户名 -d 数据库名
其中,
-h
后面跟的是PostgreSQL数据库服务器的主机名或IP地址。-p
后面跟的是PostgreSQL数据库服务器的端口号,默认为5432。-U
后面跟的是连接数据库时使用的用户名。-d
后面跟的是要连接的数据库名。
例如,连接到IP地址为192.168.1.128,端口为5432,用户名为postgres,数据库名为mydb的PostgreSQL服务器,命令如下:
psql -h 192.168.1.128 -p 5432 -U postgres -d mydb
输入密码:
如果连接的用户设置了密码,系统会提示你输入密码。输入正确的密码后,即可成功连接到远程的PostgreSQL数据库服务器。执行命令:
连接到数据库后,你可以在psql
提示符下执行SQL命令或查询。例如,你可以使用\l
命令列出所有的数据库,或使用\c
命令切换到其他数据库。退出psql:
使用\q
命令退出psql
提示符,结束与数据库的连接。
请注意,进行网络测试前,需要确保PostgreSQL服务器已经正确配置,允许远程连接,并且防火墙设置不会阻止客户端的连接请求。
二、PostgreSQL Replication Slots 介绍
PostgreSQL的复制槽(Replication Slots)主要用于物理复制数据的场景,可以视为数据库复制的一种中间件,用于在源节点和目标节点之间传输数据变动信息。其主要作用包括:
- 确保数据的完整性和一致性:复制槽能够确保下游节点保留所有需要的WAL(Write Ahead Log)文件,以便在发生故障时可以进行恢复。
- 作为中间件:在进行物理复制时,复制槽可以作为一个中间件,将逻辑数据变动信息从源节点传输到目标节点。
关于复制槽的使用场景,以下是一些关键点:
- 流复制:在PostgreSQL的流复制机制中,复制槽起到了关键作用。通过创建和使用复制槽,可以确保主节点上的WAL文件被正确地保留并传输到备节点,从而实现数据的实时同步。
- 结合Flink:虽然Flink本身并不直接使用PostgreSQL的复制槽功能,但在某些场景下,Flink可以与PostgreSQL的复制功能结合使用。例如,Flink CDC(Change Data Capture)功能可以基于PostgreSQL的逻辑复制功能来捕获数据变更,并将这些变更实时地传输到目标系统。在这个过程中,虽然Flink CDC直接连接到PostgreSQL的主库以获取数据变更,但PostgreSQL的复制槽功能仍然在主库上创建和管理逻辑复制槽,以确保数据的完整性和一致性。
复制槽的使用需要注意以下几点:
- 非持久化:默认情况下,复制槽只在源数据库的当前会话中有效,关闭会话后将丢失。如果需要在数据库重启后仍然保留复制槽,需要手动将其持久化。
- 唯一性:每个复制槽都必须具有唯一的名称,以便于管理和识别。
- 输出插件:复制槽必须与输出插件关联,以确定要将更改如何传播到目标数据库。常见的输出插件包括pgoutput、wal2json等。
总之,PostgreSQL的复制槽功能是实现物理复制的重要工具,能够确保数据的完整性和一致性,并提供高效的复制方式。在使用复制槽时,需要仔细评估和计划,并确保有足够的技术支持来应对可能出现的问题。
三、PostgreSQL Replication Slots 相关的 SQL 操作与概念
在 PostgreSQL 中,replication slots 是用于保证逻辑复制一致性的重要机制。当使用逻辑复制时,主服务器上的 WAL(Write-Ahead Logging)日志会不断地被生成并发送到备服务器。而 replication slots 的作用就是确保即使备服务器暂时无法消费这些 WAL 日志,主服务器也不会清理这些日志,从而避免数据丢失。
以下是关于 replication slots 的一些常见 SQL 操作和概念,以博客格式呈现:
1. 查询 Replication Slots
要查看当前数据库中所有的 replication slots,可以使用以下 SQL 查询:
SELECT
active_pid,
slot_name,
database,
active,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS wal_lag_pretty,
pg_size_pretty(
pg_wal_lsn_diff(confirmed_flush_lsn, restart_lsn)
) AS wal_lag_confirmed_pretty,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
) AS wal_lag_unconfirmed_pretty,
wal_status,
*
FROM
pg_replication_slots
ORDER BY
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;
上述查询会返回 replication slots 的详细信息,包括当前活动的进程 ID(如果有的话)、slot 名称、数据库、是否活跃、WAL 延迟(以易于阅读的格式显示)等。
2.查询相关的 WAL 设置
要查看与 WAL 相关的配置设置,可以使用以下 SQL 查询:
SELECT * FROM pg_settings WHERE name IN ('wal_level', 'max_wal_senders', 'wal_sender_timeout', 'max_replication_slots'); |
这将返回 wal_level
(WAL 的级别)、max_wal_senders
(可以同时连接到主服务器进行 WAL 发送的最大备服务器数量)、wal_sender_timeout
(WAL 发送进程的超时时间)和 max_replication_slots
(可以创建的最大 replication slots 数量)等设置的值。
3.删除 Replication Slot
当不再需要某个 replication slot 时,可以使用以下 SQL 命令来删除它:
SELECT pg_drop_replication_slot('test_base_dws_1'); |
这将删除名为 test_base_dws_1
的 replication slot。请注意,在删除之前,请确保没有备服务器正在使用该 slot,否则可能会导致数据丢失。
4.创建 Logical Replication Slot
要创建一个逻辑 replication slot,可以使用以下 SQL 命令:
SELECT pg_create_logical_replication_slot('test_base_dws_1', 'decoderbufs'); |
这将创建一个名为 test_base_dws_1
的逻辑 replication slot,并使用 decoderbufs
作为输出插件(通常用于逻辑复制)。
5.终止后端进程
如果你需要终止一个与 replication slot 相关的后端进程(例如,一个停滞的 WAL 发送进程),可以使用以下 SQL 命令(但请注意,这通常是一个较为激进的操作,应谨慎使用):
SELECT pg_terminate_backend(<pid>); |
将 <pid>
替换为要终止的进程的进程 ID。这将发送一个 SIGTERM 信号给该进程,尝试优雅地终止它。如果进程没有响应,可以使用 SIGKILL 信号(但通常不建议这样做,因为它不会给进程任何清理的机会)。
6.REPLICA IDENTITY
处理增量数据不写入的问题
ALTER TABLE public.base_test_info REPLICA IDENTITY FULL;
在 PostgreSQL 中,REPLICA IDENTITY
是一个与逻辑复制相关的设置,它决定了当在复制的主库上执行删除或更新操作时,哪些列的信息应该包含在 WAL(Write-Ahead Logging)记录中,以便备库能够正确地识别哪些行被删除或更新。
REPLICA IDENTITY
可以设置为以下几种类型:
DEFAULT
: 只包括主键列(如果存在的话)。FULL
: 包括所有列。NOTHING
: 不包括任何列(通常不推荐)。USING INDEX <index_name>
: 使用指定的索引来确定哪些列应该被包括