docker-pgautoupgrade

docker-pgautoupgrade copied to clipboard

I'm trying to upgrade from postgres 14 to 15 while retaining data.

When I use the image to pgautoupgrade/pgautoupgrade:latest instead of postgres:14 in the compose file and restart, I get the following errors.

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
If you want to create a new database system, either remove or empty
he directory "/var/lib/postgresql/data" or run initdb
with an argument other than "/var/lib/postgresql/data".

Maybe I'm misunderstanding but shouldn't the data directory be not empty? It should, and does, have data from version 14 that I want migrated to 15. Thank you for any help.

Oh, now that's strange. It's supposed to have moved your old data files to an "old" subdirectory prior to doing that, and it's also supposed to have created a brand new subdirectory (called "new" too) to do the initdb into.

https://github.com/pgautoupgrade/docker-pgautoupgrade/blob/585a87d03365fa238915a933c24d1bf68c9af3f6/docker-entrypoint.sh#L377-L402

Which docker image / tag is this happening with?

I'm using postgres:14.5 from here.

I would also like say I have this warning regarding psql.

WARNING: The supplied POSTGRES_PASSWORD is 100+ characters.

  This will not work if used via PGPASSWORD with "psql".

  https://www.postgresql.org/message-id/flat/E1Rqxp2-0004Qt-PL%40wrigleys.postgresql.org (BUG #6412)
  https://github.com/docker-library/postgres/issues/507

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Could 100+ character could be a sticking point?

Interesting. I've not personally come across a situation where people were using a 100+ character password, so I didn't know it was even a thing.

I'll have to look into it over the next few days. :smile:

---

On a different note, the docker image you're coming from (postgres:14.5) is not an "alpine" based image. So I'd kind of expect it'll probably not be totally 100% compatible.

You're not the first person to try the pgautoupgrade docker image coming from a non-alpine one, so it's clearly something we should try better to support. That's another thing I'll need to investigate over the next few days as well. :smile:

Thank you so much. I understand this is an open source project so I greatly appreciate your contribution and time into this.

(Fat fingers, erroneously closed the issue)

No worries at all. Thanks for trying it out, and providing feedback, which is how projects improve over time. :smile:

I also got this error, starting from an postgres:11-alpine to pgautoupgrade/pgautoupgrade:latest (currently latest: 15-alpine3.8).

I upgrading a Weblate database.

Here is my log:

PostgreSQL Database directory appears to contain a database; Skipping initialization
******************************************************
Performing PG upgrade on version 11 database files
******************************************************
Moving the old database files prior to pg_upgrade
'/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'
'/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base'
'/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
mv: can't rename '/var/lib/postgresql/data/old': Invalid argument
'/var/lib/postgresql/data/old' -> '/var/lib/postgresql/data/old/old'
'/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts'
'/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem'
'/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf'
'/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf'
'/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical'
'/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact'
'/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify'
'/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot'
'/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial'
'/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots'
'/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat'
'/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp'
'/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans'
'/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc'
'/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase'
'/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal'
'/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact'
'/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf'
'/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf'
'/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
PostgreSQL 11 database files found, upgrading to PostgreSQL 15
Initialising PostgreSQL 15 data directory
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data/new ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
    /usr/local/bin/pg_ctl -D /var/lib/postgresql/data/new/ -l logfile start
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:  role "postgres" does not exist
could not connect to source postmaster started with the command:
"/usr/local-pg11/bin/pg_ctl" -w -l "/var/lib/postgresql/data/new/pg_upgrade_output.d/20230806T174251.151/log/pg_upgrade_server.log" -D "/var/lib/postgresql/data/old" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
Data page checksums are disabled.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".

And the corresponding DB service into docker-compose:

  database:
    # image: postgres:11-alpine
    image: pgautoupgrade/pgautoupgrade:latest
    container_name: weblate_database
    env_file:
      - ./environment
    volumes:
      - postgres-data:/var/lib/postgresql/data
    restart: always
    networks:
      - backend

environment contains Postgres env (I set fake data, but my password use special chars like @):

POSTGRES_PASSWORD=p@ssw0rd
POSTGRES_USER=weblate
POSTGRES_DATABASE=weblate
POSTGRES_HOST=database
POSTGRES_PORT=

The database is a little bit heavy (the exported SQL of the database is greater than 320mb)

Thanks for the bug report. Looking at the log output there, this line is standing out:

connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:
 role "postgres" does not exist

That sounds like your PostgreSQL database uses something other than the "postgres" user as the superuser. Probably weblate by looking at your environment variables. :smile:

Co-incidentally, the recent dev tagged image has some support for that. If you try it again, but using the dev tagged image there's a decent chance it'll work.

Would you be ok to try that out and let us know if it does indeed do the job?

If it does, we can promote the dev tag to latest (etc) so others in the same situation have things automatically work for them too. :smile:

I tested with dev tag, it works now!

Excellent! I've pushed a rebuilt dev image to 0.5.0, latest, and 15-alpine3.8 tags.

That should fix this problem for others using a similar setup too. :smile:

I have a very similar case. I had the same error as this issue description. Found the culprit in my case, logs:

postgres-postgres-1    | ------------------------------------
postgres-postgres-1    | 2023-11-19 22:48:45.170 CET [29] FATAL:  lock file "postmaster.pid" already exists
postgres-postgres-1    | 2023-11-19 22:48:45.170 CET [29] HINT:  Is another postmaster (PID 1) running in data directory "/var/lib/postgresql/data/old"?

After that, postgres wanted to start, but the data directory contained new and old subdirs instead of typical data. After restoring the old content, removing the postmaster.pid it proceeded succesfully without any more issues.

I'm assuming the issue arose due to my auto-upgrade setup. Is watchtower recommended for upgrading the postgres? What could be the cause for dangling pidlock file?

Hi, thanks so much for this awesome project

I'm getting this error

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data".

here's my docker compose

  postgres:
    image: 'pgautoupgrade/pgautoupgrade:16-alpine'
    environment:
      POSTGRES_USER: 'redacted'
      POSTGRES_PASSWORD: 'redacted'
      POSTGRES_DB: 'redacted'
      PGDATA: '/var/lib/postgresql/data/pgdata'
    volumes:
      - 'postgres:/var/lib/postgresql/data'
    ports:
      - '5432'

before the upgrade, image was 'postgres:12-alpine'

any ideas?

Hmmm, that sounds like the initdb command the script runs is somehow not being pointed to /var/lib/postgresql/data/new. That's a temporary directory created by the script before hand just for initdb purposes.

The way I tend to debug stuff like this is by starting the container (with this image and the PG data mounted), but tell the script to stop just before the upgrade runs. Then I manually run the lines of the upgrade script 1 by 1 to see where it breaks, and why.

That whole "tell it to stop just before the upgrade script runs" is done by defining the environment variable PGAUTO_DEVEL=before before starting the container. I normally do it from the command line, but you could do the same thing using the docker compose.

If you're up for investigating, that'd be the approach to use. Note, it's not something you'd want to be debugging live on your production database, as the database will be unavailable to other applications meanwhile. ie if you're up for it, then do it in a copy somewhere where it won't affect things

Thoughts? :smile:

will work on that

For reference, this is the code to manually step through when trying to figure out what's going wrong:

https://github.com/pgautoupgrade/docker-pgautoupgrade/blob/54179ffc234a5a095bf7e7e5a52f0eb52a7f9912/docker-entrypoint.sh#L362-L553

And yep, it's copiously commented and tries to be as straight forward as possible. ie tries to be easy to see what it's doing at all times

thanks!

so, the place where i have the problem is a hosted environment that doesn't have great logs and such. but i can push whatever i want there and then ssh in while it's running.

i'm not the greatest at docker - here's how i'm trying to deploy without doing any upgrades: https://github.com/jjb/docker-pgautoupgrade/commit/abbf1e31eafe39f4afabc124095bb5773e6810e7

that failed, without any logs (don't ask). any ideas?

Ahhh. An important concept with Docker is that a container only runs while there's a main process running. If that main process ends, then the docker container will exit thinking it's work has been done.

By commenting out those three lines, you've most likely stopped the PostgreSQL (PG) server from launching... so there's nothing left to run and the container just exits.

To work around that, add a never ending while loop to the end of that docker entry point script:

while :; do
    sleep 5
done

That'll leave the container running, then with another instance of your terminal program you can jump into the running container:

$ docker exec -it CONTAINER_NAME /bin/bash

From there, you can switch to the postgres user and prod things as needed.

Does that help? :smile:

never mind, i see the entrypoint needs to run... will figure out a better way to NOOP it

haha posted that at the same time your message came in

thanks for the suggestion! in my case, pg does need to come up so healtchchecks are good, otherwise i can't ssh in. so i just need to NOOP the upgrade and allow postgres to run. looking into making a clean short circuit now.

Cool, all good. :smile:

🤷 https://github.com/jjb/docker-pgautoupgrade/commit/24b809bcd873cf951303460d15dc0274f3c80fc8

"root" execution of the PostgreSQL server is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server.

Hmmm, if you want to have PostgreSQL run without the upgrade happening, you could just comment out the whole upgrade piece. Or you could muck around with the test that it uses for determining if the upgrade should happen:

https://github.com/jjb/docker-pgautoupgrade/blob/24b809bcd873cf951303460d15dc0274f3c80fc8/docker-entrypoint.sh#L375

Changing that line to something like this would stop the upgrade from automatically running too:

if [ "one" != "two" ]; then

^^^ that'll always fail, so the stuff inside the then clause won't run.

"root" execution of the PostgreSQL server is not permitted.

Is that when trying to manually start PostgreSQL after you've jumped into a running container? If so, then switch to the postgres user first (su - postgres).

trying this at the end of the entrypoint script

exec su-exec postgres "$@"

Hmmm, that doesn't sound like the right approach (to me).

---

Oh, there might be a conceptual misunderstanding.

For the upgrade pieces to run, it needs to happen when the PostgreSQL container itself is running... but the PostgreSQL server software inside the container has to be shut down.

That's why the upgrade scripting check and commands are before the entry point script starts PostgreSQL. That way, the upgrade is all taken care of and done before PostgreSQL starts.

Does that make sense, or am I misunderstanding the approach you're taking? :smile:

sure, that makes sense

but, can't i still hack around on the running instance? (this is all in a throw-away environment) either by first shutting down pg, or just not caring and plowing over files to test the commands?

postgres: could not access the server configuration file "/var/lib/postgresql/data/pgdata/postgresql.conf": No such file or directory

i'll try your one=two approach

but, can't i still hack around on the running instance?

Sure, you definitely can. Try running the docker container with that PGAUTO_DEVEL environment variable defined.

That'll leave the docker container running, before the upgrade script runs.

You can then jump into the container (from another terminal session) using the docker exec command, and if you switch to the postgres user you can start the database (pg_ctl start) and/or try manually running the upgrade commands.

in my environment, i need a passing pg healthcheck before i can SSH in

initdb: error: directory "/var/lib/postgresql/data/pgdata" exists but is not empty initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data/pgdata" or run initdb with an argument other than "/var/lib/postgresql/data/pgdata".