postgresql热备以及主从切换的实现

663 阅读2分钟

主备部署

前置条件

主备部署主要主机和备机的数据库所用的版本要完全一致,同时要求开始时需要对master机进行一次全备操作。所以空间要留充足,而且不要在业务高峰期做这件事。

主机和备机 的修改主要涉及到

  • pg_hba.conf
  • postgresql.conf
  • ***recovery.conf(salve)或recovery.done(master)***

三个文件

这里的配置之:

master 10.0.4.119

slave 10.0.4.104

这个配置针对的数据库版本为 11.3-4

master配置

pg_hba.conf


# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

host    all             all             0.0.0.0/0            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

host    replication     all             10.0.4.104/32            md5

host    replication     all             10.0.4.119/32            md5

host    replication     all             ::1/128                 md5

postgresql.conf






listen_addresses = '*'      # what IP address(es) to listen on;

                    # comma-separated list of addresses;

                    # defaults to 'localhost'; use '*' for all

                    # (change requires restart)

port = 5432             # (change requires restart)

max_connections = 100           # (change requires restart)





shared_buffers = 128MB          # min 128kB

dynamic_shared_memory_type = windows    # the default is the first option



wal_level = replica         # minimal, replica, or logical



max_wal_size = 1GB

min_wal_size = 80MB





log_destination = 'stderr'      # Valid values are combinations of

        

logging_collector = on      # Enable capturing of stderr and csvlog

        

log_file_mode = 0640            # creation mode for log files,

                

log_timezone = 'Asia/Hong_Kong'



datestyle = 'iso, ymd'

#intervalstyle = 'postgres'

timezone = 'Asia/Hong_Kong'



lc_messages = 'Chinese (Simplified)_People''s Republic of China.936'            # locale for system error message

                    # strings

lc_monetary = 'Chinese (Simplified)_People''s Republic of China.936'            # locale for monetary formatting

lc_numeric = 'Chinese (Simplified)_People''s Republic of China.936'         # locale for number formatting

lc_time = 'Chinese (Simplified)_People''s Republic of China.936'                # locale for time formatting



# default configuration for text search

default_text_search_config = 'pg_catalog.simple'

recovery.done


standby_mode=on

primary_conninfo= 'host=10.0.4.104 port=5432 user=postgres password=abc.123456 application_name=s1'

salve配置

数据同步

这个是最重要的一步,要是没有这一步就会报

database system identifier differs

这个卡了我好久,翻了不少资料。

解决方法其实很简单,在启动备机前要先通过命令将主机的数据同步过来,其实就是把data文件夹拷贝过来

下面这个指令是在bin目录下执行的。


./pg_basebackup.exe -h 10.0.0.119 -U postgres -D ../data -P -X fetch

配置变更

***pg_hba.conf(与maste一样)***

***postgresql.conf(与master一样)***

recovery.conf


standby_mode=on

primary_conninfo= 'host=10.0.4.119 port=5432 user=postgres password=abc.123456 application_name=s1'

主备切换

postgresql实际上是通过检测目录下是否存在_recovery.conf 来判定_是主机还是备机的

所以主备切换的过程如下

  • 主机关闭或发生异常手动关闭
  • 重命名主机的recovery.done为reconver.conf
  • 重启主机
  • 切换到备机
  • 重命名备机的recovery.conf为recovery.done
  • 重启备机

在切换回来的过程极为类似,就不赘述了。