文章目录概况安装PostgreSQL设置主节点设置从节点验证故障处理，主从节点切换从节点扩容概况CentOS Linux release 7.7.1908PostgreSQL13三台服务器，一主两从，实时复制。主节点读写，从节点只读，读写分离不借助插件或第三方中间件，仅使用PostgreSQL自带的流复制功能Replication主节点可建库建表、可读写，两个从节点为只读，程序使用时可以将查询和统计相关服务读取从节点，通过读写分离减少数据库读写压力设置同步复制，为热备份，数据实时同步，对于大多数业务来说，可以认为三个库数据完全相同，当一个库故障时，其他库仍然可提供服务，增加服务可用性当主节点故障时，数据无法更新，只能手动将从节点升格为主节点，需要修改服务的数据库连接配置等，服务短暂不可用（也可提前准备好监控和切换脚本，服务不可用时尽早发现和切换）。需要根据自己业务特点，是否能接受此种情况，来决定是否使用此方式。单点故障，自动切换的集群，可以考虑使用PGPool-II 等第三方中间件搭建，后面研究好我会出一篇文档安装PostgreSQL命令如下，具体可参考我之前的博客《PostgreSQL简介和安装部署》# 安装 repository 源:sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm# 安装 PostgreSQL13:sudo yum install -y postgresql13-server# 初始化数据库，并设置启动命令和开机启动:sudo /usr/pgsql-13/bin/postgresql-13-setup initdbsudo systemctl enable postgresql-13sudo systemctl start postgresql-13# 另外，查看状态，关闭命名和重启命令sudo systemctl status postgresql-13sudo systemctl stop postgresql-13sudo systemctl restart postgresql-13123456789101112131415三个节点安装完成后，就可以开始使用了可以创建用户、赋予权限、修改密码，可以建库建表早一些功能测试等默认安装创建的数据库，没有主从之分，都是主节点，可以使用这条语句验证下是否为从节点，查询结果为"f"表示false，当前数据库节点为主库节点sudo -u postgres /usr/pgsql-13/bin/psql -c "select pg_is_in_recovery()"1下面开始搭建集群，关闭三个PostgreSQL服务，开始改配置文件设置主节点主从节点可以自己规划好，准备好不同机器就行，也可以使用最小集群一主一从主要是修改2个配置文件，都在data目录下，一个是数据库基础配置，一个是访问权限控制默认初始化的数据库，data目录在/var/lib/pgsql/13/data修改文件夹里面的pg_hba.conf，设置 Replication 访问策略，允许流复制# replication privilege.# 追加一条“允许postgres用户，通过全部网络地址使用 Replication ”的策略host replication postgres 0.0.0.0/0 trust123继续修改数据库配置文件postgres.conf，进行一些参数设置，允许流复制# -----------------------------# PostgreSQL configuration file# -----------------------------# - Connection Settings -listen_addresses = '*' # what IP address(es) to listen on;#port = 5432 # (change requires restart)max_connections = 200 # (change requires restart)# - Authentication -#authentication_timeout = 1min # 1s-600spassword_encryption = scram-sha-256 # md5 or scram-sha-256#------------------------------------------------------------------------------# RESOURCE USAGE (except WAL)#------------------------------------------------------------------------------# - Memory -shared_buffers = 1GB # min 128kB 官方推荐内存设置带大小为系统内存的1/4temp_buffers = 64MB # min 800kBwork_mem = 64MB # min 64kBmax_stack_depth = 4MB # min 100kBdynamic_shared_memory_type = posix # the default is the first option#------------------------------------------------------------------------------# WRITE-AHEAD LOG#------------------------------------------------------------------------------wal_level = replica # minimal, replica, or logicalfsync = on # flush data to disk for crash safetysynchronous_commit = on # synchronization level;wal_log_hints = on # also do full page writes of non-critical updates# - Checkpoints -checkpoint_timeout = 10min # range 30s-1dmax_wal_size = 1GBmin_wal_size = 80MB# - Archiving -archive_mode = on # enables archiving; off, on, or always# (change requires restart)archive_command = 'cp %p /data/postgresql/archive/%f' # command to use to archive a logfile segment#-----------------------------------------------------------------------------# REPLICATION#------------------------------------------------------------------------------max_wal_senders = 10 # max number of walsender processes 设置了可以最多有几个流复制的链接# (change requires restart)wal_keep_size = 1024 # in megabytes; 0 disablesmax_slot_wal_keep_size = 10 # in megabytes; -1 disableswal_sender_timeout = 120s # in milliseconds; 0 disables# - Standby Servers -hot_standby = on # "off" disallows queries during recovery#------------------------------------------------------------------------------# REPORTING AND LOGGING#------------------------------------------------------------------------------# - Where to Log -log_destination = 'stderr' # Valid values are combinations oflogging_collector = on # Enable capturing of stderr and csvloglog_directory = 'log' # directory where log files are written,# can be absolute or relative to PGDATAlog_filename = 'postgresql-%a.log' # log file name pattern,log_truncate_on_rotation = on # If on, an existing log file with thelog_rotation_age = 1d # Automatic rotation of logfiles willlog_rotation_size = 50MB # Automatic rotation of logfiles willlog_min_duration_statement = 2000 # 配置数据库慢查询时间，如果超过配置的时间，就会将查询的SQL语句记录到日志# - What to Log -log_connections = onlog_disconnections = onlog_line_prefix = '%m [%p] ' # special values:log_timezone = 'Asia/Shanghai'datestyle = 'iso, mdy'#intervalstyle = 'postgres'timezone = 'Asia/Shanghai'# These settings are initialized by initdb, but they can be changed.lc_messages = 'en_US.UTF-8' # locale for system error message# stringslc_monetary = 'en_US.UTF-8' # locale for monetary formattinglc_numeric = 'en_US.UTF-8' # locale for number formattinglc_time = 'en_US.UTF-8' # locale for time formatting# default configuration for text searchdefault_text_search_config = 'pg_catalog.english'shared_preload_libraries = '' # citus (change requires restart)1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586启动服务，使配置生效，主节点设置完毕sudo systemctl start postgresql-13# 或sudo systemctl restart postgresql-13123设置从节点主从要保持一致，主要是 data 保持一致，我们可以直接将主节点data同步到从节点如果从节点没有关闭，可以先关闭 sudo systemctl stop postgresql-13去默认的data位置，删除所有文件