Обсуждение: [general] rsync'd database requires reindex - why ?
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.
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.
On Tue, Nov 22, 2011 at 7:32 AM, marcin kowalski <yoshi314@gmail.com> wrote: > i'm simply stopping postgresql If you do do pg_ctl stop -m immediate then the copy will be corrupt. You need to do a correct shutdown for it to work. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services