[GENERAL] Large Objects and Replication question

[Howard Cole]

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

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

Thanks.

Howard
www.selestial.com

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alexey Klyukin]

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

[Tatsuo Ishii]

> 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

[Tatsuo Ishii]

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

[Alexey Klyukin]

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

[Daniel Verite]

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

[Tatsuo Ishii]

> > 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

--

[Linos]

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�: