Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use Bucardo5 to achieve PostgreSQL main database replication. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

For demonstration purposes, I used a server provided by Amazon Web Services (AWS) that can be quickly created and freely used, that is, a basic t1.micro server running Amazon Linux. If you follow the prompts, it will create a server instance for you for free and simply. Once the instance is created, we can log in to the server using the ec2-user account through the SSH protocol, and we can start installing 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 point, we still can't expect you to be a PostgreSQL, because this release's socket communication directory uses / var/run/postgresql and / tmp. After adjusting the permissions of 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 do database replication! To get the sample data, I used the open source Shakespeare project. It has a small, freely available, simple database schema that is easy to load. This small project on github contains an off-the-shelf PostgreSQL database schema, which we will now be able to load into 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. It's very simple to do this:

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

Bucardo needs to install some dependency packages. If you install a different operating system release, you may want to install a different dependency package: here are the dependency packages that Amazon Linux needs to install when I write this article. (if you're lucky, your distribution may already include Bucardo, in which case the following steps don't need to be performed, just run "yum install bucard"-just make sure you're using version 5 or better! (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

The 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 the dependencies above have been successfully installed, we are ready to install Bucardo. We will get the official package, verify it, extract it, and then install it:

$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 minor adjustments to the bucardorc file (the file that sets some global information). Then run "bucardo install", which creates the master database of 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 Bucardo is installed, you're ready to copy. At this point, we have three databases that can replicate each other. Below, we can replicate the three databases with only two commands:

Bucardo add dbs S1 Added table S2 3 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 "Added table" public.character_work "Added table" public.paragraph "Added table" public.wordform "Added table" public.work "

In the first command, we tell Bucardo how to connect to the three databases, we tell Bucardo the name of the database, and Bucardo treats the three databases as (s 1, 2, 3). You can also specify the port and host, but in this example, the default port is 5432 and no host is required (using the Unix Socket communication mechanism).

The second command creates a named replication system whose sync name is bard. Bucardo needs to know where and how to replicate, so we tell it to use three databases, S1 and S3. Each database can be used as a source database, so we add this information to them. Finally, we need to know what to copy. In this case, we need to copy all the tables (or, more precisely, all databases with primary keys or unique indexes). Note: Bucardo always puts databases and tables in naming groups-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 current example, it all happens automatically, and dbgroup and relgroup are both named after sync.

Let's verify that replication is running, that is, check that the update rows are replicated to all the databases contained in sync:

$bucardo start$ psql shake1-c\ > "update character set speechcount=123 where charname='Hamlet'" UPDATE 1$ for i in {1 grep 2 shake3 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 look at Bucardo's log file "log.bucardo" to see if there is a copy operation:

$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 delete is running first on the other two databases before running insert (technically adopted COPY). Let's take a look at how Bucardo handles conflicts. We will update the same line on all servers, resulting in conflicts:

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

Checking the log shows that there is indeed a conflict, and the conflict is well resolved. The default conflict resolution indicates that the last updated database is the winner, and all three databases now 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 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 developed this example, Bucardo sometimes ran very fast, so there were no conflicts. That is, because updates are performed sequentially. So before the next update, there is a time window for Bucardo to finish copying the update. In addition, the "pause sync" function is also 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 a lot more features than we demonstrate here. Future blog posts will include other features it can do, from copying to non-PostgreSQL systems such as Oracle, Mysql, or MongoDB, to using custom conflict resolution. And the conversion of the running data during replication. If you have any questions, please explain them in the comments below, or write a text message to Bucardo mailing list bucardo-general@bucardo.org.

The above is the editor for you to share how to use Bucardo5 to achieve PostgreSQL main database replication, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.