Unable to get flyway to connect to postgres through unix socket

Question

I am extending the docker postgres image by attempting to run flyway migrations via an initialization script in docker-entrypoint-initdb.d.

As pointed out here, during the initialization phase the database is listing only on the unix socket.

I am having trouble running flyway migrate as I am unable to connect to the database via unix socket, and just keep getting variations of:

WARNING: Connection error: Connection to :5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. (Caused by Connection refused (Connection refused)) Retrying in 1 sec...

Dockerfile:

FROM postgres:10.14-alpine

ENV POSTGRES_USER=postgres
ENV POSTGRES_PASSWORD=password
ENV POSTGRES_DB=mydb
ENV FLYWAY_VERSION="6.0.8"
ENV JAVA_HOME /usr/lib/jvm/java-1.8-openjdk/jre
ENV PATH $PATH:/usr/lib/jvm/java-1.8-openjdk/jre/bin:/usr/lib/jvm/java-1.8-openjdk/bin
ENV JAVA_VERSION 8u181
ENV JAVA_ALPINE_VERSION 8.252.09-r0

RUN { \
    echo '#!/bin/sh'; \
    echo 'set -e'; \
    echo; \
    echo 'dirname "$(dirname "$(readlink -f "$(which javac || which java)")")"'; \
    } > /usr/local/bin/docker-java-home \
    && chmod +x /usr/local/bin/docker-java-home

RUN set -x \
    && apk add --no-cache \
    openjdk8-jre="$JAVA_ALPINE_VERSION" \
    && [ "$JAVA_HOME" = "$(docker-java-home)" ]

# Install Flyway
WORKDIR /flyway

RUN apk --no-cache add --update bash openssl \
    && wget https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${FLYWAY_VERSION}/flyway-commandline-${FLYWAY_VERSION}.tar.gz \
    && tar -xzf flyway-commandline-${FLYWAY_VERSION}.tar.gz \
    && mv flyway-${FLYWAY_VERSION}/* . \
    && rm flyway-commandline-${FLYWAY_VERSION}.tar.gz \
    && ln -s /flyway/flyway /usr/local/bin/flyway

# Change ownership of flyway to postgres user
RUN chown postgres /flyway/flyway

# Copy flyway sql migrations and configuration
COPY flyway-sql-migrations/sql_migrations /flyway/sql_migrations
COPY test-utils/flyway.conf /flyway/flyway.conf

# Copy flyway initialization script
COPY test-utils/flyway-migrate.sh /docker-entrypoint-initdb.d/flyway-migrate.sh

flyway-migrate.sh:

#!/bin/sh

flyway -configFiles=/flyway/flyway.conf migrate

flyway.conf:

flyway.url=jdbc:postgresql://:5432/mydb
flyway.user=postgres
flyway.password=password
flyway.locations=filesystem:/flyway/sql_migrations
flyway.table=flyway_schema_history
flyway.connectRetries=60

After doing some researching and consulting these posts (same issue, problem identified but no answer was given), I have tried connecting with the following variations of flyway.url strings, with no success:

flyway.url=jdbc:postgresql://:5432/mydb
flyway.url=jdbc:postgresql:///mydb
flyway.url=jdbc:postgresql://%2Fvar%2Flib%2Fpostgresql%2F.s.PGSQL.5432/mydb
flyway.url=jdbc:postgresql://%2Fvar%2Frun%2Fpostgresql%2F.s.PGSQL.5432/mydb
flyway.url=jdbc:postgresql://:5432/mydb?host=/var/run/postgresql
flyway.url=jdbc:postgresql:///mydb?host=/var/run/postgresql

Does anyone have any recommendations on how to configure the JDBC url so that I can connect flyway to postgresql during the initialization phase?

Thank you!

Why not do this from your application container, at its startup time? — David Maze
– David Maze, Commented Aug 31, 2020 at 2:04
I'm setting this database up as part of a compose file so I can run integration tests against a graphql server. Due to the nature of how docker-compose works, I think it would be better to have the database schema setup and seeded before I start the graphql server — Andrew Chung
– Andrew Chung, Commented Aug 31, 2020 at 2:20
I do not think flyway supports connections to unix socket. I would go with Andrew suggested execution from outside DB container, and making sure connection is up before. — GintsGints
– GintsGints, Commented Aug 31, 2020 at 6:59
You want to look over the docs for the postgres jdbc driver: jdbc.postgresql.org/documentation/head/connect.html. A quick read through that show that unix socket support is provided by a seperate driver you have to add. — Philip Liddell
– Philip Liddell, Commented Sep 1, 2020 at 9:36

Krišjānis Petručeņa · Accepted Answer · 2023-08-18 17:48:46Z

0

I've recently faced the exact same problem. The solution for me was to use container's default gateway IP.

To find it the following command can be used inside migration script:

ip route | awk '/default/ { print $3 }'

As a prerequisite install iproute2:

apt-get install -y iproute2

answered Aug 18, 2023 at 17:48

Krišjānis Petručeņa

134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Sekas · Accepted Answer · 2024-01-04 16:20:00Z

The problem is that during the initialization phase, the PostgreSQL database starts by default listening only on the UNIX socket, which is not accessible via TCP/IP network connections used by Flyway.

The easiest way to solve this is to manually restart the DB in your flyway-migrate.sh script and start it with listen_addresses='localhost', which configures Postgres to listen for connections on the TCP/IP network interface.

So, my flyway-migrate.sh is:

#!/bin/bash

echo '----    Restarting DB to enable IPv4 database listener    ----'
pg_ctl -D "$PGDATA" -m fast -w stop
pg_ctl -D "$PGDATA" \
            -o "-c listen_addresses='localhost'" \
            -w start
echo '-----------------    Restart completed    ---------------------'


flyway -configFiles=/flyway/flyway.conf migrate

Before the restart, the log shows that the server is listening only on a Unix socket:

2024-01-04 15:47:40.876 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

After the restart, the log indicates that the server is now listening on both IPv4 and Unix sockets:

2024-01-04 15:47:41.141 UTC [63] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-01-04 15:47:41.141 UTC [63] LOG:  could not bind IPv6 address "::1": Cannot assign requested address
2024-01-04 15:47:41.142 UTC [63] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

Collectives™ on Stack Overflow

Unable to get flyway to connect to postgres through unix socket

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related