CODEWITHSUNDEEP
postgresqlamazon-rdspg-dump
bfissa
bfissa

Reputation: 429

Dump Contents of RDS Postgres Query

Short Version of this Question:

I'd like to dump the contents of a Postgres query from a db instance hosted in RDS inside of a shell script.

Complete Version:

Right now I'm writing a shell script that I would like to dump the contents of a query into a .dump file from a source database, and run the dump file on a destination database instance. Both db instances are hosted in RDS.

MySQL allows you to do this using the mysqldump tool, but the recommended answer to this problem in Postgres seems to be to use the COPY command. However, the COPY command isn't available in RDS instances. The recommended solution in this case seems to be to use the '\copy' command, which does the same thing locally using the psql tool. However, it doesn't seem like this is a support option inside of a shell script.

What's the best way to accomplish this?

Thank you!

Upvotes: 1

Views: 326

Answers (1)

mija
mija

Reputation: 69

I am not familiar with shell, but I have used batch file in Windows to dump output of query to a file and to import the file on another instance.

Here is what I used to export from postgres RDS to file on Windows. 

SET PGPASSWORD=your_password
cd "C:\Program Files (x86)\pgAdmin 4\v3\runtime"
psql -h your_host -U your_username -d your_databasename -c "\copy (your_query) TO 
path\file_name.sql"

All above commands are in one batch file.

Upvotes: 2

Related Questions