Thread: How to migrate from PGSQL 9.3 to 9.6

How to migrate from PGSQL 9.3 to 9.6

From

"Sinclair, Ian D (Ian)"

Date:

30 November 2016, 01:20:21

I’ve never worked with Postgres, but I’ve been tasked with upgrading our product from using 9.3 to 9.6. Please feel free to direct me to appropriate newbie docs.

I’ve found the RPMS I need, but I’ve come across a setup file that I think I need to get a correct version of. I found references to postgresql-setup, but I have a feeling that I may need a version specific copy of that script. If I get the latest version for 9.6 will it recognize my 9.3 database and upgrade it correctly? Or do I need to edit the content to indicate that the old version is 9.3. The PGPREVMAJORVERSION reference below is what makes me wonder.

In my current db code I found a script called db_service.sh that includes these lines:

# Version 9.3.2 Devrim Gunduz <devrim@gunduz.org>

# Add process name to the status() call. Patch from Darrin Smart

# PGVERSION is the full package version, e.g., 9.3.0

# Note: the specfile inserts the correct value during package build

PGVERSION=9.3.5

# PGMAJORVERSION is major version, e.g., 9.3 (this should match PG_VERSION)

PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^$[0-9]*\.[0-9]*$.*$/\1/'`

PGPREVMAJORVERSION=9.2

# Source function library.

INITD=/etc/rc.d/init.d

. $INITD/functions

I’m not sure that this is a standard PG script or a conglomeration of other things. Is this based on the setup script or do I need new versions of both?

The current database is running on RHEL 6 and the new version will be running on RHEL 7.2. Does that affect what I need? I found an exchange in the archives about someone upgrading 9.4 to 9.5 and having problems getting the correct upgrade syntax. If you can provide that it would be greatly appreciated.

I believe I also need to upgrade our JDBC version. What is the minimum version that is compatible with PGSQL 9.6?

If these are too dumb for the general list feel free to redirect me to the novices list.

Thanks,

Ian

Re: How to migrate from PGSQL 9.3 to 9.6

From

John R Pierce

Date:

30 November 2016, 01:47:21

On 11/29/2016 2:20 PM, Sinclair, Ian D (Ian) wrote:

I’ve found the RPMS I need, but I’ve come across a setup file that I think I need to get a correct version of. I found references to postgresql-setup, but I have a feeling that I may need a version specific copy of that script. If I get the latest version for 9.6 will it recognize my 9.3 database and upgrade it correctly? Or do I need to edit the content to indicate that the old version is 9.3. The PGPREVMAJORVERSION reference below is what makes me wonder.
....

The current database is running on RHEL 6 and the new version will be running on RHEL 7.2. Does that affect what I need? I found an exchange in the archives about someone upgrading 9.4 to 9.5 and having problems getting the correct upgrade syntax. If you can provide that it would be greatly appreciated.

presumably these are two separate machines?    depending on how large your databases are, it might just be easiest to do this...

on the old system: configure the existing postgres to allow network connections from the new system.

on the new system:
    (install postgres...   )
    (initialize postgres database engine...)
    (configure postgresql.conf parameters for suitable performance (in particular, ensure maintenance_work_mem is 1GB or so assuming you have lots of ram, this will *hugely* speed up index creation)
    # su - postgres
    (create $HOME/.pgpass with the password to the old system's postgres user)
    $ pg_dumpall -h oldsystem | psql

however, if the databases are rather large, this could take many hours or even days.

I believe I also need to upgrade our JDBC version. What is the minimum version that is compatible with PGSQL 9.6?

I would update to the latest version of the pg jdbc driver, not the 'minimum' version. why mess with bugs that have been fixed ? choose the proper jdbc driver based on your Java JRE version (eg, jdbc42 for Java 8)

-- 
john r pierce, recycling bits in santa cruz

Re: How to migrate from PGSQL 9.3 to 9.6

From

"Sinclair, Ian D (Ian)"

Date:

30 November 2016, 21:27:51

The actual upgrade will be that I have an existing server running 9.3 on RHEL 6.2. We’ll have to do a backup of the database, then deploy a new OVA to get to RHEL 7 with PG 9.6, then restore the database. Are there any specific steps that I’ll have to execute in the scripts that will restore the database to get the 9.3 data to work with 9.6?

Since all my users will be getting to 9.6 from a new system deployment, not by any sort of partial upgrade, do I need to worry about trying to get a correct version of the setup script with upgrade handling? (Other than whatever I need for the answer above.)

Thanks

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: November 29, 2016 5:47 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to migrate from PGSQL 9.3 to 9.6

On 11/29/2016 2:20 PM, Sinclair, Ian D (Ian) wrote:

I’ve found the RPMS I need, but I’ve come across a setup file that I think I need to get a correct version of. I found references to postgresql-setup, but I have a feeling that I may need a version specific copy of that script. If I get the latest version for 9.6 will it recognize my 9.3 database and upgrade it correctly? Or do I need to edit the content to indicate that the old version is 9.3. The PGPREVMAJORVERSION reference below is what makes me wonder.
....

The current database is running on RHEL 6 and the new version will be running on RHEL 7.2. Does that affect what I need? I found an exchange in the archives about someone upgrading 9.4 to 9.5 and having problems getting the correct upgrade syntax. If you can provide that it would be greatly appreciated.

I believe I also need to upgrade our JDBC version. What is the minimum version that is compatible with PGSQL 9.6?

--

john r pierce, recycling bits in santa cruz

Re: How to migrate from PGSQL 9.3 to 9.6

From

Adrian Klaver

Date:

30 November 2016, 21:52:21

On 11/30/2016 10:27 AM, Sinclair, Ian D (Ian) wrote:
> The actual upgrade will be that I have an existing server running 9.3 on
> RHEL 6.2. We’ll have to do a backup of the database, then deploy a new
> OVA to get to RHEL 7 with PG 9.6, then restore the database. Are there
> any specific steps that I’ll have to execute in the scripts that will
> restore the database to get the 9.3 data to work with 9.6?

Dump the 9.3 database using the pg_dump(all) from 9.6.

Go here:

https://www.postgresql.org/docs/9.6/static/release.html

and read the notes for 9.4, 9.5, 9.6:

https://www.postgresql.org/docs/9.6/static/release-9-4.html
https://www.postgresql.org/docs/9.6/static/release-9-5.html
https://www.postgresql.org/docs/9.6/static/release-9-6.html

In particular the section:

E.8.2. Migration to Version X.X

>
>
>
> Since all my users will be getting to 9.6 from a new system deployment,
> not by any sort of partial upgrade, do I need to worry about trying to
> get a correct version of the setup script with upgrade handling? (Other
> than whatever I need for the answer above.)
>
>
>
> Thanks
>
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to migrate from PGSQL 9.3 to 9.6

From

John R Pierce

Date:

30 November 2016, 23:12:00

On 11/30/2016 10:27 AM, Sinclair, Ian D (Ian) wrote:

The actual upgrade will be that I have an existing server running 9.3 on RHEL 6.2. We’ll have to do a backup of the database, then deploy a new OVA to get to RHEL 7 with PG 9.6, then restore the database. Are there any specific steps that I’ll have to execute in the scripts that will restore the database to get the 9.3 data to work with 9.6?

Since all my users will be getting to 9.6 from a new system deployment, not by any sort of partial upgrade, do I need to worry about trying to get a correct version of the setup script with upgrade handling? (Other than whatever I need for the answer above.)

the upgrade scripts, like pg_upgrade, are for in place upgrades (same server, postgres X.Y to X.newer).

if the old server will still be running when you have the new one ready, what I already said, configure the old server to allow the postgres user on the new server to access it over the network, then, from the postgres user on the new server, do this..

pg_dumpall -h oldserver | psql

this assumes the new version of postgres is installed, initialized, and running, but otherwise untouched

My personal preference for a full backup dump-n-restore goes something like this...

dump script, run as the postgres system user...

   DD=$(date +%F)
   PP=/path/of/where/to/put/dumps
   pg_dumpall -g -f $PP/pg_dumpall.globals-$DD.sql
   for db in $(psql -tc "select datname from pg_database where not datistemplate"); do
       pg_dump -Fc --schema-only -f $PP/pgdump.$db.$DD.dump -d $db
   done

this creates a globals dump in plain sql, then a compressed dump of each non-system database.

to restore these on a new cluster...

    psql -f $PP/pg_dumpall.globals-2016-11-15.sql
    for db in $PP/*.dump; do
        pg_restore -d postgres --clean --create $db
    done

but this is more for regular backups, the advantage of the compressed format is pg_restore has a lot more options during database restore.

-- 
john r pierce, recycling bits in santa cruz