Upgrading from PostgreSQL 9.3 to 11.6

On 12/20/19 5:58 AM, tony@exquisiteimages.com wrote:

I have a PostgreSQL 9.3 installation that I finally have time to migrate
to a currently supported version. The server that the installation is
installed on is also at its end of service.

Can anyone help me with completing this conversion as securely and
efficiently as possible with the least amount of down time? Also, once
it has been converted over, is there any best practice for ensuring that
there has been no corruption?

The choices off the top of my head:

1) pg_dump/pg_restore using the 11.6 binaries of both.

2) Using pg_upgrade(www.postgresql.org/docs/11/pgupgrade.html) from 11.6.

3) Setting up Slony(http://www.slony.info/).

Downtime is hard to predict, especially if you have not done a procedure
before. The simplest would be the dump/restore.

I would go through the Release Notes for the first release of each major
version 9.4, 9.5, 9.6 and 11 to check out any behavior changes that
might trip you up. Given the number of versions you are stepping over
setting up a test instance of 11.6 to do the upgrade against would be a
good thing to do.

The database is 600GB in size. We do not have any custom types being used.

Thanks for any direction.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2019-12-20 09:02, Justin wrote:

Hi Tony

Can you describe the database in greater detail? how many tables,
type of data, how big is the down time window?

So this will be on new server hardware, and new DB install, what OS?
and extensions being used, has there been any testing

with apps on latter versions of PostgreSQL???

On Fri, Dec 20, 2019 at 8:58 AM <tony@exquisiteimages.com> wrote:

I have a PostgreSQL 9.3 installation that I finally have time to
migrate
to a currently supported version. The server that the installation
is
installed on is also at its end of service.

Can anyone help me with completing this conversion as securely and
efficiently as possible with the least amount of down time? Also,
once
it has been converted over, is there any best practice for ensuring
that
there has been no corruption?

The database is 600GB in size. We do not have any custom types being

used.

Thanks for any direction.

Thanks for the reply!

New Server OS: Ubuntu Server 18.04.3 LTS

Schemas: 9,000

Total Tables: 900,000

Data is mostly transactional detail (90%), some summary tables (8%), and
basic client demographic data (2%)

Everyone wants the down time to be zero, but I don't think that is
really possible. I am thinking about 4 hours on a weekend night is going
to be what I can get approved.

My plan was to go through the conversion process once to see if
everything goes well. I would then use the new server as a test server
and run our full battery of applications against it to make sure that we
obtain the same results as from a back copy of the live database.