[Bucardo-general] Quick Swap Replication Setup Example

Ben Allen bsa at lanl.gov
Tue Oct 2 17:16:59 UTC 2007


Below is a quick example for getting a "swap" replication setup. This  
assumes you have already installed all the required programs and perl  
modules indicated in the Bucardo docs, as well as created the  
PostgreSQL user "bucardo" as a superuser. I'm using Bucardo 3.0.6  
during this example.

Note: Its likely you will need to the set the search_path to see the  
bucardo schema, i.e. "set search_path to bucardo,public;". This only  
needs to be done for the bucardo database;

#Create the Bucardo database.
CREATE DATABASE bucardo OWNER bucardo;

#Connect to the bucardo database add the two languages and import the  
bucardo schema.
\c bucardo
CREATE LANGUAGE plpgsql;
CREATE LANGUAGE plperlu;
\i bucardo.schema

#Create the two test databases
CREATE DATABASE test1 OWNER bucardo;
CREATE DATABASE test2 OWNER bucardo;

#Connect to the first database and create a test table.
\c test1
CREATE LANGUAGE plpgsql;
CREATE LANGUAGE plperlu;

#Notice that we need a primary key, also notice that it is an  
integer. We need to know that for later.
CREATE TABLE name (
	id int CONSTRAINT id_pkey PRIMARY KEY,
	title varchar(40) NOT NULL
);

#Connect to the second database and create a test table
\c test2
CREATE LANGUAGE plpgsql;
CREATE LANGUAGE plperlu;
CREATE TABLE name (
	id int CONSTRAINT id_pkey PRIMARY KEY,
	title varchar(40) NOT NULL
);

#Connect to the Bucardo database.
\c bucardo

#Add the two databases to the db table. Note these two databases are  
on the same host so you can leave the dbhost column blank.
INSERT INTO db(name,dbname,dbhost,dbuser) VALUES  
( 'test1','test1','','bucardo');
INSERT INTO db(name,dbname,dbhost,dbuser) VALUES  
( 'test2','test2','','bucardo');

#Add the goat for the one table we created. Note that we use "int"  
for the pkeytype. The pkeytype can be any of the following values:  
'smallint', 'int', 'bigint', 'text', 'timestamp', or 'date'.
INSERT INTO goat(db,tablename,pkey,pkeytype,standard_conflict) VALUES  
('test1', 'name','id','int','source');

#Create a test herd.
INSERT INTO herd(name) VALUES ('test_herd');

#Map the one goat we created to the test herd.
INSERT INTO herdmap(herd,goat) SELECT 'test_herd', id from goat where  
db='test1' and tablename in ('name');

#Create the sync. The source column needs the name of the herd we  
just created.
INSERT INTO sync 
(name,source,targetdb,synctype,checktime,disable_triggers,disable_rules) 
  VALUES ('test_sync','test_herd','test2','swap','1  
minute','pg_class','pg_class');

Start the Bucardo daemon.
./bucardo_ctl start "Starting"
Checking for existing processes
Removing /var/run/bucardo/fullstopbucardo
Starting Bucardo

#Add some data in the first database.
\c test1
INSERT INTO name VALUES ( '1', 'hello' );

#Connect to the second database and see if the data has been copied  
over.
\c test2
You are now connected to database "test2".
test2=# SELECT * from name;
id | title
----+-------
   1 | hello
(1 row)

#Add some data to the second database.
INSERT INTO name VALUES ( 2, 'hi');

#Connect back to the first database and see if the data has been copied.
test2=# \c test1
You are now connected to database "test1".
test1=# SELECT * from name;
id | title
----+-------
   1 | hello
   2 | hi
(2 rows)


Hope this helps someone to get started. Feel free to reply with any  
corrections, or things I've missed.

Regards,

Ben Allen


-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20071002/546416c3/attachment.html 


More information about the Bucardo-general mailing list