Re: PostgreSQL upgrade 9.3.4 -> 9.3.10

Hello:

On Tue, Jan 12, 2016 at 10:28 AM, Dev Kumkar <devdas(dot)kumkar(at)gmail(dot)com> wrote:
> I want to upgrade my database from version 9.3.4 to 9.3.10.
>
> For this task, do I need to upgrade database using pg_upgrade utility?
> http://www.postgresql.org/docs/9.3/static/pgupgrade.html

No, that is a minor release. Minor version upgrades are normally just
install new vesion & restart. In fact we usually install the new
version over the old one and just restart the server ( but YMMV,
specially if you use one of those operating systems which do not let
you delete open files ).

Minor (9.3.4->9.3.10) version normally preserve on disk formats, so
nothing needs to be done.

Mayor ( 9.3.4 -> 9.4.x ) do not, and they normally need either a dump
& restore or a pg_upgrade ( which works like an optimized dump &
restore ).

In any case, read the release notes ( 9.3 docs, at
end,http://www.postgresql.org/docs/9.3/static/release.html ) for
versions 9.3.5-10, specially the headers, any special instructions
should be contained here, when you read 'A dump/restore is not
required for those running 9.3.X.' it meand you can just install and
restart.

> From the details it looks like for minor version upgrade pg_upgrade utility
> is not required.
> "pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL
> data files to be upgraded to a later PostgreSQL major version without the
> data dump/reload typically required for major version upgrades, e.g. from
> 8.4.7 to the current major release of PostgreSQL. It is not required for
> minor version upgrades, e.g. from 9.0.1 to 9.0.4."
>
> Can someone please provide more details here and also what steps needs to be
> done to upgrade to 9.3.10 level?

When postgres changes on-disk formats it bumps the major version.
Normally these used to mean you needed to dump & restore the database,
( Using the NEWER dump/restore utilities, as 9.4 dump/restore know how
to deal with a 9.3 DB, but 9.3 D/R do not even know wether a 9.4
version exists )

pg_upgrade was developed because the on-disk format changes are
tipically minor, and a special program could be made to transform the
data from a version to a later one faster than dumping & restoring,
but is more or less equivalent to doing that. As minor version
upgrades do not need dump/restore, they do not need pg_upgrade either.

Francisco Olarte.