Add localization configuration to Postgres installation

[Eprince-hub]

Closes https://github.com/upleveled/system-setup/issues/73

Add the Perl commands to set the lc_time and lc_messages to English during Postgres installations. Remove the LC_ALL setting from the macOS setup, as it is no longer required. Setup a PGDATA variable for the Linux system as this is not set by the current commands

Windows

The current setup doesn't set language

echo "export PATH=\$PATH:\"/c/Program Files/PostgreSQL/16/bin\"" >> "$USERPROFILE/.bash_profile"
echo "export PGDATA=\"/c/Program Files/PostgreSQL/16/data\"" >> "$USERPROFILE/.bash_profile"
echo "export PSQL_PAGER=\"less --chop-long-lines --header 1\"" >> "$USERPROFILE/.bash_profile"
source "$USERPROFILE/.bash_profile"

After the PR, the language is set

echo "export PATH=\$PATH:\"/c/Program Files/PostgreSQL/16/bin\"" >> "$USERPROFILE/.bash_profile"
echo "export PGDATA=\"/c/Program Files/PostgreSQL/16/data\"" >> "$USERPROFILE/.bash_profile"
echo "export LC_ALL=en_US.UTF-8" >> "$USERPROFILE/.bash_profile"
echo "export PSQL_PAGER=\"less --chop-long-lines --header 1\"" >> "$USERPROFILE/.bash_profile"
source "$USERPROFILE/.bash_profile"

postgresql.conf file spec

One parameter is specified per line. The equal sign between name and value is optional. Whitespace is insignificant (except within a quoted parameter value) and blank lines are ignored. Hash marks (#) designate the remainder of the line as a comment. Parameter values that are not simple identifiers or numbers must be single-quoted. To embed a single quote in a parameter value, write either two quotes (preferred) or backslash-quote. If the file contains multiple entries for the same parameter, all but the last one are ignored.

https://www.postgresql.org/docs/current/config-setting.html#:~:text=Whitespace%20is%20insignificant%20(except%20within%20a%20quoted%20parameter%20value)%20and%20blank%20lines%20are%20ignored

Perl error when file not found

 ~ perl -i -pe 's/^[#\s]*lc_messages\s*=.+$/lc_messages = '\''en_US.UTF-8'\''/' testings/testee.conf
Can't open testings/testee.conf: No such file or directory.

Setting the lc_messages in the postgresql.conf file

macOS

• PGDATA is already being set on macOS, and the postgresql.conf file is always inside there

To edit the postgres.conf file and set the `lc_message = 'en_US.UTF-8'. Any of the following commands can be used

• sed: sed is available by default in macOS

• Sets error message to sed if the wanted file is not found

  sed -i -E "/^[[:space:]]*lc_messages[[:space:]]*=/ s/^#*.*$/lc_messages = 'en_US.UTF-8'/" "$PGDATA/postgresql.conf" || echo "File $PGDATA/postgresql.conf does not exist" >&2

  

• Allows sed returns default error message if wanted file is not found

  sed -i -E "/^[[:space:]]*lc_messages[[:space:]]*=/ s/^#*.*$/lc_messages = 'en_US.UTF-8'/" "$PGDATA/postgresql.conf"

  

• If the wanted file is available Before command

  

After command

Other possible commands on macOS Using perl

perl -i -pe 's/^\s*lc_messages\s*=\s*.*/lc_messages = '\''en_US.UTF-8'\''/' "$PGDATA/postgresql.conf"

perl error: Can't open nothing/postgresql.conf: No such file or directory.

Using awk

awk '/^[[:space:]]*lc_messages[[:space:]]*=/{gsub(/=.*/, "= '\''en_US.UTF-8'\''")}1' "$PGDATA/postgresql.conf" > temp && mv temp "$PGDATA/postgresql.conf"

awk error:

 ~  awk '/^[[:space:]]*lc_messages[[:space:]]*=/{gsub(/=.*/, "= '\''en_US.UTF-8'\''")}1' "nothing/postgresql.conf" > temp && mv temp "nothing/postgresql.conf"
awk: can't open file nothing/postgresql.conf
 source line number 1

Linux

The PGDATA cannot be set in Linux as the location could change depending on the Linux distribution being run. The path to the postgresql.conf file should be determined dynamically

Using sed

sed -i -E "/^[[:space:]]*lc_messages[[:space:]]*=/ s/^#*.*$/lc_messages = 'eu_US.UTF-8'/" "$(sudo -u postgres psql --tuples-only --pset format=unaligned --command 'SHOW config_file;')"

OR

sed --in-place --regexp-extended "/^[[:space:]]*lc_messages[[:space:]]*=/ s/^#*.*$/lc_messages = 'eu_US.UTF-8'/" "$(sudo -u postgres psql --tuples-only --pset format=unaligned --command 'SHOW config_file;')"

Using perl

perl -i -pe 's/^\s*lc_messages\s*=\s*.*/lc_messages = '\''en_US.UTF-8'\''/' "$(sudo -u postgres psql --tuples-only --pset format=unaligned --command 'SHOW config_file;')"

Using awk

awk '/^[[:space:]]*lc_messages[[:space:]]*=/{gsub(/=.*/, "= '\''en_US.UTF-8'\''")}1' "$(sudo -u postgres psql --tuples-only --pset format=unaligned --command 'SHOW config_file;')" > temp && mv temp "$(sudo -u postgres psql --tuples-only --pset format=unaligned --command 'SHOW config_file;')"

Tested with a Linux + PostgreSQL Docker image setup using the progress installation from the system setup

Before command

After command

postgresql.conf file path

Windows

Using hyper as this will enable us use the same commands we already used for macOS and Linux

Using sed

sed -i -E "/^[[:space:]]*lc_messages[[:space:]]*=/ s/^#*.*$/lc_messages = 'en_US.UTF-8'/" "$PGDATA/postgresql.conf"

Using perl

perl -i -pe 's/^\s*lc_messages\s*=\s*.*/lc_messages = '\''en_US.UTF-8'\''/' "$PGDATA/postgresql.conf"

Using awk

awk '/^[[:space:]]*lc_messages[[:space:]]*=/{gsub(/=.*/, "= '\''en_US.UTF-8'\''")}1' "$PGDATA/postgresql.conf" > temp && mv temp "$PGDATA/postgresql.conf"

Before command

After command

Errors when the file is not found