Re: Postgresql upgrade from 14 to 15

[Max Pyziur <pyz@xxxxxxxxx>]

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 been 
fully successful using it.

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

Max
pyz@xxxxxxxxx

> 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:  sudo 
postgresql-setup initdb |or| postgresql-setup --initdb --unit postgresql
   As 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 more 
successful than "start;" could be detris left behind)

   As postgres, run postgis script  - run 
CreateTemplatePostgis-<VersionNumber>.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


####### 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 language 
support.
psql -d template_postgis -c "CREATE LANGUAGE plpgsql ;"
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
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@xxxxxxxxxxxxxxxxxxxxxxx
To unsubscribe send an email to users-leave@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxx
Do not reply to spam, report it: https://pagure.io/fedora-infrastructure/new_issue