CODEWITHSUNDEEP
postgresql
Alexander Vidaurre Arroyo
Alexander Vidaurre Arroyo

Reputation: 2242

Is possible omit specifics tables from existing dump file when use pgsql for import data?

I have a dump file and I want import it, but it contains a log table with millions of records whereby I need exclude it when execute pgsql < dump_file. Note: I cannot use pg_restore

Edit: Since the best option is to edit the file manually, any suggestions to remove 650K lines from a 690K line file on windows?

Upvotes: 1

Views: 326

Answers (2)

jjanes
jjanes

Reputation: 44227

The correct way to is to fix whatever problem is preventing you from using pg_restore (I guess that you have already taken the dump in the wrong format?).

The quick and dirty way is to use a program to exclude what you don't want. I'd use perl, because that is what I would use. sed or awk have similar features, and I'm sure there are ways to do it in every other language you would care to look at.

perl -ne 'print unless /^COPY public.pgbench_accounts/../^\\\.$/' dump.file | psql

This excludes every line between the one that starts with COPY public.pgbench_accounts until the next following \.

Of course you would replace public.pgbench_accounts with your table's name, making sure to quote it properly if that is needed.

It might get confused if your database contains a row whose first column starts with the text "COPY public.pgbench_accounts"...

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246728

Then you have to edit the file manually.

A crude alternative might be: create a table with the same name as the log table, but with an incompatible definition or no permissions for the importing user. Then restoring that table will fail. If you ignore these errors, you have reached your goal.

Upvotes: 2

Related Questions