dump + restore didn't include schemas
Hi folks,
I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the
schemas which existed in the original databases were not created in
the new database.
The CREATE SCHEMA commands are there...
\connect "freebsddiary.org"
--
-- PostgreSQL database dump
--
SET SESSION AUTHORIZATION 'www';
--
-- TOC entry 2 (OID 3642999)
-- Name: www; Type: SCHEMA; Schema: -; Owner: www
--
CREATE SCHEMA www;
But not in the resulting database:
freebsddiary.org=# set search_path = www, phorum, public, pg_catalog;
ERROR: schema "www" does not exist
freebsddiary.org=# set search_path = phorum, public, pg_catalog;
ERROR: schema "phorum" does not exist
freebsddiary.org=#
New issue? Known bug?
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
"Dan Langille" <dan@langille.org> writes:
I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the
schemas which existed in the original databases were not created in
the new database.
New issue? Known bug?
New one on me. Look at the log output from when the dump was being
restored. I suppose there must be an error message from the CREATE
SCHEMA commands --- what is it?
regards, tom lane
On 30 Jan 2004 at 23:34, Tom Lane wrote:
"Dan Langille" <dan@langille.org> writes:
I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the
schemas which existed in the original databases were not created in
the new database.
New issue? Known bug?New one on me. Look at the log output from when the dump was being
restored. I suppose there must be an error message from the CREATE
SCHEMA commands --- what is it?
I found three of these messages in /var/log/messages on the box which
had the problem.
ERROR: permission denied for database pg_freebsddiary.org.schemas
Nothing else. I don't have the output from the restore. I tried
reproducing the problem on another box but schemas were always
correctly created. I can't reproduce the problem situation. Sorry.
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
"Dan Langille" <dan@langille.org> writes:
I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the
schemas which existed in the original databases were not created in
the new database.
I found three of these messages in /var/log/messages on the box which
had the problem.
ERROR: permission denied for database pg_freebsddiary.org.schemas
Oh, I bet I know what this is: the owners of those schemas don't have
CREATE SCHEMA privileges, right? You made the schemas as superuser with
CREATE SCHEMA foo AUTHORIZATION bar.
7.4's pg_dump will use AUTHORIZATION so that situations like this
restore correctly, but 7.3's pg_dump is stupid and tries to create the
schema as its owner.
In general I recommend that during an upgrade, you use the new version's
pg_dump to dump from the old server. This way you get the benefit of
whatever improvements have been made in pg_dump since the previous
release.
regards, tom lane
On 31 Jan 2004 at 11:56, Tom Lane wrote:
"Dan Langille" <dan@langille.org> writes:
I upgraded two servers today from 7.3.* to 7.4.1. In both cases, the
schemas which existed in the original databases were not created in
the new database.I found three of these messages in /var/log/messages on the box which
had the problem.ERROR: permission denied for database pg_freebsddiary.org.schemas
Oh, I bet I know what this is: the owners of those schemas don't have
CREATE SCHEMA privileges, right? You made the schemas as superuser with
CREATE SCHEMA foo AUTHORIZATION bar.7.4's pg_dump will use AUTHORIZATION so that situations like this
restore correctly, but 7.3's pg_dump is stupid and tries to create the
schema as its owner.
Does it matter that I used pg_dumpall?
In general I recommend that during an upgrade, you use the new version's
pg_dump to dump from the old server. This way you get the benefit of
whatever improvements have been made in pg_dump since the previous
release.
Should that recommendation be added to the "If You Are Upgrading"
section of INSTALL?
--
Dan Langille : http://www.langille.org/
BSDCan - http://www.bsdcan.org/
"Dan Langille" <dan@langille.org> writes:
On 31 Jan 2004 at 11:56, Tom Lane wrote:
7.4's pg_dump will use AUTHORIZATION so that situations like this
restore correctly, but 7.3's pg_dump is stupid and tries to create the
schema as its owner.
Does it matter that I used pg_dumpall?
No.
regards, tom lane