I am referring to
In this example/solution, which part of the restore process does actually the “CREATE DATABASE sandbox”? I have been searching for this answer a while now – it seems that pg_dump does not backup the names of the database it creates a backup for –> when I just backup (pg_dump) and restore (psql) it, i get “database sandbox does not exist”.
So, if I want to backup and restore the whole cluster with pg_dump, do I have to create all databases on the new server manually? I would like to be able to restore single databases later on, so pg_dumpall doesn’t seem to be the right way, or is it?
The question you are referencing is a bit of a mess, I think you would be better off not referring to it at all. It just introduces misconceptions to then partially remove some of them.
If you want to backup and restore the whole cluster, you should use “pg_dumpall”, not “pg_dump”. Using “pg_dumpall” without any of the restrictive options will emit the necessary “CREATE DATABASE” commands.
If you run “pg_dump” with
-C option, it too will emit “CREATE DATABASE” commands.
I would like to be able to restore single databases later on, so pg_dumpall doesn’t seem to be the right way, or is it?
If you want to do something different in the future, then do something different in the future. “pg_dumpall” preserves your options. You can restore the whole thing, and then drop databases you don’t want. Or you can restore the whole, and re-dump what you do want out of it. Or just dump what you want out of the original server, if that is still around. Or use vi or sed or awk or perl to munge the “pg_dumpall” .sql file so that it does only what you want.
If you don’t like any of those options, you could do a
pg_dumpall -g accompanied with a
pg_dump -C of each individual database. If your primary use case to restore a single database but without knowing ahead of time which one it will be, this would be faster than the previous options, and could be run on a smaller machine than the original. But it is also a bit harder to document, and more likely for files to get out of sync with each other.