How do I use a shell script to tell if a postgres database table exists

user_11264544 0
Viewed 1463

I need to use entrypoint to determine whether the database is available and whether a table exists.

I use the following shell to determine if a table exists

Part of the dockerfile content

ENTRYPOINT ["bash","docker-entrypoint.sh"]

Shell script content

until PGPASSWORD=postgres psql -h "db" -U "postgres" -c '\q'; do
  >&2 echo "Postgres is unavailable - sleeping"
  sleep 1
done

# Prepare variables
TABLE=web_user
SQL_EXISTS=$(printf '\dt "%s"' "$TABLE")

# Credentials
USERNAME=postgres
PASSWORD=postgres
DATABASE=postgres

echo "Checking if table <$TABLE> exists ..."

# Check if table exists
PGPASSWORD="$PASSWORD" psql -h "db" -U $USERNAME -d $DATABASE -c "$SQL_EXISTS"
# using #!/bin/bash
if [[ $(PGPASSWORD="$PASSWORD" psql -h "db" -U $USERNAME -d $DATABASE -c "$SQL_EXISTS") ]]
then
    echo "Table exists ..."

else
    echo "Table not exists ..., init database"
    python3 manage.py makemigrations web
    python3 manage.py migrate
fi

db is the service name of the postgres container in compose

What I want is for the container to start for the first time, determine that the user table does not exist, then create a new database, and then restart the container to find that the user table already exists without creating a new data table

But when I use it on a Ubuntu16.04 base image, it simply outputs table exists... the first time the container starts

psql: FATAL:  the database system is starting up
Postgres is unavailable - sleeping
Checking if table <web_user> exists ...
No matching relations found.
Table exists ...

But what I expect is to printtable not exists..., the init database and subsequent commands

The output using the python alpine base image is as follows

Checking if table <web_user> exists ...
Did not find any relation named ""web_user"".
Table not exists ..., init database
Migrations for 'web':

The difference is as follows:
No matching relations found. is based on the output of the ubuntu image,
Did not find any relation named ""web_user"". based on the output of the python:3.6.9-alpine image

Why isn't it the else part that goes?

I would appreciate it if you could tell me why did this error occur and how to fix it?

dockerfile docker shell postgresql bash
2 Answers

my guess is that the if statement is true because you successfully connect to the db (and has nothing to do whether that connection finds the string or not)

like try this:

if [[ $(PGPASSWORD="$PASSWORD" psql -h "db" -U $USERNAME -d $DATABASE) ]]
then
  echo connection successful
  echo basing your previous if on:
  echo $(PGPASSWORD="$PASSWORD" psql -h "db" -U $USERNAME -d $DATABASE -c "$SQL_EXISTS")
fi
user_41298320

I seem to have solved this problem by changing the base image from ubuntu16.04 to ubuntu 18.04. But I don't know what causes this.

user_112645440
Related