From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Re: multiple hot standby streaming replication scenario with "rotating" the primary server
Date: Mon, 11 Apr 2011 12:55:29 -0500
Message-ID: <4DA2FA41020000250003C672@gw.wicourts.gov> (raw)
In-Reply-To: <BANLkTik1CO13J8gj6HbYKqFn9FCWkHjDYw@mail.gmail.com>
References: <BANLkTinvBfV3MuL6k3PJWxbhTg2qwW4KQg@mail.gmail.com>
<BANLkTiksRW05KZtJDs6Wf75wKD2R+bjGUg@mail.gmail.com>
<4DA2E174020000250003C650@gw.wicourts.gov>
<BANLkTikKGej=BmKaBUPcQu5z16J3J085vw@mail.gmail.com>
<4DA2F31C020000250003C665@gw.wicourts.gov>
<BANLkTik1CO13J8gj6HbYKqFn9FCWkHjDYw@mail.gmail.com>
Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote:
> I have e.g. a table with:
> Indexes:
> "idx_auftrag_l1" hash (a_nr)
Any *hash* index will need to be rebuilt. Like that one.
> Seeing this and reading the docs about the caveats I see no other
> solution as to REINDEX all of my DB's :-(
As the docs say, you need to REINDEX your *hash* indexes. Most
people don't use those because they aren't WAL-logged, which can
lead to various problems (including this one) and because there seem
to be very few circumstances were they perform better than btree
indexes, which don't have such a problem.
If you switched to btree indexes where you currently have hash
indexes, the REINDEX would be totally unneeded.
> I have lot's of tables with primary key a_nr as above (though I
> know it is not reflected in the table definition, but should
> result in the same result, i.e. a_nr integer PRIMARY KEY would
> create an index to lookup the table).
I think a PRIMARY KEY constraint normally builds a btree index,
which wouldn't have this problem.
-Kevin
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: Kevin.Grittner@wicourts.gov, gerhard.hintermayer@gmail.com
Subject: Re: Re: multiple hot standby streaming replication scenario with "rotating" the primary server
In-Reply-To: <4DA2FA41020000250003C672@gw.wicourts.gov>
* 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