Thread: pg_dump file question

Hi,

My friend just notice me with some thing that make using dump files harder 
to use.

So here we go:
first we make some chaos :)
\c template1 postgres
create user foo with password 'bar' createdb nocreateuser;
\c template1 foo
create database foodb;
\c template1 postgres
alter user foo with nocreatedb;

then we... pg_dumpall -s (nowaday for explain we need only schemas :)

and we try to applay this pg_dumpall file.... and suprise :)

we have something like that:

\connect template1
.....
CREATE USER "foo" WITH SYSID 32 PASSWORD 'bar' NOCREATEDB NOCREATEUSER;
.....
\connect template1 "foo"
CREATE DATABASE "foodb" WITH TEMPLATE = template0 ENCODING = 'LATIN2';
\connect "foodb" "foo"

I think evryone see why it dont work.

I think that rebuild of dumping procedure to detect such a problems and 
remake dump file like that

\connect template1
.....
CREATE USER "foo" WITH SYSID 32 PASSWORD 'bar' CREATEDB NOCREATEUSER;
.....
\connect template1 "foo"
CREATE DATABASE "foodb" WITH TEMPLATE = template0 ENCODING = 'LATIN2';
\connect template1 "postgres"
ALTER USER "foo" WITH NOCREATEDB;
\connect "foodb" "foo"

will solve this problem.

regards
Robert Partyka
bobson@wdg.pl
www.WDG.pl 

Robert Partyka <bobson@ares.fils.us.edu.pl> writes:
> \connect template1
> .....
> CREATE USER "foo" WITH SYSID 32 PASSWORD 'bar' NOCREATEDB NOCREATEUSER;
> .....
> \connect template1 "foo"
> CREATE DATABASE "foodb" WITH TEMPLATE = template0 ENCODING = 'LATIN2';
> \connect "foodb" "foo"
> 
> I think evryone see why it dont work.

Yes -- it's a known problem with 7.2 that pg_dump can create
self-inconsistent dumps. In 7.3, this specific case has been fixed:
databases are now created using CREATE DATABASE ... WITH
OWNER. However, I'm not sure if there are other, similar problems that
haven't been fixed yet.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC