How to run sql script in sequence using docker compose

Open Source Projects Compose
,
1

I’d like to run a script “amend_user.sql” after the users are created using the flyway job.
Please advise.

Many thanks,

Sequence of steps:

  • Download image & create postgres instance
  • Run the flyway job
  • Execute “amend_user.sql” in postgres database

In the below docker-compose.yaml, this script runs as soon as the service starts whereas i would like it to run at the end or after step 2.

version: '3.8' services:   
 db:
    container_name: postgresDB
    image: postgres:15.1
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
    ports:
      - '5432:5432'
    volumes:
      - postgres:/var/lib/postgresql/data
      - ./scripts/amend_user.sql:/docker-entrypoint-initdb.d/amend_user.sql
    build:
      context: .
      dockerfile: dockerfile-db

  flyway_admin:
    image: redgate/flyway:6.6.1
    environment:
      - FLYWAY_USER=postgres
      - FLYWAY_PASSWORD=password
      - FLYWAY_URL=jdbc:postgresql://db:5432/postgres
      - FLYWAY_LOCATIONS=filesystem:/flyway/admin/versions
    command: -connectRetries=60 check
    volumes:
      - ./admin/:/flyway/sql/admin
    depends_on:
      - db

   volumes:   
     postgres:
2

Afaik, what you are trying to do is not really solvable with compose in a good way.

The postgres entrypoint script will run once, when /var/lib/postgresql/data has no existing database. So if your postgres volume has existing data in it, it will not be executed. It is part of the initial “bootstrapping”, which will have to finsh, before flyway is able to use the database.

Thus said, why don’t you put the amend_user.sql as R__amend_user.sql in your flyway folder and let flyway handle it as repeatable migration?

3

apologies for the duplicate post. Im trying to find a way to close it, which ill try shortly.

thanks for your reply. The reason why it cant go in the flyway, as this script only fixes the docker instance not the other envs that the scripts are part of.

I have found a workaroud by mapping another volume and creating another service to call the script but i have another issue. I will now update the main ticket as i now need an advice on it. thanks

4

updated::

The below is a workaround but for some real odd reason it throws the below error in the log… I am able to execute as root from the container but when i do the docker compose up, it fails? Any ideas why?

postgres:
image: postgres:15.1
environment:
  - POSTGRES_USER=postgres
  - POSTGRES_PASSWORD=password123
depends_on:
  - db
  - flyway_admin
volumes:
  - ./scripts/password_update.sh:/password_update.sh
command: ["/bin/bash", "/password_update.sh"]

password_update.sh

 #!/bin/bash -x
set -e
echo "begining of tab"
psql --username "postgres" --dbname "postgres" --port 5432 <<-EOSQL
create table public.nn (id int);
EOSQL
echo "begining of tab"

docker-compose up log

postgres_1      | begining of tab
postgres_1      | psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
postgres_1      |       Is the server running locally and accepting connections on that socket?
5

I assume the service db is the one you use with flyway_admin to provision the sql migrations. Shouldn’t the service postgres use the database from service db as well? Righ now, your script tries to access the local database inside the postgres service through a local socket connection, which didn’t start since you specified a command (which prevents the entrypoint script from being executed)

Though, my suggestion with the repeatable migration is to mount this single script as volume into the flyway folder inside the flyway container. You would simply “inject” it inside the container.

6

how do i link service ‘postgres’ with the ‘db’? With the flyway_admin, i assume this command “FLYWAY_URL=jdbc:postgresql://db:5432/postgres” does the linking with the only container locally?

I really want to keep the flyway_admin seperate as the scripts in it are common for all our other envs outside docker.
Saying that, the flyway creates the initial user and for docker only, i want to alter the user password as soon as the flyway_admin is run/complete.

Ive even tried linking it as below with no luck. Any thoughts’ ?

 postgres:
    image: postgres:15.1
    depends_on:
      - flyway_admin
      - db
    environment:
      - DB_HOST: 0.0.0.0
      - DB_PORT: 5432
      - DB_USER: postgres
      - DB_PASSWORD: password123
      - POSTGRES_PASSWORD=password123
    command: ./password_update.sh
    volumes:
      - ./scripts/password_update.sh:/password_update.sh
7

There is no container linking at all. Docker provides dns-based service discovery in custom networks (which the default network docker compose creates for you is) based on service or container names. You simply use the other service’s name as host name and the dns-based service disovery will resolve it to the right container.

The pqsl command in the password_update.sh script lacks -h "db" arguments.

Though, from my perspective the approach you are following is less clean than actually injecting the sql script into the flyway container and letting flyway handle it.

I kind of get the feeling that you don’t seem to understand my suggestion. If you map the file inside the container, it executes this sql script as an additional migration. It does neither change your flway image, nor the git repo you use to build your flyway image. As such it doesn’t affect anything than the container’s created by this compose file.

8

hello, i do appreciate your help on this matter…
So, i did add --host “0.0.0.0” and failed with the same error as before.

On your second point, even if i add this script to the flyway as part of the migration, it will mess up the other envs. How do i tell this user password update is for docker only?

e.g.

psql --username "postgres" --dbname "postgres" --port 5432 <<-EOSQL
update role test password 'test';

postgres database is a default database in any postgres instance and all users are common to all databases. The above would be applied to docker and all other envs outside docker.

Sorry if im being stupid here…

9

Please re-read my last post!

11

Hi, sorry to trouble you again but can you advise why the docker-compose does not run in sequence even though ive added the “depend_on”? Its perhaps this reason why it cant find the user “test”, which is created by flyway_admin…?

flyway_admin_1 | Database: jdbc:postgresql://db:5432/postgres (PostgreSQL 15.1)
flyway_admin_1 | WARNING: Flyway upgrade recommended: PostgreSQL 15.1 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 14.
flyway_admin_1 | Successfully validated 4 migrations (execution time 00:00.032s)
flyway_admin_1 | Creating Schema History table “public”.“flyway_schema_history” …
flyway_admin_1 | Current version of schema “public”: << Empty Schema >>
postgres_1 | begining of tab
postgres_1 | ERROR: role “test” does not exist
flyway_admin_1 | Migrating schema “public” to version “1 - Create extensions”
postgres_1 | begining of tab

12

perhaps is not linking to the existing service
psql -v ON_ERROR_STOP=1 --username "postgres" --dbname "postgres" --host "db"

13

I already wrote about it:

Since you run your containers as compose services, they are in a custom network, and therefor dns-based service discovery is available. Container linking is a deprecated relic from ancient times, and it only made sense in the default bridge network, as it never provided service discovery.