initdb: error "/var/lib/postgresql/data" exists but is not empty #7
Comments
|
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 docker-pgautoupgrade/docker-entrypoint.sh Lines 377 to 402 in 585a87d Which docker image / tag is this happening with? |
|
I'm using I would also like say I have this warning regarding 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. 😄 On a different note, the docker image you're coming from ( 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. 😄 |
|
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. 😄 |
|
I also got this error, starting from an I upgrading a Weblate database. Here is my log: 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:
- backendenvironment 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: That sounds like your PostgreSQL database uses something other than the "postgres" user as the superuser. Probably Co-incidentally, the recent 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 |
|
I tested with |
|
Excellent! I've pushed a rebuilt That should fix this problem for others using a similar setup too. 😄 |
|
I have a very similar case. I had the same error as this issue description. Found the culprit in my case, logs: 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 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 any ideas? |
|
Hmmm, that sounds like the 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 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? 😄 |
|
will work on that |
|
For reference, this is the code to manually step through when trying to figure out what's going wrong: docker-pgautoupgrade/docker-entrypoint.sh Lines 362 to 553 in 54179ff 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: jjb@abbf1e3 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: That'll leave the container running, then with another instance of your terminal program you can jump into the running container: From there, you can switch to the Does that help? 😄 |
|
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. 😄 |
|
|
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: Changing that line to something like this would stop the upgrade from automatically running too: ^^^ that'll always fail, so the stuff inside the |
Is that when trying to manually start PostgreSQL after you've jumped into a running container? If so, then switch to the |
|
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? 😄 |
|
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? |
i'll try your one=two approach |
Ahhhh. Oh, now that all makes sense. Ahhh, I have an idea. Gimme a minute to write it... 😄 |
|
Ok, so the concept I'm thinking of here is mostly working around that health check requirement. 😁 So, the things we need to have happen are: a) The container runs, starting PostgreSQL normally without attempting an upgrade. Your change of b) We need to shut down the PostgreSQL software inside the container after you've ssh-d in. If we do that though, then the main running loop of the program will be over, and the container will exit. But, we already know how to fix that using an endless while loop: Add one of those to the very end of the entry point script, so after we tell PostgreSQL to stop then the container keeps on running. c) Now you should be able to jump into the container ( So, that's only two small changes needed to the stock entry point script, and that should get you in the right spot. How does that sound? 😄 |
|
sounds good, thanks! but i think maybe you missed the error message i got when i tried my hack - see the text under the image here #7 (comment) |
|
so the if [ "$1" = 'postgres' ] && ! _pg_want_help "$@"; then
docker_setup_env
# setup data directories and permissions (when run as root)
docker_create_db_directories |
|
Ahhh. Hmmm, that error message isn't making sense to me if that's the only change to the script. Oh, it sounds like it's erroring out before it even gets to the upgrade piece. Just as an experiment, I'd try passing in an empty directory for your PostgreSQL data directory and see if that script still errors out or if the initdb piece runs ok. It could turn out to be something else unexpected is going on. |
|
okay, trying this _main() {
mkdir /tmp/my-empty-dir environment:
PGDATA: '/tmp/my-empty-dir'
volumes:
- 'postgres:/tmp/my-empty-dir' |
|
Hmmm, you might want to leave off the |
|
okay, just nuke the whole |
|
Yep, for testing purposes, just to see if the script runs ok with it gone. 😄 |
|
It looks like it's getting towards 8pm your time, and I'm starting to need sleep pretty badly myself. If you're up for it, we can schedule a time to investigate this properly over Skype or something when we're both fully awake? 😄 |
|
wow, thanks for the offer - although i fear the turnaround time on each experiment will be too long, so maybe we should get me a working shell first. you can find my email off of my website linked here https://github.com/jjb |
|
progress! and now i realized i should be using my existing pg version image 🤦 |
|
my existing version: 12 🫠 |
|
Ok, well that gets it a bit closer. 😄 I've just emailed you ( |
|
Heh Heh Heh. 12 isn't the worst I've seen. I started this project due to needing to upgrade a bunch of 9.something production systems. 😁 |
|
🫠 isn't because it's old but because your upgrade image doesn't support it... so i can't start up my disabled fork for 12.. right? but maybe i can simply hack your build scripts easily enough |
|
Heh Heh Heh. Is that The pgautoupgrade container should definitely support upgrading from PG 12. |
|
seems to be building just fine (will be half an hour because building AMD on my ARM mac) |
right but the makefile doesn't support building but changing up the invocation was trivial, so i think we are off to the races |
|
Ahhhh. You're meaning going to PG 12. Got it. Yeah, out by one version. You're right, you should be able to hack that into the Dockerfile and script without too much hassle. 😄 |
|
i think it made it past this and then it died after this |
|
Interesting. That k, plan b, lets add a version of that volume line back to your compose file, and give it an empty directory on your local system to mount: That should create an empty directory When the container starts, it should see that directory is empty and initialise it with a blank PostgreSQL database. If the rest of the container startup process errors out again, you can look at the There should be a |
|
|
i knew i was flying too close to the sun thinking i could use |
|
okay, in additinon to the above facepalm, i also realized i wasn't deploying my patched version of your code but was building main. now i'm back on track possibly getting better info. i got a build with all this glorious log output. then i put i more logging and for some reason the next run didn't have any output. still trying to figure out if my fault or something else, but want to safe this here for posterity. the interesting things are
|
|
In the light of morning I quickly created a minimal local reproduction! what did it: the version: '3'
services:
postgres:
image: 'pgautoupgrade/pgautoupgrade:15-alpine'
platform: 'linux/amd64'
environment:
POSTGRES_PASSWORD: 'password'
POSTGRES_DB: 'app'
PGDATA: '/var/lib/postgresql/data/pgdata'
volumes:
- 'postgres:/var/lib/postgresql/data'
ports:
- '5432'docker-compose up postgres |
|
okay, now realizing that isn't actually a standalone reproduction, requires the volume having something in it first. standby... |
|
With your PostgreSQL data directory, what version of PostgreSQL is it upgrading from? I should update the script in the main repository to support PG 12 as the target. That would probably help your situation out a lot. 😄 |
|
I've just added support for migrating to PG 12 (7ffe5e7), and pushed a new Docker image with the tag If you try that docker image out, it might just all work out automatically. 😄 |
I'm trying to upgrade from postgres 14 to 15 while retaining data.
When I use the image to
pgautoupgrade/pgautoupgrade:latestinstead ofpostgres:14in the compose file and restart, I get the following errors.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.
The text was updated successfully, but these errors were encountered: