Thread: Another way to Replicate

Another way to Replicate

From
"Alexander B."
Date:
Hi people,

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?

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;


We can do this? any experience?

Thanks in advance



_______________________________________________________
Yahoo! Mail - Sempre a melhor opção para você!
Experimente já e veja as novidades.
http://br.yahoo.com/mailbeta/tudonovo/



Re: Another way to Replicate

From
"Chad Wagner"
Date:
On 1/19/07, Alexander B. <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?

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.

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.



--
Chad
http://www.postgresqlforums.com/

Re: Another way to Replicate

From
"Chad Wagner"
Date:
By the way, here is a link to the documentation:

http://www.postgresql.org/docs/8.2/static/warm-standby.html


Re: Another way to Replicate

From
"Andy Shellam (Mailing Lists)"
Date:
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.


Re: Another way to Replicate

From
"Andy Shellam (Mailing Lists)"
Date:
Andy Shellam (Mailing Lists) wrote:
> 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.)

Ah, just found it - it's in CVS -
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgpitrha/