I've wedged together 2 posts here:
 
> > > You could take your backup from the origin instead of the
> subscriber.
> > 
> > Note that it seems that you can get the _data_ from the replica
> (i.e.
> > pg_dump -a), but not the schema ... that has to come from the
> primary
> > (or from your original data definition)
> 
> Sometimes.  We have databases that where pg_dump will run against
> the
> replica, we have ones that it where it fails the a sanity check and
> complains about missing OID's (pg 8.1 in both cases).
> 
> You should be able to use PITR against the replica.  You could
> recover
> the replica and then uninstall slony from the recovered node.

I'm guessing this is the easiest option; backup the replica - in the
case of failure restore the replica and remove slony - then set it up
again and resync a new subscriber.


> You can do considerably better than that...

> You need to take the *SCHEMA DUMP* from the origin, which isn't 
> hugely
> expensive.

> You can then take a data-only dump from a subscriber.

> You can then meld them together, to give you a Proper Schema, and a
> dump that *didn't* have to open a 4-hour-long transaction against 
> the
> origin node.

This sounds good, so if I take a schema dump from the publisher and
the data from the subscriber, then theres nothing stopping me
restoring the schema, then the data?

You mention a long transaction against the origin. My assumption was
that during a backup/pg_dump no other operations could happen, is
this correct?

If we were to take backups at night, a 4 hour transaction is not bad
as long as the servers could still write/read data.

Is it a bad idea to purely rely regular schema and data dumps as a
backup? Are there any other backups I should be thinking of doing?
I.e. regular file system backups?

So far from what I've read the backup options are:

WAL incremental backups
File system backups
pg_dump



      __________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com