- Create a table in one database as follows:
CREATE TABLE synctest ( id INTEGER PRIMARY KEY, field1 TEXT, field2 TEXT, field3 TEXT );
Also create this table in the replication destination database; Bucardo won't replicate schema changes or database structure.
- Tell Bucardo about the table. I won't give the SQL here because it's already available in the Bucardo documentation. Suffice it to say you need to tell the goat table about a customselect query. For my testing, I used 'SELECT id, field1 FROM synctest'. Note that the fields returned by this query must
- Include all the primary key fields from the table. Bucardo will complain if it can't find the primary key in the results of the customselect query.
- Return field names matching those of the table. This means, for example, that if you somehow transform the contents of a field, you need to make sure the query explicitly names the results something Bucardo can recognize, e.g. 'SELECT id, do_some_transformation(field1) AS field1 FROM synctest'
- Tell the sync to use the custom select statements by setting the 'usecustomselect' field in the sync table to TRUE for the sync in question
- Fire up Bucardo and see the results. Here's my source table:
58921 josh@bucardo_test# select * from uniq_test ; id | field1 | field2 | field3 ----+----------+--------+--------- 1 | alpha | bravo | charlie 2 | delta | echo | foxtrot 3 | hotel | india | juliet 4 | kilo | lima | mike 5 | november | oscar | papa 6 | romeo | sierra | tango 7 | uniform | victor | whiskey 8 | xray | yankee | zulu (8 rows)...and here's my destination table...
58922 josh@bucardo_test# select * from uniq_test; id | field1 | field2 | field3 ----+----------+--------+-------- 1 | alpha | | 2 | delta | | 3 | hotel | | 4 | kilo | | 5 | november | | 6 | romeo | | 7 | uniform | | 8 | xray | | (8 rows)
Welcome to End Point’s blog
Ongoing observations by End Point people
Replicate only parts of my table
A day or two ago in #slony, someone asked if Slony would replicate only selected columns of a table. A natural response might be to create a view containing only the columns you're interested in, and have Slony replicate that. But Slony is trigger-based -- the only reason it knows there's something to replicate is because a trigger has told it so -- and you can't have a trigger on a view. So that won't work. Greg chimed in to say that Bucardo could do it, and mentioned a Bucardo feature I'd not yet noticed. Bucardo is trigger-based, like Slony, so defining a view won't work. But it allows you to specify a special query string for each table you're replicating. This query is called a "customselect", and can serve to limit the columns you replicate, transform the rows as they're being replicated, etc., and probably a bunch of other stuff I haven't thought of yet. A simple example:
- ► 2016 (70)
- ► 2015 (122)
- ► 2014 (157)
- ► 2013 (164)
- ► 2012 (210)
- ► 2011 (144)
- ► 2010 (147)
- Search Engine Optimization Thoughts
- osCommerce dead and reborn
- Replicate only parts of my table
- Bare git repositories and newspapers
- Puppet PDX will meet on 2/27/09
- Announcing Release of PostgreSQL System Impact (PG...
- Testing in the Web Environment
- Hot topic: user groups!
- Test::Database Postgres support
- David Mamet on software development
- NY Puppet Users Group meeting
- Slony1-2.0.0 + PostgreSQL 8.4devel
- Using cron and psql to transfer data across databa...
- Git it in your head
- ▼ February (14)
- ► 2008 (59)
- ► 2007 (12)