On December 3, 2004 02:14 pm, David Parker wrote:
> After successfully putting it off for a while, I'm finally having to
> confront the need to replicate a database that has large objects in it. In
> one of our schemas, we support application objects that are essentially
> software agents, which get represented in the database as some metadata
> plus an oid field that stores binary content, e.g., a shared library .so
> file.
>
> I know there's no support in slony for replicating large objects,
> primarily, as I understand it, because it's not possible to create triggers
> on the pg_largeobject catalog object (there may well be other issues).
>
> First of all, has anybody been thinking about this? Has any experimenting
> been done?
>
> Given a table like:
>
> create table agent
> {
>    id  int,
>    name varchar(64),
>    description varchar(255),
>    ...
>    data oid
> }
>
> I was thinking something like this might work:
>
> 1) remote_worker.c sync_event, for a given table insert/update, detects the
> presence of an oid field. This would require inspecting more schema
> metadata about the tables in the set, of course. I'm already on shaky
> ground, here, because I don't fully understand yet how this sync_event
> works.
Well we wouldn't scan for oid but instead add another command to slonik, that 
tells that a specific attribute refrences an oid.

>
> 2) for the given oid field, the worker reads that lob from the provider db
>
> 3) writes the lob into the receiver db
>
> 4) swizzles the oid in the replicated data from the providers local oid to
> the receivers local oid.
>
> This results in data that is not completely identical because the lob oids
> are different, which might well be a problem. I also haven't thought about
> the initial copy_set, though I guess it would do basically the same thing.

For this how does the large object dump/restore facility work in PG now (I 
think it's a contrib package)

>
> I'm sure there are a hundred holes in this, so I'd appreciate comments. Or
> if it's completely impossible then somebody can put me out of my misery
> immediately....

Provided we can get around the possibility of oid collisions in some nice way 
I think this method could work.  Do you want to take the legwork time and 
provide us (me) with a breakdown of how the LO dump/restore stuff does it? 
and if it's not too ugly, and time permitting I'llsee about  hacking around 
on this a bit.

>
> Thanks.
>
> - DAP
> ---------------------------------------------------------------------------
>------- David Parker    Tazz Networks    (401) 709-5130
> ?
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com