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?

  • Not sure what you are trying to accomplish. You mean start a dump from an application or you mean run the process on your app server operating system? Windows or *Nix? – ETL Feb 28 '13 at 0:59
  • I have web app and I have button to backup db, do something, and then restore db. My application is also using db from same server, but I am backing up and restoring some other database. Does this make sense? – mikipero Feb 28 '13 at 1:12
  • Linux? Windows? So you want to initiate a backup from the applications running on the apps server, but you want the backup to occur on the DB server. Right? – ETL Feb 28 '13 at 1:19
  • yes. *nux is everywhere – mikipero Feb 28 '13 at 8:38

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.

| improve this answer | |

What you would need to do is from your application code, ssh to your database server and run the command. SSH allows you to run a command remotely.

On Windows, use PsExec.

Alternatively, you can create a web application and run it on your database server which when it gets a specific URL call, it does the backup and another, does the restore.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.