Thread

1. pg_dumpall's output not totally usable

   Olivier Jeannet <jeannet@montrouge.tt.slb.com> — 2000-05-29T19:46:40Z

   Your name		:	Olivier Jeannet
   Your email address	:       ojeannet@slb.com
   
   
   System Configuration
   ---------------------
     Architecture (example: Intel Pentium)  	: Intel Pentium
   
     Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.2.13 ELF
   
     PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-7.0
   
     Compiler used (example:  gcc 2.8.0)		: gcc version egcs-2.91.66
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   
   I have downloaded PostgreSQL V7.0, compiled and installed it (it was
   quite easy) on another location than 6.5 .
   
   I want to save the database used in the 6.5 version to put it on the
   7.0 version. On 6.5, I typed "pg_dumpall >/tmp/db.out" . Then, on 7.0,
   I typed "psql -d template1 -f /tmp/db.out" (I followed the INSTALL file).
   By the way the database is not very big, the output is 2.5 MB.
   
   The problem is that the output of pg_dumpall doesn't totally work, and I
   found that some statements are not in the proper order. So I edited the
   output, made some cuts and pastes, and was able to successfully restore the
   database. The few statements I had to put earlier in the output file are
   "CREATE FUNCTION" statements, like :
   
    CREATE FUNCTION "countadm_moduletable" (int4 ) RETURNS int4 AS 'SELECT
    count(*) FROM ADM_MODULETABLE WHERE MODULETABLEID = $1' LANGUAGE 'SQL';
   
   Does anyone know about this problem ?
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible: 
   ----------------------------------------------------------------------
   
   I can give the original scripts used to create the database, they are
   quite small :
   
   -rw-r--r--           6795 May 15 10:07 STAR_createTables.sql
   -rw-r--r--           1500 May 15 10:07 STAR_createIndexes.sql
   -rw-r--r--           7586 May 22 17:55 STAR_fillTables.sql
   -rw-r--r--           2611 May 15 12:27 SERPME_createTables.sql
   -rw-r--r--           1188 May 15 12:31 SERPME_createIndexes.sql
   -rw-r--r--            755 May 15 12:33 SERPME_createTransacRecords.sql
   
   
   If you know how this problem might be fixed, list the solution below:
   ---------------------------------------------------------------------
   
   No idea.
   
   
   -- 
   Olivier Jeannet - POS Servers team
     My Windoz PC didn't work well because I had not rebooted it enough...
   
   

2. Re: pg_dumpall's output not totally usable

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-29T21:17:25Z

   Olivier Jeannet <jeannet@montrouge.tt.slb.com> writes:
   > The problem is that the output of pg_dumpall doesn't totally work, and I
   > found that some statements are not in the proper order. So I edited the
   > output, made some cuts and pastes, and was able to successfully restore the
   > database. The few statements I had to put earlier in the output file are
   > "CREATE FUNCTION" statements, like :
   >  CREATE FUNCTION "countadm_moduletable" (int4 ) RETURNS int4 AS 'SELECT
   >  count(*) FROM ADM_MODULETABLE WHERE MODULETABLEID = $1' LANGUAGE 'SQL';
   > Does anyone know about this problem ?
   
   Yeah, it's a known problem... pg_dump is pretty stupid about the order
   it dumps stuff in.  There's been talk of analyzing the dependencies
   among all items in the database, but that looks *hard*.  A 99% solution
   is to dump things in order by OID, and that's what we'll probably do
   sometime soon.
   
   			regards, tom lane