How to run shell script commands in an sh file in parallel?

Question

I'm trying to take backup of tables in my database server.

I have around 200 tables. I have a shell script that contains commands to take backups of each table like:

backup.sh

psql -u username ..... table1 ... file1;
psql -u username ..... table2 ... file2;
psql -u username ..... table3 ... file3;

I can run the script and create backups in my machine. But as there are 200 tables, it's gonna run the commands sequentially and takes lot of time. I want to run the backup commands in parallel. I have seen articles where in they suggested to use && after each command or use nohup command or wait command.

But I don't want to edit the script and include around 200 such commands.

Is there any way to run these list of shell script commands parallelly? something like nodejs does? Is it possible to do it? Or am I looking at it wrong?

Sample command in the script:

psql --host=somehost --port=5490 --username=user --dbname=db -c '\copy dbo.tablename TO "/home/username/Desktop/PostgresFiles/tablename.csv" with DELIMITER ","';

Chances are the database is already giving you tae tables as fast as it can, and attempting to make it do more things at the same time will just produce congestion. — tripleee
– tripleee, Commented Nov 20, 2019 at 17:54
My shell commands are using \copy which is a client side CSV creation which was working fine when I ran the script normally. But if I use the above mentioned approach, it is considering \copy as COPY which is server side copy for which the db user doesn't have access to. — Underoos
– Underoos, Commented Nov 20, 2019 at 17:58
@tripleee updated question with sample command in the script. — Underoos
– Underoos, Commented Nov 20, 2019 at 18:00
That should not happen unless there are things going on which you aren't showing, such as passing the command to a remote shell. — tripleee
– tripleee, Commented Nov 20, 2019 at 18:06
I tried the command that is mentioned in the below answer by drldcsta — Underoos
– Underoos, Commented Nov 20, 2019 at 18:07

dash-o · Accepted Answer · 2019-11-21 16:05:39Z

2

You can leverage xargs to run command in parallel, AND control the number of concurrent jobs. Running 200 backup jobs might overwhelm your database, and result in less than optimal performance.

Assuming you have backup.sh with one backup command per line

xargs -P5 -I{} bash -c "{}" < backup.sh

The commands in backup.sh should be modified to allow quoting (using single quote when possible, escaping double quote):

psql --host=somehost --port=5490 --username=user --dbname=db -c '\copy dbo.tablename TO \"/home/username/Desktop/PostgresFiles/tablename.csv\" with DELIMITER \",\"';

Where -P5 control the number of concurrent jobs. This will be able to process command lines WITHOUT double quotes. For the above script, you change "\copy ..." to '\copy ...'

Simpler alternative will be to use a helper backup-table.sh, which will take two parameters (table, file), and use

xargs -P5 -I{} backup-table.sh "{}" < tables.txt

And put all the complex quoting into the backup-table.sh

edited Nov 21, 2019 at 16:05

answered Nov 20, 2019 at 20:29

dash-o

14.7k1 gold badge14 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Underoos Over a year ago

I'm getting psql: warning: extra command-line argument error for all the words in the script.

dash-o Over a year ago

Oops, I did not realize that the full command has double quotes. I've posted small update - but it will require changing double quotes in backup.sh to single quote, as per answer. Hope it will get you more mileage.

Underoos Over a year ago

Same issue even after replacing " with ' and ' with ".

dash-o Over a year ago

Can you share sample line from backup.txt (after the changes) ?

Underoos Over a year ago

Updated in the question. BTW, I'm using Ubuntu. I suppose it shouldn't cause an issue.

|

Ole Tange · Accepted Answer · 2019-12-06 23:12:42Z

1

doit() {
  table=$1
  psql --host=somehost --port=5490 --username=user --dbname=db -c '\copy dbo.'$table' TO "/home/username/Desktop/PostgresFiles/'$table'.csv" with DELIMITER ","';
}
export -f doit

sql --listtables -n postgresql://user:pass@host:5490/db | parallel -j0 doit

answered Dec 6, 2019 at 23:12

Ole Tange

34.2k9 gold badges93 silver badges112 bronze badges

Comments

drldcsta · Accepted Answer · 2019-11-21 19:50:07Z

0

Is there any logic in the script other than individual commands? (EG: and if's or processing of output?).

If it's just a file with a list of scripts, you could write a wrapper for the script (or a loop from the CLI) EG:

$ cat help.txt
echo 1
echo 2
echo 3

$ while read -r i;do bash -c "$i" &done < help.txt
[1] 18772
[2] 18773
[3] 18774
1
2
3
[1]   Done                    bash -c "$i"
[2]-  Done                    bash -c "$i"
[3]+  Done                    bash -c "$i"

$ while read -r i;do bash -c "$i" &done < help.txt
[1] 18820
[2] 18821
[3] 18822
2
3
1
[1]   Done                    bash -c "$i"
[2]-  Done                    bash -c "$i"
[3]+  Done                    bash -c "$i"

Each line of help.txt contains a command and I run a loop where I take each command and run it in subshell. (this is a simple example where I just background each job. You could get more complex using something like xargs -p or parallel but this is a starting point)

edited Nov 21, 2019 at 19:50

answered Nov 20, 2019 at 17:53

drldcsta

4233 silver badges8 bronze badges

2 Comments

Underoos Over a year ago

My shell commands are using \copy which is a client side CSV creation which was working fine when I ran the script normally. But if I use the above mentioned approach, it is considering \copy as COPY which is server side copy for which the db user doesn't have access to.

tripleee Over a year ago

You need to use read -r to have read not mangle backslashes. You really basically always want the -r option with read unless you specifically require the wacky legacy behavior from the original sh.

Collectives™ on Stack Overflow

How to run shell script commands in an sh file in parallel?

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related