bi-directional syncing help request

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.

sorry, half asleep and typing rubbish.
--all you need to do is switch master and slave so that "master" is the one
box you are currently on

On 9 August 2013 15:35, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:

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.

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

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.

On 9 August 2013 16:10, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:

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.

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

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
yes, as said, the pre-condition is that she is the one and only possible
data producer.

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

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.

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

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.

Not only that, but she mentioned that her laptop is a Mac and the
office machine is a Linux server.  A solution would probably need
to use *logical* copies like pg_dump, slony, or Londiste.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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