Thread: Schemas not created on restore

I'm restoring a full dump from a 7.3.3 system to a new 7.4.1 system and am
getting a 'permission denied' error when it tries to create the user
schemas.  Below is the relevant portion of the dumpall, is this a problem
with permissions on the dumping system or with the restore?

SET SESSION AUTHORIZATION 'uscf';

--
-- TOC entry 2 (OID 16977)
-- Name: uscf; Type: SCHEMA; Schema: -; Owner: uscf
--

CREATE SCHEMA uscf;
--
Mike Nolan

Mike Nolan <nolan@gw.tssi.com> writes:
> I'm restoring a full dump from a 7.3.3 system to a new 7.4.1 system and am
> getting a 'permission denied' error when it tries to create the user
> schemas.

Yeah, this is an error in the 7.3 pg_dump logic for schemas.  Use the
pg_dump from the 7.4 installation to dump the 7.3 server, or manually
edit the dump file ...

            regards, tom lane

> Yeah, this is an error in the 7.3 pg_dump logic for schemas.  Use the
> pg_dump from the 7.4 installation to dump the 7.3 server, or manually
> edit the dump file ...

When I try to build 7.4.1 on that system, I get the following error
in the regression test:

/home/postgres/src/postgresql-7.4.1/src/test/regress/./tmp_check/install//usr/lo
cal/pgsql/bin/createdb: relocation error: /home/postgres/src/postgresql-7.4.1/sr
c/test/regress/./tmp_check/install//usr/local/pgsql/bin/createdb: undefined symb
ol: get_progname
pg_regress: createdb failed

Do I need to actually install 7.4, or can I just use pg_dump and pg_dumpall
from it?  (I get the same 'undefined symbol: get_progname' message when
I try that.)
--
Mike Nolan

>When I try to build 7.4.1 on that system, I get the
>following error in the regression test:
>
>/home/postgres/src/postgresql-7.4.1/src/test/regress/./tmp_chec
k/install//usr/local/pgsql/bin/createdb: relocation error:
>/home/postgres/src/postgresql-7.4.1/sr
>c/test/regress/./tmp_check/install//usr/local/pgsql/bin/created
>b: undefined symbol: get_progname
>pg_regress: createdb failed
>
>Do I need to actually install 7.4, or can I just use pg_dump
>and pg_dumpall from it?  (I get the same 'undefined symbol:
>get_progname' message when I try that.)

Got bitten by that myself yesterday. The problem seems to be,
that you configured the 7.4.1 to reside (after install)
in the same location, where your 7.3 currently is. Somehow a
wrong library or executable is used in the regression tests
from the 7.3 installation.

Quick solution: make clean; configure --prefix=/different/path; make; make
check.

Btw. make check might drop an error on the random select test,
but that error is truly random, i.e. in a second 'make check'
it might work like a charm.

But you definitly want to go for 7.4. An application we use, heavily relies
on IN and NOT IN queries with subselects
(posted here before). Until yesterday you could wander off
to fetch a <insert personal favorite beverage here>, now
these queries are about 20 times faster.... hmmm maybe I
should switch back to 7.3. to have a good excuse lingering
in the kitchen :)

  Jan