[Resending, forgot to CC list] On Wed, Feb 10, 2010 at 10:47 AM, Josh Kupershmidt <schmi...@gmail.com>wrote:
> > On Wed, Feb 10, 2010 at 10:09 AM, Iñigo Martinez Lasala < > imarti...@vectorsf.com> wrote: > >> Two questions. >> >> I could, of course, create a data-only dump (in fact I've already done >> it). However, when restoring, I cannot use pg_restore since it's in plain >> format, don't you? pg_restore only works with tar or compressed formats, I >> think. I could restore data-only dump with psql, but then I've to disable >> triggers and psql does not have an option in order to disable them. >> >> > Here's what I meant by the first routine. Let's dump database "test" and > restore into database "restoration". > > pg_dump --schema-only -Ujosh --format=c --file=test.schema.pgdump test > pg_restore -Ujosh --dbname=restoration test.schema.pgdump > pg_dump --data-only -Ujosh --format=p --disable-triggers > --file=test.data.pgdump test > # clean up test.data.pgdump here > psql -Ujosh restoration < test.data.pgdump > > So for the restoration of the actual data, you'd use psql, but the > disabling of triggers would be handled for you (you should see ALTER TABLE > ... DISABLE TRIGGER ALL; and ALTER TABLE ... ENABLE TRIGGER ALL; or similar > in test.data.pgdump) > > Second one. You say I could restore a compressed dumpfile into a plaintext >> file. Is this possible? How? And after cleaning this plaintext file, how do >> I restore it again into database without using psql since pg_restore only >> accept tar or compressed file formats? >> > > To turn a pg_dump file which was dumped with, say, --format=c into a > plaintext file loadable by SQL: > > pg_dump -Ujosh --format=c --file=test.Fc.pgdump test > pg_restore test.Fc.pgdump > test.plaintext.pgdump > # clean up test.plaintext.pgdump here > psql -Ujosh restoration < test.plaintext.pgdump > > This was the second option I mentioned. You would then have to use psql to > restore this plaintext file. You might be able to jump through some hoops > and turn the plaintext dump back into a pg_restore compressed dump, but I > don't see any point in this -- the plaintext dump here should have the > CREATE TRIGGER statements after the table creation and population steps, > which is likely exactly what you want. > > Josh >
