On 15/04/19 2:26 PM, Mark Kirkwood wrote:
>
> On 10/04/19 8:20 PM, Andreas Kretschmer wrote:
>>
>>
>> Am 10.04.19 um 07:40 schrieb Daulat Ram:
>>> We have two node postgresql database version 9.6 with streaming
>>> replication which is running on docker environment, os Linux
>>> (Ubuntu) and we have to migrate on PostgresQL11. I need your
>>> suggestions & steps to compete the upgrade process successfully.
>>
>> there are exists several ways to do that. You can take a normal dump
>> and replay it in the new version, you can use pg_upgrade, and you can
>> use a logical replication (using slony, londiste or pg_logical from
>> 2ndQuadrant). There is no 'standard way' to do that, all depends on
>> your requirements and knowledge how to work with that tools.
>>
>>
>>
>
> The docker environment makes using pg_upgrade more difficult, as you
> need to modify (or build a new) container with the old and new
> Postgres versions installed. I'm interested in seeing how hard that
> would be (will update this thread if I find anything useful).
>
>
>
It transpires that it is not too tricky to build a 'migration' container:
- get relevant Postgres Dockerfile from https://hub.docker.com/_/postgres
- Amend it to install 2 versions of Postgres
- Change ENTRYPOINT to run something non Postgres related (I used 'top')
- Build it
To use pg_upgrade the process is:
- stop your original Postgres container
- run the migration one, attaching volume from the Postgres container +
a new one
- enter the migration container and initialize the new version's datadir
- run pg_upgrade from old to new version
- tidy up config and pg_hba for the upgraded datadir
- exit and stop the migration container
(see attached for notes and Dockerfile diff)
You can then run a new Postgres container (of the new version) using the
new volume.
While the process is a bit fiddly, it is probably still way faster than
a dump and restore.
regards
Mark
