Re: [ADMIN] how to upgrade PG

On Mon, Apr 18, 2016 at 10:19 PM, Huang, Suya <Suya(dot)Huang(at)experian(dot)com>
wrote:

> Hello,
>
>
>
> Our PG runs on Ubuntu boxes and it’s 9.3.4, thinking of upgrading to
> 9.3.12 or 9.5.2.
>
>
>
> Is the normal approach of upgrade something like below, no matter if it’s
> major version or minor version upgrade? My focus is on pg_dump at the
> moment.
>
> #1 pg_dump
>
> Dump DB
>
> Apt-get update
>
> Apt-get install new version
>
> Createdb
>
> Import DB
>
>
>
> #2 pg_upgrade (a little complicated, worth trying on test environment)
>
>
>
> #3 using trigger based replication tool (not interested at this moment)
>
>
>

​How you upgrade largely depends on how you installed in the first place.

Minor version upgrades, i.e., where only the last digit changes, does not
require any kind of data transfer - only the binaries are changed and they
can be replaced with minimal downtime.

Major version upgrades can be done via pg_dump/pg_restore or the utility
pg_upgrade. These perform the data transfers - it is up to you to actually
install the newer version and setup the system properly before using those
commands. The documentation for both is excellent and should you have
specific questions, or just observations, this list is an excellent
resource.

In most cases, as you are showing apt-get commands, I am reasonably certain
that apt-get update/apt-get upgrade on the PostgreSQL package will
automatically stop and restart the postgresql service are the appropriate
time so as to minimize downtime during the minor version upgrade.

Please note that "pg_dump" does not export globals - you also need to use
the pg_dumpall utility to obtain those. Again, the documentation - and the
Internet - have copious information on the topic; it is a very well
traveled path.
​

Typically on the major version upgrade you would perform the apt-get
install on the new version, put it through its paces, then perform the
upgrade commands. After you are done you may chose to remove the old
version or just leave it around - the choice is yours.

David J.