Can I restore single database in Postgres RDS database instance that has multiple databases within?

Question

Terminology I'm gonna use	Definition I associate with
Database Instanse/Cluster	The name you see in AWS RDS services page
Database	the ones you see when you use \l after connecting to Database instance through CLI

I don't want to restore the whole database instance only one database inside the Postgres RDS. You have to connect to Postgres to be able to see these databases either GUI like PgAdmin or pg cli, AFAIF you cannot see them in AWS console (you can see them in CloudSQL in GCP).

Out of these databases, I want to restore only one and leave the others as is, if I use the backup restore option it will restore all the databases in the instance. I have read the documentation, there is no reference to what I need.

If I have to extract that one database and restore it, are pgdumb and pgrestore my friends? or is there another nifty way to do this?

Answer 1

While you can Exporting DB snapshot data to Amazon S3 - Amazon Relational Database Service, it exports in Parquet format that isn't ideal for loading back into your 'live' database.

Therefore you could use the following process:

• Restore the Snapshot to a new database instance
• Connect to the restored instance and use pg_dump to export your desired logical database
• Connect to the 'live' instance and rename the existing database (which is a bit safer than deleting it)
• Restore the dump by passing it into a psql command-line (the format of the dump is actual SQL commands) -- you can find plenty of information online about how to do this