Thread: After restoring a database using pg_restore , cannot insert or update records in tables with primary keys
After restoring a database using pg_restore , cannot insert or update records in tables with primary keys
From
vikas.dogra@tabs.toshiba.com (Vikas)
Date:
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
Re: After restoring a database using pg_restore , cannot insert or update records in tables with primary keys
From
Tom Lane
Date:
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