I am going to set up a backup stone ( http://backup.imtqy.com/backup/v4/ ) to run on my EC2 instance, copy the PostgreSQL database to RDS, and save the backup in a new S3 bucket.
The backup stone runs the pg_dump command, but AWS does not allow you to install the same version of Postgres as on EC2 and RDS, resulting in the following error:
pg_dump: server version: 9.4.7; pg_dump version: 9.2.13 pg_dump: aborting because of server version mismatch
This is because the EC2 instance has a version:
$ pg_dump --version pg_dump (PostgreSQL) 9.2.13
And the RDS instance has a version:
9.4.7-R1 (with the only version 9.5.2-R1)
In EC2, running yum list postgres * only offers available packages prior to PostgreSQL 9.3.
So it looks like I cannot either downgrade RDS or upgrade EC2 to the appropriate version.
Here is my gem configuration configuration if it helps: https://gist.github.com/anonymous/35f6f9e81846f53693fb03662c2192ad
Before too many people start reminding me that RDS has built-in backups, I know. My use case: instead of having a full database reserve, I would also like to roll back the data of individual users at different time periods without affecting the entire database. I planned to keep these manual backups and eventually write a script to pull the previous user data out of them.
My friend recommended another option: if the user wants a rollback, I can deploy a new RDS from automatic snapshots, clone my EC2 instance, connect them to each other, collect user data from this snapshot and then merge these changes back into the main EC2 instance.