How to make a postgres backup with pg_backup using xojo app?

Databases
1

Hi folks,

I would like to initiate a pg_backup command on a remote postgres server.
It seems impossible from a xojo app using postgres plugin, you can only send sql queries ?
or did I miss something.
do I need to make a ssh connexion to the remote server and send a shell command ?

thanks.

2

Unless something has changed recently, yeah, pg_backup can only be initiated from the shell.

3

Well what I do on my Debian servers is always run a cron job doing a backup (o;

On certain systems even hourly and keep the backups for 30 days…

That way you always get backups even if you’re on vacation or asleep :wink:

4

there are automatic pg_backup on the server, but sometimes, for testing or more backup purpose, I would like to download a pg_dump to my local machine, using my xojo client app for that.
seems it’s not possible, or I would have to recreate a backup of all tables, sequences, roles, using a sequence of queries…

5

Then probably there’s only SSH from local machine left and pipe through the pg_dump command and save the dump locally…

Or…what about a web hook you call with special header tokens for authentication and let the webserver do the dump and send it to you?

6

If you can get the SSH connection then you can connect via pgadmin and I think you can have it write the backup files locally.

7

this could be a solution start …

8

Had a case this year where I needed to migrate our online shop from a Debian 11 machine to a Debian 12 machine…

Only pgAdmin did the job successfully (o;

pg_restore failed on Debian 12 for the older database version…and even Navicat failed miserably…

2 Likes