Dumping and restoring on db server - postgres

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.

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.