Re: Continuous PITR (was Re: multimaster)

On Fri, 1 Jun 2007, Ron Johnson wrote:

> How difficult would it be to modify the process (the postmaster?) that writes
> the xlogs(?) to tee them to a listening process across the cloud on the DR
> machine, which then applies them to the DR database?

On an 8.2 server, you can practically do this right now by aggressively
using the pg_xlogfile_name_offset() function and piping the data over to
somewhere else.  See section 23.4.4 of
http://www.postgresql.org/docs/8.2/static/warm-standby.html

It's not quite putting a "tee" in the engine itself, but there's not a lot
of practial difference if you're copying over the data only a moment after
it appears with the offset function instead; it's just a different set of
efficiency trade-offs (more polling the way it's implemented, but the base
engine never gets held up waiting for network I/O to the DR box).

P.S. it's not the "the cloud" anymore, it's "the tubes".

> This then begs the question: are CREATE|ALTER TABLESPACE commands stored in
> the xlogs?

See section 23.3.5 of
http://www.postgresql.org/docs/8.2/static/continuous-archiving.html for
information about what goes into the WAL when you do that.  You'd need to
implement a process that made sure to sync changes in the underlying
filesystem before modifying a tablespace.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD