Thread: reindexing

Hi all -

On Mon, Feb 7, 2011 at 17:12, akp geek <akpgeek@gmail.com> wrote:
> Hi all -
>         I ran query this morning, I got a wrong results. I have run the same
> query in an other environment with same data and I got the result set I was
> expecting.
>        After that I did a re index and on the table I was getting incorrect
> results, the data then came out fine,
>         Do I have to reindex periodically to make sure the data retrieval
> would be correct?

In general, no. That would be silly. However, if you are using hash
indexes, per the fine manual
(http://www.postgresql.org/docs/9.0/interactive/indexes-types.html):
"Hash index operations are not presently WAL-logged, so hash indexes
might need to be rebuilt with REINDEX after a database crash. They are
also not replicated over streaming or file-based replication. For
these reasons, hash index use is presently discouraged."

REINDEX will also 'fix' a btree index if it somehow got corrupted.
Depending on the type of corruption, I would expect postgres to
complain (or segfault) in most cases instead of returning the wrong
results. Anything interesting in your server logs?

Also you failed to note what version of postgres you are using-- its
hard to tell if you are hitting a known bug or not.

thanks.. the index I was having is gist on a to_tsvector column . version we have is 8.3

On Mon, Feb 7, 2011 at 17:28, akp geek <akpgeek@gmail.com> wrote:
> thanks.. the index I was having is gist on a to_tsvector column . version we
> have is 8.3

What minor version? I sounds like you _could_ be hitting any of the below:
- (8.3.14) Fix detection of page splits in temporary GiST indexes
(Heikki Linnakangas)
- (8.3.10) Fix incorrect WAL data emitted during end-of-recovery
cleanup of a GIST index page split
- (8.3.9) Fix incorrect logic for GiST index page splits, when the
split depends on a non-first column of the index (Paul Ramsey)
- (8.3.6) Fix whole-index GiST scans to work correctly (Teodor)
- (8.3.5) Fix GiST index corruption due to marking the wrong index
entry "dead" after a deletion (Teodor)

... I got bored of doing your homework after this point. :-(

Is there a way we can validate a postgers backup? (short of restoring it
somewhere)

Thanks!

On 02/09/2011 07:52 AM, Vick Khera wrote:

On Tue, Feb 8, 2011 at 8:06 PM, u235sentinel <u235sentinel@gmail.com> wrote:

Is there a way we can validate a postgers backup? (short of restoring it somewhere)

Define "validate" for your purpose.  Once you do that, then you can come up with the procedure for accomplishing your validation.  Hint: simply restoring it somewhere may not be sufficient...

For validate what I'm looking to do is provide either some log or message provided by postgres that will alert us when/if the backup did 'not' complete successfully.  So I guess it's more of a pg_dump validation I'm looking into.

I've been googling and found pg_rman which doesn't sound like what I'm looking for.  It has a validate function but their documentation is a little light.

Thanks!

On 09/02/2011 15:10, u235sentinel wrote:
> On 02/09/2011 07:52 AM, Vick Khera wrote:
>> On Tue, Feb 8, 2011 at 8:06 PM, u235sentinel <u235sentinel@gmail.com
>> <mailto:u235sentinel@gmail.com>> wrote:
>>
>>     Is there a way we can validate a postgers backup? (short of
>>     restoring it somewhere)
>>
>>
>> Define "validate" for your purpose. Once you do that, then you can
>> come up with the procedure for accomplishing your validation. Hint:
>> simply restoring it somewhere may not be sufficient...
>>
> For validate what I'm looking to do is provide either some log or
> message provided by postgres that will alert us when/if the backup did
> 'not' complete successfully. So I guess it's more of a pg_dump
> validation I'm looking into.

Well, pg_dump returns 0 on success, and error messages on standard error
on failure.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie