14

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

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
5
  • 1
    Does tmux work this way: 'Will I be able to shutdown my local machine, and the query still get proccessed at the remote server' ?
    – dezso
    Oct 16, 2012 at 19:05
  • Yes, the session is running on the remote machine - a power outage or loss of internet connection is a non issue (in regard to the remote session :) ). Note that you need to start the process from within the terminal multiplexer (afaik).
    – c0dem4gnetic
    Oct 16, 2012 at 19:16
  • can you provide guidance of how this is implemented? psql is client software thats running on your local machine, then tmux is making ssh connection to a remote machine running a postgres server? If that's the case I've sadly realized this won't work with a Redshift instance without an EC2 running as well.
    – Merlin
    Sep 17, 2018 at 6:08
  • @Merlin tmux runs on the remote machine and establishes the shell session. It is from this that you detach and attach. When running psql locally you could run it in a tmux session, but the client connection is only kept as long as network conditions allow - just as a normal ssh session.
    – c0dem4gnetic
    Apr 10, 2019 at 18:03
  • @c0dem4gnetic i think psql must still be be installed on the remote server. Seems like local tmux -> EC2 + tmux + psql logging into redshift/postgres db to keep the session alive.
    – Merlin
    Apr 12, 2019 at 2:31
12

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.

Improve this answer
2
  • pg_agent looks promising enough. I will try it and update the results.
    – user528025
    Oct 4, 2012 at 20:51
  • 3
    Or, for a one-time operation, the at command might serve you better than cron.
    – Erwin Brandstetter
    Oct 4, 2012 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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