I have posted this to the GENERAL list, but never got a response, so I'm
trying here.
We are migrating from 6.2.1 to 6.4.2. The problem is that normal pg_dump
for 6.2.1 doesn't work for 6.4.2. I have hundreds of databases to migrate.
However, I found a solution which relies on the fact that most of those
databases have identical structures, each holding the data for one
particular workgroup for the same sort of application.
So, I collected SQL scripts for each kind of database, creating only the
schema. I want to run the schema, and then use pg_dump -a in order to load
the data.
The schema scripts do not contain the creation of any sequences, because
sequences are passed as part of pg_dump -a, and then they have the correct
values, whereas if we create them beforehand, they will retain the wrong
value.
Thus, I do the following for each database/owner pair in the old installation:
1) Connect to new installation as postgres superuser and drop the database.
2) Connect as the owner of the database and create the database.
3) Connect to the newly created database as its owner. Run the schema script.
4) pg_dump -a from the old installation's database, and pipe the result to
the new installation.
Now, this works great for every database I own, but not for databases owned
by user "royya". Since I removed the definition of sequences, the table
which have fields with DEFAULT nextval('sequence') refuse to be created,
claiming that sequence does not exist.
The same script, if ran from my own user, works perfectly well, and allows
me to create the table without having the sequence pre-made. And well it
should - the sequence name is merely a string parameter to a function. Why
should it check its existence?
I checked and found that the difference between user "royya" and my own
user was that he is not allowed to create users. If I use ALTER USER royya
CREATEUSER, giving him that privilege, the scripts start working for him,
too.
Can anybody shed light? I know that at worst, I can give him the privilege
only for the period of the migration, and then revoke it. However, I think
this is a bug and should not happen at all.
Here is an example session:
test=> \connect - royya
connecting as new user: royya
test=> CREATE TABLE example( field1 int4 DEFAULT nextval( 's' ) );
ERROR: pg_aclcheck: class "s" not found
test=> \connect - herouth
connecting as new user: herouth
test=> CREATE TABLE example( field1 int4 DEFAULT nextval( 's' ) );
CREATE
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
