hot standby

 

 

 

环境描述

主服务器

IP:192.168.1.200/24

计算机名称:pg1

操作系统:linux

数据库:postgresql-9.1.2

备服务器

IP:192.168.1.201/24

计算机名称:pg2

操作系统:linux

数据库:postgresql-9.1.2

Hot standby配置

Primary

[root@pg1 ~]# su – postgres

[postgres@pg1 ~]$ psql

postgres=# create user repli replication login connection limit 2 encrypted password 'repuser';

postgres=# \q

[postgres@pg1 ~]$ vi /u01/pg9.1/data/pg_hba.conf

host   replication       repli          192.168.1.201/24         md5

[postgres@pg1 ~]$ mkdir -p /u01/pg9.1/data/archivedir

[postgres@pg1 ~]$ vi /u01/pg9.1/data/postgresql.conf

listen_addresses = '*'

wal_level = hot_standby

archive_mode = on

archive_command ='cp -i %p /u01/pg9.1/data/archivedir/%f </dev/null'

max_wal_senders = 1   -----允许连接备库数量

wal_keep_segments = 64

log_connections = on

[postgres@pg1 ~]$ pg_ctl restart

[postgres@pg1 ~]$ psql

postgres=# select pg_start_backup('base backup for log streaming');

postgres=# \q

[postgres@pg1 ~]$ exit

[root@pg1 postgresql-9.1.2]# cd /u01/pg9.1

[root@pg1 pg9.1]# tar cvfj data.tar.bz2 data

[root@pg1 pg9.1]# $ scp data.tar.bz2 root@192.168.1.201:/u01/pg9.1

 

Standby

[root@pg2 /]# cd /u01/pg9.1

[root@pg2 pg9.1]# tar xfv data.tar.bz2

[root@pg2 pg9.1]# chown postgres data

[root@pg2 pg9.1]# cd data

[root@pg2 data]# vi postgresql.conf

listen_addresses = '*'

#wal_level = hot_standby

#archive_mode = on

#archive_command ='cp -i %p /u01/pg9.1/data/archivedir/%f </dev/null'

#max_wal_senders = 1   -----允许连接备库数量

#wal_keep_segments = 64

hot_standby = on

[root@pg2 data]# cp /u01/pg9.1/share/postgresql/recovery.conf.sample recovery.conf

[root@pg2 data]# vi recovery.conf

standby_mode = 'on'

primary_conninfo='host=192.168.1.200 port=5432 user=repli password=repuser'

trigger_file = '/tmp/postgresql.trigger.5432'

[root@pg2 data]# rm -rf postmaster.pid

 

主库

[postgres@pg1 pg9.1]$ psql

postgres=# select pg_stop_backup(), current_timestamp;

 

从库

[root@pg2 data]# su - postgres

[postgres@pg2 ~]$ pg_ctl start

 

检查

1、检查备库日志

[postgres@pg2 ~]$ cd /u01/pg9.1/data/pg_log/

2、

主库

postgres=# select pg_current_xlog_location();

postgres=# select pg_switch_xlog();

postgres=# select pg_current_xlog_location();

备库

postgres=# select pg_last_xlog_receive_location();

 

<think>我们正在解决在PostgreSQL热备用(HotStandby)模式下无法使用Serializable隔离级别的问题。根据引用[2],Serializable隔离级别是最严格的事务隔离级别,它模拟串行事务执行。然而,在热备用模式下,默认只支持读已提交(ReadCommitted)和可重复读(RepeatableRead)隔离级别,而不支持Serializable隔离级别。原因分析:热备用服务器是只读的,它通过应用主服务器传来的WAL日志来保持与主服务器的同步。在热备用模式下,事务隔离级别的支持受到限制,因为备用服务器不能执行写操作,而Serializable隔离级别在某些情况下需要能够中止事务(这涉及写操作或锁的获取,但在备用服务器上不允许写操作)。解决方案:根据PostgreSQL官方文档,热备用服务器只支持读已提交和可重复读隔离级别。因此,在热备用服务器上尝试使用Serializable隔离级别会导致错误。但是,我们可以考虑以下替代方案:1.在热备用服务器上使用可重复读(RepeatableRead)隔离级别,这是热备用支持的最高隔离级别。可重复读隔离级别在大多数情况下可以避免不可重复读和幻读,但它不能提供Serializable隔离级别的严格保证。2.如果应用必须使用Serializable隔离级别,那么只能将查询发送到主服务器(可写服务器)上执行。但这样会增加主服务器的负载,并且可能影响高可用性。3.在应用层处理,例如在热备用上使用较低的隔离级别,并接受可能出现的异常,或者设计应用逻辑来容忍这些异常。4.等待PostgreSQL未来的版本是否支持在热备用上使用Serializable隔离级别(目前截至PostgreSQL15,仍然不支持)。因此,针对当前问题,最直接的解决方案是避免在热备用服务器上使用Serializable隔离级别,而是使用可重复读隔离级别。如果确实需要Serializable隔离级别,那么只能将查询路由到主服务器。步骤:1.修改应用程序,将需要Serializable隔离级别的查询发送到主服务器。2.对于其他只读查询,可以继续使用热备用服务器,并设置隔离级别为可重复读或读已提交。示例:在psql中设置事务隔离级别为可重复读```sqlSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;```或者,在连接字符串中指定:```sqljdbc:postgresql://standby-host:5432/db?defaultTransactionIsolation=REPEATABLE_READ```注意:在热备用服务器上,即使设置隔离级别为Serializable,实际上也会被映射到可重复读(在PostgreSQL9.1及以上版本中,热备用服务器将Serializable映射为可重复读)。但是,显式设置Serializable会报错吗?实际上,在热备用上执行`SETTRANSACTIONISOLATIONLEVELSERIALIZABLE`命令不会报错,但事务实际运行在可重复读级别。然而,根据文档,在热备用上不允许开始一个Serializable事务,会报错。具体行为可能取决于版本。根据PostgreSQL12的文档:在备用服务器上,可以请求serializable隔离级别,但只能提供可重复读的隔离。这是因为在备用服务器上不能使用谓词锁(predicatelocking),而这是Serializable隔离级别所必需的。因此,在备用服务器上,Serializable实际上会降级为可重复读,但不会报错。然而,在某些版本中,可能会直接报错。为了确保兼容性,建议在热备用服务器上明确使用可重复读隔离级别。如果遇到错误,例如:```ERROR:cannotsettransactionisolationleveltoserializableduringrecovery```那么说明你使用的PostgreSQL版本不允许在恢复(热备用)时设置Serializable隔离级别。在这种情况下,只能使用可重复读。因此,总结解决方案:在热备用服务器上,将事务隔离级别设置为可重复读(RepeatableRead)而不是Serializable。如果应用程序要求严格的Serializable隔离,那么将查询发送到主服务器。另外,请注意,在热备用服务器上使用可重复读隔离级别时,由于备用服务器是只读的,所以不会出现写冲突,但可能会遇到快照过旧的问题(例如,查询需要的数据可能已经被主服务器清理掉了),这时可以通过调整`hot_standby_feedback`和`vacuum_defer_cleanup_age`等参数来减少此类问题。参考引用[2]中关于Serializable隔离级别的描述,以及PostgreSQL官方文档中关于热备用和隔离级别的部分。相关问题:1.如何在PostgreSQL主从复制环境中正确设置事务隔离级别?2.热备用服务器支持哪些事务隔离级别?3.在热备用服务器上使用可重复读隔离级别有哪些限制?4.如何避免在热备用服务器上因快照过旧导致的查询取消?</think>### PostgreSQL热备用模式Serializable隔离级别解决方案 在PostgreSQL热备用(Hot Standby)模式下使用Serializable隔离级别会报错,因为热备用本质上是只读副本,而Serializable隔离级别需要完整的写操作支持来实现其严格的并发控制机制[^2]。以下是系统级解决方案: #### 1. **理解限制原因** - Serializable隔离级别要求事务可序列化执行,需通过谓词锁(prevent locks)和冲突检测机制实现[^2] - 热备用节点无法执行写操作,无法创建必要的锁结构或中止冲突事务 - 错误示例:`ERROR: cannot execute SERIALIZABLE transactions during recovery` #### 2. **推荐解决方案** **(1) 降级隔离级别(首选方案)** ```sql -- 在热备用连接中显式设置隔离级别 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` - **优势**:完全兼容热备用模式,避免错误 - **说明**:`REPEATABLE READ`是热备用支持的最高隔离级别,可防止脏读和不可重复读[^2] **(2) 路由查询到主节点** ```sql -- 应用层逻辑示例 IF needs_serializable THEN EXECUTE ON primary_node; ELSE EXECUTE ON standby_node; END IF; ``` - **适用场景**:必须使用Serializable的严格事务 - **要求**:配置负载均衡器识别事务类型 **(3) 调整PostgreSQL参数** ```ini # postgresql.conf 主节点配置 max_prepared_transactions = 100 # 启用两阶段提交 hot_standby_feedback = on # 减少快照冲突 ``` - **注意**:这只能缓解但不能根本解决Serializable限制 #### 3. **架构级优化** **(1) 读写分离设计** ```mermaid graph LR A[应用层] -->|Serializable事务| B[主节点] A -->|只读查询| C[热备用节点] ``` **(2) 异步提交优化** ```sql ALTER SYSTEM SET synchronous_commit = remote_apply; ``` - 减少主备延迟,降低`REPEATABLE READ`的幻读风险 #### 4. **错误处理机制** ```python # Python psycopg2错误处理示例 try: conn.set_session(isolation_level='SERIALIZABLE') except psycopg2.OperationalError as e: if "during recovery" in str(e): conn.set_session(isolation_level='REPEATABLE READ') logger.warning("Fallback to REPEATABLE READ on standby") ``` ### 关键注意事项 1. PostgreSQL官方文档明确说明:**热备用不支持Serializable隔离级别**[^2] 2. 即使强制设置,实际执行仍会降级为`REPEATABLE READ` 3. 主备切换时需要重建事务上下文 4. 监控工具建议: ```bash SELECT datname, usename, state, backend_xmin FROM pg_stat_activity WHERE isolation_level = 'serializable'; ``` > **重要提示**:在金融等严格场景中,如需热备用的Serializable支持,可评估PostgreSQL衍生版本(如CockroachDB),但需权衡兼容性成本。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值