search

dokku / dokku-postgres

a postgres plugin for dokku
MIT License
484 stars 97 forks source link

Run migrating script during upgrade command #178

Closed tribela closed 3 years ago

tribela commented 5 years ago

dokku postgres:upgrade <service> command just changing docker image tag. And it causes error like this:

The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.2 (Debian 11.2-1.pgdg90+1).

upgrade subcommand should run migration script like this before starting new container

josegonzalez commented 5 years ago

Unfortunately, I am super unlikely to implement a fix here due to lack of time.

If you'd like to contribute the change, that would be excellent. Please note that this sort of thing is kinda weird because you need to update the data for every major/minor version upgrade. We might actually need to do a dump/restore to make it work...

tribela commented 5 years ago

I agree that dump/restore method is more graceful(But has more downtime). I'll look for that

josegonzalez commented 5 years ago

I think even the method you posted has extended downtime, so not sure we can do this easily :(

k3it commented 5 years ago

what is the best way to run pg_upgrade manually after the migration? the dokku created container seems restarting in a loop after the upgrade

coisnepe commented 4 years ago

@Kjwon15 How did you end up solving your issue? I'm stuck with a seemingly inaccessible container now and cannot access the data…

coisnepe commented 4 years ago

So here's what I ended up doing:

From now on I'll make sure to dokku postgres:export db_name > db_name.sql before doing anything potentially destructive!

EmilStenstrom commented 3 years ago

Update: Use the method described here instead: https://github.com/dokku/dokku-postgres/commit/5855d1bdd58d30102da9d744e0da85c2964c6fd2

I made the same mistake, and rendered by database unusable because of the The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.6 (Debian 11.6-1.pgdg90+1). error.

I tried to use the steps outlined above to get things rolled back again, but had to add few things. Here's the full list if anyone else encounters this:

  1. Copy data: cp -r /var/lib/dokku/services/postgres/db_name/data .
  2. Unlink the container: dokku postgres:unlink db_name app_name
  3. Destroy the container: dokku postgres:destroy db_name
  4. Create a new container by specifying the version: dokku postgres:create db_name -I 10.0
  5. Stop the created container: dokku postgres:stop db_name
  6. Delete the data directory: rm -r /var/lib/dokku/services/postgres/db_name/data
  7. Copy over the previous directory: cp -r data /var/lib/dokku/services/postgres/db_name
  8. Start: dokku postgres:start db_name

Now, when you copied over the data directory, the old password for the database user was copied with it. But the postgers:create step created a new user and password. So now the password set in DATABASE_URL does not match the one in the database. Let's change that.

  1. Enter the database container: dokku postgres:enter app_name
  2. Start a postgres prompt: psql postgres://postgres:<new-password>@localhost:5432/db_name
  3. Change the password to the new one: ALTER USER postgres WITH PASSWORD <new-password>;

NOW you're ready to link the app again:

  1. Link the container: dokku postgres:link db_name app_name
odscjames commented 3 years ago

I'm also having a problem here. I have a request: whilst this is outstanding; can the upgrade section of the readme be updated to make people aware of this issue?

odscjames commented 3 years ago

Here's what I just got working for a 11->12 upgrade:

dokku ps:stop live
dokku postgres:export live-database > /tmp/live-export
dokku postgres:unlink live-database live
dokku postgres:destroy live-database
dokku postgres:create live-database --image-version 12.5
dokku postgres:import live-database < /tmp/live-export
dokku postgres:link live-database live
dokku ps:start live

But I'm not sure: will this work on all version upgrades? I think sometimes there is export files version incompatibility?

EmilStenstrom commented 3 years ago

@odscjames In you instructions: Maybe keep the old database until you verified that the import works?

odscjames commented 3 years ago

@EmilStenstrom Sure - I was just pointing out a general route.

My main request is still that this is put in the readme, as it's a signifiant "gotcha".

f3l1x commented 3 years ago

Thank you @odscjames. It works 11 => 13.2

josegonzalez commented 3 years ago

There is a pull request open for doing the tianon-based upgrade, and the upgrade process is documented as of https://github.com/dokku/dokku-postgres/commit/5855d1bdd58d30102da9d744e0da85c2964c6fd2. Going to close this for now, but feel free to comment in the PR (or create an alternative to keep it moving).