Andrew Sullivan <ajs at crankycanuck.ca> writes:
> On Fri, Jan 11, 2008 at 09:34:24AM -0500, Bill Moran wrote:
>> 
>> I do this quite frequently (to grab data out of our production environment
>> to our lab for testing purposes, for example).  I've yet to see any
>> problems ... what is it that's supposed to go awry when I do this?
>> What is the danger?
>
> Slony has fooled with the catalog tables.  You get an unrestorable dump. 
> Some versions of pg_dump actually won't dump as a result of this -- they
> notice the problem, and quit.

An oddity is that sometimes pg_dump will complain, and sometimes it
won't.

Note that the trouble is more precise in nature than merely
"unrestorable dump;" the trouble is that when pg_dump tries to dump
the schema, it may encounter problems due to the way that triggers get
re-attached (on subscribers) to the primary key index rather than to
the table.

- It shouldn't mean that the schema dump of the table loses columns;

- It definitely doesn't mean that the portion of pg_dump that generates
  the COPY statement has anything improper about it;

- The trouble *is* that the trigger trickery may corrupt that part of
  the schema dump.

> There's a tool that ships with Slony that is supposed to allow dumping on a
> replica.  It doesn't seem to have had a lot of testing.  It'd be nice to get
> some reports of its utility and reliability.

Actually, it's now used in the regression test that tests out log
shipping, so I think that the claim that it "doesn't seem to have had
a lot of testing" is becoming unfair.

Note that this all is to change in the next major Slony-I release;
with the changes in trigger support in PG 8.3, there is no longer a
need to fool with the system catalog, so pg_dump becomes usable again.
-- 
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/unix.html
"Some people, when confronted with a Unix problem, think "I know, I'll
use sed." Now they have two problems."  -- Jamie Zawinski