From: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
To: Alexander B. <burbello3000@yahoo.com.br>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Another way to Replicate
Date: Fri, 19 Jan 2007 19:26:46 +0000
Message-ID: <45B11B76.3060803@mailnetwork.co.uk> (raw)
In-Reply-To: <81961ff50701191049r5f729dcbqd074731edd0e6de1@mail.gmail.com>
References: <45B110F6.9080607@yahoo.com.br>
<81961ff50701191049r5f729dcbqd074731edd0e6de1@mail.gmail.com>
Chad Wagner wrote:
> On 1/19/07, *Alexander B.* <burbello3000@yahoo.com.br
> <mailto:burbello3000@yahoo.com.br>> wrote:
>
> I would like to know if is possible to replicate in postgres applying
> binary logs (wal logs) like the same way is done on Oracle!!
> Did anybody tried to do that?
>
You could do it, yes, there was a PITR High Availability project in
PgFoundry (http://pgfoundry.org/projects/pgpitrha/) launched a while
ago, but this doesn't seem to have released any files (I thought I
remembered that they did release a test snapshot.)
The basic principle for pre-8.2 servers is that your master sends its
WAL log files to your slave.
You add a base backup on your slave and start it in recovery mode.
Your slave's restore_command script checks for the next file in the
sequence (passed as a parameter from PostgreSQL) - when it's received,
it copies into the xlog directory and passes a success return code to
PGSQL. Before the file is received, the script sits in a loop that
checks every minute or so for a new file, which will keep PostgreSQL
waiting for it. When the file comes in, it copies it and passes a
success code back.
You'd then have to build in a mechanism so you can touch a file if you
want to bring PostgreSQL up out of recovery mode, which terminates the
script with a non-zero code so PostgreSQL knows it's received all files
and starts up.
>
> Oracle doesn't use binary logs for replication, for regular snapshots
> it uses a "materialized log" -- which is nothing more than a table
> with a primary key and what type of change. If we are talking
> multi-master replication, then Oracle uses Advanced Queues
> (essentially another table) and pushes the data. All off this occurs
> over DB links.
>
> As for binary (archived redo) logs in Oracle, they can be used for a
> Hot Standby. Which PostgreSQL also supports, as I understand it this
> is a new feature for 8.2.
Yep, warm standby is in 8.2, I've not investigated it, though.
>
> I personally haven't investigated Slony, but I believe it functions
> similar.
>
> Applying the simple ideia:
> - for each archived Wal logs, transfer to slave server;
> - after transfer, apply recover on postgres;
> - repeat the steps above, all the time;
>
>
> Again, this is a hot (warm in PostgreSQL) standby database. I don't
> think you can bring online a database in read only while it is
> actively applying the archived WAL logs.
No, while the recovery is taking place, users trying to connect will get
a "FATAL: Database system is starting up" error.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: andy.shellam-lists@mailnetwork.co.uk, burbello3000@yahoo.com.br
Subject: Re: Another way to Replicate
In-Reply-To: <45B11B76.3060803@mailnetwork.co.uk>
References: <45B110F6.9080607@yahoo.com.br> <81961ff50701191049r5f729dcbqd074731edd0e6de1@mail.gmail.com> <45B11B76.3060803@mailnetwork.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox