template1, createdb, schemas, and owners

[CSN]

I have two machines between which I exchange dumps a
lot. On the first (Windows/cygwin), pgsql was set up
with "Administra tor" as the main superuser - who owns
all schemas in template0 and template1. On the second
machine (Linux), "postgres" is pgsql's main superuser.
On whatever machines I do "createdb", the owner of the
schemas in template0/1 is copied over to the schemas
in the new database, even when specifying the owner
parameter (shouldn't the owner of the database own all
schemas in it?). This creates problems when dumping
and importing between the machines. The "SET SESSION
AUTHORIZATION 'Administrator' ;" causes errors when
trying to import on the machine without user
"Administrator" .

What's the best way to remedy the problems caused by
the two different superusers? I've thought about
trying to change all instances of "Administra tor" to
"postgres" on the first machine, but don't know how to
go about it.

TIA,
CSN


_______________ _______________ ____
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Rachel McConnell]

CSN wrote:

I have two machines between which I exchange dumps a
lot. On the first (Windows/cygwin), pgsql was set up
with "Administra tor" as the main superuser - who owns
all schemas in template0 and template1. On the second
machine (Linux), "postgres" is pgsql's main superuser.
On whatever machines I do "createdb", the owner of the
schemas in template0/1 is copied over to the schemas
in the new database, even when specifying the owner
parameter (shouldn't the owner of the database own all
schemas in it?). This creates problems when dumping
and importing between the machines. The "SET SESSION
AUTHORIZATION 'Administrator' ;" causes errors when
trying to import on the machine without user
"Administrator" .

What's the best way to remedy the problems caused by
the two different superusers? I've thought about
trying to change all instances of "Administra tor" to
"postgres" on the first machine, but don't know how to
go about it.

TIA,
CSN

If you haven't already, check out pg_dump's -O option. This suppresses
all ownership data from the backup, so you'll never get any "SET SESSION
AUTHORIZATION.. ." lines at all. My setup doesn't use schemas, though,
so I can't be sure there aren't any issues lurking there, but I can't
see why there would be.

Rachel
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster