I observe the following issue on PostgreSQL 9.0.4 on at least the following platforms:
* FreeBSD 6.3 (amd64) `uname -a`: FreeBSD <hostname> 6.3-STABLE FreeBSD 6.3-STABLE #1: Fri May 30 18:11:47 PDT 2008 root@<hostname>:/data/obj/data/home/<username>/symbols/builddir_amd64/usr/src/sys/MESSAGING_GATEWAY.amd64_INSTALL amd64 * Mac OS X 10.6.8 (i386) `uname -a`: Darwin joule 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:33:36 PDT 2011; root:xnu-1504.15.3~1/RELEASE_I386 i386 i386 * semi-current Debian testing (amd64) `uname -a`: Linux gray 2.6.30-2-amd64 #1 SMP Mon Dec 7 05:21:45 UTC 2009 x86_64 GNU/Linux If the comment/description of a database object (table, function, etc.) ends in a backslash (which generally works fine otherwise), then pg_restore is unable to completely restore a custom-format dump of the schema. pg_restore does not complain, but silently(!) stops issuing DDL statements to the server starting with the first "COMMENT ON …" statement that would have set an object comment/description ending in a backslash. Reproduce as follows: $ createdb test0 $ createdb test1 $ psql -c "CREATE TABLE bar (); COMMENT ON TABLE bar IS 'bar\\';" test0 COMMENT $ psql -c "CREATE TABLE foo (); COMMENT ON TABLE foo IS 'foo';" test0 COMMENT $ pg_dump --format custom --file test0.pg_dump --schema-only test0 $ pg_restore -d test1 test0.pg_dump $ psql -c '\dt+' test0 List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+--------+---------+------------- public | bar | table | julian | 0 bytes | bar\ public | foo | table | julian | 0 bytes | foo (2 rows) $ psql -c '\dt+' test1 List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+--------+---------+------------- public | bar | table | julian | 0 bytes | (1 row) This also happens with PostgreSQL 8.4. To demonstrate that this is not an academic issue, these are a few functions I have defined, and their comments: List of functions -[ RECORD 1 ]-------+------------------------------------------------------------------------ Schema | public Name | escape_are ... : ... Description | escape advanced regexp (ARE) special characters: .*+?|[](){}^$\ -[ RECORD 2 ]-------+------------------------------------------------------------------------ Schema | public Name | escape_control ... : ... Description | escape control characters: \a\b\t\n\v\f\r\e\\ -[ RECORD 3 ]-------+------------------------------------------------------------------------ Schema | public Name | escape_like ... : ... Description | escape LIKE pattern special characters: %_\ I have worked around the issue by appending a space character to each of those function descriptions. What makes the problem really bad is that it silently renders your custom-format database dumps (which pg_dump creates just fine) useless, which you notice only after you do a restore (without an error being thrown) and your restored database being incomplete. -Julian
signature.asc
Description: This is a digitally signed message part.