Shulou(Shulou.com)06/01 Report--

Preface

The early gitlab has begun to promote testing, and recently made an active and standby postgresql. Here is the plan and the holes encountered in the implementation process.

The installation of postgresql tools is not described here.

Basic information primary_ip: 192.168.10.2, standby_ip: 192.168.10.3, PGDATA: / opt/gitlab/postgresql/data, postgresql_version: (PostgreSQL) 9.6.8, PGCONF_DIR: $PGDATA

The configuration files involved in modification are:

Postgresql.conf-postgresql master configuration file pg_hba.conf-postgresql access rules file recovery.conf-postgresql standby library access master library configuration file considerations! 1. The versions of active and standby postgresql should be consistent! 2. Postgresql.conf configuration files need to be consistent! 3. After raising the right of the reserve library as the main library, remember not to start the original master library directly! Prepare the operation on primary 192.168.10.2 host operation

1. Prepare the main library for the reserve library and modify the configuration file

Cat postgresql.conf

Wal_level = hot_standby # minimal, replica, or logical max_wal_senders = 2 # max number of walsender processes hot_standby = on # "on" allows queries during recovery max_connections = 300 # (change requires restart) archive_mode = on restore_command =''

Cat pg_hba.conf

Host all all 127.0.0.1/32 trust host all all:: 1/128 trust host replication gitlab_replicator 192.168.10.3/32 trust

Cat recovery.done

Restore_command =''recovery_target_timeline =' latest' standby_mode = on primary_conninfo = 'host=192.168.10.3 port=5432 user=gitlab_replicator'

two。 Create an account for replication and grant replication permission

Postgres=#CREATE USER gitlab_replicator REPLICATION LOGIN

3. Basic backup prepares boot data for library

Postgres=#SELECT pg_start_backup (back_20180929); cd / opt/gitlab/postgresql & & tar zcf base_data.tar.gz data postgres=#SELECT pg_start_stop (); host operation on standby 192.168.10.3

1. Decompress the basic data

Upload the base_data.tar.gz created on the main library to the standby host and extract it to the data directory

Tar zxf base_data.tar.gz-C / opt/gitlab/postgresql/

two。 Modify the configuration file

Note: this part of the postgresql.conf file must be consistent with the configuration of the master library, otherwise errors may occur during the recovery of the master-slave switch

Cat postgresql.conf

Wal_level = hot_standby # minimal, replica, or logical max_wal_senders = 2 # max number of walsender processes hot_standby = on # "on" allows queries during recovery max_connections = 300 # (change requires restart) archive_mode = on restore_command =''

Cat pg_hba.conf

Host all all 127.0.0.1/32 trust host all all:: 1/128 trust host replication gitlab_replicator 192.168.10.2/32 trust

Cat recovery.conf

Restore_command =''recovery_target_timeline =' latest' standby_mode = on primary_conninfo = 'host=192.168.10.2 port=5432 user=gitlab_replicator'

3. Start the standby library, execute sql in the main library, and verify it in the standby library

Master-slave switching

The judgment of the master and backup database is based on whether the recovery.conf file currently exists.

When the standby library is promoted to the primary library, the recovery.conf file is automatically renamed to recovery.done. At the same time, the main database should be reduced to a standby database, and the backup method is to rename the recovery.done file.

Mv recover.done recovery.conf

In this way, the updated data promoted to the main database will be synchronized after dealing with the failure of the main database.

Here is a simple idea and script, assuming that there is no network failure between the master and standby, and there is no simultaneous master or standby.

Judge the status of the main database

1. When shut down

Determine whether the standby database is in archive recovery and execute to reduce the primary database to the standby database, upgrade the standby database to the primary database, and send an alarm for the rest of the status.

two。 When in production

Determine whether the standby database is in archive recovery, and send an alarm for the rest of the status.

3. When in archive recovery

Determine whether the standby database is in production, and send an alarm for the rest of the status.

4. When shut down in recovery

Send an alarm

Shell script #! / bin/bash PRIMARY_IP= "192.168.10.2" STANDBY_IP= "192.168.10.3" PGDATA= "/ DATA/postgresql/data" SYS_USER= "root" PG_USER= "postgresql" PGPREFIX= "/ opt/pgsql" pg_status () {ssh ${SYS_USER} @ $1 / "su-${PG_USER}-c'${ PGPREFIX} / bin/pg_controldata-D ${PGDATA} / | grep cluster' | awk-F:'{print\ $2}'| sed's / ^ [\ t] *\ | [\ t] * $/ /'"} # recover to primary recovery_primary () {ssh ${SYS_USER} @ $1 /" su-${PG_USER}-c'{PGPREFIX } / bin/pg_ctl promote-D ${PGDATA}'} # primary to recovery primary_recovery () {ssh ${SYS_USER} @ $1 / "su-${PG_USER}-c'cd ${PGDATA} & & mv recovery.done recovery.conf'"} send_mail () {echo "send SNS"} case "`pg_status ${PRIMARY_IP} `"in" shut down ") case" `pg_status ${STANDBY_IP}` "in" in archive recovery ") primary_recovery ${PRIMARY_IP} recovery_primary ${STANDBY_IP} ; "shut down in recovery" | "in production") send_mail;; esac "in production") case "`pg_status ${STANDBY_IP} `" in "shut down in recovery" | "shut down" | "in production") send_mail Esac echo "primary" "in archive recovery") case "`pg_status ${STANDBY_IP} `" in "shut down") primary_recovery ${STANDBY_IP} recovery_primary ${PRIMARY_IP} "shut down in recovery" | "in archive recovery") send_mail;; esac echo "recovery" "shut down in recovery") case "`pg_status ${STANDBY_IP} `" in "shut down in recovery" | "shut down" | "in archive recovery") send_mail Esac echo "recovery down";; esac error handling error 1FATAL: no pg_hba.conf entry for replication connection from host "192.168.1.2", user "standby", SSL off

You need to add the user to the pg_hba.conf file of 192.168.1.2 and configure the authentication method and password

Error 2FATAL: database system identifier differs between the primary and standbyDETAIL: The primary's identifier is 6589099331306617531, the standby's identifier is 6605061381709180314

This is due to the fact that the missing data is not fully synchronized when the original primary database is restored after the standby database has been promoted to the primary database.

Error 3FATAL: number of requested standby connections exceeds max_wal_senders (currently 0) FATAL: hot standby is not possible because max_connections = 100is a lower setting than on the master server (its value was 200) FATAL: hot standby is not possible because max_locks_per_transaction = 64 is a lower setting than on the master server (its value was 128)

This is because the number of secondary libraries exceeds the maximum number of connections allowed for the primary database.

The configuration here is 0

This question proposes that after upgrading the standby database to the primary database, the original primary database should be reduced to the standby database to synchronize data, so it is necessary to note that the configuration of this part of the master and backup should be consistent.

Postscript

Postgresql master and master synchronization needs to be realized by three-party middleware, and relevant information can be queried if necessary.

The references for this article are the official documents of postgresql.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.