How do I troubleshoot restore operation failures in Amazon RDS for PostgreSQL?

Resolution

Amazon RDS for PostgreSQL supports these restore methods:

• Automated or manual database snapshots
• PITR within your backup retention period
• PostgreSQL native tools, such as pg_dump and pg_restore, for logical backups

Use the following sections to troubleshoot your type of restore issue.

Check pg_restore version compatibility

If your pg_restore utility version isn't compatible with the version used to create the backup, then you might receive one of the following error messages:

"Unsupported version (1.15) in file header"

"Could not find header for file 'toc.dat' in tar archive"

If PostgreSQL utilities require version alignment between the pg_dump backup creation tool and the pg_restore restoration tool, then you receive one of the error messages.

To resolve the issue, check your pg_restore version. Run the following command on the operating system (os) where the PostgreSQL client utilities are installed:

pg\_restore --version

Make sure that your pg_restore version is equal to or newer than the pg_dump version used to create the backup. If needed, upgrade your pg_restore utility to match or exceed the backup version. For more information, see PostgreSQL pg_dump and pg_restore utility.

Resolve backup format mismatches

If you use pg_restore with a plaintext SQL backup file, you might receive the following error message:

"Input file appears to be a text format dump. Please use psql"

To resolve the issue, use the psql utility instead of pg_restore to import your backup file. For more information, see Importing data into PostgreSQL on Amazon RDS.

Note: To avoid this issue when you create future backups, specify the correct format during backup creation. Use pg_dump with -Fc for custom format or -Ft for tar format options if you plan to restore with pg_restore.

Monitor restore performance

During snapshot restoration or PITR operations, your database instance uses lazy loading. Data blocks then load on demand when accessed. Your instance might experience slower performance until the frequently accessed data caches.

To monitor your restore progress, run the following command:

aws rds describe-db-instances --db-instance-identifier instance-id

Note: Replace instance-id with your database instance identifier.

For information to improve performance after a restore operation, see Best practices for working with PostgreSQL.

Manage database endpoints

If you need to maintain the same endpoint name after a restore operation, you can rename your database instance.

If you use read replicas, then make sure to update their configuration after a restore operation.

Related information

Restoring to a DB instance

Restoring a DB instance to a specified time for Amazon RDS

Best practices for migrating PostgreSQL databases to Amazon RDS and Amazon Aurora