trouble with db-restore
Hi,
Yesterday our system-harddisk crashed and i had to restore
the postgres databases from the backup-tape.
i simply use pg_dumpall with no params for dumping the
data, but when i used psql for restoring the data,
every view was created as a table and was filled up
with the data which the view had been delivered at
the time of the backup.
it was no problem, because actually we dont use much
views so i could restored it by hand. but the
question is, is it possible to backup views, and
the bigger question for me is, what additional surprises do
i have to expect from pg_dumpall?
--
SC-Networks www: www.SC-Networks.de
Web Design, Netzwerke,
3D Animation und Multimedia
Heiko Irrgang Tel.: 08856/9392-00
Im Thal 2 Fax: 08856/9392-01
82377 Penzberg Mail: Irrgang@SC-Networks.de
I am surprised and have never heard of this happening.
Hi,
Yesterday our system-harddisk crashed and i had to restore
the postgres databases from the backup-tape.i simply use pg_dumpall with no params for dumping the
data, but when i used psql for restoring the data,
every view was created as a table and was filled up
with the data which the view had been delivered at
the time of the backup.it was no problem, because actually we dont use much
views so i could restored it by hand. but the
question is, is it possible to backup views, and
the bigger question for me is, what additional surprises do
i have to expect from pg_dumpall?--
SC-Networks www: www.SC-Networks.de
Web Design, Netzwerke,
3D Animation und Multimedia
Heiko Irrgang Tel.: 08856/9392-00
Im Thal 2 Fax: 08856/9392-0182377 Penzberg Mail: Irrgang@SC-Networks.de
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Heiko Irrgang <irrgang@SC-Networks.de> writes:
i simply use pg_dumpall with no params for dumping the
data, but when i used psql for restoring the data,
every view was created as a table and was filled up
with the data which the view had been delivered at
the time of the backup.This is extremely surprising; I've never heard of such a report before.
It would seem that pg_dump failed to recognize your views as being
views. The only theory I can come up with offhand is that you are
using a pg_dump version that is not compatible with the database
server version you are running. Since you have said nothing about
what version you are using, it's hard to guess more ...regards, tom lane
On the first server, where the backup was done
a binary distribution was installed
(think it was from postgresql.org). The filenames were called:
postgresql-7.0.2-3mdk.i686.rpm, etc
On the new server, there is 7.0.2 too, but compiled from source,
dont know if this could be a hint. i'm shure that the
pg_dump used was the version which was delivered with the
binaries.
The whole thing is running on a turbolinux turbocluster server 4.0,
kernel 2.2.12, glibc 2.1.2
--
SC-Networks www: www.SC-Networks.de
Web Design, Netzwerke,
3D Animation und Multimedia
Heiko Irrgang Tel.: 08856/9392-00
Im Thal 2 Fax: 08856/9392-01
82377 Penzberg Mail: Irrgang@SC-Networks.de
Import Notes
Reply to msg id not found: 1697.979063403@sss.pgh.pa.us
Heiko Irrgang <irrgang@SC-Networks.de> writes:
i simply use pg_dumpall with no params for dumping the
data, but when i used psql for restoring the data,
every view was created as a table and was filled up
with the data which the view had been delivered at
the time of the backup.
This is extremely surprising; I've never heard of such a report before.
It would seem that pg_dump failed to recognize your views as being
views. The only theory I can come up with offhand is that you are
using a pg_dump version that is not compatible with the database
server version you are running. Since you have said nothing about
what version you are using, it's hard to guess more ...
regards, tom lane
I have seen that before on my own system. Same PG version for dump/restore.
Wasn't a big deal for me to redo the views since I had so few (i.e. 1) I
never reported it, but it happened more than once. Not sure of version, but
was 7.0.1 or 7.0.2.
--rob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Heiko Irrgang" <irrgang@SC-Networks.de>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, January 09, 2001 1:03 PM
Subject: Re: trouble with db-restore
Show quoted text
Heiko Irrgang <irrgang@SC-Networks.de> writes:
i simply use pg_dumpall with no params for dumping the
data, but when i used psql for restoring the data,
every view was created as a table and was filled up
with the data which the view had been delivered at
the time of the backup.This is extremely surprising; I've never heard of such a report before.
It would seem that pg_dump failed to recognize your views as being
views. The only theory I can come up with offhand is that you are
using a pg_dump version that is not compatible with the database
server version you are running. Since you have said nothing about
what version you are using, it's hard to guess more ...regards, tom lane
"rob" <rob@cabrion.com> writes:
I have seen that before on my own system. Same PG version for dump/restore.
Wasn't a big deal for me to redo the views since I had so few (i.e. 1) I
never reported it, but it happened more than once.
You should have reported it :-( ... as I said, I hadn't heard about this
failure mode before. I assume that the dump script shows the views as
CREATE TABLE not CREATE VIEW? I'd ask you to send me the dump script,
but it's unlikely to tell me much if the error is upstream. Can you
provide a script for creating a view that pg_dump fails to dump
correctly?
regards, tom lane
Tried this morning to recreate the problem, but could not (as of v 7.0.2).
Perhaps it was 7.0.1? Sorry for not reporting earlier. I'll keep my eye out
for this issue in the future.
--rob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "rob" <rob@cabrion.com>
Cc: "Heiko Irrgang" <irrgang@SC-Networks.de>; <pgsql-general@postgresql.org>
Sent: Tuesday, January 09, 2001 11:43 PM
Subject: Re: trouble with db-restore
"rob" <rob@cabrion.com> writes:
I have seen that before on my own system. Same PG version for
dump/restore.
Wasn't a big deal for me to redo the views since I had so few (i.e. 1)
I
Show quoted text
never reported it, but it happened more than once.
You should have reported it :-( ... as I said, I hadn't heard about this
failure mode before. I assume that the dump script shows the views as
CREATE TABLE not CREATE VIEW? I'd ask you to send me the dump script,
but it's unlikely to tell me much if the error is upstream. Can you
provide a script for creating a view that pg_dump fails to dump
correctly?regards, tom lane
rob wrote:
Tried this morning to recreate the problem, but could not (as of v 7.0.2).
Perhaps it was 7.0.1? Sorry for not reporting earlier. I'll keep my eye out
for this issue in the future.
7.0.1 == 7.0.2, just 7.0.2 has documentation in the tarball.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com