I'd say that you should be mirroring Postgres between staging and production if you think you'll have to do a complex DB migration more than once. Doing migrations manually can be so error prone that you will almost certainly recoup the time investment in setting up the migration.
I'm not a Postgres expert, but here is an overview of replication options.
Question: Can Postgres be setup on a 10GB or 100GB sized system to not use special backup software, but instead just use traditional filesystem backup software (filesystem snapshots?) and has a reasonable method of recovery using this method?
Yes, if the file system snapshots are atomic. This is extremely important. You must have an atomic snapshot, you can't just copy the data directory directly. The usual method is to take a snapshot with a SAN, logical volume manager, snapshot-capable file system, etc, and mount it on another path, then back that up. So you're using a pre- and post- backup script.
Here "atomic" is used in its computer science meaning, of indivisible, a single moment in time where everything is either before or after that moment. In the case of a snapshot, it means an instant in time, the state of the storage at that particular moment.
It is my understanding that Microsoft's Volume Shadow Copy Service (for Windows) is only atomic at the file level, so you cannot use backup systems that rely on it for consistency.
If you aren't really using a file system snapshot, you're just copying the data on the file system live, you can still do that but you must take extra steps. Per the documentation you can tell PostgreSQL the backup is taking place, and it will go into a no-overwrite mode that makes backups safe while it's running. However, to restore such a backup you require files that are written after the post-backup script calling pg_stop_backup() runs. The easiest way to make sure you have those files is to make sure WAL archiving is enabled; otherwise you'll need some extra scripting hooks in your backup system to append them to the backup.
Usecase 1: to avoid special backup approach when using Postgres and just use normal file system. No downtime, or < 5 seconds.
For this, just use pg_dump or pg_basebackup. Neither require downtime, and are simple.
Any decent backup system supports pre- and post-backup hooks that make this easy.
Usecase 1: to avoid special backup approach when using Postgres and just use normal file system. No downtime, or < 5 seconds.
For that you will want atomic snapshots, and you'll need to make sure the images are in the same snapshot as PostgreSQL.
Otherwise you risk inconsistencies where the filesystem and DB don't quite match.
Best Answer
http://www.linuxvirtualserver.org/
would allow you to put a load balancer (or better, two) in front of your production and backup servers.
You could use heartbeat alone which would allow a public IP to be served by two IPs. It would have to sense the primary or secondary going down, and then would send out an announcement to become that public IP.
You would have something like:
Everyone would see the single IP which would be served virtually by whichever machine was 'active'