Thread

1. upgrade path / versioning roles

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-09-01T16:41:34Z

   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) as rolname, nspacl FROM pg_namespace
   
   
   Cheers,
   
   Patrick
   
   

2. Re: upgrade path / versioning roles

   Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-01T18:31:15Z

   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
   
   

3. Re: upgrade path / versioning roles

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-09-01T18:52:28Z

   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
   
   

4. Re: upgrade path / versioning roles

   Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-01T18:59:37Z

   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
   
   

5. Re: upgrade path / versioning roles

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-09-02T18:25:23Z

   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