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
p...@brama.com
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@lists.fedoraproject.org
To unsubscribe send an email to users-le...@lists.fedoraproject.org
