Introduction

pg_dump is the standard way to take a logical backup of a PostgreSQL database, including one hosted on Amazon RDS. It does not replace RDS snapshots, but it is the right tool when you need a portable dump for migration, selective restore, or schema review.

What pg_dump Does on RDS

Amazon RDS manages the underlying server, but pg_dump still connects the same way a normal PostgreSQL client does. It reads database objects and data through SQL connections, then writes the result to a file on the machine where you run the command.

That means your backup host must be able to reach the RDS endpoint and authenticate with a database user that has enough privileges to read the objects you want to export.

For a complete dump, custom format is usually the best default because it works well with pg_restore and supports selective restore.

Connection Setup

First, make sure the security group for the RDS instance allows inbound access from the machine running the dump. Then gather:

• RDS endpoint
• port, usually 5432
• database name
• username
• password

A common pattern is to provide the password through an environment variable:

1export PGPASSWORD='your-password'
2
3pg_dump \
4  -h mydb.abcdefghijkl.ca-central-1.rds.amazonaws.com \
5  -p 5432 \
6  -U app_user \
7  -d app_db \
8  -Fc \
9  -f app_db.dump

This command creates a logical backup in PostgreSQL custom format. The dump file is written locally, not into RDS storage.

If you prefer a plain SQL file:

1pg_dump \
2  -h mydb.abcdefghijkl.ca-central-1.rds.amazonaws.com \
3  -p 5432 \
4  -U app_user \
5  -d app_db \
6  --no-owner \
7  --no-privileges \
8  > app_db.sql

Plain SQL is easy to inspect, but custom format is usually more flexible for real restores.

Practical Options That Matter

Some pg_dump flags are especially useful with RDS:

• '-Fc creates custom format.'
• '--schema=public dumps a single schema.'
• '--table=orders dumps one table.'
• '--no-owner avoids ownership commands that may fail on another instance.'
• '--no-privileges skips grant statements when you only need data and schema.'

For larger databases, parallel dump can help, but it requires directory format:

1pg_dump \
2  -h mydb.abcdefghijkl.ca-central-1.rds.amazonaws.com \
3  -p 5432 \
4  -U app_user \
5  -d app_db \
6  -Fd \
7  -j 4 \
8  -f app_db_dir

That tells PostgreSQL to use four worker jobs. It is useful when network bandwidth and database load allow it, but do not run aggressive dump jobs during peak traffic without testing.

Restore Validation

A backup is only meaningful if you can restore it. Before you rely on the dump, inspect or test it:

pg_restore -l app_db.dump | head

Restore into a staging database when possible:

1createdb restore_test
2pg_restore \
3  -d restore_test \
4  --clean \
5  --if-exists \
6  app_db.dump

If the restore target is also RDS, make sure the target roles and extensions exist or adjust the dump flags to avoid environment-specific objects.

RDS-Specific Notes

pg_dump is a logical backup, while RDS automated backups and snapshots are physical service-managed backups. They solve different problems:

• use RDS snapshots for fast instance-level recovery
• use pg_dump for migration, partial restore, inspection, and portability

You also do not get true superuser access on RDS. Some objects or extensions may behave differently from self-managed PostgreSQL. When moving between environments, --no-owner and --no-privileges often reduce restore friction.

If SSL is required, add the normal PostgreSQL SSL options or connection parameters supported by your client version.

Common Pitfalls

The most common failure is network access. If the backup host cannot reach the RDS endpoint because of VPC routing or security-group rules, pg_dump will fail before authentication even matters.

Another issue is using the wrong tool for the goal. Teams sometimes expect pg_dump to behave like a full-instance disaster recovery mechanism. It is not a replacement for RDS snapshots.

Permissions are another source of confusion. A low-privilege user may be able to connect but still fail on tables, schemas, or extension-owned objects.

Finally, do not skip restore testing. A dump file that exists is not the same thing as a backup you can trust.

Summary

• 'pg_dump works against RDS PostgreSQL the same way it works against other PostgreSQL servers.'
• The dump file is created on the client machine, not inside RDS.
• Use custom format with -Fc for flexible restores.
• Make sure networking, credentials, and database privileges are correct before running the backup.
• Test restores with pg_restore so the backup is operational, not just present.