Thread: 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-01 > > 82377 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 19026
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
>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 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
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 > 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 > > 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