pg_dump and –if-exists little gem
pg_dump is a very useful tool to dump (and hence prepare to restore) a single PostgreSQL database.
When I use it, I usually add the options:
--cleantoDROPthe database I’m dumping;--createto issue aCREATE DATABASEand reconnect to it.
--clean option is that the SQL file begins, after the useual preamble, with something like:
DROP DATABASE miniondb;
While this is what I want, if I need to restore the backup on a fresh machine, where the target database was not already in place, the restore will cause a warning saying that the database cannot be dropped because it does not exist (yet). And thic could be annoying from time to time! But being PostgreSQL such a great advanced piece of software,
pg_dump provides an option for add the very useful IF EXISTS to DROP DATABASE: -if-exists comes to the rescue!
% pg_dump --clean --create --if-exists ...
The above will result in the
DROP DATABASE miniondb IF EXISTS;, that in turn will stop annoying me when the database is not already in place.
After all, the documentation for the --clean option already mentioned it clearly:
If any of the objects do not exist in the destination database,
ignorable error messages will be reported during restore,
unless --if-exists is also specified.
and much more on the option documentation itself:
--if-exists
Use DROP ... IF EXISTS commands to drop objects in --clean mode. This suppresses “does
not exist” errors that might otherwise be reported. This option is not valid unless
--clean is also specified.
Note that
--if-exists refers to objects, not only the whole database!