Assistance with Creating Replica or Reliable Backup for AWS PostgreSQL Database

To address your needs for creating a replica or reliable backup of your AWS-hosted PostgreSQL database, here are some recommendations:

1. External Replication: Unfortunately, setting up an external read replica from your AWS RDS PostgreSQL instance to an on-premises server is not directly supported by AWS. RDS replicas are typically confined within the AWS ecosystem. However, you have alternative options for replication:

a) Logical Replication: You can set up logical replication from your AWS RDS PostgreSQL to your on-premises PostgreSQL server. This method allows you to replicate specific tables or the entire database. To implement this:

• Enable logical replication on your RDS instance by modifying the parameter group.
• Create a publication on the source (AWS) database.
• Set up a subscription on your target (on-premises) database.

b) Third-party tools: Consider using tools like Bucardo or pglogical for more advanced replication scenarios.

2. Reliable Backup Strategy: For creating large, consistent backups of your PostgreSQL database, consider the following approaches:

a) Native PostgreSQL utilities:

• Use pg_dump with parallel jobs option (-j) to speed up the backup process for large databases.
• Employ the directory format (-Fd) when taking the backup, which allows for parallel restores.
• Example command: pg_dump -Fd -j 4 -f /path/to/backup_directory your_database_name

b) AWS Database Migration Service (DMS):

• Set up a one-time full load task from your RDS PostgreSQL to your on-premises PostgreSQL.
• This method can handle large databases and provides resilience against network issues.

c) RDS Snapshot + AWS DataSync:

• Take an RDS snapshot of your database.
• Use AWS DataSync to transfer the snapshot to your on-premises environment.
• Restore the snapshot to your local PostgreSQL server.

To address the backup failures you're experiencing:

1. Break down the backup process: For very large tables, consider backing them up separately using table-level pg_dump commands.

2. Increase resources: Ensure your RDS instance has sufficient CPU and memory during the backup process. Consider scaling up temporarily if needed.

3. Use compression: Employ compression options with pg_dump to reduce file size and transfer time.

4. Implement retry logic: If using custom scripts for backup, implement retry mechanisms for resilience against temporary failures.

5. Monitor and log: Implement detailed logging to identify specific tables or issues causing failures.

Remember to test your chosen method thoroughly in a non-production environment before implementing it for your production database. Also, ensure you have sufficient storage and network capacity on your local server to handle the large database size.

Sources
Base backup - AWS Prescriptive Guidance
Native PostgreSQL client applications - AWS Prescriptive Guidance
Amazon RDS for PostgreSQL Resources – AWS
Using a PostgreSQL database as an AWS DMS source - AWS Database Migration Service

Agree with the Agent.. I was going to suggest using a pg_dump or you can use AWS DMS https://aws.amazon.com/dms/ to create a copy of your database so long as the VPC has access to your On Prem Server