Re: Restore 1 Table from pg_dumpall?

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