PostgreSQL upgrade.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL upgrade.

Daulat Ram-2

Hello team.

 

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.

 

Regards,

Daulat

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade.

Andreas Kretschmer-3


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.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade.

Mark Kirkwood-2

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).

regards

Mark



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL upgrade.

Mark Kirkwood-2
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



DOCKER-UPGRADE-POSTGRES (1K) Download Attachment
Dockerfile.diff (3K) Download Attachment