Re: PostgreSQL upgrade.

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