PostgreSQL - Asynchronous Replication + Pooling + Failover

The easy-to-understand option for asynchronous master-slave replication based on Postgresql 9.1

For the first time, the challenge was to single-handedly implement full-fledged replication and for the first time a mini-manual was written, which I want to present here.

For the Master Slave replication system, a combination was used.

• PostgreSQL 9.1 (DB) +
• Bucardo 4.5 (replicator) +
• PgPool-II (puller and filer)

Bucardo

Asynchronous Postgres replication system written in Perl5.
')
Convenient built-in postgres and easy, reflected in the database, setting.

Creates its own database, in which replicated servers, databases, tables, enclosed in sheets (lists) are written.
The communication type is Pushdelta (Trigger. One way master-slave).
Change of structure does not support. Works both ways, i.e. in the case of a temporary shutdown of the master, when it is restored, it will automatically “catch up” with the slave.

A rough plan for installing bucardo on the Wizard:

sudo aptitude install bucardo # + install Perl mods (DBI, DBD::Pg, DBIx::Safe) sudo bucardo_ctl install # connection settings 

Next, manually create the management database bucardo, fill it with bucardo.schema (by default it is attached to 8.4 and in older versions there is a misfire with auto-creation of the management database)

  sudo bucardo_ctl add db bucardo_dbname name=master_dbname #  - # +      bucardo.db (   ) sudo bucardo_ctl add db bucardo_dbname name=slave_dbname #  - sudo bucardo_ctl add table tbl_name db=bucardo_dbname herd=source_name #     (herd) sudo bucardo_ctl add sync sync_name type=pushdelta source=herd_name targetdb=slave_dbname #     sudo bucardo_ctl start #   

I installed it on the slave, but did not fill it (since the slave was alone and there was no place to go from it anyway)

PgPool-II

It has ample opportunities, suffers from a lack of manuals. It supports parallel queries, load balancing, distribution of connections to the database by pools, as well as FailOver, i.e. automatic switch from master to slave and back in cases of problems with connections.

On debian it is placed from the repository, it does not depend on the postgres version.

  sudo aptitude install pgpool2 

Configs stored in / etc / pgpool2 /

• pgpool.conf - Basic settings (about them below)
• pcp.conf - System - you can not touch
• pool_hba.conf - Configure connection access. You can use your own, you can - postgresovy. Better postgresovy

I used PgPool as a puller, load balancer and, most importantly, a filer.

An example of setting /etc/pgpool2/pgpool.conf (partially) For the Wizard:

  listen_addresses = '*' port = 9999 socket_dir = '/var/run/postgresql' pcp_port = 9898 pcp_socket_dir = '/var/run/postgresql' backend_hostname0 = master_server backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/master_data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = slave_server backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/slave_data' backend_flag1 = 'ALLOW_TO_FAILOVER' connection_cache = on replication_mode = off load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' parallel_mode = on pgpool2_hostname = '' system_db_hostname = master_server system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' failover_command = '/etc/pgpool2/failover.sh %d %P %H %R' recovery_user = 'postgres' recovery_password = '' recovery_1st_stage_command = '/etc/pgpool2/recovery_1st_stage.sh' recovery_2nd_stage_command = '' recovery_timeout = 90 client_idle_limit_in_recovery = 0 

Also in / etc / pgpool2 /, the following scripts are created that are specified in the config file:

'' failover.sh '' - Actually a script that executes if the wizard crashes

  #!/bin/bash -x FALLING_NODE=$1 # %d OLDPRIMARY_NODE=$2 # %P NEW_PRIMARY=$3 # %H PGDATA=$4 # %R if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then if [ $UID -eq 0 ] then su postgres -c "ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger" else ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger fi exit 0; fi; exit 0; 

'' recovery_1st_stage.sh '' - Script with a talking name

  #!/bin/bash -x PGDATA=$1 REMOTE_HOST=$2 REMOTE_PGDATA=$3 PORT=5432 PGHOME=/home/yugo-n/pgsql-9.2.1 ARCH=$PGHOME/data/arch rm -rf $ARCH/* ssh -T postgres@$REMOTE_HOST " LD_LIBRARY_PATH=$PGHOME/lib:LD_LIBRARH_PATH; rm -rf $REMOTE_PGDATA $PGHOME/bin/pg_basebackup -h $HOSTNAME -U r_user -D $REMOTE_PGDATA -x -c fast rm $REMOTE_PGDATA/trigger" ssh -T postgres@$REMOTE_HOST "rm -rf $ARCH/*" ssh -T postgres@$REMOTE_HOST "mkdir -p $REMOTE_PGDATA/pg_xlog/archive_status" ssh -T postgres@$REMOTE_HOST " cd $REMOTE_PGDATA; cp postgresql.conf postgresql.conf.bak; sed -e 's/#*hot_standby = off/hot_standby = on/' postgresql.conf.bak > postgresql.conf; rm -f postgresql.conf.bak; cat > recovery.conf << EOT standby_mode = 'on' primary_conninfo = 'host="$HOSTNAME" port=$PORT user=r_user' restore_command = 'scp $HOSTNAME:$ARCH/%f %p' trigger_file = '$PGDATA/trigger' EOT " 

An important point! It is necessary that all files / folders used by pgpool are with owner postgres :: postgres

For Slave, everything is almost the same, only with master_slave_mode = off and failover_command = ''

PgPool is launched by the simplest console

 sudo pgpool 

Thus, we obtain a master-mono-slave replication with load sharing and a filer.

Connection always goes only to the master via the pgPool port (here - 9999).
In normal operation, the master is written, the back-end is replicated to the slave, and the reading is done from that and the other.
In case of shutdown of the slave, when it resumes its operation, all the data of the idle time will be automatically replicated.
If the wizard is disabled, without breaking the user connection, pgpool redirects read and write completely to the replica, temporarily making it a “master”. When the master is picked up, he catches up with all the data from the slave and the reverse switching occurs, again without breaking the user connection.

There is a certain problem in the fact that it was not possible to create rights management in some way, so that with a live master the slave would only have RO rights, and when it fell it would switch to RW (and then restore it again), but since the external call goes only to address of the Master, the danger remains only in the playful hands of developers.

Hopefully, the article will be used by beginners in DBA.
And thank you all for your attention!