In it's infinite wisdom, the upgrade from F23 to F24 updates PostgreSQL from 9.4 to 9.5.5 making existing databases unusable.
I suppose that there is a warning about this somewhere but it certainly isn't part of the upgrade process.
I know that I can use pg_upgrade to migrate my databases but need the 9.4 binaries to do that.
Are the files in /usr/lib64/pgsql/postgresql-9.4/bin the correct ones to use with pg_upgrade?
On 11/27/2016 10:27 PM, Stephen Davies wrote:
In it's infinite wisdom, the upgrade from F23 to F24 updates PostgreSQL from 9.4 to 9.5.5 making existing databases unusable.
What else could it do? Never upgrade PostgreSQL?
I suppose that there is a warning about this somewhere but it certainly isn't part of the upgrade process.
I know that I can use pg_upgrade to migrate my databases but need the 9.4 binaries to do that.
Are the files in /usr/lib64/pgsql/postgresql-9.4/bin the correct ones to use with pg_upgrade?
Install postgresql-upgrade.
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
On 28/11/16 17:45, 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 _______________________________________________ users mailing list -- users@lists.fedoraproject.org To unsubscribe send an email to users-leave@lists.fedoraproject.org
postgresql-upgrade was already installed as I tried to use it for the 9.3 to 9.4 upgrade.
Thanks for that link.
I also had to use the PGSETUP_INITDB_OPTIONS facility to get the new database locale correct but all is now good.
(I still think there should have been more warning that the F24 upgrade would break databases. My databases were off the air for 24 hours.)
On 11/28/2016 10:08 PM, Stephen Davies wrote:
My databases were off the air for 24 hours.
On the up side, you've learned a couple of important lessons about operating production services. The first of them is this: Set up a test environment and actually test your changes there, first.
Increasingly, the other best practice is this: don't upgrade your systems in-place. Decouple your data and your software. When you need to update your software, build a new system and connect your data to it. Automate the process. With automation in place, this process is easier than upgrading your services, and provides a fall-back mechanism in the event of failure. I was reluctant to adopt that mode of operation, at first, but as I get closer to that practice, I find operations less laborious and more reliable.
On Wed, 2016-11-30 at 09:22 -0800, Gordon Messmer wrote:
On 11/28/2016 10:08 PM, Stephen Davies wrote:
My databases were off the air for 24 hours.
On the up side, you've learned a couple of important lessons about operating production services. The first of them is this: Set up a test environment and actually test your changes there, first.
Increasingly, the other best practice is this: don't upgrade your systems in-place. Decouple your data and your software. When you need to update your software, build a new system and connect your data to it. Automate the process. With automation in place, this process is easier than upgrading your services, and provides a fall-back mechanism in the event of failure. I was reluctant to adopt that mode of operation, at first, but as I get closer to that practice, I find operations less laborious and more reliable.
The OP might want to reconsider using Fedora in a production server. That's not what it is designed to do. Any public-facing services I used to run back in the day were based on CentOS.
poc
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 pyz@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();