[postgis-users] Late to the party: upgrading F23->F24, PostgreSQL 9.4.9->9.5.4, PostGIS 2.1.8->2.2.1

[Robert Burgholzer]

I am in the midst of a similar migration right now, though I am going from
9.3 to 9.5, and my OS is CentOS 6.  I too have had some troubles, and could
not find a reference that actually worked for me on CentOS, although I had
succeeded with a nearly identical migration on Ubuntu!  In the end, the
stumbling block was a change in characer encoding from 9.3 to 9.5.  Once, I
realized how to manage that,  I used "pg_dump" and "postgis_restore.pl" and
it seemed to work fine (I am, as I said, still doing it and testing it).
For me, the steps were as follows:

1. Install 9.5 along side 9.3, setting 9.5 to listen to port 5434.

# Postgres repo needs to be updated
sudo yum localinstall
https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-3.noarch.rpm
# The old repo was:
http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-2.noarch.rpm
sudo yum install postgresql95-server
sudo yum install postgresql95-devel
sudo yum install postgresql95-contrib
# install postgis
sudo yum install postgresql95-devel
sudo yum install postgresql95-contrib

# shut it off so if we get interupted it doesn't over-run the 9.3 install
sudo chkconfig postgresql-9.5 off
sudo mkdir /data/postgres/9.5
sudo chown postgres /data/postgres/9.5
sudo su - postgres
# initialize the cluster
/usr/pgsql-9.5/bin/initdb /data/postgres/9.5/

# create users drupal on new clusters
createuser -p 5434 normaluser
createuser -s -p 5434 superuser

# turn it on if needed
/usr/pgsql-9.5/bin/pg_ctl -D /data/postgres/9.5/ start -l logfile.9.5

2. Wrote a script called "migrate9.3to9.5.sh" to loop through my databases
one by one.

#!/bin/sh
# migrate9.3to9.5.sh
dbadmin="superuser"
dbuser="normaluser"
dbs="gistest"
host1="localhost"
host2="localhost"
port1=5432
port2=5434
postgis_restore2="/usr/pgsql-9.5/share/contrib/postgis-2.2/postgis_restore.pl"


for db in $dbs; do
  # use -E because default postgres encoding changes somewhere from
9.3 to 9.5 (in centos anyhow)
  pg_dump -h localhost -p 5432 -U $dbadmin -E SQL_ASCII -Fc -b -v -f
"/tmp/$db.backup" $db
  createdb -h $host2 -p $port2 $db
  echo "create extension postgis;" | psql -U $dbadmin -h $host2 -p $port2 $db
  perl $postgis_restore2 "/tmp/$db.backup" | psql -h $host2 -p $port2
$db 2> errors.txt
  echo "grant all on database \"$db\"to $dbuser" | psql $dbname -U
$dbadmin -p $port2 -h $host2
  echo "grant all on all tables in schema public to $dbuser" | psql
$dbname -U $dbadmin -p $port2 -h $host2
done

3. Change the 9.5 port back to 5432 after I am satisfied that it worked
well.

On Mon, Oct 31, 2016 at 7:06 PM, Max Pyziur <pyz at brama.com> wrote:

>
> Greetings,
>
> I have a series of machines on which I run Fedora along with PostgreSQL &
> PostGIS (two laptops & a desktop). I've begun upgrading these machines
> starting with the least used/critical one.
>
> I've run into what seems to be typical w/in the PostgreSQL/PostGIS
> community as expressed in the subject line: there are several challenges in
> upgrading to get to PSQL 9.5.4/PostGIS 2.2.1.
>
> Google returns a few pages where there are explanations on how to upgrade,
> along with a Bugzilla page.
>
> If possible, could someone please recommend the authoritative one of these
> or offer general guidance?
>
> Much thanks.
>
> Max Pyziur
> pyz at brama.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users




-- 
--
Robert W. Burgholzer
 'Making the simple complicated is commonplace; making the complicated
simple, awesomely simple, that's creativity.'  - Charles Mingus
Athletics: http://athleticalgorithm.wordpress.com/
Science: http://robertwb.wordpress.com/
Wine: http://reesvineyard.wordpress.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161101/5495ed1f/attachment.html>