Run migrating script during upgrade command

[tribela]

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]

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]

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

[josegonzalez]

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

[k3it]

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]

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

[coisnepe]

So here's what I ended up doing:

• Copy the 'corrupted' data out of the container. It should be located here : cp /var/lib/dokku/services/postgres/db_name/data .
• Destroy the container: dokku postgres:destroy db_name
• Create a new container by specifying the version: dokku postgres:create db_name -I 10.0
• Stop the created container: dokku postgres:stop db_name
• Delete the data directory: rm -r /var/lib/dokku/services/postgres/db_name/data
• Copy over the previous directory: cp -r data /var/lib/dokku/services/postgres/db_name
• Start: dokku postgres:start db_name
  Theoretically, db_name should start without errors.

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

[EmilStenstrom]

Update: Use the method described here instead: 5855d1b

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.

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

NOW you're ready to link the app again:

12. Link the container: dokku postgres:link db_name app_name

[ghost]

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?

[ghost]

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]

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

[ghost]

@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]

Thank you @odscjames. It works 11 => 13.2

[josegonzalez]

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