Re: 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.
>

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