Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

* Hartmut Goebel (h(dot)goebel(at)goebel-consult(dot)de) wrote:
> Am 04.06.2010 13:56, schrieb Stephen Frost:
> > Quoting all column names makes the dump script much more difficult for
> > human consumption, which is important.
>
> I don't agree with you here. But this may be a matter of personal taste.
>
> Esp. I think, functionality is much ore important than a small decrees
> of readability. At least pg_dump should get an option
> "--quote-column-names", so this can be switcced on if necessary.

Something like '--quote-identifiers' might be alright, so long as it's
defaulted to 'off'. Of course, I don't know that it'd actually solve
your problem at all- after all, keywords can and will change between
major versions and even if your pg_dump quotes all identifiers, anything
else using the database (eg: applications) would need to as well.
Keyword changes aren't the only thing an application or other DB user
needs to be concerned about when changing major versions of PG either.

If you're using pg_dump for backups, then when you need to restore, it
should be into the same version of PG that you took the pg_dump from.
If you're using pg_dump to upgrade, use the pg_dump from the version
you're upgrading *to*, and do so in a test environment first to make
sure that the restore works correctly, that the applications and other
DB users are happy with the new version, etc, etc, before even thinking
about upgrading a production system.

Note also that having multiple major versions of PG installed (eg: 8.3
and 8.4) at the same time is made easier on some platforms (Debian-based
ones, specifically).

Thanks,

Stephen