Re: BUG #5874: pg_dumpall CREATE DATABASE statements 'missing' parameters

On Wed, Feb 9, 2011 at 4:57 AM, Jan-Peter Seifert
<Jan-Peter(dot)Seifert(at)gmx(dot)de> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5874
> Logged by:          Jan-Peter Seifert
> Email address:      Jan-Peter(dot)Seifert(at)gmx(dot)de
> PostgreSQL version: 8.4.7
> Operating system:   Windows 7 64-Bit
> Description:        pg_dumpall CREATE DATABASE statements 'missing'
> parameters
> Details:
>
> Hello,
>
> it's not really a bug ... but CREATE DATABASE statements for databases with
> the same Locale/Encoding as the server miss the parameters ENCODING,
> LC_COLLATE and LC_CTYPE. So you 'can't' migrate to a server with a different
> Locale/Encoding setting.
> I'd be very happy if one had the option to switch between OS specific names
> e.g. German_Germany.28591 on Windows and de_DE on Linux automatically ( -b
> WIN ) or manually ( -w German_Germany.28591,de_DE+...) as well.

These seem to go in opposite directions - if I'm understanding
correctly, we could solve the first problem by always emitting the
locale and encoding parameters, but then that'd make it more likely
that you would run into conflicts in the second case.

I think the best option may be to dump globals with pg_dumpall -g and
then back up each database with pg_dump -Fc. The custom format dumps
are really nice (selective restore, parallel restore) and of course
that also gives you the option to create the database first and then
restore the dump into it, which would let you dodge this problem.

> I'd also be very happy if one could specify a comma separated list of
> databases to skip (e.g. template1, postgres etc.)

Seems like it could be useful, or maybe a list of databases to include
would be better, not sure.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company