Postgresql – Dumping and restoring on db server – postgres

postgresql

Is there a way to execute a backup and restore of PostgreSQL DB from App server without calling pg_dump from App server and executing pg_restore also on App server. In this way I'm executing operations from App server on DB server and making unnecessary network traffic.

I would like to execute dumping and restoring directly on DB server, but to initiate it from my application.

My current situation if that I make extra network traffic, wait more time and also I need to install PostgreSQL client on my app server.

Any hints?

Best Answer

There are a bunch of ways of doing this. My recommendation would be to have a dedicated database for maintenance operations and use something like pg_message_queue to send messages to listening applications. You could then have a listener which could initiate pg_dump or pg_restore based on what is passed to it.

This would end up being queries like:

SELECT * FROM pg_mq_send_message('backup_db', 'mydbname'::text);

The listener could then be notified in real time (or wake up and poll, both methods are supported), and go ahead and backup bydbname using psql. You could do the same with restore, with a different queue.

You could try to just use LISTEN and NOTIFY without queue tables, but there is virtually no security on those commands themselves, so that could lead to issues. Also note this is lighter weight than ssh and API is totally asynchronous.

Disclaimer I am the author of pg_message_queue.

Related Question