Thread: creation permissions when moving from 7.2 -> 7.4

I am attempting to transfer a cluster of 28 databases from one machine to
another.  The former machine runs PostgreSQL 7.2.3; the latter 7.4.2.

I first tried a filesystem-level backup.  I stopped the old server, copied
its data directory, restarted the old server, transferred the copied data
to the new machine, and attempted to start the new server.  The new server
complained about the version mismatch.  Fair enough.

I then tried pg_dump and its ilk.  As we rely on OIDs and BLOBs, I didn't
use pg_dumpall for everything, but rather used a shell script to grab the
global stuff and to iterate through the databases with pg_dump:

  #!/bin/sh

  export PGUSER=postgres
  export PGPASSWORD=************

  pg_dumpall -g > globals.sql

  for i in $(psql template1 -t -A -c \
    "select datname from pg_database;"); do
    echo "Dumping catalog [$i] ..."
    pg_dump -b -o -Ft $i > $i.tar
  done

I then attempted to restore this data on the new machine:

  #!/bin/sh

  export PGUSER=postgres

  psql template1 -f globals.sql

  for i in *.tar; do
    echo "Restoring from [$i] ..."
    pg_restore -d template1 -C $i.tar
  done

The new server's pg_hba.conf's first line is "local all all trust".

Most of the databases are owned by usesysid #1 ("postgres") and were
restored properly.  The ones owned by a different user did not:

  Restoring from [ajp.tar] ...
  pg_restore: [archiver (db)] could not execute query: ERROR:
  permission denied to create database

Given that (a) I'm using PGUSER=postgres and (b) my pg_hba.conf should be
sufficiently liberal anyway, I'm wondering why this is not working.

Also, template0 failed due to a null tar file, and template1 failed due to
it already existing.  Would I be correct in assuming that the globals.sql
step takes care of the necessary items in template1, and that I can leave
template0 alone altogether?

--
Kevin DeGraaf

Kevin DeGraaf <kevin@kevindegraaf.net> writes:
>   Restoring from [ajp.tar] ...
>   pg_restore: [archiver (db)] could not execute query: ERROR:
>   permission denied to create database

> Given that (a) I'm using PGUSER=postgres and (b) my pg_hba.conf should be
> sufficiently liberal anyway, I'm wondering why this is not working.

I think you've got the other user not permitted to create databases?
I believe 7.4's pg_dump creates a script that handles this case
correctly, but 7.2's doesn't.  (A hint for next time is that it's
usually best to use the latest available pg_dump.)

> Also, template0 failed due to a null tar file, and template1 failed due to
> it already existing.  Would I be correct in assuming that the globals.sql
> step takes care of the necessary items in template1, and that I can leave
> template0 alone altogether?

template0 is do-not-touch.  You need only worry about template1 if you
created stuff in it to have copied to other databases by CREATE
DATABASE.  (The most common thing of that sort is procedural languages;
you'd be best advised to use "createlang" to reinsert them anyway.)

            regards, tom lane