Re: Another way to Replicate - Mailing list pgsql-admin

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.