Re: [GENERAL] pg-dump problem

> Preface:
> =======
>
> Well, finally, we have decided to start backing up the Postgres databases
> we have. And the decision even came before anything got destroyed...
>
> So, our sysadmin basically made a modified version of the dumpall script,
> that creates individual files for each database, which include the
> day-of-week in their name. The effect is to have seven days worth of backup
> on disk. Since the disk is backed up on tape anyway, it more-or-less covers
> all.
>
> Since this is based on dumpall, the files are, of course, merely pg_dump
> outputs, no bells and whistles.
>
> The postgres version is 6.2.1
>
> The problem:
> ===========
>
> Well, as we all know, when one creates a backup scheme, one must try to
> restore from backup, to make sure that when shit happens, the backup will
> not turn out to be useless.
>
> So I tried to restore from one of those pg_dump outputs into a newly
> created database.
>
> It failed. Output includes several occurences of the explanation of
> backslash commands, courtesy of psql. Some - but not all - of the tables
> were restored correctly.
>
> I think I traced down the problem to the fact that I have default values
> which are SQL functions. Pg_dump dumps the SQL functions AFTER it dumps the
> tables. So, when a table is defined, and the function on which it is based
> does not exist, the creation fails. Then, the copy into that table fails,
> and then, PSQL tries to interpret the input for that COPY as commands...
>
> What can I do about it? I guess it is a "damn if you do, damn if you
> don't", because some functions require the existence of tables, and some
> tables require the existence of functions... But what am I to do about my
> backups?

I believe this was fixed in the coming 6.4 beta, September 1.

I also believe we now show the load as it is being loaded by default.
You can use a psql option to get that in earlier releases.
--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)