You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster. From the command line on the server, I'd do something like this. (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql
This includes the globals--information about users and groups, tablespaces, and so on.
If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either
pg_dumpall --globals-only, orpg_dumpall --roles-only (if you only need roles)
like this.
$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql
Outputs are just text files.
After you move these files to a different server, load the globals first, then the database dump.
$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql
I thought pg_dumpall would at least backup foreign keys, but even that
seems to be an 'option'. According to:
http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even
with pg_dumpall I need to use a -o option to backup foreign keys
No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys". (Read the dump file in your editor or file viewer.)
If you are concerned about your logfile and the postgresql.conf, just do a file system backup of them before running initdb.
If your database did suffer from a harddisk corrupted then it is probably advisable to run initdb to make sure everything (including the system tables) are re-created properly.
As the configuration files are just plain text (as are the logfiles) you can simply copy the backup over into the new data directory after running initdb.
(Note: I don't know what pg_dropcluster does. It is not a standard Postgres tool)
Best Answer
This is going to depend on how you are doing your backups and if you are doing restores on the same database cluster that you are using for your live data.
The simplest case ( for small databases ) is that you are dumping your databases with
pg_dumpand storing them in the cloud as SQL files, possibly compressed.Once you have backups you can load them into a database cluster using
psql; you have various options in how you do this whether or not you specify thatpg_dumpdrops entities before creating them and whether to include database creation statements.If you are wanting to ensure the integrity of your backups, without overwriting a database you are using ( doing the restore on the same cluster you dumped from ) you want to dump the database without the create database statements and you want to have the restore script create a database with a different name.
There are a lot of variables in play in terms of what you can include in the dump and a fair bit of subtlety about ownership and permissions that are going to be dependent on what sort of restore you are doing and what roles are going to need access.