CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rds
karel lahmy
karel lahmy

Reputation: 417

AWS postgres copying data from one database to the other daily?

I want to create an automated job that can copy the entire database to a different one, both are in AWS RDS Postgres, how can I do that?

Thanks.

Upvotes: 1

Views: 770

Answers (2)

John Rotenstein
John Rotenstein

Reputation: 269816

I have had success in the past running a script that dumps data from one Postgres server and pipes it into another server. It was basically like this pseudo-code:

psql target-database -c "truncate foo"
pg_dump source-database --data-only --table=foo | psql target-database

The pg_dump command outputs normal SQL commands that can be piped into a receiving psql command, which then inserts the data.

To understand how this works, run pg_dump on one table and then take a look at the output. You'll need to tweak the command to get exactly what you want (eg using --no-owner to avoid sending access configurations).

Upvotes: 0

Anton
Anton

Reputation: 4052

You can use Database create/restore snapshot.

Here is the example for command line:

aws rds create-db-snapshot \
    --db-instance-identifier mydbinstance \
    --db-snapshot-identifier mydbsnapshot 

aws rds restore-db-instance-from-db-snapshot \
    --db-instance-identifier mynewdbinstance \
    --db-snapshot-identifier mydbsnapshot

The same APIs such as CreateDBSnapshot are available for multiple languages via AWS SDK.

Upvotes: 1

Related Questions