So I have a PostgreSQL instance running in docker and I made a backup of the database with the following command:
docker exec 7c1abbe82ff2 pg_dump -U ash -Fc pokeapi > backup.dump
I also have an Amazon RDS PostgreSQL database and I'm trying to populate that database using my backup file on my machine. I feel like I've tried everything under the sun and haven't been able to accomplish this task.
What seems the most promising and what I'm trying right now is using pg_restore to do this. Specifically:
pg_restore -v -h RDS_host_name.rds.amazonaws.com --port=5432 -U username -d RDS_database_name -j 2 backup.dump
But then I just get: pg_restore: connecting to database for restore and it never does anything else from there. I'm fairly positive that my connection details are correct because if I intentionally input the wrong username I get an error in the RDS console. But when I input all the correct info I get nothing in the RDS console.
Also worth noting that I currently have my RDS instance open to all traffic from any source so I don't think that's the issue but I'm new to AWS so perhaps I messed that up or missed something.
I've also tried using psql to connect to the RDS instance (which I am able to do) but when I try \i backup.dump I get the following error:
The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.
Any help to solve this issue or to give me another method of populating the RDS database would be greatly appreciated!
EDIT - Solved
Was finally able to figure out a solution.
docker exec 7c1abbe82ff2 pg_dump -U usernam --format=plain --no-owner databaseName > newBackup.dump
In my dump command I had to include "--format=plain" instead of "-Fc". By doing this I was able to log into my RDS server in the CLI and do the \i backup.dump and have it actually work. Something about it needing the plain formatting.