Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E03019FB2AD for ; Fri, 19 Jan 2007 15:26:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 40371-04 for ; Fri, 19 Jan 2007 15:26:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from perth.mailnetwork.co.uk (perth.mailnetwork.co.uk [80.175.28.46]) by postgresql.org (Postfix) with ESMTP id B85CC9FB28E for ; Fri, 19 Jan 2007 15:26:13 -0400 (AST) Received: from [90.240.58.117] (port=1124 helo=[10.100.9.2]) by perth.mailnetwork.co.uk with esmtpa (Exim 4.63) (envelope-from ) id 1H7zNN-0001b3-5G; Fri, 19 Jan 2007 19:26:13 +0000 Message-ID: <45B11B76.3060803@mailnetwork.co.uk> Date: Fri, 19 Jan 2007 19:26:46 +0000 From: "Andy Shellam (Mailing Lists)" User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: "Alexander B." CC: "pgsql-admin@postgresql.org" Subject: Re: Another way to Replicate References: <45B110F6.9080607@yahoo.com.br> <81961ff50701191049r5f729dcbqd074731edd0e6de1@mail.gmail.com> In-Reply-To: <81961ff50701191049r5f729dcbqd074731edd0e6de1@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.72 tagged_above=0 required=5 tests=BAYES_50, SARE_SPEC_REPLICA, SPF_PASS X-Spam-Level: X-Archive-Number: 200701/175 X-Sequence-Number: 24331 Chad Wagner wrote: > On 1/19/07, *Alexander B.* > 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.