Thread: Restore live backup from posgtres/data/base/* gives PHPPGADMIN errors, works fine otherwise

Hi there fellow admins,

One of our databases had to be restored from a backup tape. Since there
weren't any dumps made. All I had was a live backup (I know I know) of
/var/lib/postgres/data/base/<number>. The number for the corresponding
database I manages to discover after some searching with strings and grep.
First of all: isn't there an easy way or a file to look up the number
for the corresponding databases??

Second question... what could have gone wrong while restoring just the
corresponding directory for the database? In the log files I only found
out that the database wasn't shut down properly (when I started
postgres again after the restore) which I assume is correct because it was
restored from a live system backup. One CLOG file turned up to be missing,
however no database-transactions are used in this database application.
Recovery was done and successful by postmaster. The database is now up and
running. The website running from the content works fine again. All
records including BLOBS are there as well. In PSQL no strange warnings or
errors...

HOWEVER.... when I browse the DB with PHPPGADMIN (version 2.4.1-2) I get
all kinds of resultset errors.

For example: "Warning: Supplied argument is not a valid PostgreSQL result
resource in /usr/share/phppgadmin/db_details.php on line 344"

And this is ONLY for tables from the restored DB, the
other (non-restored) DB's give no PHPPGADMIN DB-errors.

Has anyone a clue what could be wrong. Or are there tools I can run to do
more consistency checks / rebuilds / whatever. 

Postgres version: 7.2.1-2woody4

Thanks in advance!!

-- 
Kind regards,

Jo Voordeckers

Op Thu, 12 Feb 2004 21:18:10 +0100, schreef Jo Voordeckers:

> restored from a live system backup. One CLOG file turned up to be
> missining according to syslog, however no database-transactions are used
> in this database application.

Hmm I think I misunderstood the transaction nature of the PG_CLOG files.
The one that seems to be missing 0023 is also giving those errors in the
application at some specific point. Should I restore this file also from
the backup tape? On the tape there are 2 files in PG_CLOG, currently
there are 2 files also but with different names. That's why I'm affraid
that restoring the 0023 it's missing could mess up the other DB's. Am I
correct?

What exactly are these PG_CLOG files for... they seem pretty important and
not corresponding to a single DB. What I don't understand is that some
query's error on this while others run fine... And again how can you
identify the PG_CLOG files's corresponding DB's.

-- 
Kind Regards,

Jo Voordeckers

Jo Voordeckers wrote:
> What exactly are these PG_CLOG files for... they seem pretty
> important and not corresponding to a single DB. What I don't
> understand is that some query's error on this while others run
> fine... And again how can you identify the PG_CLOG files's
> corresponding DB's.

The clog files store which transactions were commited or rolled back 
("commit log") and they apply to the entire database cluster.  
Therefore (and for other, related reasons), you cannot restore a single 
database file into an existing cluster without either trashing that one 
database, the cluster, or both.