0

I have a file make_partition.sql like:

CREATE INDEX my_nice_index ON mytable (ts);

CREATE TABLE mytable_2017_10 AS SELECT * FROM mytable WHERE date_trunc('month', ts) = '2017-10-01';
CREATE TABLE mytable_2017_11 AS SELECT * FROM mytable WHERE date_trunc('month', ts) = '2017-11-01';

I run it in the background using screen and then the command:

psql postgresql://usr:pw@host.com:5432/db_name -f make_partition.sql

And detach from the screen. It completes, but then it appears that only the first query was run. I've tested this a few times with the same result.

How can I get it to run them all? Do I have to put them in a procedure like this? How to run multiple SQL queries?

LittleBobbyTables
  • 4,361
  • 9
  • 38
  • 67

2 Answers2

0

When you 'detach' from the screen, what is likely happening is an HUP signal is sent to the process and the script stops executing, the query that is currently executing will finish because it is part of a different process.

You can properly send it to the background and make it safe to detach in a couple of ways, one of the ways is using the command nohup (see man nohup for more) which ignores the HUP signal.

An alternative is to start the script as you have above, then pause it by pressing CTRL+Z, you can then send it to the background using bg and finally you can ensure it is ignoring the HUP signal using disown -h (see man disown for more).

Adam Dadvar
  • 384
  • 1
  • 7
0

This question is relatively old, and Adam Dadvar's answer provides the basics, but I'll provide one other piece of the puzzle that could trip someone up. If the SQL within your file will create output after each statement, you need to redirect at least the stdout into files, rather than letting it go to the terminal. Doing the latter will mean it's displaying the results and waiting for you to spacebar through the results before running the next command. If your code may produce error that you want to ignore, you should also redirect that output. Concretely, using the disown option, it would look something like:

psql postgresql://usr:pw@host.com:5432/db_name -f make_partition.sql 1> messages.log 2> errors.log

Then use CTRL+Z to suspend the current process, disown -h %1 to disown the process, and bg to "background" the process.

M. Andersen
  • 107
  • 8