send jobs via psql asynchronously

Question

I want to backup a huge number of records and delete them, but I don't have complete access to database server so I can't run my script on the server. because it takes so long I need to submit jobs(say REINDEX for example) and then turn off my computer. Is it possible to do something like this?

Answer 1

Not really - there is pgAgent - part of pgAdmin package, that could be abused to do it, but it's not its main purpose.

Generally, to run such long queries/jobs, you need somewhere to have psql session to live until it will finish. Usually, psql on screen on some kind of server - database server, or app server, doesn't matter much.

Answer 2

If your server runs on Linux you can start a psql job and send it to the background, then it runs regardless of the client interface. Something like this:

psql mydb -c 'REINDEX TABLE huge_table' > ~/reinedex.log &

Note the & at the end of the command line. You can do the same interactively by pressing [ctrl][z]. Have a look at the fg command, too: man fg.

The example redirects the output of the command to a log file in the home directory of the user: > ~/reinedex.log

Works with as many jobs in parallel as you want.