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

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Max Pyziur

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
[hidden email]
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Late to the party: upgrading F23->F24, PostgreSQL 9.4.9->9.5.4, PostGIS 2.1.8->2.2.1

Robert Burgholzer-2
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 <[hidden email]> 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
[hidden email]
_______________________________________________
postgis-users mailing list
[hidden email]
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

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Late to the party: upgrading F23->F24, PostgreSQL 9.4.9->9.5.4, PostGIS 2.1.8->2.2.1

Devrim GÜNDÜZ-2
In reply to this post by Max Pyziur

Hi,

On Mon, 2016-10-31 at 19:06 -0400, Max Pyziur wrote:

> 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?
Community RPMs include the .so file of the previous version, along with the
current version I did not test, but upgrading should work :-)  Just install 9.4
and 9.5 in parallel, if you are installing RPMs from the community repository. 

Paul recently wrote a blog post about this, too:

http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html

Regards,
--
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

signature.asc (836 bytes) Download Attachment