It would seem that the bucardo_ctl installer script for version 4.4.0 has some problems. I spent a little time trying to fix the script, but it was too much of a pain.
I found a great write up by Richard Martin that describes getting bucardo running without bucardo_ctl. I simplified his procedure to replicate the three tables from the pgbench data that have primary keys. From there you should be able to adopt it for any database. You can use bucardo to replicate tables that do not have primary keys, but primary keys are generally a good thing to have.
I did modify the bucardo.schema file to remove the account creation stuff. The script tries to switch to the bucardo db as the bucardo user after it creates it and then can cause a problem with most default postgres installs that use ident auth.
Feel free to download my modified bucardo.schema. For this example, I have used the database names ‘db_left’ and ‘db_right’. They are both on localhost, so we have to worry less about getting across a network.
Be sure to use better passwords if you are setting this up for reals. Please do things like use ssl/ssh tunnels.
The first step is to create the databases and populate them with pgbench data. For ubuntu 9.10 with postgresql 8.4, pgbench is in /usr/lib/postgresql/8.4/bin/
createdb db_left
createdb db_right
pgbench -i db_left
pgbench -i db_right
/usr/lib/postgresql/8.4/bin/pgbench -i db_left
/usr/lib/postgresql/8.4/bin/pgbench -i db_right
Next, run the following script in psql or your postgresql client of choice.
create user bucardo SUPERUSER;
create database bucardo owner bucardo;
alter user bucardo with password 'bucardo';
\connect db_left;
create language plpgsql;
\connect db_right;
create language plpgsql;
\connect bucardo;
create language plpgsql;
create language plperlu;
\i mod_bucardo.schema
INSERT INTO db(name, dbname, dbhost, dbuser, dbpass)
VALUES ('left','db_left','127.0.0.1','bucardo', 'bucardo');
INSERT INTO db(name, dbname, dbhost, dbuser, dbpass)
VALUES ('right','db_right','127.0.0.1','bucardo', 'bucardo');
INSERT INTO dbgroup(name) VALUES ('remote_dbs');
INSERT INTO dbmap(db,dbgroup) VALUES ('left','remote_dbs');
INSERT INTO dbmap(db,dbgroup) VALUES ('right','remote_dbs');
INSERT INTO goat(db, schemaname, tablename, standard_conflict) VALUES('left', 'public', 'pgbench_accounts', 'source');
INSERT INTO goat(db, schemaname, tablename, standard_conflict) VALUES('left', 'public', 'pgbench_branches', 'source');
INSERT INTO goat(db, schemaname, tablename, standard_conflict) VALUES('left', 'public', 'pgbench_tellers', 'source');
INSERT INTO herd(name) VALUES ('HERD_NAME');
insert into herdmap(herd,goat) select 'HERD_NAME', id from goat;
INSERT INTO sync(name,source,targetdb,synctype,checktime) VALUES ('left','HERD_NAME','right','swap','10 minutes');
update sync set status='active' where name ='left';
This will set up basic replication between db_left and db_right. To kick off the bucardo process, use the following command:
sudo bucardo_ctl --dbuser='bucardo' --dbhost='localhost' --dbpass='bucardo' start "full_copy"
And to check the status to make sure it is running:
sudo bucardo_ctl --dbuser='bucardo' --dbhost='localhost' --dbpass='bucardo' status
Days back: 3 User: bucardo Database: bucardo Host: localhost PID of Bucardo MCP: 7771
Name Type State PID Last_good Time I/U/D Last_bad Time
====+=====+=====+====+=========+=====+=====+========+====
left| S |idle |7775|4m27s |0s |0/0/0|unknown |
Sorry about the backticks in those last commands. I think that is the code snippet wordpress plugin :(
To test the replication insert a record into one of the database and look for it to be replicated.
\c db_left
insert into pgbench_branches (bid, bbalance, filler) values (2, 10, 'test');
\c db_right
select * from pgbench_branches;
insert into pgbench_branches (bid, bbalance, filler) values (3, 10, 'the other way');
\c db_left
select * from bgpench_branches;
bid | bbalance | filler
-----+----------+------------------------------------------------------------------------------------------
1 | 0 |
2 | 10 | test
3 | 10 | the other way
(3 rows)
It may take a few seconds for the new record to show up. There should also be a logfile in your current working directory that will have a lot of diagnostic info.
And that is multimaster replication.
Hi Jonathan. The setup I described above is a multimaster replication scenario, even though it is only in use on a single server. The two databases are both masters. To have them on separate machinse, you’d just have to edit the connection info in the db table insert statements, as long as access was squared away.
The bucardo server can be on another machine entirely. All it needs is access to the replicated databases and it’s own postgres database, which can also be on an entirely different server.
Best of luck!
If I am setting up a multi-master scenario between 2 separate servers, do I need to install Bucardo on both master servers?
could you elaborate on how to set up Bucardo on multiple master DBs & hosts?
Thanks for the comment!
There seem to be multiple bugs for bucardo_ctl installation in the bucardo bugzilla. I commented on this one a week or two ago:
http://bucardo.org/bugzilla/show_bug.cgi?id=1
I’m using postgres 8.4, so maybe that is the problem.
Could you describe some of the problems you had with bucardo_ctl? You *should* be able to do all this through bucardo_ctl pretty easily, but if there are problems, [email protected] or the Bucardo bug tracker (http://bucardo.org/bugzilla/) would love to hear about them. And, of course, if you’ve already reported them, many thanks :)