Message-ID: <50F5A106.3050109@identified.com>
Date: Tue, 15 Jan 2013 10:33:42 -0800
From: Phil Monroe <phil@identified.com>
User-Agent: Postbox 3.0.6 (Macintosh/20121022)
MIME-Version: 1.0
To: Heikki Linnakangas <hlinnakangas@vmware.com>
CC: pgsql-admin@postgresql.org,
	Infrastructure <infrastructure@identified.com>,
	Vladimir Giverts <vlad@identified.com>, Tejas <tejas@identified.com>,
	Sasha Kipervarg <sasha@identified.com>
Subject: Re: Postgres WAL Recovery Fails... And Then Works...
References: <50F0CB3E.4080708@identified.com> <50F534C6.4000501@vmware.com>
In-Reply-To: <50F534C6.4000501@vmware.com>
Content-Type: multipart/alternative;
	boundary="------------000808010000000507070507"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------000808010000000507070507
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Sorry, Initial response got blocked since I replied with the logs quoted 
again.

>
> Also, which version of postgres are you using?


PostgreSQL 9.2.1 on Ubuntu 12.04

>
>
> Except in my case no number of restarts helped. You didn't say, were
> you explicitly copying $PGDATA or using some other mechanism to
> migrate the data elsewhere?


So we have a very large database (~5TB), so we use a script to do 
parallel rsyncs to copy the data directory 
(https://gist.github.com/4477190/#file-pmrcp-rb). The whole copy process 
ended up taking ~3.5 hours. So we did a physical copy of $PGDATA (which 
is located at /var/lib/postgresql/9.2/main/ on both machines.). We 
followed the following process to do this:

1. Master archives WAL files to Backup Host.
2. Execute on Master: psql -c "select pg_start_backup('DATE-slave-restore')"
3. Execute on Master: RCP='rsync -cav --inplace -e rsh' 
EXCLUDE='pg_xlog' pmrcp /var/lib/postgresql/9.2/main/ 
prd-db-01:/var/lib/postgresql/9.2/main/ > /tmp/backup.log
4. Execute on Master: psql -c "select pg_stop_backup()"
5. On Slave, setup recovery.conf to read WAL archive on Backup Host
6. Execute on Slave: pg_ctlcluster 9.2 main start (as described in 
initial email)


Best,
Phil

--------------000808010000000507070507
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<HTML><HEAD><META content="text/html; charset=UTF-8" http-equiv="Content-Type"></HEAD><BODY style="font-family: Monaco;"><div style="font-family: Monaco;">Sorry, Initial response got blocked since I replied with the logs quoted again.<BR>
<BR>
<blockquote type="cite"><BR>
Also, which version of postgres are you using?<BR>
</blockquote><BR>
<BR>
PostgreSQL 9.2.1 on Ubuntu 12.04<BR>
<BR>
<blockquote type="cite"><BR>
<BR>
Except in my case no number of restarts helped.&nbsp; You didn't say, were<BR>
you explicitly copying $PGDATA or using some other mechanism to<BR>
migrate the data elsewhere?<BR>
</blockquote><BR>
<BR>
<BR>
So we have a very large database (~5TB), so we use a script to do parallel rsyncs to copy the data directory (https://gist.github.com/4477190/#file-pmrcp-rb). The whole copy process ended up taking ~3.5 hours. So we did a physical copy of $PGDATA (which is located at /var/lib/postgresql/9.2/main/ on both machines.). We followed the following process to do this:<BR>
<BR>
1. Master archives WAL files to Backup Host.<BR>
2. Execute on Master: psql -c "select pg_start_backup('DATE-slave-restore')"<BR>
3. Execute on Master: RCP='rsync -cav --inplace -e rsh' EXCLUDE='pg_xlog' pmrcp /var/lib/postgresql/9.2/main/ prd-db-01:/var/lib/postgresql/9.2/main/ &gt; /tmp/backup.log<BR>
4. Execute on Master: psql -c "select pg_stop_backup()"<BR>
5. On Slave, setup recovery.conf to read WAL archive on Backup Host<BR>
6. Execute on Slave:&nbsp; pg_ctlcluster 9.2 main start (as described in initial email)<BR>
<BR>
<BR>
Best,<BR>
Phil<BR>
</div></BODY></HTML>

--------------000808010000000507070507--