Postgresql-8.4: File System Level Backup (& recovery failure)

[Ennio-Sr]

Hi all!

I'm trying to recover my database from a file system backup (as
decribed at #24.2 of Postgresql 8.4 Documentation).

WHile the files in the backup directory are owned by
'postgres.postgres', when they are extracted from the backup.tar the
owner becomes 'avahi.haldaemon' or ('messagebus.ssh' if they are copied
directly from backup dir to a new one).
I do change file ownership to postgres.postgres but cannot see any of
the tables and get a void 'mydb' only!

As I am not sure to have stopped the postgres server *before* making the
original copy to the backup dir, is there any chance for me to:
1. ascertain that the backup was made while postgres was running and
therefore it's completely useless;
2. make sure that failure to recover has nothing to do with the version
of postgresql-client. AAMOF, I've lost access to original database
tables after removing postgresql-client-9.1 in the believe it was
redundant: I had previously downgraded to version 8.4 after having
the problem as exposed in my post of Oct 17 "WARNING: psql version
8.4, server version 9.1" unluckily unanswered and not getting any
further warning I was convinced to be runnning everithing on 8.4;
3. make sure there is no possibility to recover old data this way and
use an older db.dump (which, of course, would be the easiest way, but
would leave me with so many doubts pending....)

Thanks for your attention,
Ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]

[Tom Lane]

Post by Ennio-Sr
As I am not sure to have stopped the postgres server *before* making the
1. ascertain that the backup was made while postgres was running and
therefore it's completely useless;

Use pg_controldata to print out the contents of the pg_control file as
extracted from the backup. If it doesn't say the cluster state is "shut
down", then you've probably got an inconsistent filesystem backup.

Post by Ennio-Sr
2. make sure that failure to recover has nothing to do with the version
of postgresql-client.

Shouldn't ...

regards, tom lane

[Ennio-Sr]

Post by Tom Lane

Post by Ennio-Sr
As I am not sure to have stopped the postgres server *before* making the

Use pg_controldata to print out the contents of the pg_control file as
extracted from the backup. If it doesn't say the cluster state is "shut
down", then you've probably got an inconsistent filesystem backup.

Mmh... it does say 'shut down'; as to the other items to my
un-experienced eyes they look so similar to the result I get on a bare
'new' (empty) cluster just created in a different dir...
Does the 'shut down' verdict means there are other ways to attempt
before giving up the challenge?

Thank you so much for your help.
Best regards, ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]

[Tom Lane]

Post by Ennio-Sr

Post by Tom Lane
Use pg_controldata to print out the contents of the pg_control file as
extracted from the backup. If it doesn't say the cluster state is "shut
down", then you've probably got an inconsistent filesystem backup.

Mmh... it does say 'shut down'; as to the other items to my
un-experienced eyes they look so similar to the result I get on a bare
'new' (empty) cluster just created in a different dir...

In that case, in principle restoring the backup should work. Are you
sure you copied the entire $PGDATA directory tree? If you omitted
portions like pg_xlog or pg_clog that could be problematic. What
failure messages do you get *exactly*?

regards, tom lane

[Ennio-Sr]

Post by Tom Lane
In that case, in principle restoring the backup should work. Are you
sure you copied the entire $PGDATA directory tree? If you omitted
portions like pg_xlog or pg_clog that could be problematic.

I think so; this is exactly the command I gave:

\cp -a -u -f -r /var/lib/postgres*/8.4 /mnt/wd2/hda*/var/lib/postgresql/

(the reverse slash for 'cp' was to overcome my alias settings for the
command)
For sure the receiving directory did have an older backup ...

Post by Tom Lane
What failure messages do you get *exactly*?

No failure message at all: the '=> \d' command just says 'No relations
found"

Perusing the files in the 'imported' directory I saw something that
could be stramge:
(The command was:
"less /bkupdir/var/lib/postgresql/8.4/main/postmaster.opts":
/usr/lib/postgresql/8.4/bin/postgres "-D" "/var/lib/postgresql/8.4/main" \
"-c" "config_file=/etc/postgresql/8.4/main/postgresql.conf" \
"-c" "external_pid_file=/var/run/postgresql/8.4-main.pid"
????????????????


Would that mean that the older files (overwritten by the '-u' cp option)
were copied while postgres was running?

Thanks again. Regards, ennio.

PS: How is it that when I created the new cluster in the brand new
directory '/dbase/ whith the command:
# pg_createcluster -D /dbase -start 8.4 new
the subdir 'new' was not added to /dbase?
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]

[Tom Lane]

Post by Ennio-Sr
Perusing the files in the 'imported' directory I saw something that
/usr/lib/postgresql/8.4/bin/postgres "-D" "/var/lib/postgresql/8.4/main" \
"-c" "config_file=/etc/postgresql/8.4/main/postgresql.conf" \
"-c" "external_pid_file=/var/run/postgresql/8.4-main.pid"
????????????????

It's not real clear to me whether this is the archive copy or the
copied-back file tree that you're trying to use; but if it's the
latter it's not good that the -D switch doesn't match where the
files are. It looks like you are trying to use one of the packagings
that claims to support multiple PG versions concurrently; if so it
may be that you have to put the copied-back file tree exactly where
the package scripts think it should be.

Post by Ennio-Sr
PS: How is it that when I created the new cluster in the brand new
# pg_createcluster -D /dbase -start 8.4 new
the subdir 'new' was not added to /dbase?

You certainly do *not* want to use createcluster after you've copied
back the archived file tree. Just start the postmaster.

regards, tom lane

[Ennio-Sr]

Post by Tom Lane

Post by Ennio-Sr
/usr/lib/postgresql/8.4/bin/postgres "-D" "/var/lib/postgresql/8.4/main" \
"-c" "config_file=/etc/postgresql/8.4/main/postgresql.conf" \
"-c" "external_pid_file=/var/run/postgresql/8.4-main.pid"
????????????????

It's not real clear to me whether this is the archive copy or the
copied-back file tree that you're trying to use;

It's the backup copy I made to a usb disk (mounted on /mnt/wd2); so
there should be no external_pid_file?

Post by Tom Lane
but if it's the
latter it's not good that the -D switch doesn't match where the
files are. It looks like you are trying to use one of the packagings
that claims to support multiple PG versions concurrently; if so it
may be that you have to put the copied-back file tree exactly where
the package scripts think it should be.

I'm using GNU/Linux Debian Squeeze postgresql-8.4.
After the removal of postgresql-client-9.1 I had to reinstall the Squeeze
distro and during re-installation I choose to re-format the partition
relative to /var: so, if the copied back files have to be in the same
physical place there is no hope...

Post by Tom Lane

Post by Ennio-Sr
# pg_createcluster -D /dbase -start 8.4 new
the subdir 'new' was not added to /dbase?

You certainly do *not* want to use createcluster after you've copied
back the archived file tree. Just start the postmaster.

But this is exactly what I found on the docs..
It seems strange to me that while the /var/lib/postgresql/8.4/ location
gets a subdir 'main', the newly created '/dbase' (in the root fs) does
not create any such dir as the name given to the new cluster (i.e.
'new')

I'llb back within 2 or 3 hours..
Regards, ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]

[Ennio-Sr]

Post by Ennio-Sr
I'llb back within 2 or 3 hours..

Sorry, it took me more than a few hours...

Anyway, nothing relevant to add: I made a few more attempts with no
success. Finally I recovered data from a recent dumpall and all went
well.

In the meantime I got more acquainted with the fancies Debian pg's
version has as compared with the original pg.

I tried the File System Level Backup on the recovered data and it worked
as foreseen; the reason why it does not work with the old backuped data
might depend on the conditions I made that backup: the system was almost
semi-disrupted (the same fact that copied files were shown as owned by
'messagesbus.ssh' or 'avahi.haldaemon' means something was wrong
although I was unable to find any explanation concerning this point...).

I've learnt the lesson and will behave consequently ;-)

Thank you again, Tom, for your time and the wonderful program you
provide.

All the best,
Ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]