From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Lonni J Friedman <netllama@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: corrupted indexes when using base backups generated from hot standby
Date: Tue, 15 Jan 2013 12:57:23 +0200
Message-ID: <50F53613.6000702@vmware.com> (raw)
In-Reply-To: <CAP=oouE5niXgAO_34Q+FGq=tS6EsMHXXRRoByov3zn9pU4249w@mail.gmail.com>
References: <CAP=oouE5niXgAO_34Q+FGq=tS6EsMHXXRRoByov3zn9pU4249w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 09.01.2013 20:28, Lonni J Friedman wrote:
> Greetings,
> I'm running postgres-9.2.2 in a Linux-x86_64 cluster with 1 master and
> several hot standby servers. Since upgrading to 9.2.2 from 9.1.x a
> few months ago, I switched from generating a base backup on the
> master, to generating it on a dedicated slave/standby (to reduce the
> load on the master). The command that I've always used to generate
> the base backup is:
> pg_basebackup -v -D /tmp/bb0 -x -Ft -U postgres
>
> However, I've noticed that whenever I use the base backup generated
> from the standby to create a new standby server, many of the indexes
> are corrupted. This was never the case when I was generating the
> basebackup directly from the master. Now, I see errors similar to the
> following when running queries against the tables that own the
> indexes:
> INDEX "debugger_2013_01_dacode_idx" contains unexpected zero page at block 12
> HINT: Please REINDEX it.
> INDEX "smoke32on64tests_2013_01_suiteid_idx" contains unexpected zero
> page at block 111
> HINT: Please REINDEX it.
>
> I've confirmed that the errors/corruption doesn't exist on the server
> that is generating the base backup (I can run the same SQL query which
> fails on the new standby, successfully). So it seems that I'm
> potentially misunderstanding some part of the process. My setup
> process is to simply untar the basebackup in the $PGDATA directory,
> and copy over all the WAL logs into $PGDATA/pg_xlog.
That process sounds correct. Since you're using pg_basebackup -x option,
you don't even need to copy the WAL logs, although it shouldn't do any
harm either . The tar file should contain everything needed to restore
the backup.
Can you provide more information? The log output would be nice. How
large is the database? What kind of activity is there in the master
while the backup is taken?
- Heikki
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: hlinnakangas@vmware.com, netllama@gmail.com
Subject: Re: corrupted indexes when using base backups generated from hot standby
In-Reply-To: <50F53613.6000702@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox