Re: PostgreSQL broken by upgrade to F24

[Max Pyziur]

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-migrati...
>
> 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
[e-mail]

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();