Thread: upgrade path / versioning roles

I think we still recommend using *new* pg_dump to dump *old* server when
upgrading. If one tries that with today's pg_dump (8.1beta1) against 
a 8.1devel server of 6 May, i.e., predating roles, one gets:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  relation "pg_catalog.pg_roles" does not exist
pg_dump: The command was: SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner)
asrolname, nspacl FROM pg_namespace
 


Cheers,

Patrick

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> I think we still recommend using *new* pg_dump to dump *old* server when
> upgrading. If one tries that with today's pg_dump (8.1beta1) against 
> a 8.1devel server of 6 May, i.e., predating roles, one gets:

[ shrug... ]  The current code is expecting that "8.1" means what it
means today.  For dumping from a between-releases snapshot, you'd better
use the pg_dump from the same snapshot.
        regards, tom lane

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> I tried the fix mentioned in the earlier message to encourage
> validation. Now dumping this "fixed" database, and loadinging it into the
> new database gives:

> ALTER FUNCTION
> psql:./huge.db:4403: ERROR:  function plpgsql_validator(oid) does not exist
> CREATE FUNCTION
> ALTER FUNCTION
> psql:./huge.db:4517: ERROR:  language "plpgsql" does not exist
> HINT:  You need to use "createlang" to load the language into the database.

Ah, right, *that's* why it's a good idea to have the dependency from the
language to the function ;-) ... else there's no guarantee pg_dump will
dump them in the right order.  If you want you could add a suitable
pg_depend row.
        regards, tom lane

On Thu, Sep 01, 2005 at 02:31:15PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > I think we still recommend using *new* pg_dump to dump *old* server when
> > upgrading. If one tries that with today's pg_dump (8.1beta1) against 
> > a 8.1devel server of 6 May, i.e., predating roles, one gets:
> 
> [ shrug... ]  The current code is expecting that "8.1" means what it
> means today.  For dumping from a between-releases snapshot, you'd better
> use the pg_dump from the same snapshot.

OK - that way around just gets the NOTICE

psql:./huge.db:11: NOTICE:  SYSID can no longer be specified

- fair enough.

BTW there was an earlier thread about plpgsql validation. The aforementioned
8.1devel database has a long history and createlang was run against an
early 7 incarnation, with pg_dumpall -> new database all the way to the
present. I tried the fix mentioned in the earlier message to encourage
validation. Now dumping this "fixed" database, and loadinging it into the
new database gives:

ALTER FUNCTION
psql:./huge.db:4403: ERROR:  function plpgsql_validator(oid) does not exist
CREATE FUNCTION
ALTER FUNCTION
psql:./huge.db:4517: ERROR:  language "plpgsql" does not exist
HINT:  You need to use "createlang" to load the language into the database.

now to unfix the fix...

Cheers,

Patrick

On Thu, Sep 01, 2005 at 02:59:37PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > I tried the fix mentioned in the earlier message to encourage
> > validation. Now dumping this "fixed" database, and loadinging it into the
> > new database gives:
> 
> > ALTER FUNCTION
> > psql:./huge.db:4403: ERROR:  function plpgsql_validator(oid) does not exist
> > CREATE FUNCTION
> > ALTER FUNCTION
> > psql:./huge.db:4517: ERROR:  language "plpgsql" does not exist
> > HINT:  You need to use "createlang" to load the language into the database.
> 
> Ah, right, *that's* why it's a good idea to have the dependency from the
> language to the function ;-) ... else there's no guarantee pg_dump will
> dump them in the right order.  If you want you could add a suitable
> pg_depend row.

:-) Thanks all OK..

One other thing I noticed on this longlived database, is that one can
pg_dump new tables, and their associated sequences will be created,
but for old tables, the associated sequences need creating. The 
difference seems to be old:

Table "public.meter"id | integer | not null default nextval('"meter_id_seq"'::text)

new:

Table "public.summary"id | integer | not null default nextval('public.summary_id_seq'::text)

that the old sequence hasn't got the schema explicitly appended..

(Just in case someone else comes across this..)

Cheers,

Patrick