Re: Postgresql upgrade from 14 to 15

Max Pyziur Sun, 07 May 2023 06:45:06 -0700

On Fri, 5 May 2023, Clifford Snow wrote:

I just successfully upgraded my laptop to Fedora 38.


Unfortunately, upgrading postgresql isn't working for me. Running 
postgresql-setup upgrade produces and error log in 
/var/lib/pgsql/upgrade_postgresql.log which tells me to read the last few lines 
of
/var/lib/pgsql/data/pg_upgrade_output.d/20230505T172148.011/log/pg_upgrade_dump_16385.log.
 But I'm not able to find the files. Even after a complete search of my system 
using find.

Any suggestions on how to find the error log or to successfully upgrade 
Postgresql?

I've tried uusing postgresql-setup upgrade a few times and have never beenfully successful using it.

I have a set of notes and scripts that have worked for me over the lasteight years. I am appending them below. YMMV


Max
p...@brama.com

Thanks,
Clifford

--
@osm_washington
www.snowandsnow.us
OpenStreetMap: Maps with a human touch


General outline of steps:
############
I. Pre Fedora/PostgreSQL (in this case 37-> 38) upgrade
   Backup databases
   Make note of all users ( \du in monitor )

(use sudo where root work is required)

II. Post Fedora/PostgreSQL (37-> 38) upgrade
   Initialize postgresql (First six steps adapted 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_<oldVersion>/As root, initialize your new (in this case v 15.1.2) database: sudopostgresql-setup initdb |or| postgresql-setup --initdb --unit postgresqlAs user postgres, Copy your pg_hba.conf: cp/var/lib/pgsql/data_<old_version>/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 moresuccessful than "start;" could be detris left behind)

As postgres, run postgis script - runCreateTemplatePostgis-<VersionNumber>.sh Script below)


   As postgres, create users (createuser -i someusername)

Create databases make sure users are correct and postgis template isutilized for postgis database (createdb -T template_postgis ngdata)


  - Restore databases


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

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

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


        pg_dump -U pyz $DBNAME > "$BACKUPDIR/$DBNAME"

        sleep 2

done

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

####### Make PostGIS template Script (if needed) ########
#!/usr/bin/env bash
POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/postgis-3.3
createdb -E UTF8 template_postgis # Create the template spatial database.

# createlang -d template_postgis plpgsql # Adding PLPGSQL languagesupport.

psql -d template_postgis -c "CREATE LANGUAGE plpgsql ;"

psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHEREdatname='template_postgis';"psql -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql # Loading thePostGIS SQL routines

psql -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.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 PostGIS template if needed ########

###### Create Databases #######
#!/usr/bin/bash

# Create Non-PostGIS databases
createdb SomeDBName

# Create PostGIS databases
createdb  -T template_postgis SomePostGISDBName
###### End Create Databases #######


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

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

# perl /usr/share/pgsql/contrib/postgis-2.2/postgis_restore.pl$DBASEB | psql -h localhost -p 5432 -U postgres $DBASE

        psql  -f $DBASEB $DBASE
done
####### End Restore Databases Script ##########

_______________________________________________
users mailing list -- users@lists.fedoraproject.org
To unsubscribe send an email to users-le...@lists.fedoraproject.org
Fedora Code of Conduct: 
https://docs.fedoraproject.org/en-US/project/code-of-conduct/
List Guidelines: https://fedoraproject.org/wiki/Mailing_list_guidelines
List Archives: 
https://lists.fedoraproject.org/archives/list/users@lists.fedoraproject.org
Do not reply to spam, report it: 
https://pagure.io/fedora-infrastructure/new_issue

Re: Postgresql upgrade from 14 to 15

Reply via email to