Use Bucardo5 to implement master database replication of PostgreSQL, bucardo5postgresql

Use Bucardo5 to implement master database replication of PostgreSQL, bucardo5postgresql

The next generation of asynchronous primary database replication system Bucardo 5 is released. This version removes the restrictions on two database sources in the old version, allowing more source databases (Primary databases) and more target databases (Backup databases ). Bucardo can also be copied to other types of target databases, including MySQL, MariaDB, Oracle, SQLite, MongoDB, and Redis. Bucardo has been completely rewritten. This version is more powerful and more efficient than the previous version of Bucardo 4. You can visit the Bucardo wiki to find the latest Bucardo version.

This article quickly introduces Bucardo. In future blog posts, we will introduce the powerful features of Bucardo. Now we will introduce how to simply implement multi-master database replication.

For ease of demonstration, I used a server that can be quickly created and used by Amazon Web Service (AWS), that is, a basic t1.micro server running Amazon Linux. If you continue as prompted, it will create a server instance for you for free and simply. Once the instance is created successfully, we can use the ec2-user account to log on to the server via SSH protocol, then we can start to install PostgreSQL and Bucardo.
 

# Always a good idea:$ sudo yum update# This also installs other postgresql packages:$ sudo yum install postgresql-plperl# Create a new Postgres cluster:$ initdb btest

At this time, we still cannot expect your PostgreSQL, because the socket communication directory of this release version uses/var/run/postgresql and/tmp. After adjusting the permissions of the first directory, we can start PostgreSQL and create the first test database:
 

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

Next we can copy the database! To get the sample data, I used the open-source Shakespeare project. It has a small, usable, and simple database mode that is easy to load. This small project on github contains a ready-made PostgreSQL database model. Now we can load it to the new database:
 

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

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

$ createdb shake2 -T shake1$ createdb shake3 -T shake1

Some Dependent packages need to be installed in Bucardo. If the operating system you install is released differently, you may have to install different dependent packages: The following is the dependency package that Amazon Linux needs to install when I write this article. (If you are lucky, your release package may already contain Bucardo. In this case, the following steps are not required, you only need to run "yum install bucard".-but make sure you are using version 5 or a better version! (View through 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

The yum software warehouse in this system does not contain the Perl module DBIx: Safe, so we need to install this module through CPAN. Once all the dependencies above are successfully installed, we are ready to install Bucardo. We will obtain the official compressed package, verify and decompress it, 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 Bucardo-5.0.0 bucardo$ cd bucardo$ perl Makefile.PL$ make$ sudo make install

We make some small adjustments to the bucardorc file (the file that sets some global information. Run the "bucardo install" command to create the primary database of the bucardo, which contains the information required by the Bucardo service process:
 

$ mkdir pid$ echo -e "piddir=pid\nlogdest=." > .bucardorc$ bucardo install --batch --quietCreating superuser 'bucardo'

Now that you have installed Bucardo, you are ready to copy it. At this time, we have three databases that can be copied to each other. Below we can use only two commands to replicate the three databases to each other:
 

 bucardo add dbs s1,s2,s3 dbname=shake1,shake2,shake3Added databases "s1","s2","s3"$ bucardo add sync bard dbs=s1:source,s2:source,s3:source tables=allAdded sync "bard"Created a new relgroup named "bard"Created a new dbgroup 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"

The First Command tells the Bucardo how to connect to the three databases, tells the Bucardo database name, and then the Bucardo regards the three databases as (s1, s2, s3 ). You can also specify the port and host, but in this example, the default port is 5432, and the host is not required (the Unix Socket communication mechanism is used ).

The second command creates a named replication system with the sync name bard. Bucardo needs to know where to copy data and how to copy data, so we tell it to use three databases s1, s2, and s3. Every database can be used as the source database, so we add such information to them. Finally, we need to know what to copy. In this example, we need to copy all tables (or more precisely, copy all databases with primary keys or unique indexes ). Note: Bucardo always places the database and table in the name group. In this example, the database and table are hard encoded as 10. However, the value is usually the length of the table View Controller array. In this example, all these operations are performed automatically. Both dbgroup and relgroup are named after sync.

Let's verify whether the replication is running, that is, check whether the update row is copied to all databases contained in sync:
 

$ bucardo start$ psql shake1 -c \> "update character set speechcount=123 where charname='Hamlet'"UPDATE 1$ for i in {1,2,3}; do 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 conflicts=0

There are two delete and two insert commands above, because updating a row means that the first operation on the other two databases is delete, and then executing insert (technically used COPY ). Next, let's take a look at how Bucardo handles the conflict. We will update the same row on all servers so that there will be a conflict:
 

$ for i in {1,2,3}; do psql shake$i -tc \> "update character set speechcount=$i$i$i \> where charname='Hamlet'"; doneUPDATE 1UPDATE 1UPDATE 1

Check the log to check whether a conflict exists and the conflict is well resolved. The default conflict solution indicates that the last updated database is the winner, and now all three databases have the same rows as the last updated 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 conflicts=1 $ for i in {1,2,3}; do psql shake$i -tc \> "select current_database(), speechcount \> from character where charname='Hamlet'"; done | grep s shake1    |   333 shake2    |   333 shake3    |   333

When we develop this example, Bucardo sometimes runs very fast, so there is no conflict. That is to say, the update is executed in sequence. Therefore, before the next update, there is a time window for the Bucardo to complete the replication of the update. In addition, the "Pause sync" function is very convenient, as long as you need to temporarily stop running sync, run the following command:
 

$ bucardo pause bardSyncs paused: bard$ psql shake1 -c "update character set speechcount=1234 where charname='Hamlet'"UPDATE 1$ psql shake2 -c "update character set speechcount=4321 where charname='Hamlet'"UPDATE 1$ bucardo resume bardSyncs 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 conflicts=1

Bucardo 5 has many more features than we have demonstrated here. In future blog posts, we will include other functions that can be completed, from copying to non-PostgreSQL systems such as Oracle, Mysql, and MongoDB to using custom conflict solutions. And convert the running data during replication. If you have any questions, please explain in the comments below, or write a text message to the Bucardo email list bucardo-general@bucardo.org.

For so many years, if not many people contribute code, raise vulnerabilities, test Bucardo, and ask (or answer !) It is impossible to release this major version because of a major issue. View the Changes file to view the list of some contributors. Thank you all. Thank you very much, Jon Jenner. He started this project a long time ago.