Postgres Pro
Downloads
Home   >   mailing lists

Thread: 8.0 beta1: pg_dump/restore failing

8.0 beta1: pg_dump/restore failing

From
Edmund Bacon
Date:
pg_dump/restore in 8.0beta1 are not working well with formats other
thans plain text:



$ pg_restore --version
pg_restore (PostgreSQL) 8.0.0beta1
$ pg_dump --version
pg_dump (PostgreSQL) 8.0.0beta1

$ createdb test_8
CREATE DATABASE
$ createlang plpgsql test_8

$ psql test_8 -c \
 > " create function foo() returns int as 'begin return 1; end;'
language 'plpgsql'"
CREATE FUNCTION

$ psql test_8 -c "select foo()";
  foo
-----
    1
(1 row)



$ pg_dump --format=c --file=test_8.dump test_8

$ pg_restore --format=c --clean --dbname=test_8 test_8.dump
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar-quoted string at or near "$$begin return 1;" at
character 115
pg_restore: WARNING:  there is no transaction in progress
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar-quoted string at or near "$$
     LANGUAGE plpgsql;" at character 1
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" does not exist
--
-- PostgreSQL database dump complete
--

WARNING, errors ignored on restore: 10
$

Note that there was a problem restoring the function.
Did --clean remove the public schema?
Hmm ...  maybe there's a problem with --clean

$ dropdb test_8
DROP DATABASE
$ pg_restore --format=c --create --dbname=test test_8.dump
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar-quoted string at or near "$$begin return 1;" at
character 115
pg_restore: WARNING:  there is no transaction in progress
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar-quoted string at or near "$$
     LANGUAGE plpgsql;" at character 1
pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.foo() does not exist
--
-- PostgreSQL database dump complete
--

WARNING, errors ignored on restore: 3
$


Yep, but we still can't restore the function.

tests with format=t show the same behaviour.
tests with format=p succeed

--
Edmund Bacon <ebacon@onesystem.com>

Re: 8.0 beta1: pg_dump/restore failing

From
Tom Lane
Date:
Edmund Bacon <ebacon@onesystem.com> writes:
> pg_dump/restore in 8.0beta1 are not working well with formats other
> thans plain text:

What seems to be broken is --clean mode, because it drops and fails to
restore the public schema.  I can see no reason that it wouldn't
work exactly the same regardless of dump format though.  Are you
sure you didn't get confused because things were already broken
once the destination DB's public schema was gone?

            regards, tom lane

Re: 8.0 beta1: pg_dump/restore failing

From
Tom Lane
Date:
Edmund Bacon <ebacon@onesystem.com> writes:
> The problem is that pg_restore is not correctly recognizing the ending
> $$ quotes on functions:

This is a known bug.  There was a preliminary patch posted for it a
couple days ago.

            regards, tom lane

Re: 8.0 beta1: pg_dump/restore failing

From
Edmund Bacon
Date:
Yes. My appologies for the poor bug report.

I have tried this on RedHat 9, gcc 3.2.2 x86 and HP-UX 10.20 gcc 3.2.3
pa-risc

The problem is that pg_restore is not correctly recognizing the ending
$$ quotes on functions:  Note that in the pg_restore text output at the
bottom of the message, the closing $$ quotes are there, but pg_restore
using the custom format (or tar format) doesn't recognize them.  Doing a
strings -a on test.dump also shows the closing $$ quotes.

e.g.

# -- start with a fresh database:

$ createdb test
CREATE DATABASE

# -- do a restore
$ pg_restore --format=c --dbname=test test.dump
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar
-quoted string at or near "$$begin return 1;" at character 115
pg_restore: WARNING:  there is no transaction in progress
pg_restore: [archiver (db)] could not execute query: ERROR:
unterminated dollar
-quoted string at or near "$$
     LANGUAGE plpgsql;" at character 1
pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.foo
() does not exist
--
-- PostgreSQL database dump complete
--

WARNING, errors ignored on restore: 3

# -- try again with fresh database, but use psql and output from pg_dump

$ dropdb test
DROP DATABASE
$ createdb test
CREATE DATABASE

$ pg_restore test.dump | psql test -f -
SET
SET
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
ALTER FUNCTION
REVOKE
REVOKE
GRANT
GRANT
$

#### -- and here is the pg_restore output

$ pg_restore test.dump
--
-- PostgreSQL database dump
--

[ ### snip creating language plpgsql, etc ### ]

--
-- Name: foo(); Type: FUNCTION; Schema: public; Owner: ebacon
--

CREATE FUNCTION foo() RETURNS integer
     AS $$begin return 1; end;$$
     LANGUAGE plpgsql;


ALTER FUNCTION public.foo() OWNER TO ebacon;

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

$

Tom Lane wrote:
> Edmund Bacon <ebacon@onesystem.com> writes:
>
>>pg_dump/restore in 8.0beta1 are not working well with formats other
>>thans plain text:
>
>
> What seems to be broken is --clean mode, because it drops and fails to
> restore the public schema.  I can see no reason that it wouldn't
> work exactly the same regardless of dump format though.  Are you
> sure you didn't get confused because things were already broken
> once the destination DB's public schema was gone?
>
>             regards, tom lane

--
Edmund Bacon <ebacon@onesystem.com>

Re: 8.0 beta1: pg_dump/restore failing

From
Bruce Momjian
Date:
Yes, we realize this is a problem and are working on a solution for beta2.

---------------------------------------------------------------------------

Edmund Bacon wrote:
> Yes. My appologies for the poor bug report.
>
> I have tried this on RedHat 9, gcc 3.2.2 x86 and HP-UX 10.20 gcc 3.2.3
> pa-risc
>
> The problem is that pg_restore is not correctly recognizing the ending
> $$ quotes on functions:  Note that in the pg_restore text output at the
> bottom of the message, the closing $$ quotes are there, but pg_restore
> using the custom format (or tar format) doesn't recognize them.  Doing a
> strings -a on test.dump also shows the closing $$ quotes.
>
> e.g.
>
> # -- start with a fresh database:
>
> $ createdb test
> CREATE DATABASE
>
> # -- do a restore
> $ pg_restore --format=c --dbname=test test.dump
> pg_restore: [archiver (db)] could not execute query: ERROR:
> unterminated dollar
> -quoted string at or near "$$begin return 1;" at character 115
> pg_restore: WARNING:  there is no transaction in progress
> pg_restore: [archiver (db)] could not execute query: ERROR:
> unterminated dollar
> -quoted string at or near "$$
>      LANGUAGE plpgsql;" at character 1
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> public.foo
> () does not exist
> --
> -- PostgreSQL database dump complete
> --
>
> WARNING, errors ignored on restore: 3
>
> # -- try again with fresh database, but use psql and output from pg_dump
>
> $ dropdb test
> DROP DATABASE
> $ createdb test
> CREATE DATABASE
>
> $ pg_restore test.dump | psql test -f -
> SET
> SET
> COMMENT
> SET
> CREATE FUNCTION
> ALTER FUNCTION
> CREATE FUNCTION
> ALTER FUNCTION
> CREATE LANGUAGE
> CREATE FUNCTION
> ALTER FUNCTION
> REVOKE
> REVOKE
> GRANT
> GRANT
> $
>
> #### -- and here is the pg_restore output
>
> $ pg_restore test.dump
> --
> -- PostgreSQL database dump
> --
>
> [ ### snip creating language plpgsql, etc ### ]
>
> --
> -- Name: foo(); Type: FUNCTION; Schema: public; Owner: ebacon
> --
>
> CREATE FUNCTION foo() RETURNS integer
>      AS $$begin return 1; end;$$
>      LANGUAGE plpgsql;
>
>
> ALTER FUNCTION public.foo() OWNER TO ebacon;
>
> --
> -- Name: public; Type: ACL; Schema: -; Owner: postgres
> --
>
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
> REVOKE ALL ON SCHEMA public FROM postgres;
> GRANT ALL ON SCHEMA public TO postgres;
> GRANT ALL ON SCHEMA public TO PUBLIC;
>
>
> --
> -- PostgreSQL database dump complete
> --
>
> $
>
> Tom Lane wrote:
> > Edmund Bacon <ebacon@onesystem.com> writes:
> >
> >>pg_dump/restore in 8.0beta1 are not working well with formats other
> >>thans plain text:
> >
> >
> > What seems to be broken is --clean mode, because it drops and fails to
> > restore the public schema.  I can see no reason that it wouldn't
> > work exactly the same regardless of dump format though.  Are you
> > sure you didn't get confused because things were already broken
> > once the destination DB's public schema was gone?
> >
> >             regards, tom lane
>
> --
> Edmund Bacon <ebacon@onesystem.com>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073