Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvCb3-0005Tn-0U for pgsql-admin@arkaria.postgresql.org; Tue, 15 Jan 2013 19:54:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TvCb2-00020a-Fl for pgsql-admin@arkaria.postgresql.org; Tue, 15 Jan 2013 19:54:56 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvCb0-0001zF-Jy for pgsql-admin@postgresql.org; Tue, 15 Jan 2013 19:54:54 +0000 Received: from mail-pa0-f49.google.com ([209.85.220.49]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvCay-0005Wx-Qb for pgsql-admin@postgresql.org; Tue, 15 Jan 2013 19:54:54 +0000 Received: by mail-pa0-f49.google.com with SMTP id bi1so307971pad.8 for ; Tue, 15 Jan 2013 11:54:51 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=x-received:message-id:date:from:user-agent:mime-version:to:cc :subject:references:in-reply-to:content-type:x-gm-message-state; bh=Zgr91qJgcCiHGmF+qSj8NUCsFYx+mqJzo+2jjskD4BQ=; b=OUSdGQ0sX4l4yuryOErCY1bijOvCAgyBIQWZR7qERA6RJ66mDXKY7ac5Gw0HyJEnmy ga7h1mAVMRKA52wkltq4ZsIYbw/+p8Xl4K0Pm+ax+2YAo4rs3ePFGKs8ntYTlRLHmEh0 9/jv0/jkkWmKz7vfywo9kn/QurNqbS5AtD9aKDNlcuj23EFUkg2xIDxB94Yl0YpRLfkF H6Z2Ly5yCUQl8d9Z5Sjs2zy/dDH17D6c762NseiJyikJaw74+dC1dKIPCDb0v8Zi1SlI G0P9zlGGKUz4PlDyhOQRwPuS41+U68dpkxVOFrlAYwYsIkqyyAl9aXrMYRB2DLlhZG9z xY6Q== X-Received: by 10.68.241.232 with SMTP id wl8mr266664757pbc.144.1358279691874; Tue, 15 Jan 2013 11:54:51 -0800 (PST) Received: from phil-mbp.local ([38.99.37.210]) by mx.google.com with ESMTPS id c2sm11377408pay.34.2013.01.15.11.54.50 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 15 Jan 2013 11:54:51 -0800 (PST) Message-ID: <50F5B409.9000508@identified.com> Date: Tue, 15 Jan 2013 11:54:49 -0800 From: Phil Monroe User-Agent: Postbox 3.0.6 (Macintosh/20121022) MIME-Version: 1.0 To: Heikki Linnakangas CC: pgsql-admin@postgresql.org, Infrastructure , Vladimir Giverts , Tejas , Sasha Kipervarg 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="------------010501080400030306000204" X-Gm-Message-State: ALoCoQmpv3VAVruptnxq8Iytcy8EDDAy7yRzabcOAXaqaO2TQmSRxjAUn+XP9QkK56jrom+ttmCF X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------010501080400030306000204 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 Heikki Linnakangas wrote: > > 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 --------------010501080400030306000204 Content-Type: text/html; charset=UTF-8 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

Heikki Linnakangas wrote:

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
--------------010501080400030306000204--