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.)
A partly online backup is certainly possible but switching to and back from archivelog mode needs downtime. As an alternative you could run in regular archivelog mode and trash the generated archived log files, until you run a backup.
For the backup to be recoverable it is important that it has ALL archived log files that were generated during the backup. End the backup with a log switch and also include those archives in the backup.
You must see this kind of backup as partially online. It does allow you to restore to the point in time where the backup completed, not to any other point in time. Very similar to regular offline backups, only, taken online. It saves downtime.
Why do you want this? This is a lot of effort just to safe maybe 10G of archive storage per day. It also introduces an extra error source and many dba's won't be able to work with this and expect to have all archives. My advice would be to keep it regular online backups, with regular archived log backups and keep things simple.
Best Answer
No need to recreate the database. Set up
archive_modeandarchive_command, restart the server, create a base backup, and there you go. (Details vary depending on version.)It is quite common to "upgrade" from pg_dump to PITR backups as the database grows.