yum install xen

install a remote Centos 5.3 image from my local mirror…

virt-install

eh… that’s it.  Rather simple.  See here for proof….

Now for Windows Server 2003…..  Perhaps a longer post is looming…

For the cut & paste brigade: (RTFM)

rubyrep generate myrubyrep.conf

rubyrep scan -c myrubyrep.conf

nohup rubyrep replicate -c myrubyrep.conf &

Remember the nohup and the &, to background it, unless you’re actually at the console of the production server – which you probably aren’t….  In fact stick the nohup and & on the scan command too as that could take a few hours on a big DB…

Then tail -f nohup.out to watch the (lack) of output, output is normally bad – loss of connection:

2009-07-31T22:22:13+01:00 Exception caught: no connection to ‘right’ database

and what not… but rubyrep will just keep going once the connection comes back up.  Perfect!

PythonOption TracLocale en_GB

in your httpd.conf (between the locations)

My setup:

Sony 46″ TV 1080p running from my Mac Mini 2.26GHz Intel Core 2 Duo processor via the DVI to HDMI cable. I use EyeTV from Elgato to watch live DVB broadcasts and Plex for all my movies, DVD’s mkv’s, avi’s – you name it. Remote Buddy makes the setup complete for the Apple Remote.  No hassle no messing, just out of the box – as it shoud be.  No skipping, no shudder, perfect sound – and a very quiet (silent) HTPC solution.

If you are even more lazy than me then install HostGIS – Gregor will save the day…. it’s the best install of GIS stuff I’ve ever seen….

But onto CentOS and Mapserver etc…..

Boy, it’s made my life a lot easier…….

unless you don’t backup your CDP linux backup software database….. and then you lose ALL your backups…. so if you use CDP backup software backup your /usr/r1soft directly ALL the time…. if that directory gets corrupted – you’re screwed….

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

\i tables.sql

INSERT INTO herd(name) VALUES ('HERD_NAME');

To make the links: as user DB_USER

\t
\o links.sql

select ‘\” || relname || ‘\’,’
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

Then add this around the list of tables

INSERT INTO herdmap(herd,goat) SELECT 'HERD_NAME', id
FROM goat WHERE db='DB_NAME' AND tablename IN (

LIST OF TABLES GOES HERE…..

);

** MAKE SURE YOU REMOVE ANY REFERENCES TO BUCARDO ** – again I do this by hand

then as user Bucardo

\i links.sql

To finalise the sync:

INSERT INTO sync(name,source,targetdb,synctype,checktime,disable_triggers,disable_rules)
VALUES ('the_sync','HERD_NAME','other_server','swap','10 minutes','pg_class','pg_class');

First time only, or after a massive cock up:

INSERT INTO herd(name) VALUES ('HERD_NAME_full_copy');
\i full_copy_links.sql
INSERT INTO sync(name,source,targetdb,synctype,stayalive,kidsalive,ping,checktime,disable_triggers,disable_rules)
VALUES ('fullsync','full_copy','other-server','fullcopy',false,false,false,'10 minutes','pg_class','pg_class');

Deactivate the_sync and make full_copy sync active to kick off only the full copy

update sync set status='inactive' where name ='the_sync';
update sync set status='active' where name ='fullsync';
./bucardo_ctl start "full_copy"
./bucardo_ctl kick fullsync
then
update sync set status='active' where name ='the_sync';
update sync set status='inactive' where name ='fullsync';
./bucardo_ctl stop "full_copy"
./bucardo_ctl start "swap"

To get some nicer defaults:
update bucardo_config set value='email@email.com' where setting='default_email_to';
update bucardo_config set value='email@email.com' where setting='default_email_from';
update bucardo_config set value='http://stats_page.com/cgi-bin/' where setting='stats_script_url';

./bucardo_ctl status

will show basic info – much the same as:

http://stats_page.com/cgi-bin/bucardo-report

log file is: /var/log/bucardo.log

As root on main_server under /root/Bucardo/Bucardo-3.0.9

./bucardo_ctl stop "Stopping to add a new slave database - Greg"
./bucardo_ctl start "Restarting after donut break - Greg"

Housekeeping
This SQL is setup in a cronjob:
SELECT bucardo.bucardo_purge_delta('5 minutes'::interval);
select bucardo_purge_q_table('5 minutes'::interval);

When you change anything in any database!

DELETE FROM sync;
DELETE FROM goat;
DELETE FROM db;
DELETE FROM dbgroup;
DELETE FROM dbmap;
DELETE FROM herd;
DELETE FROM herdmap;

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 (’dromore’,'remote_dbs’);

\i tables.sql

INSERT INTO herd(name) VALUES (’leisure’);

\i links.sql

To finalise the sync:

INSERT INTO sync(name,source,targetdb,synctype,checktime,disable_triggers,disable_rules)
VALUES ('leisure_sync','leisure','dromore','swap','10 minutes','pg_class','pg_class');

To re-install bucardo

as psql:
dropdb bucardo

as DB_USER:
drop schema bucardo cascade

then just re-install from above

The Inserts (Just for reference)

INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(’banbridge’, ‘accounts’, ‘id’, ‘int’, ’source’);
INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(’banbridge’, ‘activities’, ‘id’, ‘int’, ’source’);
INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(’banbridge’, ‘activity_product_group_map’, ‘id’, ‘int’, ’source’);
INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(’banbridge’, ‘activity_product_map’, ‘id’, ‘int’, ’source’);
…
INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(’banbridge’, ‘vouchers’, ‘id’, ‘int’, ’source’);
INSERT INTO goat(db, tablename, pkey, pkeytype, standard_conflict) VALUES(’banbridge’, ‘work_addresses’, ‘id’, ‘int’, ’source’);

The Links (Just for reference)

INSERT INTO herdmap(herd,goat) SELECT ‘leisure’, id
FROM goat WHERE db=’banbridge’ AND tablename IN (
‘accounts’,
‘activities’,
‘activity_product_group_map’,
‘activity_product_map’,
…
‘vouchers’,
‘work_addresses’);

Hot Restore
What I did: (found this somewhere but I can’t remember where…. probably the bucardo mailing list)
On A site
1. Stopped Bucardo (bucardo_ctl stop ‘testing hot restoration’)
2. Inserted, deleted, updated records
3. Created dump (pg_dump –data-only –disable-triggers mydb > test.dump)
4 Inserted, delete, updated records.
5. Transferred the dump to the site B
6. To imitate the database crash. Deleted (truncated) all tables on site B including bucardo_delta, bucardo_track and bucardo_delta_targets.
7. Restored dump (./bin/psql ; \i test.dump)
Note. Because I created the dump in the step 3 as a plain text I did not use pg_restore).
8. Switched conflict_resolution value for all tables in the table goat from skip to target
Note. I think this is an excessive precaution.
9. Started Bucardo (bucardo_ctl start ‘testing hot restoration’)
10. Was amused that Bucardo did not report any conflicts and restored all changes made before and after the dump creation.