Save and restore RDS PostgreSql

Hi,

Did you confirm that you are not (still) in a "lazy loading" situation where restore in still under way: see tbis blog post https://aws.amazon.com/blogs/database/amazon-rds-snapshot-restore-and-recovery-demystified/

Reference docs details it here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html

If you db is big, that may well be the case: restore is still under way!

Best,

Didier

It seems like you're encountering issues with backing up and restoring your RDS PostgreSQL database using custom scripts outside of AWS's native backup mechanisms. There are a few important points to consider:

1. AWS Backup for RDS: This is the native AWS solution for backing up RDS databases. It creates automated backups and allows for point-in-time recovery within the AWS ecosystem. However, this doesn't generate SQL files that you can access directly.

2. Custom backup scripts: If you're using custom bash scripts to create SQL dumps of your database, these operations are performed at the application level and not at the RDS instance level. This is why you don't see these operations in the RDS logs.

3. Restoring from SQL files: When you restore from SQL files using your custom scripts, you're likely executing SQL commands against the database. If you're not seeing changes in your tables after a restore operation, there could be several reasons:

   • The restore script might not be executing successfully.
   • The connection to the database might not be established correctly.
   • The SQL commands in your restore file might not be correct or might be failing silently.
   • You might be looking at the wrong database or schema after the restore.

To troubleshoot this:

1. Check your backup and restore scripts for any errors.
2. Ensure your scripts are connecting to the correct RDS instance and database.
3. Add error checking and logging to your scripts to capture any issues during execution.
4. Try restoring a small amount of data first and verify the changes immediately after.
5. Check the PostgreSQL logs on your RDS instance for any error messages related to your restore operations.

Remember that while custom backup solutions can be useful, they don't integrate with RDS's native backup and restore mechanisms. For critical data, it's recommended to use AWS's native backup solutions in conjunction with your custom scripts for additional data protection.
Sources
Restore an RDS database - AWS Backup
Backup and recovery for Amazon RDS - AWS Prescriptive Guidance

Can you please describe what you're doing in your backup and restore scripts more in detail?