Re: Incremental Refresh - PostgreSQL 9.2

Keith,

* Keith (keith(at)keithf4(dot)com) wrote:
> However, once you restore that backup and bring it up as a standalone
> database instance, you can no longer use the WAL stream from the original
> instance to "replay" those changes. This is also the case with any sort of
> rsync'ing of the data files from one instance to another.

Right, but if you don't mind throwing away the changes which were made
to the restored server (the test server) then you can use rsync (if the
source database is shut down) to re-sync the restored server without
having to re-copy everything (I'd strongly recommend using the CRC
option to rsync). The same is possible with pgBackRest, which allows
you to keep the source database online (pgBackRest will check that the
hashes match between the backup and the restored system when doing
incremental restores, there's no need to explicitly ask for that as
there is with rsync).

> You may be better off looking into a logical replication solution to copy
> your data to a testing system if it needs to be a standalone system. That
> is not currently built into Postgres, but it's on track for possible
> inclusion with 9.6. In the mean time look into tools such as Mimeo or
> Bucardo.
>
> https://github.com/omniti-labs/mimeo
> https://bucardo.org/wiki/Bucardo

Right, and there's also Slony. These are useful if the requirement is
that changes to the test/restored server be persisted.

Thanks!

Stephen