Large Objects and Replication question

On Dec 2, 2009, at 4:23 PM, Howard Cole wrote:

Does anyone know of a replication solution that can handle large objects? Preferrably on a per database rather than per cluster basis.

Take a look at Mammoth Replicator: https://projects.commandprompt.com/public/replicator.

Additionally there is a list of available open-source replication solutions here:
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

Personally I'm not aware of other solutions supporting large objects, but I'm sure there should be more of them :).

Incidentally - out of interest - why doesn't Slony handle large objects?

Here is an excerpt from the Slony documentation:

Slony-I does not automatically propagate schema changes, nor does it have any ability to replicate large objects. There is a single common reason for these limitations, namely that Slony-I collects updates using triggers, and neither schema changes, large object operations, nor TRUNCATE requests are able to have triggers suitable to inform Slony-I when those sorts of changes take place. As a result, the only database objects where Slony-I can replicate updates are tables and sequences.
http://www.slony.info/documentation/slonyintro.html#INTRODUCTION

I'm not sure that it's up-to-date, since 8.4 has at least on truncate triggers, but still there are no triggers on large objects yet.

--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc

Does anyone know of a replication solution that can handle large
objects? Preferrably on a per database rather than per cluster basis.

pgpool-II can handle large objects. However you need to use newer API
of libpq to create large objects:

Oid lo_create(PGconn *conn, Oid lobjId);

I'm not sure Java or some other languages support the equivalent API
though.

You cannot use old API lo_creat() since it relies on OID, which
pgpool-II does not guarantee OIDs can be replicated.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

BTW

Additionally there is a list of available open-source replication solutions here:
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

The link http://www.slony2.org/ mentioned in the wiki page above
apparently does nothing to do with Slony-II. Can someone please fix it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

On Dec 2, 2009, at 5:48 PM, Tatsuo Ishii wrote:

BTW

Additionally there is a list of available open-source replication solutions here:
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

The link http://www.slony2.org/ mentioned in the wiki page above
apparently does nothing to do with Slony-II. Can someone please fix it.

Removed the link.

--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc

Tatsuo Ishii wrote:

However you need to use newer API
of libpq to create large objects:

Oid lo_create(PGconn *conn, Oid lobjId);

[...]

You cannot use old API lo_creat() since it relies on OID, which
pgpool-II does not guarantee OIDs can be replicated.

Does it mean that lo_create(conn, 0) is supported while
lo_creat(conn,INV_READ|INV_WRITE) is not ?
It sounds strange from the user's point of view, because they do the same
thing, don't they?

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

However you need to use newer API
of libpq to create large objects:

Oid lo_create(PGconn *conn, Oid lobjId);

[...]

You cannot use old API lo_creat() since it relies on OID, which
pgpool-II does not guarantee OIDs can be replicated.

Does it mean that lo_create(conn, 0) is supported while
lo_creat(conn,INV_READ|INV_WRITE) is not ?
It sounds strange from the user's point of view, because they do the same
thing, don't they?

Well, I do not recommend to use lo_create(conn, 0) either.

Actually users can use lo_create(conn, 0) or lo_create without any
problem until you try to extract existing large objects by oid.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Symmetric-ds it is a replication solution that handles large objects, it is
asynchronous and multi-master, i have been using between 30 separate postgresql
connected by a slow link and until now i have been working without problems, i
think this project should be in the wiki.

http://symmetricds.codehaus.org/

Regards,
Miguel Angel.

El 02/12/09 15:23, Howard Cole escribi�: