Re: PostgreSQL broken by upgrade to F24

[Max Pyziur <pyz@xxxxxxxxx>]

On Mon, 28 Nov 2016, Raman Gupta wrote:

> > On 11/27/2016 10:27 PM, Stephen Davies wrote:
> > Install postgresql-upgrade.
>
> If you have any databases that use postgis, postgresql-upgrade fails spectacularly. You do have to take some manual steps to get the binaries for the older version, so that the upgrade completes. This blog post was helpful to me:
>
> https://juergritter.wordpress.com/2016/08/06/upgrading-postgis-after-migration-from-fedora-23-to-fedora-24/
>
> Regards,
> Raman

If you're only using one machine for Fedora and have already upgraded, 
then you'll some other source to get you through this. In my case, I have 
three, and generally use the least used one to do the first upgrade.

I have some notes and scripts ( my own hacks); perhaps they can be useful 
for you. I'm appending them below.

fyi,

Max Pyziur
pyz@xxxxxxxxx


General outline of steps:
############
I. Pre 23->24 upgrade
  Backup databases in custom format (see script below

  Make note of all users ( \du in monitor ) or use SQL script below



II. Post 23->24 upgrade
  Initialize postgresql (First six steps from here: 
http://tso.bzb.us/2016/06/postgresql-upgrade-fedora-24.html)

  Install the upgrade utilities:  dnf install postgresql-upgrade
  Become user postgres:   su - postgres
  Rename the data directory:  mv /var/lib/pgsql/data/ 
/var/lib/pgsql/data_9.4/
  As root, initialize your 9.5 database:  sudo postgresql-setup initdb
  As user postgres, Copy your pg_hba.conf:  cp 
/var/lib/pgsql/data_9.4/pg_hba.conf/ /var/lib/pgsql/data/pg_hba.conf/
  To avoid any potential password issues, temporarily change "md5" to 
"trust" in both pg_hba.conf files

  start postgresql
  As root, systemctl restart postgresql (generally "restart" is more 
successful than "start;" could be detris left behind)

  Run postgis script (As postgres, run CreateTemplatePostgis-2.2.sh Script 
below)

  As postgres, create users (createuser -i someusername)

  Create databases make sure users are correct and postgis template is 
utilized
   for postgis database createdb  -T template_postgis ngdata


 - Restore databases

  Check Postgis Version

####################
###################

####### Backup Script ##########
#!/usr/bin/bash

for DBASE in list databases here
do
        BACKUPDIR="/path/to/backup/dir"
        DBASEB="$BACKUPDIR/$DBASE.backup"

        pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f $DBASEB 
$DBASE
        sleep 2

done

####### End Backup Script ##########

###### ShowUsers.sql #######
SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
###### End ShowUsers.sql #######

####### CreateTemplatePostgis-2.2.sh Script ##########
#!/usr/bin/env bash
POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/postgis-2.2
createdb -E UTF8 template_postgis # Create the template spatial database.

# This next line shows an error
createlang -d template_postgis plpgsql # Adding PLPGSQL language support.

psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE 
datname='template_postgis';"
psql -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql # Loading the 
PostGIS SQL routines
psql -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql

# Added the following line
psql -d template_postgis -f $POSTGIS_SQL_PATH/rtpostgis.sql

psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;" # 
Enabling users to alter spatial tables.
psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

####### End CreateTemplatePostgis-2.2.sh Script ##########


####### Restore Databases Script ##########
#!/usr/bin/bash

for DBASE in bls comdata commodities dcmms demo events iea kmldata paper 
pg2 pivottable postgis_in_action refineries
do
        BACKUPDIR="/path/to/backup/dir"
        DBASEB="$BACKUPDIR/$DBASE.backup"
	perl /usr/share/pgsql/contrib/postgis-2.2/postgis_restore.pl 
$DBASEB | psql -h localhost -p 5432 -U postgres $DBASE
done
####### End Restore Databases Script ##########

In the psql monitor issue the following command
 select postgis_full_version();
_______________________________________________
users mailing list -- users@xxxxxxxxxxxxxxxxxxxxxxx
To unsubscribe send an email to users-leave@xxxxxxxxxxxxxxxxxxxxxxx