2

I'm working on a project where a Postgresql database needs to be shared across several physical locations. Each location has limited connectivity, and may only have access to the outside world once or twice a day. So the database has to be available locally at each location, but must also synchronize with the master database when possible.

I am not yet familiar with replication or clustering. Are these good solutions? Or is there a better way of doing it? I would appreciate some advice on this. :)

NOTE: clashing of primary keys from different locations would not be an issue, this has been taken care of.

Improve this question
1
  • 1
    Read only or read write access at these several locations? Read only would be much, much easier. Commented Sep 30, 2011 at 11:11

1 Answer 1

Reset to default
3

If the remote locations require read-only access to the data, you can set up asynchronous replication fairly easily using log shipping, which is a built-in feature of PostgreSQL. In this configuration, the master server drops WAL (write-ahead log) files to a shared location where the remote servers can periodically connect and read the logs to bring themselves up to date.

If all servers are performing writes independently, what you're looking for is asynchronous multi-master replication. The Postgres docs mention Bucardo and rubyrep as options for accomplishing this. According to the docs, both are limited to master-to-master replication (or master to multiple slaves), but Bucardo supposedly has true multi-master replication planned for version 5.0, and rubyrep mentions a method for keeping multiple servers synchronized.

(I have servers using PostgreSQL's log shipping and streaming replication features, but I have no direct experience with Bucardo or rubyrep.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.