Обсуждение: [GENERAL] How to upgrade PostgreSQL minor version?

On 04/26/2017 03:36 AM, Ron Ben wrote:
> Hi :)
>
> I do have a test enviroment and I will test eveything there first but I
> had cases where eveything worked on test but on production I had
> difficultes. It's rare but can happen.
> Problems with upgrading PostgreSQL isn't like upgrading a package which
> effects few functions.
> Since the documntation says just replace the executables I'm lost...
> I notced that 9.3.5 and 9.3.9 requires sepcific test. Further more it
> says secificly not to perform the tests for 9.3.9 with versions piror to
> 9.3.5 as it might not work : "The lingering effects of a
> previously-fixed bug in pg_upgrade could also cause such a failure, in
> installations that had used pg_upgrade versions between 9.3.0 and
> 9.3.4."  So this means I have to upgrade to 9.3.5 and only then to 9.3.16

This only applies if you used the pg_upgrade program to upgrade to a 9.3
cluster. If you used dump/restore it does not apply:

https://www.postgresql.org/docs/9.3/static/release-9-3-5.html
"
In pg_upgrade, remove pg_multixact files left behind by initdb (Bruce
Momjian)

If you used a pre-9.3.5 version of pg_upgrade to upgrade a database
cluster to 9.3, it might have left behind a file
$PGDATA/pg_multixact/offsets/0000 that should not be there and will
eventually cause problems in VACUUM. However, in common cases this file
is actually valid and must not be removed. To determine whether your
installation has this problem, run this query as superuser, in any
database of the cluster:

WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
        NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
        NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
        EXISTS (SELECT * FROM list WHERE file != '0000')
        AS file_0000_removal_required;

If this query returns t, manually remove the file
$PGDATA/pg_multixact/offsets/0000. Do nothing if the query returns f.
"

> How do I perform upgrade in steps to specific versions?

9.3.3 -- > 9.3.4

https://www.postgresql.org/docs/9.3/static/release-9-3-4.html

"However, the error fixed in the first changelog entry below could have
resulted in corrupt data on standby servers. It may be prudent to
reinitialize standby servers from fresh base backups after installing
this update."

So if you are not running any standbys a no-op.

9.3.4 --> 9.3.5

The above mentioned pg_upgrade bug, so if you did not use pg_upgrade
another no-op.

9.3.5 -- > 9.3.6

https://www.postgresql.org/docs/9.3/static/release-9-3-6.html

"However, if you are a Windows user and are using the "Norwegian
(Bokmål)" locale, manual action is needed after the upgrade to replace
any "Norwegian (Bokmål)_Norway" locale names stored in PostgreSQL system
catalogs with the plain-ASCII alias "Norwegian_Norway". For details see
http://wiki.postgresql.org/wiki/Changes_To_Norwegian_Locale"

So if you are not using the Norwegian local on Windows another no-op.

9.3.6 -- > 9.3.7

https://www.postgresql.org/docs/9.3/static/release-9-3-7.html

"However, if you use contrib/citext's regexp_matches() functions, see
the changelog entry below about that."

9.3.7 -- > 9.3.8

https://www.postgresql.org/docs/9.3/static/release-9-3-8.html

If you have covered the above nothing to do.

9.3.8 -- > 9.3.9

https://www.postgresql.org/docs/9.3/static/release-9-3-9.html

"However, if you are upgrading an installation that was previously
upgraded using a pg_upgrade version between 9.3.0 and 9.3.4 inclusive,
see the first changelog entry below."

If you did not use pg_upgrade a no-op.

9.3.9 --> 9.3.14

All refer you back to the above entry for 9.3.9

9.3.14 --> 9.3.15

https://www.postgresql.org/docs/9.3/static/release-9-3-15.html

"However, if your installation has been affected by the bug described in
the first changelog entry below, then after updating you may need to
take action to repair corrupted free space maps.



Fix WAL-logging of truncation of relation free space maps and visibility
maps (Pavan Deolasee, Heikki Linnakangas)

It was possible for these files to not be correctly restored during
crash recovery, or to be written incorrectly on a standby server. Bogus
entries in a free space map could lead to attempts to access pages that
have been truncated away from the relation itself, typically producing
errors like "could not read block XXX: read only 0 of 8192 bytes".
Checksum failures in the visibility map are also possible, if
checksumming is enabled.

Procedures for determining whether there is a problem and repairing it
if so are discussed at
https://wiki.postgresql.org/wiki/Free_Space_Map_Problems.
"

9.3.15 --> 9.3.16

https://www.postgresql.org/docs/9.3/static/release-9-3-16.html

"However, if your installation has been affected by the bug described in
the first changelog entry below, then after updating you may need to
take action to repair corrupted indexes.

...

Fix a race condition that could cause indexes built with CREATE INDEX
CONCURRENTLY to be corrupt (Pavan Deolasee, Tom Lane)

If CREATE INDEX CONCURRENTLY was used to build an index that depends on
a column not previously indexed, then rows updated by transactions that
ran concurrently with the CREATE INDEX command could have received
incorrect index entries. If you suspect this may have happened, the most
reliable solution is to rebuild affected indexes after installing this
update.
"


>
> I'm runing 9.3.3 in production server and in test server.
> Operating system is Ubuntu server 12
>
> I'm still not sure that I can upgrade directly from 9.3.3 to 9.3.16 -
> The documntation always tells to "if you are upgrading from version
> earlier than 9.3.X check 9.3.X-1". It assums that everyone upgrading
> once the version was released which is not always the case. And as

No it does not, that is why the 'if upgrading form version earlier then
version X.X.x' instructions.

You can upgrade from 9.3.3 --> 9.3.16. If you did not use pg_upgrade and
are not using standbys you will not be affected by the early bugs. To
verify you run the query in the 9.3.5 release notes. The bugs in the
last two entries are going exist in any version before them so you might
as well skip over those versions anyway and then check using the
instructions in the release notes.


> mentioned earlier 9.3.5 and 9.3.9 can create problems as they require
> specific checks.
>
>
> I'd appriciate any insight.
>
>
>
>
>
>
>     ב �פר׳ 26, 2017 13:26, Sameer Kumar כתב:
>
>
>
>         On Wed, Apr 26, 2017 at 4:41 PM Ron Ben <ronb910@walla.co.il
>         <mailto:ronb910@walla.co.il>> wrote:
>
>             I'm runing PostgreSQL 9.3.3 and I want to upgrade it to the
>             latest 9.3 version
>             The documontation does not specify what needs to be done
>             other than "just install the executables".
>             This is wierd as for example 9.3.5 release notes request to
>             run a specifc query to  check for pg_multixact files left.
>
>             I saw this question:
>             https://serverfault.com/questions/563667/update-9-3-x-minor-verison-of-postgres-on-ubuntu-12-04
>             I'm not sure how this will handle the 9.3.5 check issue.
>
>
>
>
>         What is the version of PostgreSQL that you are running?
>
>         Though there are some general guidelines/procedure (which is as
>         simple as stop the server, upgrade binaries and start, there
>         will also be some peculiar releases which has a bug-fix. While
>         the bug-fix covers you in future, there might have been
>         incidents where you are already bitten by bug being fixed (worse
>         if you have not yet identified it). To cover such cases, you
>         would need to follow some custom procedure. Again, mostly the
>         custom steps are not for patch application but more of a
>         mitigation against risks of bug or in some cases bug-fix.
>
>         Are you running with a standby or just stand-alone setup?
>
>
>
>             Is it really just runing one command?
>             What if there are errors and problems? how do i revert back?
>             Sadly there is not enough information regarding minor verion
>             updates...
>
>             I'm also runing ubuntu server 12 which will be updated as
>             well later this quarter.
>
>
>         Do you have a test environment? If I were you I would have a
>         non-prod that mimics my production setup exactly and I would
>         apply the patch there first.
>
>         --
>
>         --
>
>         Best Regards,
>
>         *Sameer Kumar | Senior Solution Architect*
>
>         *ASHNIK PTE. LTD.*
>
>         101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
>
>         T: +65 6438 3504 | www.ashnik.com <http://www.ashnik.com/>
>
>         Skype: sameer.ashnik |   M: +65 8110 0350
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/26/2017 06:56 AM, Ron Ben wrote:
> Hi,
>
> What are standbys?

Standbys are the standby servers when you are using replication:

https://www.postgresql.org/docs/9.3/static/high-availability.html

I am guessing you are not using replication.

> My question was more of how to actualy do the update to a specific versions.

That depends on how you installed Postgres. If you used packages then
you can use the package manager. If you built from source, then you need
to download the latest and build it but not install it yet. Shutdown the
existing running instance of Postgres and then install the latest
version. In the current Postgres version numbering system X.X.x the X
refers to major versions the x to a minor version. As a rule minor
version to minor version upgrades do not require a dump/restore cycle or
use of pg_upgrade to deal with the date. You are just upgrading the
binaries. Since it is a rule there have been exceptions so is important
to read the release notes. FYI, starting with Postgres 10 the versioning
will change to X.x. Since this is your first time going through the
process I would dumpall the Postgres cluster first, before you do the
upgrade, just to be on the safe side:

https://www.postgresql.org/docs/9.3/static/app-pg-dumpall.html



> What are the commands to do it? normaly the default upgrade is for the
> latest version.

See above.

> I'm not sure what is the recommndation. Should I just update directly to
> 9.3.16? It's very confusing how to do it somoothly.

The changes in the various versions from 9.3.4 to 9.3.16 are cumulative
so by going directly to 9.3.16 you get all of them. You just need to go
through the list I provided to see if any of them actually apply to you
and deal with them in the 9.3.16 instance you start up.

>


--
Adrian Klaver
adrian.klaver@aklaver.com