keepalived实现postgresql主从切换

港控/mmm° 2022-01-26 02:21 551阅读 0赞

主 keepalived配置文件

[root@bgsbtsp0006-dqf:keepalived]# cat keepalived.conf 
    
    ! Configuration File for keepalived
    
    vrrp_script check_pg_alived {
            script "/etc/keepalived/scripts/check_pg.sh"
            interval 2
    }
    
    
    vrrp_instance VI_1 {
        state MASTER
        interface bond0
        virtual_router_id 22
        priority 100
        nopreempt
        advert_int 2
        authentication {
            auth_type PASS
            auth_pass abcdefgh
        }
    
    
        virtual_ipaddress {
            172.18.1.72/24
        }
       
        notify_master "/etc/keepalived/scripts/master.sh"
        notify_backup "/etc/keepalived/scripts/backup.sh"
        notify_fault "/etc/keepalived/scripts/fault.sh"
    
       track_script {
           check_pg_alived
       }
    
    }

从 keepalived配置文件

[root@bgsbtsp0006-dqf:keepalived]# cat keepalived.conf 
    
    ! Configuration File for keepalived
    
    vrrp_script check_pg_alived {
            script "/etc/keepalived/scripts/check_pg.sh"
            interval 2
    }
    
    
    vrrp_instance VI_1 {
        state BACKUP
        interface bond0
        virtual_router_id 22
        priority 80
        nopreempt
        advert_int 2
        authentication {
            auth_type PASS
            auth_pass abcdefgh
        }
    
    
        virtual_ipaddress {
            172.18.1.72/24
        }
       
        notify_master "/etc/keepalived/scripts/master.sh"
        notify_backup "/etc/keepalived/scripts/backup.sh"
        notify_fault "/etc/keepalived/scripts/fault.sh"
    
       track_script {
           check_pg_alived
       }
    
    }

check\_pg.sh文件

[root@bgsbtsp0006-dqf:scripts]# cat check_pg.sh 
    #!/bin/bash
    num=`ps -ef |grep postgresql|grep -v grep |wc -l`
    if [ $num -ge 1 ];then
       status=0
    else
       status=1
    fi
    exit $status

master.sh文件

[root@bgsbtsp0006-dqf:scripts]# cat master.sh 
    #!/bin/bash
    LOGFILE=/etc/keepalived/log/keepalived.log
    
    export PGDATABASE=postgres
    export PGPORT=5432
    export PGUSER=postgres
    export PGBIN=/opt/postgresql/bin
    LOGFILE=/etc/keepalived/log/keepalived.log
    
    
    
    
    SQL1='SELECT pg_is_in_recovery from pg_is_in_recovery();'
    
    db_role=`echo $SQL1  | ${PGBIN}/psql  -d $PGDATABASE -U $PGUSER -At -w`
    
    if [ $db_role == 't' ];then
       echo -e `date +"%F %T"` "the current database is standby DB! [切换至master角色] " >> $LOGFILE
       su - postgres <<EOF
       /opt/postgresql/bin/pg_ctl promote -D /opt/postgresql/data
    EOF
    
    else
       echo -e `date +"%F %T"` "the current database is master DB!" >> $LOGFILE
    fi

backup.sh文件

[root@bgsbtsp0006-dqf:scripts]# cat backup.sh 
    #!/bin/bash
    
    export PGDATABASE=postgres
    export PGPORT=5432
    export PGUSER=postgres
    export PGBIN=/opt/postgresql/bin
    LOGFILE=/etc/keepalived/log/keepalived.log
    
    
    SQL1='SELECT pg_is_in_recovery from pg_is_in_recovery();'
    
    db_role=`echo $SQL1  | ${PGBIN}/psql  -d $PGDATABASE -U $PGUSER -At -w`
    
    if [ $db_role == 't' ];then
       echo -e `date +"%F %T"` "the current database is standby DB!" >> $LOGFILE
    else
       echo -e `date +"%F %T"` "the current database is master DB!" >> $LOGFILE
    fi

fault.sh 文件

[root@bgsbtsp0006-dqf:scripts]# cat fault.sh 
    #!/bin/bash
    LOGFILE=/etc/keepalived/log/keepalived.log
    num=`ps -ef |grep postgresql|grep -v grep |wc -l`
    if [ $num -ge 1 ];then
      echo -e `date +"%F %T"` "主库未down机" >> $LOGFILE
    else
      echo -e `date +"%F %T"` "主库down机!" >> $LOGFILE
    fi

主postgresql.conf

[root@bgsbtsp0006-dqf:data]# cat postgresql.conf |grep -v "^#" |grep -v "^$"|grep -v $'^\t'
    listen_addresses = '*'		# what IP address(es) to listen on;
    port = 5432				# (change requires restart)
    max_connections = 500			# (change requires restart)
    superuser_reserved_connections = 10	# (change requires restart)
    shared_buffers = 128MB			# min 128kB
    dynamic_shared_memory_type = posix	# the default is the first option
    wal_level = logical			# minimal, replica, or logical
    full_page_writes = on			# recover from partial page writes
    wal_log_hints = on			# also do full page writes of non-critical updates
    archive_mode = on		# enables archiving; off, on, or always
    archive_command = '/bin/true'		# command to use to archive a logfile segment
    max_wal_senders = 10		# max number of walsender processes
    wal_keep_segments = 64		# in logfile segments, 16MB each; 0 disables
    hot_standby = on			# "on" allows queries during recovery
    log_destination = 'csvlog'		# Valid values are combinations of
    logging_collector = on	# Enable capturing of stderr and csvlog
    log_directory = 'pg_log'		# directory where log files are written,
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	# log file name pattern,
    log_rotation_age = 1d			# Automatic rotation of logfiles will
    log_rotation_size = 10MB		# Automatic rotation of logfiles will
    log_statement = 'all'			# none, ddl, mod, all
    log_timezone = 'PRC'
    datestyle = 'iso, mdy'
    timezone = 'PRC'
    lc_messages = 'en_US.UTF-8'			# locale for system error message
    lc_monetary = 'en_US.UTF-8'			# locale for monetary formatting
    lc_numeric = 'en_US.UTF-8'			# locale for number formatting
    lc_time = 'en_US.UTF-8'				# locale for time formatting
    default_text_search_config = 'pg_catalog.english'

从postgresql.conf

[root@bfd-yiz-1p23 /opt/postgresql/data]# cat postgresql.conf |grep -v "^#" |grep -v "^$"|grep -v $'^\t'
    listen_addresses = '*'		# what IP address(es) to listen on;
    port = 5432				# (change requires restart)
    max_connections = 500			# (change requires restart)
    superuser_reserved_connections = 10	# (change requires restart)
    shared_buffers = 128MB			# min 128kB
    dynamic_shared_memory_type = posix	# the default is the first option
    wal_level = logical			# minimal, replica, or logical
    full_page_writes = on			# recover from partial page writes
    wal_log_hints = on			# also do full page writes of non-critical updates
    archive_mode = on		# enables archiving; off, on, or always
    archive_command = '/bin/true'		# command to use to archive a logfile segment
    max_wal_senders = 10		# max number of walsender processes
    wal_keep_segments = 64		# in logfile segments, 16MB each; 0 disables
    hot_standby = on			# "on" allows queries during recovery
    log_destination = 'csvlog'		# Valid values are combinations of
    logging_collector = on	# Enable capturing of stderr and csvlog
    log_directory = 'pg_log'		# directory where log files are written,
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	# log file name pattern,
    log_rotation_age = 1d			# Automatic rotation of logfiles will
    log_rotation_size = 10MB		# Automatic rotation of logfiles will
    log_statement = 'all'			# none, ddl, mod, all
    log_timezone = 'PRC'
    datestyle = 'iso, mdy'
    timezone = 'PRC'
    lc_messages = 'en_US.UTF-8'			# locale for system error message
    lc_monetary = 'en_US.UTF-8'			# locale for monetary formatting
    lc_numeric = 'en_US.UTF-8'			# locale for number formatting
    lc_time = 'en_US.UTF-8'				# locale for time formatting
    default_text_search_config = 'pg_catalog.english'

免密钥操作，

pg\_hba文件配置

# TYPE  DATABASE        USER            ADDRESS                 METHOD
    
    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             0.0.0.0/0               trust
    # IPv6 local connections:
    host    all             all             ::1/128                 trust
    
    host    replication     repl            172.18.1.0/24           trust
    host    all             repl            172.18.1.0/24           trust
    
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                trust
    #host    replication     postgres        127.0.0.1/32            trust
    #host    replication     postgres        ::1/128                 trust

recovery.conf 文件

recovery_target_timeline = 'latest'
    standby_mode = on
    primary_conninfo = 'host=172.18.1.22 port=5432 user=repl password=baifendian'		# e.g. 'host=localhost port=5432'

结论是：keepalived得master节点挂了 也会执行切换。pg得主挂了 也切换。