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

$ 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 (AT) onesystem (DOT) com>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Edmund Bacon <ebacon (AT) onesystem (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Edmund Bacon <ebacon (AT) onesystem (DOT) com> writes:
This is a known bug. There was a preliminary patch posted for it a
couple days ago.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

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 (AT) onesystem (DOT) com>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Yes, we realize this is a problem and are working on a solution for beta2.

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

Edmund Bacon wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org