restore single table dump from a sql file

Question

I have full dump of sql file like dump_full.sql of size 1.3GB

And it has some tables like

dancing_core_spice
dancing_sea_beast

forde_clear_one
forde_super_now

Now what i want is need to restore/fetch/dump only data of these tables from dump_full.sqlfile

psql -U postgres --table=dancing_core_spice dump_full.sql > dancing_core_spice.sql

i tried the above command but it is not working

So can anyone please let me know how to take the dump of only single table from the sql file(full dump)

score 2 · Answer 1 · edited May 23 '17 at 12:17

2

For these type of operations the dump file should be in the postgresql custom format created with pg_dump:

pg_dump -Fc

Then you can restore a single table with pg_restore

pg_restore --table=dancing_core_spice dump_full.sql > dancing_core_spice.sql

This question provides tips to handle your actual case:

edited May 23 '17 at 12:17

Community

answered Jan 28 '14 at 11:36

Francisco Puga

You can't use pg_restore with a SQL file as the input. – Jan 28 '14 at 11:40
It is giving me `pg_restore: [archiver] input file appears to be a text format dump. Please use psql.` – Shiva Krishna Bavandla Jan 28 '14 at 11:45
We can restore the dropped table from a script in three step process. First extract the table structure from the sql dump , secondly extract the data from the sql file. Third step is to run the sql's in order to create table and restore . a) sed -n '/CREATE TABLE tblname/,/);/p' /tmp/dump.sql >/tmp/ct.sql b)sed -n '/COPY tblnamel/,/\\./p' /tmp/dump.sql > /tmp/cv.sql c)psql -U postgres >\i /tmp/ct.sql >\i /tmp/cv.sql >\q – sramay Jun 24 '15 at 09:25
Thanks for the tip sramay. Feel free to open a new answer. But take into account that sometimes instead of COPY the dump is done with INSERT statements or even one INSERT per row statement – Francisco Puga Jun 24 '15 at 09:45

1 Answers1