Archive for January 14th, 2009
PostgreSQL replication with Bucardo HOW-TO
by Richard Martin on Jan.14, 2009, under PostgreSQL
To setup Bucardo on a server as a multi-master
All the code and stuff is on Main server – the other server just listens…. so it does not need any “config”
To install (on the main server):
As root:
yum install postgresql-pl.x86_64
cpan
install Bundle::CPAN
then install the required Perl modules with
install DBI etc…
To install:
as postgres in psql:
CREATE USER bucardo SUPERUSER;
on ALL target databases: (as postgres)
createlang plpgsql DATABASE
as postgres in psql:
CREATE DATABASE bucardo OWNER bucardo;
as bucardo in psql:
CREATE LANGUAGE plpgsql;
CREATE LANGUAGE plperlu;
as postgres:
psql -f bucardo.schema -U bucardo bucardo
To make importable SQL files you can to save you some typing:
(inside psql)
\t
\o filename.sql
RUN A SQL QUERY and the output goes to the filename.sql
you can then just \i the files
RUN THE SQL for INSERTS and LINKS
To setup the replication and get it all going:
as bucardo on the main server:
INSERT INTO db(name, dbname, dbhost, dbuser)
VALUES ('main-server','DB_NAME','nnn.nnn.nnn.nnn','DB_USER');
INSERT INTO db(name, dbname, dbhost, dbuser)
VALUES ('other-server','DB_NAME',' nnn.nnn.nnn.nnn','DB_USER');
INSERT INTO dbgroup(name) VALUES (’remote_dbs’);
INSERT INTO dbmap(db,dbgroup) VALUES (’REMOTE_DB’,'remote_dbs’);
To generate the inserts you need: as user DB_USER
\t
\o tables.sql
This gives you a list of all tables you (probably want to replicate):
select 'INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(\'main-server\', \'' || relname || '\', \'id\', \'int\', \'source\');'
from pg_class, pg_user
where usesysid=relowner and
(relkind ='r' or relkind='s') and (usename ='DB_NAME') and
(relkind !='i' or relname !~'^xinx')
order by relname;
\o
** MAKE SURE YOU REMOVE ANY REFERENCES TO BUCARDO ** – I just delete them by hand
then as user BUCARDO
