Re: Slony v. DBMirror

Grant McLean wrote:
> On Thu, 2005-05-05 at 14:16 -0400, Jeff - wrote:
>
>>One of the biggest things for Slony is that you can install slony,
>>set things up and it will bring the slave(s) "up to speed". You
>>don't need to do an initial data dump (I think you still need to load
>>the schema on the slaves, but not the data). That is a BIG win for
>>us folks who can't take a machine down while pg_dump runs (and while
>>it is restored on hte slave)
>
>
> Why would you need to take anything down to run pg_dump? And surely
> bringing a slave up to speed using Slony would be much slower than
> dump/restore?
>
You don't need to take Postgres down to use pg_dump - it works just fine.

The problem with replication (with DBmirror at least) is that you have
to create a backup in a very specific order to make sure your new backup
ends up in sync and transactions are neither replicated more than once,
or not replicated at all:

1. Stop client access to the database (so you don't create any more
transactions to replicate)

2. Stop the replication script when the dbmirror tables are empty

3. pd_dump the master

4. pg_restore the slave

5. Restart client apps and replication (doesn't matter which order)

If you don't do this then there is a chance of missing or more likely
duplicating transactions which can obviously cause problems.

Having said that - it would be fairly straight-forward to write a
recover script that avoided these problems by taking note of the
transaction sequence IDs in the replication tables. If I get a chance
I'll look into doing that - doesn't feel like a huge job!

Pete