Problem
I use pg_dump to create an archive for our database schema only. I would like to reduce the size of this file (and reduce the recovery time), omitting everything except one of the many partitioned tables in the dump.
For example, the database has the following partitioned tables (by date). I only want to save the latter.
awp_partition.awp_text_search_history_201209 awp_partition.awp_text_search_history_201210 awp_partition.awp_text_search_history_201211 awp_partition.awp_text_search_history_201212 plus hundreds more...
I created the pg_dump command (called inside the bash script), designed to exclude all of these tables except the last, using an expression with a negative regular expression:
pg_dump -h 11.111.11.11 -p 5432 -U username -F c -s \ -T 'awp_partition.awp_text_search_history_(?!201212)\d{6}' \ dbname > /home/me/tmp/prod3.backup
However, when I run this command, all tables are excluded from the dump file.
What i tried
I tried using a combination of the include and exclude parameters of the table, but trying to exclude all tables, including one that matches the exception pattern, caused the entire dump to fail.
I tested my regex using the Postgres regexp_matches () function and it matched the expected tables correctly. However, based on the documentation: pg_dump documentation
Only dump table tables (or views or sequences or external tables) that match the table. Multiple tables can be selected by writing multiple -t switches. In addition, the table parameter is interpreted as a template in accordance with the same rules that are used by psql \ d commands (see Templates), therefore several tables can also be selected by writing wildcards in the template. When using wildcards, be careful to quote the pattern, if necessary, to prevent the shell from expanding with wildcards;
And psql documentation documentation
Advanced users can use regular expression notation, such as character classes, such as [0-9], to match any digit. All special regular expression characters work except as described in section 9.7.3. which is taken as a delimiter, as mentioned above *, which translates to a regular expression notation.,? which translates to. and $, which matches literally. Can you emulate these wildcards if necessary by writing? for., (R + |) for R or (R |) for R ?. $ is not required as a regular expression character, since the pattern must match the entire name, unlike the regular interpretation of regular expressions (in other words, $ is automatically added to your pattern). Write * at the beginning and / or end if you do not want the template to be bound. Note that in double quotes, all special regular expression characters lose their special meanings and correspond literally. In addition, special regular expression characters are matched literally in operator name patterns (i.e., the \ do argument).
I understand that the syntax of the negative review operator may not be supported in this operation.
What to do?
It seems like I need to change the pattern matching strategy, and I'm struggling to come up with a way to exclude all but one of these tables using psql \ d patterns. Any ideas?