creation permissions when moving from 7.2 -> 7.4

From: Kevin DeGraaf <kevin(at)kevindegraaf(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: creation permissions when moving from 7.2 -> 7.4
Date: 2004-05-06 21:16:15
Message-ID: Pine.LNX.4.58.0405061647020.7387@ferrari
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2004-05-06 21:19:26 Re: regexp replace?
Previous Message Lee Harr 2004-05-06 21:13:32 Re: 7.2 or 7.4 for critical data?