Drop psql tables with special prefixes

Question

I am currently using the pg_dump tool to output tables from my database. I would like to automate this process, however, I did not find a way to specify pg_dump to dump multiple databases with the same prefix.

Any help in this matter would be greatly appreciated.

Answer 1

Examples from the manual :

To reset all tables whose names begin with emp in the detroit schema, except for a table named employee_log:

$ pg_dump -t 'detroit.emp *' -T detroit.employee_log mydb> db.sql

Reset all patterns whose names start from east or west and ending with gsm, excluding any patterns whose names contain the word test:

$ pg_dump -n 'east * gsm' -n 'west * gsm' -N 'test' mydb> db.sql

Same thing using regular expression notation for switch consolidation:

$ pg_dump -n '(east | west) * gsm' -N 'test' mydb> db.sql

Reset all database objects, except for tables whose names begin with ts _:

$ pg_dump -T 'ts_ *' mydb> db.sql

Answer 2

I'm not sure if I understand exactly what you said. I advise you to edit the shell script to flush each database.

Answer 3

You can use the -t option several times if you want to dump more than one table.

$ pg_dump -t table1 -t table2 mydb> dump.sql

-t table --table = table

Dump only tables (or views or sequences) matching table. Multiple 

Tables can be selected by writing a few switches.