CODEWITHSUNDEEP
postgresql
S_M
S_M

Reputation: 31

Extract only one specific table from a compressed Postgres sql dump

I have a big posgresql compressed dump from which I need to extract the data from one table. The compressed posgresql dump is 25G and if I uncompress it it will become 300GB sql file. Need help to get data about one particular table without uncompressing the dump. is it possible?

Upvotes: 0

Views: 1151

Answers (1)

jjanes
jjanes

Reputation: 44137

Assuming it is a plain format dump, you can use perl (or sed or awk or ...) to pull out the data section for one table. The .. operator says where to start, and where to stop.

zcat dump.sql.gz | perl -ne 'print if /^COPY public.pgbench_accounts/ .. /^\\\.$/'| pgbench_accounts.sql

It is possible that this will lead to false matches in weird/malicious circumstances, like if the string 'COPY public.pgbench_accounts' were to occur as a value in the first column of some table.

To get the non-data part of the table definition, I would just use nearly the reverse of the above to strip out all of the data and get just the definitions (of all objects, not just one), which should generate a small enough file to be edited by hand to pull out just the one you want.

zcat dump.sql.gz | perl -lne 'print if not /^COPY /../^\\\.$/' > edit_me.sql

If you don't like the manual work, you could push just the definition-only part through psql into some dummy database, then redump out just the table you want.

createdb foobar
zcat dump.sql.gz | perl -lne 'print if not /^COPY /../^\\\.$/' | psql foobar
pg_dump foobar -t pgbench_accounts > pgbench_accounts.def.sql

You might want to dump pre-data and post-data sections separately, so you can populate the table before creating indexes. Again, this is subject getting confused by weird/malicious content.

Upvotes: 1

Related Questions