pg_restore fails while restoring dump

The error message "pg_restore: warning: errors ignored on restore: 40" during a PostgreSQL database restore indicates that there were multiple issues that pg_restore encountered but chose to ignore, based on its default or specified behavior. Here are several steps and considerations for troubleshooting and resolving the issue when restoring a PostgreSQL dump into an RDS Aurora serverless instance:

1. Check Error Details Although the main log shows only a warning count, more detailed errors might have been printed during the restore process. These messages can provide crucial insights into what exactly is failing. If the customer doesn’t have other logs, advise them to rerun the command with increased verbosity and log the output to a file for a detailed review.

For example:

pg_restore --verbose --no-owner --no-acl --clean --if-exists -h xxxxx.cluster-xxxxx.ap-south-1.rds.amazonaws.com -p 5432 -U dbadmin -d database1 -1 "/home/ubuntu/database_DB.dmp" > restore.log 2>&1

2. Examine Resource Limits Restores might fail due to resource limitations on the server, such as memory or timeout settings. Since this is an Aurora Serverless instance, check if the auto-scaling configuration and maximum allocated resources are sufficient for the restore operation. Sometimes, increasing the compute resources temporarily during the restore can help.

3. Permissions and Ownership The --no-owner and --no-acl options in pg_restore skip restoring ownership and privilege settings. This is usually necessary when moving between systems where the original users and roles might not exist. However, ensure that the dbadmin user has sufficient permissions to create all necessary database objects.

4. Compatibility Issues Even though both the source and destination are PostgreSQL 15.6, make sure that there are no compatibility issues with specific extensions or custom types used in the dump that might not be fully supported or enabled in Aurora.

5. Partial Restore Strategy If errors relate to specific database objects that can be identified from the verbose log, consider restoring the database in parts or excluding problematic objects if they are not critical to the application’s functionality.

6. Network Issues Given that the restore process fails after a few hours, it’s worth considering potential network issues or timeouts. Check if there are any network policies or settings that might be interrupting the connection.

7. SQL Errors and Corrections If specific SQL errors are identified from the logs, manual intervention might be necessary to modify the dump file or to execute corrective SQL statements directly on the database before or after the restore.

8. Support and Documentation Refer to AWS RDS Aurora's documentation for any known issues or best practices for data import. AWS Support can also be a valuable resource if the problem persists, as they can provide insights specific to the configuration and environment of the Aurora instance.

Encourage the customer to collect and review the detailed logs, and then address the issues based on the specific errors encountered. This structured approach should help in identifying the root cause and resolving the restore issues.