Thread: Application Dependency/Limitations of Postgres Version Upgrade

On 03/09/2018 10:49 PM, amitabh kumar wrote:
> Hi,
> 
> I would like to know about application dependency of PostgreSQL version 
> upgrade.
> 
> We have multiple servers with PG versions in 8.4, 9.2, 9.3, 9.4 and 9.5, 
> that we want to upgrade to 9.6. We want to be sure that all applications 
> will run smoothly after upgrade.

The only way I know to be sure is to is to set up a test instance of 9.6 
and test your applications. As an example of the things that could trip 
you up:

Your 8.4 --> 9.x
https://www.postgresql.org/docs/10/static/release-9-1.html#id-1.11.6.121.4

"

     Change the default value of standard_conforming_strings to on 
(Robert Haas)

     By default, backslashes are now ordinary characters in string 
literals, not escape characters. This change removes a long-standing 
incompatibility with the SQL standard. escape_string_warning has 
produced warnings about this usage for years. E'' strings are the proper 
way to embed backslash escapes in strings and are unaffected by this change.
     Warning

     This change can break applications that are not expecting it and do 
their own string escaping according to the old rules. The consequences 
could be as severe as introducing SQL-injection security holes. Be sure 
to test applications that are exposed to untrusted input, to ensure that 
they correctly handle single quotes and backslashes in text strings.

"

I would as matter of course also review the release notes for each major 
release since 8.4 to look for further gotchas.


> 
> Is there any dependency or limitation of applications in 9.6 after 
> upgrading from these versions ? OS platforms we are using are in Linux 
> 5, 6.7. 6.8 and Windows 10, 12.
> 
> I am happy to provide more information if need.
> 
> Regards,
> 
> Amitabh
> PostgreSQL DBA
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com