How to dump postgres database and sync with other postgres database using nodejs-CodePudding

I have 2 database servers. One for dev and one for production, what I want to do is to create a dumb of production DB and sync it with dev DB at a specific time using nodejs.

CodePudding user response：

I asume you meant dump, and googled that for you: https://www.joseverissimo.com/blog/automatically-export-a-database-and-import-it-to-another-using-node-js

Now you just should make sure it runs as a cronjob.

CodePudding user response：

Postgres provides pg_dump (single DB) and pg_dumpall (all DBs) utilities out of the box. The simplest way would probably be a cron job to automate the backup of your source DB and scp it over to a destination server, then restore the DB on the destination server by a cron job:

On the source server:

crontab -e
0 0 * * 0 pg_dumpall -U postgres > ~/backups/backup.bak
# scp to your destination server

If you’re working in one of the major cloud environments they will have their own tools that can help, eg AWS you can automate snapshots, then restore from a snapshot (or have a lambda perform the restore from the snapshot as you suggest in nodeJs)

Or (super-cool way) is to use AWS DMS (Data Migration Services) CDC - Change Data Capture and you can replicate a source DB instantaneously with one or many target replicas (avoiding the need for dumps and restores)