On 01/22/2013 09:57 AM, Rich Shepard wrote:
I neglected to dump a single table before adding additional rows to
it via
psql. Naturally, I messed up the table. I have a full pg_dumpall of all
three databases and all their tables in a single .sql file from 2 days
ago.
The file is 386M in size and emacs is taking a very long time to move
around
in it.
Is there a way I can extract a single table's schema and data from the
full backup? If so, I can then drop the fubar'd table and do it correctly
this time.
If you dump using the custom format you can use pg_restore to output the
schema, data or both for a specified table.
If you have the basic text dump, then sed works reasonably well:
Table definition:
sed -n '/^CREATE TABLE yourtable (/,/^);/p' yourdump > tableschema.psql
Data:
sed -n '/^COPY yourtable (/,/^\\\./p' yourdump > tabledata.psql
This is imperfect as it doesn't include the various indexes,
constraints, sequences, etc. but it gets you most of the way there. You
can probably extract the relevant alterations with:
sed -n '/^ALTER TABLE .*yourtable$/,/;$/p'
And you can grep for index creation.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
