Slony1 PostgreSQL Replication - Backup of slave nodes?

Author

Backup of slave nodes?

F.Sluiter

2006-04-05, 8:28 pm

We have a setup where several databases share a schema with 5 tables.
Many other tables are dependend with foreign keys on these 4 tables,
but each database has its own unique set of these and the data in
those is unique per database: The other schema's and tables between
the slaves are therefore different from each other.
Updates to the shared tables are infrequent (once a day a few records)
and I can enforce that ids (pkeys) will not change or get deleted and
all records are timestamped upon creation.
Each database has its own maintainer(s) and is owned by a different
organisation and each organisation has a few hundred users .

To keep the 4 tables consistent between all the databases, I am
considering slony to replicate the tables from a single source to the
slaves. (Slaves are not allowed to change those tables only the master
may).

Now the catch:
How do I backup the clients? Each night we do a pg_dump on all the
databases. And in case of trouble we need to be able to separately
restore a single database, sometimes even to a version from a few
weeks ago.

How do I get the shared tables in sync with the master, considering
that emptying those tables for a rebuild is not a workable option
because of the foreign key constraints?

Is slony the way to go? or is dbmirror a better solution in this case,
because that doesn't demand to start from an empty table, it just
needs a table that is the same, which although a pain, can be done
manually after the dump is restored and before bringing replication
online again. I'm not even sure if dbmirror is stable enough, it does
not seem to be used a lot.

A third alternative is creating a trigger that contacts all databases
through dblink directly, or just copy/past all the sql statements on
the master via a script to all clients. When I log that with a
timestamp in a file, I can recreate databases up to any point in time
and reissue that sql to the shared tables to make them consistent
again. But that sounds a lot like a manual replication system and I'd
rather use something that is automated.

Any answers, thoughts and comments are welcome!

Floris

Jan Wieck

2006-04-06, 7:38 am

On 4/5/2006 4:26 PM, F.Sluiter wrote:
> We have a setup where several databases share a schema with 5 tables.
> Many other tables are dependend with foreign keys on these 4 tables,
> but each database has its own unique set of these and the data in
> those is unique per database: The other schema's and tables between
> the slaves are therefore different from each other.
> Updates to the shared tables are infrequent (once a day a few records)
> and I can enforce that ids (pkeys) will not change or get deleted and
> all records are timestamped upon creation.
> Each database has its own maintainer(s) and is owned by a different
> organisation and each organisation has a few hundred users .
>
> To keep the 4 tables consistent between all the databases, I am
> considering slony to replicate the tables from a single source to the
> slaves. (Slaves are not allowed to change those tables only the master
> may).
>
> Now the catch:
> How do I backup the clients? Each night we do a pg_dump on all the
> databases. And in case of trouble we need to be able to separately
> restore a single database, sometimes even to a version from a few
> weeks ago.
>
> How do I get the shared tables in sync with the master, considering
> that emptying those tables for a rebuild is not a workable option
> because of the foreign key constraints?

If you keep the shared tables in one schema and the "local" tables in
another, you can create a pg_dump of only the local tables. In case you
need to rebuild a node, you drop it completely, recreate the "shared"
part and let it subscribe. When the subscription is done and the shared
part is back in sync, you restore the dump of the local part.

Jan

>
> Is slony the way to go? or is dbmirror a better solution in this case,
> because that doesn't demand to start from an empty table, it just
> needs a table that is the same, which although a pain, can be done
> manually after the dump is restored and before bringing replication
> online again. I'm not even sure if dbmirror is stable enough, it does
> not seem to be used a lot.
>
> A third alternative is creating a trigger that contacts all databases
> through dblink directly, or just copy/past all the sql statements on
> the master via a script to all clients. When I log that with a
> timestamp in a file, I can recreate databases up to any point in time
> and reissue that sql to the shared tables to make them consistent
> again. But that sounds a lot like a manual replication system and I'd
> rather use something that is automated.
>
> Any answers, thoughts and comments are welcome!
>
> Floris
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

F.Sluiter

2006-04-06, 8:28 pm

Thank you for the answer.

So if I understand correctly I would dump the slave databases in two steps:
first schema only and then only the data that is not shared.

Restoring is:
restore schema's and subscribe the node. wait untill it is in sync and
then load the data.

Three more questions then, hope you don't mind:
1 Is the schema dump from a running slave usable for recreation, or do
I need to filter out the slony stuff from this schema only dump?
2. Is there any way of automating this? I have found the -l and -L
option of pg_restore, but I couldn't find a similar option for
pg_dump.
3. Should I use slony1_dump.sh for this datadump of the slave?

Any help is much appreciated!

Regards,

Floris

On 4/6/06, Jan Wieck <JanWieck-/ E1597aS9LQAvxtiuMwx3
w@public.gmane.org> wrote:
> On 4/5/2006 4:26 PM, F.Sluiter wrote:
>
> If you keep the shared tables in one schema and the "local" tables in
> another, you can create a pg_dump of only the local tables. In case you
> need to rebuild a node, you drop it completely, recreate the "shared"
> part and let it subscribe. When the subscription is done and the shared
> part is back in sync, you restore the dump of the local part.
>
>
> Jan
>
>
>
>
> --
> #===================
====================
====================
===========#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #
>

Jan Wieck

2006-04-06, 8:28 pm

On 4/6/2006 4:15 PM, F.Sluiter wrote:

> Thank you for the answer.
>
> So if I understand correctly I would dump the slave databases in two steps:
> first schema only and then only the data that is not shared.

You cannot dump the schema of replicated tables on a subscriber. Only
the data. You must obtain the schema of replicated tables on their origin.

This should not be a problem since the schema is unlikely to change that
much. Otherwise you would have some severe headaches with Slony anyway.

>
> Restoring is:
> restore schema's and subscribe the node. wait untill it is in sync and
> then load the data.

.... then load the "non-shared" data only. The "shared" data has been
restored by the subscribe.

>
> Three more questions then, hope you don't mind:
> 1 Is the schema dump from a running slave usable for recreation, or do
> I need to filter out the slony stuff from this schema only dump?

The schema dump of a subscriber is possibly damaged for subscribed
tables. Take a look at the script in the tools directory that can
extract a clean schema from a "master" node to get an idea how to get
those tables schema. This is another reason to separate the shared and
private parts into separate schemas. pg_dump can dump a single schema.

> 2. Is there any way of automating this? I have found the -l and -L
> option of pg_restore, but I couldn't find a similar option for
> pg_dump.

There is a way for everything to automate it. Look for the pg_dump
option -s.

> 3. Should I use slony1_dump.sh for this datadump of the slave?

I suggest you write your own scripts for that. This is a case I have not
seen before and I doubt that any out of the box tools coming with slony
are even written with that in mind, let alone being tested for use in
this context.

Jan

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Sponsored Links