CentOS7配置PostgreSQL12主从服务器（Docker-compose）

野性酷女 2023-07-11 05:37 7阅读 0赞

1、复制[hub.docker.com中官方PostgreSQL12][hub.docker.com_PostgreSQL12]文档中的docker-compose.yml，添加volumes、ports。

# Use postgres/example user/password credentials
    version: '3.1'
    
    services:
    
      db:
        image: postgres
        restart: always
        environment:
          POSTGRES_PASSWORD: example
        volumes:
          - ./data:/var/lib/postgresql/data
        ports:
          - 5432:5432
    
      adminer:
        image: adminer
        restart: always
        ports:
          - 8080:8080

> 先在主、从服务器安装相同配置的PostgreSQL12数据库
> 
>     docker-compose up -d

2、主：使用postgres账号登录，添加复制同步专用账号

psql -h IP -U postgres

CREATE ROLE replica login replication encrypted password 'PASSWORD';

3、主：配置data/pg\_hba.conf，允许replica用户来同步。

# IPv4 local connections:
    host    all             all             10.20.20.0/24            trust
    # replication privilege.
    host    replication     replica         10.20.20.0/24             md5

4、主：配置data/postgresql.conf

listen_addresses = '*'
    
    max_connections = 100
    
    shared_buffers = 128MB
    
    wal_level = replica                     # minimal, replica, or logical
    
    fsync = on                              # flush data to disk for crash safety
    
    full_page_writes = on                   # recover from partial page writes
    
    archive_mode = on               # enables archiving; off, on, or always
    
    # command to use to archive a logfile segment
    archive_command = 'cp %p /opt/docker/postgres/data/pg_archive/%f'               
    
    # command to execute at every restartpoint
    archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/data/pg_archive %r'    
    
    recovery_target_timeline = 'latest'     # 'current', 'latest', or timeline ID
    
    max_wal_senders = 10            # max number of walsender processes
    
    wal_keep_segments = 32          # in logfile segments; 0 disables
    
    hot_standby = on                        # "off" disallows queries during recovery
                                            # (change requires restart)
    max_standby_archive_delay = 300s        # max delay before canceling queries
                                            # when reading WAL from archive;
                                            # -1 allows indefinite delay
    max_standby_streaming_delay = 300s      # 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 = on               # send info from standby to prevent
    
    log_destination = 'stderr'
    
    logging_collector = on 
    
    log_directory = 'log'
    
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern
    
    log_rotation_age = 1d
    
    log_rotation_size = 10MB
    
    log_line_prefix = '%m [%p] '

>  archive\_cleanup\_command定义自动清除同步缓存文件执行的命令。
> 
> 如果主库不能自动清除同步缓存，可以采用手动操作。
> 
> 1、进入postgresql的docker容器  
>       docker  exec  -it mspbots.postgres  bash  
> 2、进入规定目录/var/lib/postgresql/data/pg\_archive  
> 3、查看最后归档信息  
>       pg\_controldata  
> pg\_control version number: 1201  
> Catalog version number: 201909212  
> Database system identifier: 6774642186203422757  
> Database cluster state: in production  
> pg\_control last modified: Tue 21 Apr 2020 09:00:12 AM  
> Latest checkpoint location: 277/D1777F68  
> Latest checkpoint's REDO location: 277/D13D1C78  
> **Latest checkpoint's REDO WAL file: 0000000100000277000000D1**  
> Latest checkpoint's TimeLineID: 1  
> Latest checkpoint's PrevTimeLineID: 1  
> Latest checkpoint's full\_page\_writes: on  
> Latest checkpoint's NextXID: 0:182519632  
> Latest checkpoint's NextOID: 71261809  
> Latest checkpoint's NextMultiXactId: 1  
> Latest checkpoint's NextMultiOffset: 0  
> Latest checkpoint's oldestXID: 480  
> Latest checkpoint's oldestXID's DB: 1  
> Latest checkpoint's oldestActiveXID: 182519632  
> Latest checkpoint's oldestMultiXid: 1  
> Latest checkpoint's oldestMulti's DB: 1  
> Latest checkpoint's oldestCommitTsXid:0  
> Latest checkpoint's newestCommitTsXid:0  
> Time of latest checkpoint: Tue 21 Apr 2020 08:58:26 AM  
> Fake LSN counter for unlogged rels: 0/3E8  
> Minimum recovery ending location: 0/0  
> Min recovery ending loc's timeline: 0  
> Backup start location: 0/0  
> Backup end location: 0/0  
> End-of-backup record required: no  
> wal\_level setting: replica  
> wal\_log\_hints setting: off  
> max\_connections setting: 100  
> max\_worker\_processes setting: 8  
> max\_wal\_senders setting: 10  
> max\_prepared\_xacts setting: 0  
> max\_locks\_per\_xact setting: 64  
> track\_commit\_timestamp setting: off  
> Maximum data alignment: 8  
> Database block size: 8192  
> Blocks per segment of large relation: 131072  
> WAL block size: 8192  
> Bytes per WAL segment: 16777216  
> Maximum length of identifiers: 64  
> Maximum columns in an index: 32  
> Maximum size of a TOAST chunk: 1996  
> Size of a large-object chunk: 2048  
> Date/time type storage: 64-bit integers  
> Float4 argument passing: by value  
> Float8 argument passing: by value  
> Data page checksum version: 0  
> Mock authentication nonce: 7bdc32fcb5f102f91259dafc40c400b367d855ed6863cfe3997500e46b43af55  
> 4、执行清理最后检查点之前的旧归档文件  
> pg\_archivecleanup  .  0000000100000277000000D0

5、主：在data目录下创建子目录“pg\_archive”,“log”

6、从：进入postgreSQL容器

docker exec -it 容器名称 bash

7、从：在postgreSQL容器中删除data目录中的配置数据，从主服务器备份数据到从服务器。

> **注意：**在容器中执行rm删除data目录，一段时间后容器会自动检测到PostgreSQL运行异常，自动重启导致容器重启失败。（因为data数据目录被删除）
> 
> 所以，如果要执行rm删除data目录方式备份数据库，最好是在主、从都是新的情况下完成，应为新的库里没数据，执行pg\_basebackup比较快，容器还没检测出来时已经完成主库的备份。
> 
> 那么，如果有主库已经有大量数据的情况下采用rm删除data目录方式会导致备份数据库失败。
> 
> **推荐做法：**
> 
> 1、不执行rm命令
> 
> 2、把主机备份保存在data/data目录中
> 
>     su postgres
>     mkdir /var/lib/postgresql/data/data
>     pg_basebackup -h 主IP -U replica -D $PGDATA/data -c fast -X stream -v -P -R
> 
>  3、备份完成后，修改docker-compose.yml，重新指向新的data目录。
> 
>     - ./data/data:/var/lib/postgresql/data
> 
> 4、重启容器
> 
>     docker-compose restart
> 
>

rm -rf /var/lib/postgresql/data/*.*
    
    pg_basebackup -h 主IP -U replica -D $PGDATA-c fast -X stream -v -P -R

> 系统提示输入密码，输入replica密码
> 
> 系统默认创建两个文件：postgresql.auto.conf、standby.signal（标识当前为从服务器）

> **pg\_basebackup命令行参数介绍： **
> 
>     pg_basebackup takes a base backup of a running PostgreSQL server.
>     
>     Usage:
>       pg_basebackup [OPTION]...
>     
>     Options controlling the output:
>       -D, --pgdata=DIRECTORY receive base backup into directory
>            指定把备份写到那个目录，如果这个目录或这个目录路径中的各级父目录不存在，
>     则pg_basebackup就会自动创建这个目录，如果目录存在，但目录不为空，则会导致pg_basebackup执行失败。
>       -F, --format=p|t       output format (plain (default), tar)
>             指定输出格式：p原样输出，即把主数据库中的各个数据文件，配置文件、目录结构都完全一样的写到备份目录；
>                                      t 把输出的备份文件打包到一个tar文件中。
>       -r, --max-rate=RATE    maximum transfer rate to transfer data directory
>                              (in kB/s, or use suffix "k" or "M")
>       -R, --write-recovery-conf
>                              write recovery.conf after backup
>                生成recovery.conf文件
>       -T, --tablespace-mapping=OLDDIR=NEWDIR
>                              relocate tablespace in OLDDIR to NEWDIR
>       -x, --xlog             include required WAL files in backup (fetch mode)
>                备份时会把备份中产生的xlog文件也自动备份出来，这样才能在恢复数据库时，应用这些xlog文件把数据库推到一个一致点，
>     然后真正打开这个备份的数据库，这个选项与 -X fetch是完全一样的。
>     使用这个选项，需要设置wal_keep_segments参数，以保证在备份过程中，需要的WAL日志文件不会被覆盖。
>     
>       -X, --xlog-method=fetch|stream
>                              include required WAL files with specified method
>           --xlogdir=XLOGDIR  location for the transaction log directory
>       -z, --gzip             compress tar output 
>             使用gzip压缩，仅能能与tar输出模式配合使用。
>       -Z, --compress=0-9     compress tar output with given compression level
>               指定压缩级别
>     
>     General options:
>       -c, --checkpoint=fast|spread
>                              set fast or spread checkpointing
>                设置checkpoint的模式。
>       -l, --label=LABEL      set backup label
>                设置备份标识，
>       -P, --progress         show progress information
>                在备份过程中实时打印备份进度
>       -v, --verbose          output verbose messages
>                  详细模式，使用了-P后，还会打印出正在备份的具体文件的信息。
>       -V, --version          output version information, then exit
>       -?, --help             show this help, then exit
>     
>     Connection options:
>       -d, --dbname=CONNSTR   connection string
>       -h, --host=HOSTNAME    database server host or socket directory
>       -p, --port=PORT        database server port number
>       -s, --status-interval=INTERVAL
>                              time between status packets sent to server (in seconds)
>       -U, --username=NAME    connect as specified database user
>       -w, --no-password      never prompt for password
>       -W, --password         force password prompt (should happen automatically)
>     
>     Report bugs to <pgsql-bugs@postgresql.org>.
> 
>

8、在主数据库中修改数据，在从数据库中可以看到变化，表示同步成功。

9、查看data/log中的日志

2020-03-03 15:48:22.453 UTC [27] LOG:  database system was shut down in recovery at 2020-03-03 15:48:21 UTC
    2020-03-03 15:48:22.453 UTC [27] LOG:  entering standby mode
    2020-03-03 15:48:22.455 UTC [27] LOG:  redo starts at 0/5000060
    2020-03-03 15:48:22.455 UTC [27] LOG:  consistent recovery state reached at 0/5000148
    2020-03-03 15:48:22.455 UTC [27] LOG:  invalid record length at 0/5000148: wanted 24, got 0
    2020-03-03 15:48:22.456 UTC [1] LOG:  database system is ready to accept read only connections
    2020-03-03 15:48:22.462 UTC [31] LOG:  started streaming WAL from primary at 0/5000000 on timeline 1

参考资料：

[Postgresql12 recovery.conf 并入postgresql.conf以及主备切换函数介绍][Postgresql12 recovery.conf _postgresql.conf]

[PostgreSQL的HA解决方案-1主从和备份（master/slave and backup）][PostgreSQL_HA_-1_master_slave and backup]

[Postgresql数据库主从备份教程][Postgresql]

[pg\_basebackup 命令行参数][pg_basebackup]

[hub.docker.com_PostgreSQL12]: https://hub.docker.com/_/postgres
[Postgresql12 recovery.conf _postgresql.conf]: https://blog.csdn.net/dazuiba008/article/details/103769890?ops_request_misc=%7B%22request_id%22%3A%22158324937319724845032288%22%2C%22scm%22%3A%2220140713.130056874..%22%7D&request_id=158324937319724845032288&biz_id=0&utm_source=distribute.pc_search_result.none-task
[PostgreSQL_HA_-1_master_slave and backup]: https://blog.csdn.net/a7892088/article/details/102332891?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
[Postgresql]: https://blog.csdn.net/skycto_com/article/details/88976748?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
[pg_basebackup]: https://blog.csdn.net/feixiangtianshi/article/details/49152691