tianon / docker-postgres-upgrade

a PoC for using "pg_upgrade" inside Docker -- learn from it, adapt it for your needs; don't expect it to work as-is!
https://hub.docker.com/r/tianon/postgres-upgrade/
MIT License
1.08k stars 116 forks source link

support custom username for example "-U my_username" when connecting #70

Open WalkerWalker opened 1 year ago

WalkerWalker commented 1 year ago

The database I am trying to upgrade has a custom username and therefore I would get the following error message when running the upgrade

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

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

would it be possible to support "-U my_username" ? Or how should I adapt the script ? thank you!

tianon commented 1 year ago

Does it work if you set "PGUSER" ?

WalkerWalker commented 1 year ago

First thank you so much for the reply and thank you in advance with the support. emm. Seems not. The error message is the same. Here is the exact command I ran.

docker run --rm --env PGUSER=my_username -v postgres-data:/var/lib/postgresql/12/data -v postgres-data14:/var/lib/postgresql/14/data tianon/postgres-upgrade:12-to-14

postgres-data is the volume used in postgres 12, mounted to /var/lib/postgresql/data postgres-data14 is the volume used in postgres 14, also mounted to /var/lib/postgresql/data In both container I can run psql -U my_username with no problem.

Here is the docker-compose file that can reproduce this error. Most likely the timescaledb structure is a bit different.

version: "3.8"
volumes:
  pg-data:
  pg-data14:

services:
  pg-upgrade:
    image: tianon/postgres-upgrade:12-to-14
    volumes:
      - pg-data:/var/lib/postgresql/12/data
      - pg-data14:/var/lib/postgresql/14/data
    environment:
      PGUSER: my_username

  pg12:
    image: timescale/timescaledb:2.8.1-pg12
    environment:
      POSTGRES_USER: my_username
      POSTGRES_PASSWORD: password
    volumes:
      - pg-data:/var/lib/postgresql/data

  pg14:
    image: timescale/timescaledb:2.8.1-pg14
    environment:
      POSTGRES_USER: my_username
      POSTGRES_PASSWORD: password
    volumes:
      - pg-data14:/var/lib/postgresql/data

Sequences of command

docker-compose up -d pg12
docker-compose up -d pg14
docker-compose stop pg12
docker-compose stop pg14
docker-compose up pg-upgrade

It will be nice to get some help in terms of adapting the script here. Thanks!

Hippoflip commented 1 year ago

I've been facing the same issue as you, I ended up tweaking the docker-upgrade script to change the initdb command in the third if statement like so :

if [ "$1" = 'pg_upgrade' ]; then
        if [ ! -s "$PGDATANEW/PG_VERSION" ]; then
                PGDATA="$PGDATANEW" eval "initdb --username=my_username"
        fi
fi

And then rebuilding the docker image. You also have to delete the folders created by your failed previous attempts, otherwise the new DB will not be init as you'd want to.

And then of course you need to add the PGUSER variable when running the image, like you mentioned earlier.

Lordroran commented 1 year ago

I got it to work with the official image and the following command: docker run --rm -e PGUSER=USER -e POSTGRES_INITDB_ARGS=--username=USER -v postgres-data:/var/lib/postgresql tianon/postgres-upgrade:11-to-15 --link

akitzing commented 1 year ago

cool, with both environment variables there are no more errors

bblanchon commented 1 year ago

It worked for me with the following changes in docker-compose.yml:

  environment:
      PGUSER: USER 
      POSTGRES_INITDB_ARGS: --username=USER

@tianon, could you include --username=$PGUSER in POSTGRES_INITDB_ARGS's default, so we only have to set the username once?