Postgres Docker upgrade fails with connection to database failed: could not connect to server: No such file or directory

Question

I am trying to upgrade PostgreSQL using a Docker container. During the upgrade process I am getting an error:

connection to database failed: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/upgrade/.s.PGSQL.50432"?"

I would appreciate any assistance on this issue.

My Dockerfile contains:

FROM postgres:13
ENV PG_MAJOR=13
ENV PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/13/bin
EXPOSE 5432
RUN mkdir -p /var/run/postgresql  \
        && chown -R postgres:postgres /var/run/postgresql  \
        && chmod 2777 /var/run/postgresql
ENV PGBINNEW /usr/lib/postgresql/13/bin
ENV PGBINOLD /usr/lib/postgresql/11/bin
ENV PGDATAOLD /var/lib/postgresql/11/data
ENV PGDATANEW /var/lib/postgresql/13/data
ENV PG_VERSION=13.3-1.pgdg100+1
RUN mkdir -p "$PGDATAOLD" "$PGDATANEW" \
        && chown -R postgres:postgres /var/lib/postgresql
RUN mkdir -p /var/log
WORKDIR /var/lib/postgresql
COPY docker-upgrade /usr/local/bin/
RUN mkdir -p /var/log
RUN touch /var/log/postgresql.log
RUN chmod 777 -R /var/log/
RUN chmod 777 -R /var/log/postgresql.log
RUN chmod 777 -R /usr/local/bin/docker-upgrade

ENTRYPOINT docker-upgrade

# recommended: --link
CMD pg_upgrade

I am running it with the following command:

docker run                                         \
  -w /tmp/upgrade                                  \
  -v "../data/postgres-$NEW-upgrade:/tmp/upgrade"  \
  -v "../11.1/data:/var/lib/postgresql/11/data"    \ # old dir
  -v "../13/data/:/var/lib/postgresql/13/data"     \ # new Dir
  -v /usr/pgsql-11/bin/:/usr/lib/postgresql/11/bin \ # old 11 binary at host machine e 
  "pgupgrade"

My upgrade log says:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/upgrade/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/11/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp/upgrade'" start
Failure, exiting

postgres_server_log (pg_upgrade)

/usr/lib/postgresql/11/bin/postgres: error while loading shared libraries: libssl.so.10: cannot open shared object file: No such file or directory
no data was returned by command ""/usr/lib/postgresql/11/bin/postgres" -V"
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "/usr/lib/postgresql/11/bin/pg_ctl".
Check your installation.

larsks · Accepted Answer · 2021-06-30 20:49:07Z

1

It looks as if you've already identified the problem:

/usr/lib/postgresql/11/bin/postgres: error while loading shared libraries:
libssl.so.10: cannot open shared object file: No such file or directory

Your postgresql 11 binaries require a version of openessl that's not available in the postgres:13 image. This is probably due to major changes between Debian version 9 (used in the postgres:11 image) and Debian version 10 (used for the postgres:13 image).

The easiest way to resolve this is to install postgresql 11 from the packages available in the postgres:13 image, rather than trying to mount them from your host.

Consider modifying your Dockerfile to look something like this:

FROM postgres:13

# Install binaries for postgresql 11
RUN apt update && apt install -y postgresql-11 postgresql-12

COPY docker-upgrade.sh /usr/local/bin/docker-upgrade.sh
RUN chmod 755 /usr/local/bin/docker-upgrade.sh

Here I'm installing both postgres version 11 and version 12, because this way we can use the same image for upgrading from 11 -> 12, 11 -> 13, or 12 -> 13.

My docker-upgrade.sh looks like this:

#!/bin/sh

PGBINNEW=/usr/lib/postgresql/$PGNEW/bin
PGBINOLD=/usr/lib/postgresql/$PGOLD/bin

export PGBINNEW PGBINOLD

PGDATANEW=/var/lib/postgresql/$PGNEW
PGDATAOLD=/var/lib/postgresql/$PGOLD

export PGDATANEW PGDATAOLD

chown postgres:postgres $PGDATANEW
chmod 750 $PGDATANEW
runuser -u postgres -- initdb $PGDATANEW

cd $PGDATANEW
runuser -u postgres -- pg_upgrade

On my system, I've build a new image named pgupdgrade using the Dockerfile and script:

docker build -t pgupgrade .

Putting it all together, assuming that we have old data in a volume named pgdata12, I can create a new pgdata13 volume with the upgraded data by running the pgupdate image like this:

docker run --rm  \
  -v pgdata12:/var/lib/postgresql/12 \
  -v pgdata13:/var/lib/postgresql/13 \
  -e PGNEW=13 \
  -e PGOLD=12 \
   pgupgrade docker-upgrade.sh

This will initialize the new volume using initdb and then run the pg_upgrade process. Once the container exits, I can start up a new postgres 13 container using the pgdata13 volume:

$ docker run -v pgdata13:/var/lib/postgresql/data postgres:13
PostgreSQL Database directory appears to contain a database; Skipping initialization

2021-06-30 20:48:37.398 UTC [1] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-30 20:48:37.398 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-06-30 20:48:37.398 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-06-30 20:48:37.400 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-30 20:48:37.403 UTC [21] LOG:  database system was shut down at 2021-06-30 20:46:37 UTC
2021-06-30 20:48:37.406 UTC [1] LOG:  database system is ready to accept connections

answered Jun 30, 2021 at 20:49

larsks

318k49 gold badges473 silver badges482 bronze badges

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

3 Comments

Justin Mahesh Over a year ago

Thx larsks ...The script went fine till initdb .While doing upgrade,it fails as could not open version file "/var/lib/postgresql/11/PG_VERSION": Permission denied Failure, exiting .However ,I can see the PG_VERSION as root user inside the container but when i switch to postgres user then getting ""Permission denied".My host is centos 7,Is there any mismatch of postgres user of host and docker?Thx a lot for the script ..appreciate any inputs

larsks Over a year ago

Because you're using a bind mount, rather than docker volume, there are probably conflicts between the ownerhsip of the files vs. the postgres user in the container. Update the script to chown -R postgres:postgres $PGDATAOLD $PGDATANEW.

Justin Mahesh Over a year ago

worked fine thanks a lot. sir. Somehow explicit start and stopping of old postgres may be needed in script because while upgrade getting error as shutdown not happened properly in old cluster.FYI...thx a lot

Collectives™ on Stack Overflow

Postgres Docker upgrade fails with connection to database failed: could not connect to server: No such file or directory

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related