Using BUCARDO5 to implement PostgreSQL primary database replication _ database Other

The next generation asynchronous multiple primary database replication System Bucardo 5 was released. This version removes the limit of two database sources in the old version, allowing for more source databases (that is, the primary database) and more target databases (that is, backing up the database). Bucardo can also be replicated to other types of target databases, including MySQL, mariadb, Oracle, SQLite, MongoDB, and Redis. Bucardo has been completely rewritten, and this version is more powerful and more efficient than the previous version of Bucardo 4. You can access the Bucardo wiki to find the latest version of Bucardo.

This article gives a quick introduction to Bucardo. Future blog posts will introduce the powerful features of Bucardo, and now we'll explain how to simply implement multiple-master database replication.

For demonstration purposes, I used the fast-creating, free-use server that Amazon Web Services (AWS) provides, the basic T1.micro server running Amazon Linux. If you continue with the prompts, it will be free and simple to create a server instance for you. Once the instance is created successfully, we can use the Ec2-user account to login to the server via the SSH protocol, and then we can start installing PostgreSQL and Bucardo.

# Always A good idea:
$ sudo yum update
# This also installs PostgreSQL packages:
$ sudo yum install pos Tgresql-plperl
# Create A new Postgres cluster:
$ initdb btest

At this point, we still can't expect oh you a PostgreSQL, because this release version of the socket communication directory using/VAR/RUN/POSTGRESQL and/tmp. Once we have adjusted the permissions on the first directory, we can start PostgreSQL, and then create the first Test database:

$ sudo chmod 777/var/run/postgresql
$ pg_ctl-d btest-l logfile start
$ createdb shake1

Then we can replicate the database! To get the sample data, I used the open Source Shakespeare project. It has a small, arbitrary, and simple database schema that is easy to load. This small project on GitHub contains a ready-made PostgreSQL database schema that we can now load into a new database:

$ sudo yum install git
$ git clone-q https://github.com/catherinedevlin/opensourceshakespeare.git
$ psql shake1 -q-f Opensourceshakespeare/shakespeare.sql
# can safely ignore the ' role does not exist ' errors

We intend to create copies of this database that can be used as other data sources. In other words, these servers have the same data and can be written to. Implementing these is very simple:

$ createdb shake2-t shake1
$ createdb shake3-t shake1

Bucardo need to install some dependency packs. If you install a different operating system, then you may have to install a different dependency pack: The following is the dependency package that Amazon Linux needs to install when I write this article. (If you're lucky, your release package may already contain bucardo, in which case, the following steps don't need to be executed, you just run "Yum install Bucard"-but make sure you're using version 5 or better version!) (View via Yum info Bucardo))

$ sudo yum install perl-extutils-makemaker perl-dbd-pg \
> Perl-encode-locale perl-sys-syslog perl-boolean \
> Perl-time-hires perl-test-simple perl-pod-parser
$ sudo yum install cpan
$ echo y | cpan dbix::safe

Perl module Dbix::safe is not included in the Yum software repository of this system, so we need to install this module through CPAN. Once all of the above dependencies are installed successfully, we are ready to install Bucardo. We will obtain the official compression package, verify, unpack, and then install:

$ WGET-NV http://bucardo.org/Bucardo.tar.gz
$ wget-nv http://bucardo.org/Bucardo.tar.gz.asc
$ gpg-q-- Keyserver pgp.mit.edu--recv-key 14964ac8
$ gpg--verify Bucardo.tar.gz.asc
$ tar xfz Bucardo.tar.gz $ ln-s Bucar do-5.0.0 bucardo
$ cd bucardo
$ perl makefile.pl
$ make
$ sudo make install

We make some minor adjustments to the Bucardorc file (the file that sets some global information). Then run "Bucardo Install", which creates the Bucardo primary database that contains the information required to bucardo the service process:

$ mkdir pid
$ echo-e "piddir=pid\nlogdest=." > Bucardorc $ bucardo Install--batch--quiet creating
su Peruser ' Bucardo '

Now that the Bucardo is installed, the next step is to copy it. At this point, we have three databases that we can replicate with each other. Below we use only two commands to realize that three databases replicate to each other:

 Bucardo Add dbs s1,s2,s3 dbname=shake1,shake2,shake3
Added Databases "S1", "S2", "S3"
$ bucardo Add Sync bard dbs=s 1:source,s2:source,s3:source tables=all
Added sync "bard"
Created a new relgroup named "Bard"
Created a new D Bgroup named "Bard" Added table Public.chapter "Added table"
 public.character "
 Added table" Public.character_work "
 Added table" public.paragraph "
 Added table" Public.wordform "
 Added table" Public.work "

First, we tell Bucardo how to connect to three databases, we tell Bucardo the name of the database, and then bucardo the three databases as (S1,S2,S3). You can also specify ports and hosts, but in this case the default port is 5432 and no host is required (using UNIX socket communication mechanisms).

The second command creates a named replication system whose sync name is called Bard. Bucardo need to know where to copy and how to replicate it, so we tell it to use three databases S1,s2 and S3. Each database can serve as the source database, so we have added this information to them. Finally we need to know what to replicate. In this case, we need to replicate all the tables (or, more precisely, all the databases that have a primary key or a unique index). Note: Bucardo always put the database and table in a named group-in this case we just hard-code it to 10, but usually this value is the length of the table View controller array. In the example now, all of this is done automatically, Dbgroup and Relgroup are named after the sync.

Let's verify that the copy is running, that is, check to see if the update line is replicated to all the databases included in sync:

$ bucardo Start
$ psql shake1-c \
> "Update character Set speechcount=123 where charname= ' Hamlet '"
update  1
$ for i in {1,2,3}; does psql shake$i-tc "select \
> Current_database (), speechcount from character \
> where charname= ' Hamlet '; Done | grep s
 shake1    |   123
 Shake2    |   123
 Shake3    |   123

We can also view the Bucardo log file "Log.bucardo" to see if there are any replication operations:

$ tail-2 Log.bucardo
(25181) KID (bard) Delta count for S1.public. " Character ": 1
(25181) KID (bard) totals:deletes=2 inserts=2

There are two delete and two insert commands, because updating a row means that the delete is first run on the other two databases before the insert is run (technically, copy). Let's take a look at how Bucardo handles conflicts. We will update the same row on all the servers, which will create a conflict:

$ for I in {1,2,3}; Do psql SHAKE$I-TC \
> "Update character set speechcount= $i $i$i \
> where charname= ' Hamlet '"; Done
UPDA TE 1
Update 1
Update 1

Viewing the log indicates that there is a real conflict, and that the conflict is resolved well. The default conflict resolution indicates that the last updated database is the winner, and now all three databases have the same row as the last update database.

$ tail Log.bucardo
(25181) KID (bard) Delta count for S1.public. " Character ": 1
(25181) KID (bard) Delta count for S2.public." Character ": 1
(25181) KID (bard) Delta count for S3.public." Character ": 1
(25181) KID (bard) Conflicts for public." Character ": 1
(25181) KID (bard) Conflicts have been resolved
(25181) KID (bard) totals:deletes=2 inserts=2 con  Flicts=1
 
$ for i in {1,2,3}; does psql shake$i-tc \
> select Current_database (), Speechcount \
> from Character where charname= ' Hamlet '; Done | grep s
 shake1    |   333
 shake2    |   333
 shake3    |   333

When we developed this example, Bucardo sometimes ran very fast, so no conflicts occurred. That is, because the update is performed sequentially. So before the next update, there is a time window that allows Bucardo to complete the updated replication. In addition, the "Pause Sync" feature is also very handy, as long as you need to temporarily stop running sync, run the following command:

$ bucardo Pause Bard
syncs Paused:bard
$ psql shake1-c "update character set speechcount=1234 where Charname= ' Ha Mlet ' "
Update 1
$ psql shake2-c" update character set speechcount=4321 where charname= ' Hamlet ' "
update 1
   
    $ Bucardo Resume Bard
syncs Resumed:bard
 
$ tail Log.bucardo
(27344) KID (bard) Delta count for S1.public. " Character ": 1
(27344) KID (bard) Delta count for S2.public." Character ": 1
(27344) KID (bard) Conflicts for public." Character ": 1
(27344) KID (bard) Conflicts have been resolved
(27344) KID (bard) totals:deletes=2 inserts=2 con Flicts=1

   

Bucardo 5 is a lot more powerful than the demo we have here. In future blog posts, we'll include other features that it can do from replication to PostgreSQL systems such as Oracle, MySQL, or MongoDB to the use of custom conflict solutions. and the conversion of running data when replicating. If you have any questions, please explain in the comments below, or write a text message to the Bucardo mailing list bucardo-general@bucardo.org.

For so many years, without a lot of people contributing code, making holes, testing bucardo, and asking questions (or answering!) Major issues, it is impossible to have this significant release of the release. Looking at the changes file, you can see a list of the contributors. Thank you all, especially Jon Jensen, who opened the project a long time ago.