PostgreSQL on ECS SLA 流复制备库+秒级快照+PITR+自动清理

点击有惊喜

标签

PostgreSQL , ECS , 阿里云 , 部署 , 物理镜像 , 流复制 , 快照备份 , 备份验证 , 自动清理

---

背景

介绍在阿里云ECS环境中，实现一个非常简单，但是可用性和可靠性满足一般企业要求的PostgreSQL环境。

包括：

1、自动启动数据库

2、包括一个物理流复制备库

3、包括自动的秒级快照备份

4、包括自动备份集有效性验证

5、包括自动清理N天以前的备份集、归档文件

6、监控请自建

部署环境介绍

1、ECS

111.111.111.199 (主)        111.111.111.223 (备)    

2、云盘

400 GB    

3、软件

OS: CentOS 7.4 x64        PostgreSQL: 10.1        ZFS: 0.7.3    

部署流程

双机

1、安装软件

yum -y install coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2    

2、修改内核配置

vi /etc/sysctl.conf        # add by digoal.zhou      fs.aio-max-nr = 1048576      fs.file-max = 76724600              kernel.sem = 4096 2147483647 2147483646 512000            kernel.shmall = 107374182            kernel.shmmax = 274877906944         kernel.shmmni = 819200                     net.core.netdev_max_backlog = 10000      net.core.rmem_default = 262144             # The default setting of the socket receive buffer in bytes.      net.core.rmem_max = 4194304                # The maximum receive socket buffer size in bytes      net.core.wmem_default = 262144             # The default setting (in bytes) of the socket send buffer.      net.core.wmem_max = 4194304                # The maximum send socket buffer size in bytes.      net.core.somaxconn = 4096      net.ipv4.tcp_max_syn_backlog = 4096      net.ipv4.tcp_keepalive_intvl = 20      net.ipv4.tcp_keepalive_probes = 3      net.ipv4.tcp_keepalive_time = 60      net.ipv4.tcp_mem = 8388608 12582912 16777216      net.ipv4.tcp_fin_timeout = 5      net.ipv4.tcp_synack_retries = 2      net.ipv4.tcp_syncookies = 1          net.ipv4.tcp_timestamps = 1          net.ipv4.tcp_tw_recycle = 0          net.ipv4.tcp_tw_reuse = 1            net.ipv4.tcp_max_tw_buckets = 262144      net.ipv4.tcp_rmem = 8192 87380 16777216      net.ipv4.tcp_wmem = 8192 65536 16777216            net.nf_conntrack_max = 1200000      net.netfilter.nf_conntrack_max = 1200000            vm.dirty_background_bytes = 204800000                   vm.dirty_expire_centisecs = 3000                   vm.dirty_ratio = 90                                      vm.dirty_writeback_centisecs = 50                        vm.swappiness = 0            vm.mmap_min_addr = 65536      vm.overcommit_memory = 0                 vm.overcommit_ratio = 90           vm.swappiness = 0                  vm.zone_reclaim_mode = 0           net.ipv4.ip_local_port_range = 40000 65535          fs.nr_open=20480000      

3、修改资源限制

# vi /etc/security/limits.conf        * soft    nofile  1024000      * hard    nofile  1024000      * soft    nproc   unlimited      * hard    nproc   unlimited      * soft    core    unlimited      * hard    core    unlimited      * soft    memlock unlimited      * hard    memlock unlimited     

4、关闭透明大页

chmod +x /etc/rc.d/rc.local      vi /etc/rc.local            # 追加             if test -f /sys/kernel/mm/transparent_hugepage/enabled; then         echo never > /sys/kernel/mm/transparent_hugepage/enabled      fi      

5、分配磁盘

parted -s /dev/vdb mklabel gpt    parted -s /dev/vdb mkpart primary 1MiB 100%    

主机

1、创建文件系统

mkfs.ext4 /dev/vdb1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L data01    

2、加载文件系统

mkdir /data01          vi /etc/fstab            LABEL=data01 /data01     ext4        defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback    0 0            mount -a     

备机

1、安装ZFS

yum install -y http://download.zfsonlinux.org/epel/zfs-release.el7_4.noarch.rpm        yum install -y kernel-devel-3.10.0-693.2.2.el7.x86_64                 yum install -y zfs    

2、手动加载zfs模块

/usr/sbin/modprobe zfs    

3、创建zpool和zfs for 数据目录，归档目录

zpool create -o ashift=12 zp1 vdb1        zfs set compression=lz4 zp1        zfs set canmount=off zp1        zfs set atime=off zp1                zfs create -o mountpoint=/data01 zp1/data01         zfs create -o mountpoint=/pg_arch zp1/pg_arch        

4、自动加载zpool

vi /etc/rc.local        /usr/sbin/modprobe zfs    /usr/sbin/zpool import zp1    

双机

1、安装PostgreSQL与EPEL软件 yum 仓库

yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm        yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm    

2、安装数据库软件与周边软件

yum install -y  postgresql10*  postgis24_10*   pgrouting_10*   osm2pgrouting_10*   plpgsql_check_10*   pgbadger pg_top10* hypopg_10*  citus_10*  cstore_fdw_10*   pg_pathman10* orafce10* pgfincore10* pgbson10*  pgmemcache-10* pldebugger10* plv8_10*  geoip10*  ip4r10*  pg_repack10*  pgsphere10*  plr10*    

3、修改环境变量

su - postgres            vi ~/.bash_profile            export PS1="$USER@`/bin/hostname -s`-> "      export PGPORT=3433      export PGDATA=/data01/pg_root$PGPORT      export LANG=en_US.utf8      export PGHOME=/usr/pgsql-10      export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH      export DATE=`date +"%Y%m%d%H%M"`      export PATH=$PGHOME/bin:$PATH:.      export MANPATH=$PGHOME/share/man:$MANPATH      export PGHOST=$PGDATA      export PGUSER=postgres      export PGDATABASE=postgres      alias rm='rm -i'      alias ll='ls -lh'      unalias vi      

主机

1、创建数据目录，修改权限

cd /data01    mkdir pg_root3433    mkdir -p pg_arch/pg3433        chown postgres:postgres /data01/*    ln -s /data01/pg_arch /pg_arch    

备机

1、创建数据目录，修改权限

cd /data01    mkdir pg_root3433    chown postgres:postgres /data01/*        cd /pg_arch    mkdir pg3433    chown postgres:postgres /pg_arch/pg3433    

主机

1、初始化数据库集群

su - postgres    initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8     

2、修改数据库参数配置

cd $PGDATA        vi postgresql.auto.conf        listen_addresses = '0.0.0.0'      port = 3433  # 监听端口      max_connections = 2000  # 最大允许的连接数      superuser_reserved_connections = 10      unix_socket_directories = '.'      unix_socket_permissions = 0700      tcp_keepalives_idle = 60      tcp_keepalives_interval = 60      tcp_keepalives_count = 10      shared_buffers = 16GB                  # 共享内存，建议设置为系统内存的1/4  .      maintenance_work_mem = 512MB           # 系统内存超过32G时，建议设置为1GB。超过64GB时，建议设置为2GB。超过128GB时，建议设置为4GB。      work_mem = 64MB                        # 1/4 主机内存 / 256 (假设256个并发同时使用work_mem)    wal_buffers = 128MB                    # min( 2047MB, shared_buffers/32 )     dynamic_shared_memory_type = posix      vacuum_cost_delay = 0      bgwriter_delay = 10ms      bgwriter_lru_maxpages = 500      bgwriter_lru_multiplier = 5.0      effective_io_concurrency = 0      max_worker_processes = 128                     max_parallel_workers_per_gather = 16        # 建议设置为主机CPU核数的一半。      max_parallel_workers = 16                   # 看业务AP和TP的比例，以及AP TP时间交错分配。实际情况调整。例如 主机CPU cores-2    wal_level = replica      fsync = on      synchronous_commit = off      full_page_writes = on                  # 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。    wal_writer_delay = 10ms      wal_writer_flush_after = 1MB      checkpoint_timeout = 30min      max_wal_size = 32GB                    # shared_buffers*2     min_wal_size = 8GB                     # max_wal_size/4     archive_mode = always      archive_command = '/bin/date'      hot_standby = on    max_wal_senders = 10      max_replication_slots = 10      wal_receiver_status_interval = 1s      max_logical_replication_workers = 4      max_sync_workers_per_subscription = 2      random_page_cost = 1.2      parallel_tuple_cost = 0.1      parallel_setup_cost = 1000.0      min_parallel_table_scan_size = 8MB      min_parallel_index_scan_size = 512kB      effective_cache_size = 32GB                 # 建议设置为主机内存的5/8。         log_destination = 'csvlog'      logging_collector = on      log_directory = 'log'      log_filename = 'postgresql-%a.log'      log_truncate_on_rotation = on      log_rotation_age = 1d      log_rotation_size = 0      log_min_duration_statement = 5s      log_checkpoints = on      log_connections = on                            # 如果是短连接，并且不需要审计连接日志的话，建议OFF。    log_disconnections = on                         # 如果是短连接，并且不需要审计连接日志的话，建议OFF。    log_error_verbosity = verbose      log_line_prefix = '%m [%p] '      log_lock_waits = on      log_statement = 'ddl'      log_timezone = 'PRC'      log_autovacuum_min_duration = 0       autovacuum_max_workers = 5      autovacuum_vacuum_scale_factor = 0.1      autovacuum_analyze_scale_factor = 0.05      autovacuum_freeze_max_age = 1000000000      autovacuum_multixact_freeze_max_age = 1200000000      autovacuum_vacuum_cost_delay = 0      statement_timeout = 0                                # 单位ms, s, min, h, d.  表示语句的超时时间，0表示不限制。      lock_timeout = 0                                     # 单位ms, s, min, h, d.  表示锁等待的超时时间，0表示不限制。      idle_in_transaction_session_timeout = 2h             # 单位ms, s, min, h, d.  表示空闲事务的超时时间，0表示不限制。      vacuum_freeze_min_age = 50000000      vacuum_freeze_table_age = 800000000      vacuum_multixact_freeze_min_age = 50000000      vacuum_multixact_freeze_table_age = 800000000      datestyle = 'iso, ymd'      timezone = 'PRC'      lc_messages = 'en_US.UTF8'      lc_monetary = 'en_US.UTF8'      lc_numeric = 'en_US.UTF8'      lc_time = 'en_US.UTF8'      default_text_search_config = 'pg_catalog.simple'      shared_preload_libraries='pg_stat_statements,pg_pathman'      

2、修改备库将用到的自动流复制参数文件

cp $PGHOME/share/recovery.conf.sample ./    mv recovery.conf.sample recovery.done        vi recovery.done        recovery_target_timeline = 'latest'    standby_mode = on    primary_conninfo = 'host=111.111.111.223 port=3433 user=rep password=xxxxxxxxxx'    restore_command = 'BASEDIR="/pg_arch/pg3433" ; find $BASEDIR -name %f -exec cp {} %p \\;'     

3、修改数据库认证权限访问控制ACL, pg_hba.conf

vi pg_hba.conf    host all all 0.0.0.0/0 md5    host replication rep 0.0.0.0/0 md5    

4、启动数据库

pg_ctl start    

5、创建流复制，数据库角色

psql -n        create role rep replication login encrypted password 'xxxxxxxxxx';    

备机

1、创建备库

su - postgres    pg_basebackup -D $PGDATA -F p -h 111.111.111.199 -p 3433 -U rep     

2、修改数据库配置

cd $PGDATA    vi postgresql.auto.conf        archive_command = 'DT="/pg_arch/pg3433/`date +%F`" ; test ! -d $DT && mkdir -p $DT ; test ! -f $DT/%f && cp %p $DT/%f'    

3、配置备库的自动同步参数文件

mv recovery.done recovery.conf        vi recovery.conf        primary_conninfo = 'host=111.111.111.199 port=3433 user=rep password=xxxxxxxxxx'    

5、修改数据目录权限

su - root    chmod 700 /data01/pg_root3433    

双机

1、系统启动时，自动启动数据库

vi /etc/rc.local            # 追加            su - postgres -c "pg_ctl start"      

2、重启ECS验证

reboot            su - postgres            psql            postgres=# show max_connections ;       max_connections       -----------------       2000      (1 row)      

备机

1、配置自动备份（快照备份）

su - root        mkdir script        vi /root/script/zfs_snap.sh            #!/bin/bash           date +%F%T    # 自动创建、清理快照    /sbin/zfs snapshot zp1/data01@`date +%Y%m%d`        /sbin/zfs destroy zp1/data01@`date +%Y%m%d -d '-10 day'`        /sbin/zfs list -t snapshot         # 自动清理归档    rm -rf /pg_arch/pg3433/`date +%F -d '-11 day'`        date +%F%T    

2、设置脚本权限

chmod 500 /root/script/zfs_snap.sh        

3、设置自动备份任务，每天备份一次

# crontab -e        1 8 * * * /root/script/zfs_snap.sh >> /root/zfs_snap.log 2>&1     

点击有惊喜