Thread: Help with Database Recovery
Please--I have searched everywhere I can think of and have found no guidance
for recovering databases where the /var/lib/pgsql/base directories are
intact but there's no pg_dump file to work from. I need to do this because
I stupidly but inadvertently uninstalled PostgreSQL and then had to
reinstall it.
Some things that I tried:
1. just copy the old base/* directories into the newly created
/var/lib/pgsql/base directory. psql was not aware of the databases.
2. use createdb to create a database, and then copy the old data files into
the corresponding base/<db> directory. psql said "couldn't open cache for
user 40" or something similar.
3. modified uid for postgres user to 40, reinstalled PostgreSQL, did "2"
again, similar result.
I'd be very grateful for suggestions on where to look for guidance for this
kind of recovery, and I'll summarize my findings to the list.
It was a great irony that Chapter (lucky) 13 in the Admin documentation
("Database Recovery") only says "This section needs to be written.
Volunteers?" If I learn something, I'll be sure to write it up.
Original message to list:
> I have inadvertently uninstalled PostgreSQL and subsequently reinstalled
> it, which has rendered my existing databases inaccessible. I'd like to be
> able to recover them. There is no pgdump output to work from.
>
> If anyone knows of a description of how to recover the data withing the
> (intact) datafiles from the directories in /var/lib/pgsql/base, I'd be
> very grateful to learn of it.
>
> I have searched the documentation and the Momjian book and the
> postgresql.org site, but haven't found anything that helps me.
>
> If I'm barking up the wrong list, I'd like to know that, too.
>
> Cheers!
> --
> David Hancock | dhancock@arinc.com | 410-266-4384
>
"Hancock, David (DHANCOCK)" wrote: > Please--I have searched everywhere I can think of and have found no guidance > for recovering databases where the /var/lib/pgsql/base directories are > intact but there's no pg_dump file to work from. I need to do this because > I stupidly but inadvertently uninstalled PostgreSQL and then had to > reinstall it. > 1. just copy the old base/* directories into the newly created > /var/lib/pgsql/base directory. psql was not aware of the databases. > 2. use createdb to create a database, and then copy the old data files into > the corresponding base/<db> directory. psql said "couldn't open cache for > user 40" or something similar. > 3. modified uid for postgres user to 40, reinstalled PostgreSQL, did "2" > again, similar result. If the two PostgreSQL installations are the same major version, then you copy the entire /var/lib/pgsql tree over. Not just the base/* trees. What version, what OS? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Sorry I didn't give more detail. OS is Linux 2.2 kernel, PostgreSQL is 6.5.3. The problem is that I copied the .../base/* directories elsewhere in preparation for making base a symlink to a different filesystem with more space. I then screwed up and removed everything in /var/lib/pgsql, not just the base directories. This necessitated a reinstall of PostgreSQL. I know, I know ... it was a very stupid maneuver on my part, but it's a strange feeling to know that I've GOT the database files, I just can't use 'em. Yet. Thanks, and Cheers! -- David Hancock | dhancock@arinc.com | 410-266-4384 Cheers! -- David Hancock | dhancock@arinc.com | 410-266-4384 -----Original Message----- From: Lamar Owen [mailto:lamar.owen@wgcr.org] Sent: Thursday, November 30, 2000 12:23 PM To: Hancock, David (DHANCOCK) Cc: 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] Help with Database Recovery "Hancock, David (DHANCOCK)" wrote: > Please--I have searched everywhere I can think of and have found no guidance > for recovering databases where the /var/lib/pgsql/base directories are > intact but there's no pg_dump file to work from. I need to do this because > I stupidly but inadvertently uninstalled PostgreSQL and then had to > reinstall it. > 1. just copy the old base/* directories into the newly created > /var/lib/pgsql/base directory. psql was not aware of the databases. > 2. use createdb to create a database, and then copy the old data files into > the corresponding base/<db> directory. psql said "couldn't open cache for > user 40" or something similar. > 3. modified uid for postgres user to 40, reinstalled PostgreSQL, did "2" > again, similar result. If the two PostgreSQL installations are the same major version, then you copy the entire /var/lib/pgsql tree over. Not just the base/* trees. What version, what OS? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
"Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com> writes:
> Sorry I didn't give more detail. OS is Linux 2.2 kernel, PostgreSQL is
> 6.5.3. The problem is that I copied the .../base/* directories elsewhere in
> preparation for making base a symlink to a different filesystem with more
> space. I then screwed up and removed everything in /var/lib/pgsql, not just
> the base directories. This necessitated a reinstall of PostgreSQL.
> I know, I know ... it was a very stupid maneuver on my part, but it's a
> strange feeling to know that I've GOT the database files, I just can't use
> 'em. Yet.
Unfortunately, you've only got *part* of the database. The above
maneuver destroyed your pg_log file, which is essential. Without it,
you've got a lot of tuples but you don't know which ones are valid.
If you did a VACUUM just before all this, then there's a reasonable
chance that the tuples you have left are mostly just valid ones.
Otherwise I'd say it's hopeless. In any case you will not be able
to reconstruct data that you can trust except after painstaking
manual examination.
How far back was your last regular whole-file-system backup? Restoring
all of /var/lib/pgsql off that is likely to be your best shot at getting
to a state that's somewhat trustworthy.
regards, tom lane
Tom and others: Thanks for the guidance. We rebuilt and restored, and will
just live with an earlier version of the data, sadder but wiser. It was
good to (a) learn about pg_log and (b) realize that pg_dump and pg_dumpall
are our good friends and we should use them.
Today I also learned that starting a subject line with "Help" diverts a
message from going to the list directly. I see why this is a good idea.
Again, thanks, all.
Cheers!
--
David Hancock | dhancock@arinc.com | 410-266-4384
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, November 30, 2000 2:24 PM
To: Hancock, David (DHANCOCK)
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Help with Database Recovery
"Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com> writes:
> Sorry I didn't give more detail. OS is Linux 2.2 kernel, PostgreSQL is
> 6.5.3. The problem is that I copied the .../base/* directories elsewhere
in
> preparation for making base a symlink to a different filesystem with more
> space. I then screwed up and removed everything in /var/lib/pgsql, not
just
> the base directories. This necessitated a reinstall of PostgreSQL.
> I know, I know ... it was a very stupid maneuver on my part, but it's a
> strange feeling to know that I've GOT the database files, I just can't use
> 'em. Yet.
Unfortunately, you've only got *part* of the database. The above
maneuver destroyed your pg_log file, which is essential. Without it,
you've got a lot of tuples but you don't know which ones are valid.
If you did a VACUUM just before all this, then there's a reasonable
chance that the tuples you have left are mostly just valid ones.
Otherwise I'd say it's hopeless. In any case you will not be able
to reconstruct data that you can trust except after painstaking
manual examination.
How far back was your last regular whole-file-system backup? Restoring
all of /var/lib/pgsql off that is likely to be your best shot at getting
to a state that's somewhat trustworthy.
regards, tom lane