Thread: 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