Thread: Restore 1 Table from pg_dumpall?
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. Rich
On Tue, 22 Jan 2013, Rich Shepard wrote: > 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. My solution: view the file in the pager I use (less), then copy relevant lines to another file opened in a text editor (joe). Rich
On 01/22/2013 10:07 AM, Rich Shepard wrote: > > On Tue, 22 Jan 2013, Rich Shepard wrote: > >> 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. > > My solution: view the file in the pager I use (less), then copy relevant > lines to another file opened in a text editor (joe). Rich, the main problem is using pg_dumpall. Unfortunately pg_dumpall has not kept up with all the other advances Postgres has had in the last decade. To set up dump based backups properly I suggest reviewing: http://www.commandprompt.com/blogs/joshua_drake/2010/07/a_better_backup_with_postgresql_using_pg_dump/ Sincerely, Joshua D. Drake > > Rich > > > -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579
On Tue, 22 Jan 2013, Joshua D. Drake wrote: > Rich, the main problem is using pg_dumpall. Unfortunately pg_dumpall has > not kept up with all the other advances Postgres has had in the last > decade. To set up dump based backups properly I suggest reviewing: > > http://www.commandprompt.com/blogs/joshua_drake/2010/07/a_better_backup_with_postgresql_using_pg_dump/ Thanks, Josh, I will read that. I have only 4 databases so I can easily back up each one individually. I make system backups daily using dirvish but understand that it's better to backup the databases separately with the appropriate tools. Regards, Rich
Rich Shepard wrote: > 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 have a server with enough free space, you could restore the whole cluster and then selectively dump what you need. -Kevin
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
On 2013-01-22, Rich Shepard <rshepard@appl-ecosys.com> 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. yeah, emacs is slow on large files. for a one-off I'd use less(1), to extract the desired table data. If I had to repeat it i'd use sed or awk -- ⚂⚃ 100% natural
On Sun, 27 Jan 2013, Jasen Betts wrote: > yeah, emacs is slow on large files. Jasen, I've noticed this over the years. > for a one-off I'd use less(1), to extract the desired table data. > If I had to repeat it i'd use sed or awk I used 'joe'. It handled the job with aplomb. Thanks, Rich
Hi, On 23 January 2013 04:57, Rich Shepard <rshepard@appl-ecosys.com> wrote: > 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. You should grep for: - CREATE TABLE - COPY statements and then note line numbers (fgrep -n). Finally, used `sed` to get the right part(s) of a file (schema + data): sed -n 'startline,endlinep' dump.sql > out.sql (ie. sed -n '10,1000p') -- Ondrej