作者

digoal

日期

2019-05-03

标签

PostgreSQL , hot_standby , recovery.conf , standby.signal , recovery.signal

背景

PostgreSQL 12 已经把recovery.conf的内容全部移入postgresql.conf，配置恢复、archive based standby、stream based standby，都在postgresql.conf中。postgresql.conf以及对应的两个signal文件来表示进入recovery 模式或standby模式。

https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY

例子

1、典型恢复模式配置

postgresql.conf

```

stream恢复模式配置

primary_conninfo = ''

或

archvie恢复模式配置

restore_command = ''

hot_standby = on

配置是否跨时间线

recovery_target_timeline = 'latest'

配置恢复目标，例如

立即（达到一致性即停止恢复）、时间、XID、restore point name, LSN.

recovery_target = '' # 'immediate' to end recovery as soon as a

                            # consistent state is reached  
                            # (change requires restart)
复制

recovery_target_name = '' # the named restore point to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_time = '' # the time stamp up to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_xid = '' # the transaction ID up to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_inclusive = on # Specifies whether to stop:

                            # just after the specified recovery target (on)  
                            # just before the recovery target (off)  
                            # (change requires restart)
复制

恢复目标到达后，暂停恢复、激活、停库

recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown'

                               # (change requires restart)
复制

```

在 \$PGDATA目录中，touch recovery.signal

2、典型standby模式配置

postgresql.conf

```

stream恢复模式配置

primary_conninfo = ''

或

archvie恢复模式配置

restore_command = ''

hot_standby = on

配置是否跨时间线

recovery_target_timeline = 'latest'

```

在 \$PGDATA目录中，touch standby.signal

3、如果standby.signal , recovery.signal两个文件都配置了，则优先为standby mode

If both standby.signal and recovery.signal files are created, standby mode takes precedence.

参考

1、https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY

2、详细相关 postgresql.conf

```

- Standby Servers -

These settings are ignored on a master server.

stream恢复模式配置

primary_conninfo = '' # connection string to sending server

                                    # (change requires restart)
复制

primary_slot_name = '' # replication slot on sending server

                                    # (change requires restart)
复制

promote_trigger_file = '' # file name whose presence ends recovery

hot_standby = on # "off" disallows queries during recovery
# (change requires restart)

max_standby_archive_delay = 30s # max delay before canceling queries

                                    # when reading WAL from archive;  
                                    # -1 allows indefinite delay
复制

max_standby_streaming_delay = 30s # max delay before canceling queries

                                    # when reading streaming WAL;  
                                    # -1 allows indefinite delay
复制

wal_receiver_status_interval = 10s # send replies at least this often

                                    # 0 disables
复制

hot_standby_feedback = off # send info from standby to prevent

                                    # query conflicts
复制

wal_receiver_timeout = 60s # time that receiver waits for

                                    # communication from master  
                                    # in milliseconds; 0 disables
复制

wal_retrieve_retry_interval = 5s # time to wait before retrying to

                                    # retrieve WAL after a failed attempt
复制

recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery

- Archive Recovery -

These are only used in recovery mode.

archvie恢复模式配置

restore_command = '' # command to use to restore an archived logfile segment

                            # placeholders: %p = path of file to restore  
                            #               %f = file name only  
                            # e.g. 'cp /mnt/server/archivedir/%f %p'  
                            # (change requires restart)
复制

archive_cleanup_command = '' # command to execute at every restartpoint

recovery_end_command = '' # command to execute at completion of recovery

配置恢复目标，例如立即（达到一致性即停止恢复）、时间、XID、restore point name, LSN.

- Recovery Target -

Set these only when performing a targeted recovery.

recovery_target = '' # 'immediate' to end recovery as soon as a

                            # consistent state is reached  
                            # (change requires restart)
复制

recovery_target_name = '' # the named restore point to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_time = '' # the time stamp up to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_xid = '' # the transaction ID up to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed

                            # (change requires restart)
复制

recovery_target_inclusive = on # Specifies whether to stop:

                            # just after the specified recovery target (on)  
                            # just before the recovery target (off)  
                            # (change requires restart)
复制

配置是否跨时间线

recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID

                            # (change requires restart)
复制

三个相关函数

pg_is_wal_replay_paused

pg_wal_replay_pause

pg_wal_replay_resume

recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown'

                            # (change requires restart)
复制

```

3、恢复模式与standby模式对应的signal文件， recovery.signal、standby.signal。

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.