Обсуждение: After restoring a database using pg_restore , cannot insert or update records in tables with primary keys

We are using Postgres Version 7.3.2
We backup and restore Postgres databases using pg_dump and pg_restore.
Though pg_restore process does not throw any exception but After using
it for restoring we are not able to insert or update records in the
tables having Primary Keys. The message we get is "The primary key is
not a btree."
When we run postgres in Debug mode then this problem does not come.

The same code of backup restore was working for postgres version 7.2.

Any ideas?

Thanks
Vikas

vikas.dogra@tabs.toshiba.com (Vikas) writes:
> We backup and restore Postgres databases using pg_dump and pg_restore.
> Though pg_restore process does not throw any exception but After using
> it for restoring we are not able to insert or update records in the
> tables having Primary Keys. The message we get is "The primary key is
> not a btree."

Do you mean "Index <foo> is not a btree"?  That's the closest match
I can find in the sources.

This implies that the version code stored in the first page of an index
is wrong.  If you see this without having gone through spectacular
failures (system crashes) then I'd have to bet that there is something
very flaky about your hardware.  Run some hardware diagnostics ---
memtest86 and badblocks are often recommended.  (Note that badblocks has
been known to miss disk failures unless run in its "destructive" test
mode...)

            regards, tom lane

We have a legacy application that currently uses an old isam style database
(from db/c).  Piece by piece, we are rewriting our application to use
Postgres.

In addition to being a software company, we also offer ASP service, and run
the application for many customers on a cluster of Solaris boxes.

I've been pondering the benefits of having:
                     a database per customer (in one cluster)
                     one big-a** database (one cluster)
                     a few clusters, a few combined databases

We perform pg_dumps every evening, one per database.  If we need to replace
some rows for a particular table, we would have to put back the entire
database somewhere, extract the rows we need, and transfer those to
production.  Besides for backing up every table separately for every
database, is there a saner way to handle this?

It is EXTREMELY important that our ASP customers do not have access to each
others data.  Some of the access to the data is by JDBC connection.  Some
is by ODBC connection.  Other than views, is their some way to secure the
data (that is not a maintenance nightmare)?

Thanks,
Naomi