Postgres Pro
Downloads
Home   >   mailing lists

Thread: Recommendation for upgrading from PostgreSQL 9.3

Recommendation for upgrading from PostgreSQL 9.3

From
Karl Martin Skoldebrand
Date:

Are there any recommendations regarding upgrading from PG 9.3 to 9.6 or 10.x?

I found a few changes at https://severalnines.com/blog/upgrading-your-database-to-postgresql-version-10 but are there more things to keep in mind regarding changes in scripts/queries that might affect an already existing application? Should we stop at 9.6 or go 10.x considering that 10.x seems almost about a year old, and 11.x is out there.

I have limited experience with PG so far, but quite long experience with MySQL and OSS in general.

 

Best regards,

 

Martin Skjoldebrand

 

============================================================================================================================

Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.

============================================================================================================================

Re: Recommendation for upgrading from PostgreSQL 9.3

From
Pavel Stehule
Date:
Hi

po 12. 11. 2018 v 10:18 odesílatel Karl Martin Skoldebrand <KS0C77263@techmahindra.com> napsal:

Are there any recommendations regarding upgrading from PG 9.3 to 9.6 or 10.x?

I found a few changes at https://severalnines.com/blog/upgrading-your-database-to-postgresql-version-10 but are there more things to keep in mind regarding changes in scripts/queries that might affect an already existing application? Should we stop at 9.6 or go 10.x considering that 10.x seems almost about a year old, and 11.x is out there.

I have limited experience with PG so far, but quite long experience with MySQL and OSS in general.


There are not reasons, why don't use PostgreSQL 10.

Regards

Pavel
 

 

Best regards,

 

Martin Skjoldebrand

 

============================================================================================================================

Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.

============================================================================================================================

RE: Recommendation for upgrading from PostgreSQL 9.3

From
Karl Martin Skoldebrand
Date:

I found a note on logical replication in PostgreSQL 10.x with the caveat “There are also a number of caveats regarding what objects are actually replicated—for example, only tables are replicated, such objects as views and sequences are not.”

 

Does replication in 10.x now include views etc? We have quite a number of those that would it be if they are included.

 

/M

============================================================================================================================

Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.

============================================================================================================================

Re: Recommendation for upgrading from PostgreSQL 9.3

From
Pavel Stehule
Date:


po 12. 11. 2018 v 11:45 odesílatel Karl Martin Skoldebrand <KS0C77263@techmahindra.com> napsal:

I found a note on logical replication in PostgreSQL 10.x with the caveat “There are also a number of caveats regarding what objects are actually replicated—for example, only tables are replicated, such objects as views and sequences are not.”

 

Does replication in 10.x now include views etc? We have quite a number of those that would it be if they are included.


PostgreSQL doesn't add new features in minor versions - so all limits for 10 are valid for 10.x

The replication doesn't copy views - but the views (not materialized views) are stored queries. So if you replicate data, then views are refreshed automatically. The significant limit of logical replication of PostgreSQL 10, 11 is impossibility to replicated DDL commands - the change of schema should be distributed by different method.

Regards

Pavel

 

/M

============================================================================================================================

Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.

============================================================================================================================

RE: Recommendation for upgrading from PostgreSQL 9.3

From
Karl Martin Skoldebrand
Date:

Thanks for your input.

 

/M.

============================================================================================================================

Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.

============================================================================================================================

Re: Recommendation for upgrading from PostgreSQL 9.3

From
Adrian Klaver
Date:
On 11/12/18 1:17 AM, Karl Martin Skoldebrand wrote:
> Are there any recommendations regarding upgrading from PG 9.3 to 9.6 or 
> 10.x?
> 
> I found a few changes at 
> https://severalnines.com/blog/upgrading-your-database-to-postgresql-version-10 
> but are there more things to keep in mind regarding changes in 
> scripts/queries that might affect an already existing application? 
> Should we stop at 9.6 or go 10.x considering that 10.x seems almost 
> about a year old, and 11.x is out there.

Go here:

https://www.postgresql.org/docs/10/release.html

and click on the notes for the first release for each major release. 
Prior to 10 the numbering system was Major.major.minor, e.g. 9.3.x --> 
9.4.x was a major change. With 10+ the scheme is Major.minor so 10.x --> 
11.x. So in your case the first release you would visit is:

https://www.postgresql.org/docs/10/release-9-4.html

To be more certain I would set up a dev database with the new version 
and test against that.


> 
> I have limited experience with PG so far, but quite long experience with 
> MySQL and OSS in general.
> 
> Best regards,
> 
> Martin Skjoldebrand
> 
>
============================================================================================================================
> 
> Disclaimer:  This message and the information contained herein is 
> proprietary and confidential and subject to the Tech Mahindra policy 
> statement, you may review the policy at 
> http://www.techmahindra.com/Disclaimer.html externally 
> http://tim.techmahindra.com/tim/disclaimer.html internally within 
> TechMahindra.
> 
>
============================================================================================================================
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

RE: Recommendation for upgrading from PostgreSQL 9.3

From
Karl Martin Skoldebrand
Date:
Thanks,

>https://www.postgresql.org/docs/10/release.html

That's a massive list. But going for the first major release makes it manageable.

> ---

>To be more certain I would set up a dev database with the new version and test against >that.

Aye, we've got a production and a test server. We're going to do the test server first and run tests on it for a bit so
weedout any incompatibilities. 
Just by a quick check, I don't see any anything seriously strange (yet). But we'll see ...

/M

============================================================================================================================

Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the Tech
Mahindrapolicy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html
<http://www.techmahindra.com/Disclaimer.html>externally http://tim.techmahindra.com/tim/disclaimer.html
<http://tim.techmahindra.com/tim/disclaimer.html>internally within TechMahindra. 


============================================================================================================================

Re: Recommendation for upgrading from PostgreSQL 9.3

From
Adrian Klaver
Date:
On 11/12/18 6:45 AM, Karl Martin Skoldebrand wrote:
> Thanks,
> 
>> https://www.postgresql.org/docs/10/release.html
> 
> That's a massive list. But going for the first major release makes it manageable.

Project policy is to only make feature changes on major version 
releases, so hitting only these will pretty much cover it.

> 
>> ---
> 
>> To be more certain I would set up a dev database with the new version and test against >that.
> 
> Aye, we've got a production and a test server. We're going to do the test server first and run tests on it for a bit
soweed out any incompatibilities.
 
> Just by a quick check, I don't see any anything seriously strange (yet). But we'll see ...
> 
> /M
>
============================================================================================================================
> 
> Disclaimer:  This message and the information contained herein is proprietary and confidential and subject to the
TechMahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html
<http://www.techmahindra.com/Disclaimer.html>externally http://tim.techmahindra.com/tim/disclaimer.html
<http://tim.techmahindra.com/tim/disclaimer.html>internally within TechMahindra.
 
> 
>
============================================================================================================================
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com