I'm still new to PostgreSQL and reading as I go, but so far haven't found exactly what I'm looking for (though there are similar things and maybe I'm lacking on the terminology).

We have an RDS instance in AWS (PostgreSQL), the data is encrypted using the AWS KMS. I want to create a new RDS instance, which I want to encrypt using a new custom key, then migrate all the data from the old instance to the new one.

The problem is that the way AWS works, the private key is stored away and hidden from me, so I can't decrypt the data and then move it to the new instance, the solution I'm thinking about is use some sort of database client, maybe write a query or a dump of the data and then push it to the new instance.

Has anyone dealt with such an issue before? In PostgreSQL terms, how can I get all the data using a client or something and then push it to the new instance?

I'm going to install pgadmin and see if that helps, any suggestions would be most welcomed.

  • Move data using a client application... its a way complicated. Can't you just use dump & restore ? – Sahap Asci Aug 24 '17 at 13:33
  • maybe, can you shortcut my education and provide the commands for testing? also, would dump and restore copy everything? including roles for example or other sorts of db specific configurations? – Naim Salameh Aug 24 '17 at 13:40
  • 1
    I recommend you to check documentation section first. Chapter 25. Backup and Restore. If you want to copy all cluster with roles you can use pg_dumpall – Sahap Asci Aug 24 '17 at 14:03
  • so I did some reading and I'm trying to use pg_dumpall. before I used that, I tried psql -d $DBNAME -h $HOST -p $PORT -U $USERNAME and it worked, I was able to connect, however, when I try this: pg_dumpall.exe -d $DBNAME -h $HOST -p $PORT -U $USERNAME -f testme.sql I get the following error: pg_dumpall: missing "=" after "billingdb" in connection info string I tried adding the = sign of course but then I get a pg_dumpall: invalid connection option "billingdb" as an error. Anything you can help me with here? – Naim Salameh Aug 28 '17 at 14:19
  • try this one pg_dumpall.exe -d "host=$HOST port=$PORT dbname=$DBNAME" -U $USERNAME -f testme.sql – Sahap Asci Aug 29 '17 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.