How to execute a query from psql without waiting for the result?

Question

My query (to create a new table from existing table) takes a very long time. So I've set up a remote database in my office - more RAM there.

I can connect to my database from home as usual with psql.

How can I tell the remote server to execute my query from the terminal without having to wait for a response?

(postgresql-9.2 , linux enviroment)

Edit: I'm open to other solutions, it's not necessary to use psql

Answer 1

Since you state that you are open to other solutions I might suggest looking at terminal multiplexers such as screen or tmux. In my opinion tmux is a better choice due to its unique name (easier to get relevant hits in search engines).

Essentially this kind of software allows you to detach from a shell and later resume the session.

Answer 2

You can try sending psql to the background:

psql -f your_sql_file.sql &

Or, connecting to the local DB, you can use dblink to dispatch a query to the remote DB:

SELECT dblink_connect('your_connection_name', 'your_connection_string');
SELECT dblink_send_query('your_connection_name', 'your_query');

Note that dblink_send_query can only send one query at a time. So, if you want to run multiple SQL statements, this is not your solution.

Or, you can start a pg_agent job on the remote server, which requires no manual intervention, therefore the state of your home box has no effect on running your job. The same can be achieved by setting up a cron (or even better, at - thanks, Erwin) job executing your script.

Additionally, if you have a long running job which you start manually, you can start a screen session on the server and run the file from there. In this case, you can log off and go home, and the script will keep running.