Thread: bi-directional syncing help request

Hi. I'm looking for suggestions for the best solution to the following situation.

I have a database roughly 300 meg with 30 tables.

For fieldwork, a copy is running on my mac laptop where I can pull up information and add new entries.

The data analysis and further development is done back at the office on a copy running on a linux server.

The development and analysis work often generates corrections on the field data input and occasionally results in schema changes.

As a result, the situation is bi-directional, so not a master-slave replication situation and I'm not a professional dba. It is my personal research data, so I don't have to worry about other users.

My question is whether there is any recommended best practice for bi-directional syncing (schema changes would only be one direction, but data entries could flow both ways)?

Any suggestions would be appreciated.

(I have also posted this question to pgsql-admin)

Paula

if it's only you using it, all you need to do is switch master and server so that "server" is the one box you are currently on. If both boxes produce data at the same time you need a lot of work to manage row versioning.

On 9 August 2013 15:27, Paula Kirsch <pl.kirsch@gmail.com> wrote:

Hi. I'm looking for suggestions for the best solution to the following situation.

I have a database roughly 300 meg with 30 tables.

For fieldwork, a copy is running on my mac laptop where I can pull up information and add new entries.

The data analysis and further development is done back at the office on a copy running on a linux server.

The development and analysis work often generates corrections on the field data input and occasionally results in schema changes.

As a result, the situation is bi-directional, so not a master-slave replication situation and I'm not a professional dba. It is my personal research data, so I don't have to worry about other users.

My question is whether there is any recommended best practice for bi-directional syncing (schema changes would only be one direction, but data entries could flow both ways)?

Any suggestions would be appreciated.

(I have also posted this question to pgsql-admin)

Paula

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

On Fri, Aug 9, 2013 at 7:36 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:

--all you need to do is switch master and slave so that "master" is the one box you are currently on

That probably isn't a reasonable solution, considering the OP mentioned that she was not a professional DBA. Setting up a hot/warm standby, failing over, resetting the slave from the new master, lather, rinse, repeat  is certainly scriptable but doesn't seem like the best option here.

I don't know of any commercial solutions, but writing some code might be required. Synchronizing data isn't that difficult, but synchronizing changes to database table structure will be a bit tricky,

not sure having to write stuff any time you do even the smallest thing on your table is more reasonable of taking the pain to write (or ask friends to help you writing) a couple of bash scripts that will do the job forever. But then again, this is true if and only if she is the one and only user who can create data.

On 9 August 2013 16:06, bricklen <bricklen@gmail.com> wrote:

On Fri, Aug 9, 2013 at 7:36 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:

--all you need to do is switch master and slave so that "master" is the one box you are currently on

That probably isn't a reasonable solution, considering the OP mentioned that she was not a professional DBA. Setting up a hot/warm standby, failing over, resetting the slave from the new master, lather, rinse, repeat  is certainly scriptable but doesn't seem like the best option here.

I don't know of any commercial solutions, but writing some code might be required. Synchronizing data isn't that difficult, but synchronizing changes to database table structure will be a bit tricky,

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

On Fri, Aug 9, 2013 at 8:12 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:

or you can just take a full dump from one box and import it on the other any time you switch. If it's not a big db it should probably be quicker than any alternative approach.

A pg_dump from one system or the other will effectively overwrite the database it is restored to, so any changes in one system or the other will be lost unless processes are put in place to move that data to a staging area before the reload.
The issue here is that it is a data and structure synchronization problem, which isn't something solved very easily using Postgresql's core tools.  Synchronizing the data using Bash/Python, FDW's etc is reasonably straightforward, but synchronizing the DDL changes will require some care.