0

I am trying to automate an app deployment process which includes creating a new postgresql database and configure initial settings in a few lines. I can already do this via the terminal manually, however I am struggling to get this working from an .sh script, what I have so far which is not working:

#!/bin/sh
sudo -u postgres psql
read -p "Enter a unique Postgresql db name: " DBN
CREATE DATABASE $DBN;
read -p "Enter a name for Database $DBN: " USN
read -p "Enter a password for user $USN: " USPSD
CREATE USER USN WITH PASSWORD 'USPSD';
ALTER ROLE USN SET client_encoding TO 'utf8';
ALTER ROLE USN SET default_transaction_isolation TO 'read committed';
ALTER ROLE USN SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE DBN TO USN;
\q

The above starts starts an interactive postgresql session but doesn't go any further. This is the output:

could not change directory to "/home/ubuntu": Permission denied
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.
postgres=#
fkay
  • 184
  • 1
  • 10
  • You are mixing shell script with bare SQL; this is like mixing Chinese with shell script as far as the shell is concerned (and vice versa, if you tried to put shell script in your SQL). – tripleee Dec 09 '22 at 11:20
  • The interactive prompts with `read` are also a problematic design; scripts in general work much better together if you pass machine-readable input as command-line arguments. – tripleee Dec 09 '22 at 11:22
  • thank you, I understand the difference between the two now. From your answer in the link above, I understand how it should work in theory. Is there a chance you could provide an example for psql similar to the ssh one in the previous post? – fkay Dec 09 '22 at 12:33
  • E.g. https://stackoverflow.com/questions/16542218/insert-bash-variables-in-psql-command demonstrates this for `psql` – tripleee Dec 09 '22 at 17:16
  • See also https://stackoverflow.com/questions/28803651/how-to-execute-multiple-queries-using-psql-command-from-bash-shell which includes additional options, including a here document, which is probably what you want here. – tripleee Dec 09 '22 at 17:22

0 Answers0