Re: 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(dot)klaver(at)aklaver(dot)com