creation permissions when moving from 7.2 -> 7.4

Поиск
Список
Период
Сортировка
От Kevin DeGraaf
Тема creation permissions when moving from 7.2 -> 7.4
Дата
Msg-id Pine.LNX.4.58.0405061647020.7387@ferrari
обсуждение исходный текст
Ответы Re: creation permissions when moving from 7.2 -> 7.4  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Lee Harr"
Дата:
Сообщение: Re: 7.2 or 7.4 for critical data?
Следующее
От: "scott.marlowe"
Дата:
Сообщение: Re: regexp replace?