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.
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? Thank you, Josh. -----Original Message----- From: Josh Kupershmidt <schmi...@gmail.com> To: Iñigo Martinez Lasala <imarti...@vectorsf.com> Cc: pgsql-admin <pgsql-admin@postgresql.org> Subject: Re: [ADMIN] Disabling triggers with psql (gforge 7.4 to 8.2 migration) Date: Wed, 10 Feb 2010 09:55:15 -0500 On Feb 10, 2010, at 4:30 AM, Iñigo Martinez Lasala wrote: > > Third one, and we haven't been able to fix it, is we cannot restore > data dump via psql due to psql does not have an option to disable > triggers. So, when restoring data, it fails. So... is there any way to > disable in a psql session all triggers same way like with pg_restore > --disable-triggers? Or can we convert plain text dump to be processed > by pg_restore? Perhaps a table by table trigger disabling script? > How about: 1. restore your schema, with triggers, using a pg_dump --schema-only ... ; pg_restore ... 2. Create a psql-loadable data-only dump using pg_dump --data-only --disable-triggers --format=p ... 3. clean the data-only dump and restore Or, you could do a full pg_dump -Fc ... of your database, then use pg_restore to restore the compressed dumpfile into a plaintext file (i.e. pg_restore [options] filename). Then run your cleanup on the plaintext file, and reload. Josh
