Unwanted multiple duplicate lines - sed

Question

Unwanted multiple duplicate lines - sed

There is a task to select addresses from the postgresql database, then connect to the selected addresses via ssh and add a specific line in the right place in the config on each host.

For example, there is a file containing values ​​in the middle of the file, after which you need to insert a new line with a different value:

# grep hostname config.cfg

hostname, port = 2234: Vasya
hostname, ip address: John, 192.168.1.5
hostname: Andrew

Next, for example, after the line

hostname: Andrew

I need to insert a new value on a new line:

hostname: Sheglina

I do it like this (tried for one host first):

for HOST in 192.168.1.1; do ssh $HOST "for VALUE in $(nl -ba /root/config.cfg|grep hostname | tail -1 | awk '{print $1}'); do sed -i $VALUE'a\hostname: Sheglina'/root/config.cfg; done"; done

This command works on a VIRTUAL MACHINE.

# grep hostname config.cfg

hostname, port = 2234: Vasya
hostname, ip address: John, 192.168.1.5
hostname: Andrew
hostname: Sheglina

BUT when I do on a real server, making a selection of ip addresses by filters, then I try to apply this command:

for HOST in $(psql -U postgres name_db -c "SELECT name, ip_address FROM servers ORDER BY ip_address;" | grep -i 'moscow' | awk '{print $5}'); do ssh $HOST "for VALUE in $(nl -ba /root/config.cfg|grep hostname | tail -1 | awk '{print $1}'); do sed -i $VALUE'a\hostname: Sheglina '/root/config.cfg;done"; done

then the hostname: Sheglina line is duplicated through each line in /root/config.cfg, not in one place. I do not need it. I don't understand why this command works for VIRTUAL MACHINE, but for SERVER it doesn't work. And even if I go to a separate node from the cluster and issue a command for the same node, there are still duplicate lines ... What could be the reason?

How it's look in REAL SERVER:

# grep hostname config.cfg
hostname: Sheglina
hostname: Sheglina
hostname: Sheglina
hostname: Sheglina
...

the old lines with the hostname value remained in the file, but the Sheglina line is duplicated through each line for some reason

Answer 1

You're making this far more complicated than it needs to be. Instead of trying to do this all in one long, complicated, and difficult to read line, break it down into steps and do them sequentially.

There are two things this script needs to do

1. fetch a list of ip addresses where the name field is like moscow from psql (using a case-insensitive match)
2. connect to each of them with ssh and insert hostname: Sheglina into /root/config.cfg, immediately after the line containing hostname: andrew.

For example:

#!/bin/bash

city='moscow'
sql="SELECT DISTINCT ip_address FROM servers WHERE name ILIKE '$city'"

# get the query output into an array called 'hosts'
hosts=( $(psql -U postgres name_db -t --csv -c "$sql") )

for host in "${hosts[@]}"; do
  ssh "$host" "sed -i -e 's/^hostname: andrew/&\nhostname: Sheglina/i' /root/config.cfg"
done

This uses psql's built-in options to extract the required data in the right format. -t suppresses the header and footer output, and --csv requests comma-separated format. The SQL query selects only the matching values - there's no need to pipe it into grep or awk - and "DISTINCT" is used in the query to prevent duplicates.

Next, ssh is used to connect to each host, and sed is used to search for hostname: andrew and append hostname: Sheglina after it.

---

NOTE: quoting can be very difficult to get right in shell, especially if you need to pass quoted values to other programs (like psql or ssh) that also need their own quoting. You can use printf '%q' to convert strings into a properly-quoted format that can be used with such commands, but postgres uses its own quoting/escaping style unless you tell it to use a posix-like style by prefixing the quote with E. This tells poostgres that the quoted string might contain posix-style string escapes.

i.e. E'quoted-string' instead of just 'quoted-string'.

e.g. if $city contains any characters that need to be escaped by shell and postgres, use something like:

sql="SELECT DISTINCT ip_address FROM servers WHERE name ILIKE E'%"$(printf "%q" "$city")"'"