Thread: restore pg_dumpall problem with update 7.0.3 to 7.1.1

Sorry for the lengthy post, just want to make sure I describe the situation
thoroughly.

Running Redhat 7.1, had pg 7.0.3 running just fine.
In 7.0.3, I did 'pg_dumpall > dump_file'. Renamed /usr/local/pgsql to
/usr/local/pgsql.old and my data dir similarly (just in case :).  Then I
installed 7.1.1; configure, gmake, gmake test (all 76 tests passed), gmake
install.  'initdb -D /home/data/postgres/data' (my data dir). Everything was
ok at this point, so I started postmaster via the script in /etc/rc.d/init.d
(is it ok to continue to use the start script from 7.0.3?)

Great! Postmaster was running (although the data dir looked mighty different
than in 7.0.3 -- I assume that's why I needed to do a dump and restore?),
all I had left to do is a restore.  As the pg user, 'psql -d template1 -f
dump_file', here's what I got (several times):

  [postgres@hostname postgres]$ psql -d template1 -f dump_file
  You are now connected to database template1.
  SELECT
  DELETE 0
  DROP
  DELETE 0
  You are now connected to database template1 as user postgres.
  psql:dump_file:11: ERROR:  CREATE DATABASE: source database "template1" is
being accessed by other users
  psql:dump_file:12: \connect: FATAL 1:  Database "eamr1" does not exist in
the system catalog.
  [postgres@hostname postgres]$

Here are the first few lines of dump_file (output from pg_dumpall before the
upgrade):

  \connect template1
  select datdba into table tmp_pg_shadow       from pg_database where
datname = 't
  emplate1';
  delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
  drop table tmp_pg_shadow;
  copy pg_shadow from stdin;
  \.
  delete from pg_group;
  copy pg_group from stdin;
  \.
  \connect template1 postgres
  create database "eamr1";
  \connect eamr1 postgres
  \connect - postgres
  CREATE SEQUENCE "change_hist_chid_seq" start 15 increment 1 maxvalue
2147483647
  minvalue 1  cache 1 ;
[etc, etc...]

As I said, using psql to restore this failed a few times, so I just
connected to template1 and manually created the first database "eamr1".
Then, while still in psql, I did '\i dump_file'.  THEN my databases were
restored.

My questions are these:
 Was there a problem with the output from pg_dumpall (above)?
 Although it seems to work just fine, is the start script from 7.0.3 ok to
use?
 Each database now consists of 69 files (67 of them are named with all
numbers). Did the physical database storage format change between 0.3 and
1.1?  (I cannot get to postgresql.org all evening to check this)
 Is this build ok for production use, or should I not trust it in light of
the problems restoring?
 Somewhat un-related (hopefully), why did phpPgAdmin stop working? In fact,
I renamed the /usr/local/pgsql.old and data directories to their original
state, re-started 0.3, and phpPgAdmin STILL did not work.

Thanks,

holtzman

"holtzman" <fill-in-the-blanks_@_._> writes:
> [pg_dumpall script fails with]
>   psql:dump_file:11: ERROR:  CREATE DATABASE: source database "template1" is
> being accessed by other users

Hm.  There is a bit of a race condition here, perhaps --- the pg_dumpall
script does

    \connect template1 $DBOWNER
    CREATE DATABASE foo;

and in the case of the very first database in the dump, the \connect is
redundant: before the \connect we were connected to template1 already.
In that case the old backend might not have finished shutting down by
the time the CREATE is issued, leading to this complaint.

This problem won't arise in dumps made with 7.1 pg_dump since they
will refer to template0, not template1, as the source for the CREATE
DATABASE copy ... and there shouldn't ever be anyone connected to
template0.

Evidently there is a risk with 7.0 dump scripts being brought forward,
however.  Removing the redundant \connect command may do as a workaround
for them.

            regards, tom lane