On Wed, Feb 10, 2010 at 10:47 AM, Josh Kupershmidt <schmiddy@xxxxxxxxx> wrote:
JoshOn Wed, Feb 10, 2010 at 10:09 AM, Iñigo Martinez Lasala <imartinez@xxxxxxxxxxxx> 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.
