Thread: 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 > 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.
I have been looking around for the RPM for 7.0 anyone have a quick link? Thanks, J
"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
"J. Atwood" wrote: > > I have been looking around for the RPM for 7.0 anyone have a quick link? An announcement will be made shortly. Shortly being within a day or two -- possibly tonight. More likely tomorrow night. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11