Re: 8.0 beta1: pg_dump/restore failing - Mailing list pgsql-bugs

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