Thread: Postgresql-8.4: File System Level Backup (& recovery failure)
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) ]
Ennio-Sr <nasr.laili@tin.it> writes:
> 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;
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.
> 2. make sure that failure to recover has nothing to do with the version
> of postgresql-client.
Shouldn't ...
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [201112, 12:30]: > Ennio-Sr <nasr.laili@tin.it> writes: > > 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) ]
Ennio-Sr <nasr.laili@tin.it> writes:
> * Tom Lane <tgl@sss.pgh.pa.us> [201112, 12:30]:
>> 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
* Tom Lane <tgl@sss.pgh.pa.us> [201112, 17:04]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> > * Tom Lane <tgl@sss.pgh.pa.us> [201112, 12:30]:
>
> 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 ...
> 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) ]
Ennio-Sr <nasr.laili@tin.it> writes:
> 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"
> ????????????????
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.
> 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?
You certainly do *not* want to use createcluster after you've copied
back the archived file tree. Just start the postmaster.
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [201112, 19:42]: > Ennio-Sr <nasr.laili@tin.it> writes: > > "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" > > ???????????????? > > 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? > 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... > > > # 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) ]
* Tom Lane <tgl@sss.pgh.pa.us> [201112, 19:42]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> 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) ]