Recovering data from binary files?
Hi all:
Well, after my earlier post regarding pg_operator being corrupted, I
received a mail from one of the Postgres developers (I think) talking
about one of the system indexes (pg_attribute_relid_attnum_index) being
corrupted, so I gave it a try and copied said file from the template1
database to the one I have to recover... and now it gives me another
error: "cannot find attribute 1 of relation pg_user". Like before, I
can't even do a \d to see the existing tables in the database, much less
insert/update anything in the pg_* tables to fix it, and after
struggling the whole morning with it, I have pretty much given up on
that path.
I am trying now to:
1) Re-create the database in another machine.
2) Copy the binary files that contain the tables (minus the pg_* ones)
to the other server, in the place where the new database has been
created ($PGDATA/base/$dbname).
3) Try to access the tables from there using psql.
The problem now is that, although I can view the tables correctly, they
all come up as empty. All the SELECTs I try on them return nothing. And
the strange thing is that pg_class.reltuples says that said table has
5478 tuples, which is correct.
Any other ideas? Is there any other way to access the binary files and
extract the information in them to text? My manager is starting to talk
about migrating to SQL Server, and frankly, at this point I'm finding
less and less arguments to counter him...
Paulo Jan.
DDnet.
-----Original Message-----
From: pgsql-general-owner@hub.org
[mailto:pgsql-general-owner@hub.org]On Behalf Of Paulo Jan
Sent: Tuesday, May 09, 2000 12:00 AM
To: pgsql-general@postgresql.org
Cc: fvilches@mail.ddnet.es
Subject: [GENERAL] Recovering data from binary files?Hi all:
Well, after my earlier post regarding pg_operator being corrupted, I
received a mail from one of the Postgres developers (I think) talking
about one of the system indexes (pg_attribute_relid_attnum_index) being
corrupted, so I gave it a try and copied said file from the template1
database to the one I have to recover... and now it gives me another
error: "cannot find attribute 1 of relation pg_user". Like before, I
can't even do a \d to see the existing tables in the database, much less
insert/update anything in the pg_* tables to fix it, and after
struggling the whole morning with it, I have pretty much given up on
that path.
I am trying now to:1) Re-create the database in another machine.
2) Copy the binary files that contain the tables (minus the
pg_* ones)
to the other server, in the place where the new database has been
created ($PGDATA/base/$dbname).
Oh it seems almost same as pg_upgrade.
However you should probably copy pg_log and pg_variable to
$PGDATA as pg_upgrade does.
In addtion if postmaster is running,you should shutdown before
processing 2).
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
Show quoted text
3) Try to access the tables from there using psql.
The problem now is that, although I can view the tables
correctly, they
all come up as empty. All the SELECTs I try on them return nothing. And
the strange thing is that pg_class.reltuples says that said table has
5478 tuples, which is correct.
Any other ideas? Is there any other way to access the
binary files and
extract the information in them to text? My manager is starting to talk
about migrating to SQL Server, and frankly, at this point I'm finding
less and less arguments to counter him...Paulo Jan.
DDnet.
Oh it seems almost same as pg_upgrade.
However you should probably copy pg_log and pg_variable to
$PGDATA as pg_upgrade does.
In addtion if postmaster is running,you should shutdown before
processing 2).Regards.
Well. It worked!!! The database was succesfully recovered.
Thanks a lot.
Paulo Jan.
DDnet.
"J. Atwood" wrote:
I have been looking around for the RPM for 7.0 anyone have a quick link?
Thanks,
J
not in any way official, but I did a cheap knock off of the 7.0RC1 rpms.
You should be able to pick them up at karl.debisschop.net/src/postgres/
These are based on RPMs by Lamar Owen at
http://www.ramifordistat.net/postgres/beta/SRPMS - I've really added
nothing (though I expect to have build of the plperl procedural language by
tomorrow). In fact I'd guess that the reason I can post this before Lamar
is because he is trying to improve the existing RPMS, whereas I just wanted
to start running 7.0 on some development machines as soon as possible.
Testing is limited, but I've deployed this successfully. He has SRPMs for
RC5 at his site, but not the final yet.
If you do use these. plan on upgrading once Lamar releases his final. But
you should be able to do that without a dump/restore cycle, which was my
prime concern.
Karl DeBisschop
Karl DeBisschop <kdebisschop@h00a0cc3b7988.ne.mediaone.net> writes:
If you do use these. plan on upgrading once Lamar releases his final. But
you should be able to do that without a dump/restore cycle, which was my
prime concern.
7.0RC5 is database-compatible with the final, earlier betas are *not*.
You can use pg_upgrade to update from any 6.5-or-later version if you
are feeling adventurous, but I'd definitely suggest making a backup
first in case things go wrong and you have to initdb and restore.
regards, tom lane
Tom Lane wrote:
Karl DeBisschop <kdebisschop@h00a0cc3b7988.ne.mediaone.net> writes:
If you do use these. plan on upgrading once Lamar releases his final. But
you should be able to do that without a dump/restore cycle, which was my
prime concern.7.0RC5 is database-compatible with the final, earlier betas are *not*.
You can use pg_upgrade to update from any 6.5-or-later version if you
are feeling adventurous, but I'd definitely suggest making a backup
first in case things go wrong and you have to initdb and restore.regards, tom lane
Sorry if I was not clear. The RPMs I prepared use Lamar Owen spec from RC1,
but use final release sources. Upgrading from final release to final release
should not be a problem, I think. I only recommended it because I understand
from his recent posts sthat Lamar has many inprovements to the RPM install
script that I have not taken advantage of.
Karl
7.0RC5 is database-compatible with the final, earlier betas are *not*.
You can use pg_upgrade to update from any 6.5-or-later version if you
are feeling adventurous, but I'd definitely suggest making a backup
first in case things go wrong and you have to initdb and restore.Sorry if I was not clear. The RPMs I prepared use Lamar Owen spec from RC1,
but use final release sources. Upgrading from final release to final release
should not be a problem, I think. I only recommended it because I understand
from his recent posts sthat Lamar has many inprovements to the RPM install
script that I have not taken advantage of.
Tom's point is that RC5 is *not* database-compatible with previous
release candidates due to a last minute bug fix in the location of
group table indices.
So anything earlier than RC5 needs (at least) a pg_upgrade, and (for
me) preferably a full dump/initdb/restore cycle.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart wrote:
7.0RC5 is database-compatible with the final, earlier betas are *not*.
You can use pg_upgrade to update from any 6.5-or-later version if you
are feeling adventurous, but I'd definitely suggest making a backup
first in case things go wrong and you have to initdb and restore.Sorry if I was not clear. The RPMs I prepared use Lamar Owen spec from RC1,
but use final release sources. Upgrading from final release to final release
should not be a problem, I think. I only recommended it because I understand
from his recent posts sthat Lamar has many inprovements to the RPM install
script that I have not taken advantage of.Tom's point is that RC5 is *not* database-compatible with previous
release candidates due to a last minute bug fix in the location of
group table indices.So anything earlier than RC5 needs (at least) a pg_upgrade, and (for
me) preferably a full dump/initdb/restore cycle.- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
My point is that my RPMS use the final release sources
Karl
My point is that my RPMS use the final release sources
Doh! Sorry, I was focused on the RC1 thing...
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
