[general] rsync'd database requires reindex - why ?

From: marcin kowalski <yoshi314(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: [general] rsync'd database requires reindex - why ?
Date: 2011-11-22 07:32:56
Message-ID: CABKsJ=Q=52X3EdasUd6tpfGLbZt-DYZqYJyzO1AVtOP-rmyp+Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list.

I'm migrating a bunch of old suse 9.3 systems with postgresql 8.2 databases
to opensuse 11.4 systems with 8.2 databases (the exact same version -
8.2.14). From there, the databases will be migrated to postgresql 9.x with
custom process.

Let's assume that 9.3 machine is machine A, and new one is machine B.

For now, i'm simply stopping postgresql on both machines, and rsync-ing the
entire data directory from A to B. And then starting postgresql on the new
one.

I do it with rsync -azvr --delete parameters.

New machine has only bare minimum databases running prior to migration -
template0, template1, postgres. Which means that any extra user database
has its directory copied over from scratch. Postgresql starts up without
any issues after transfer is finished.

However, when running the new instance, it seems indexes are quite botched,
e.g.

"select * from table where primary_key_column='somevalue';" returns
nothing.

when doing plain "select * from table;" i can see the row that should have
been returned by the previous query.

Reindexing all databases seems to resolve the problem.

This is very reproductible, as i have lots of machines to migrate and i've
hit this problem at least three times now. For now, i'm just reindexing
databases after i've done rsyncing them, but is that behavior expected ?

Both machines have postgresql-8.2.14 built by hand, with the same
configuration and with contrib installed.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ashesh Vashi 2011-11-22 07:37:53 Re: Installing postgresql on windows 7
Previous Message Benjamin Arai, Ph.D. 2011-11-22 07:20:45 Using KNN for objects that have more than 2 dimensions?