On Sun, May 7, 2023 at 6:45 AM Max Pyziur <pyz@xxxxxxxxx> wrote:
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
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 ##########
I've done something similar but not scripted. I will be manually creating a new database and then copying a backup into postgresql.
Fortunately this machine is just a laptop that I experiment with and to take on trips. My main postgresql lives on a server. It is much more complex in that one of the databases is updated every minute. Shutting it down for the length of time it takes me to backup and restore is a pain.postgresql-setup --upgrade seems like a better approach. I'd still have to back it up but it would be as critical. My server is still on Fedora 37.
Thanks again for the info. I will likely create some scripts from your examples to use on the server.
Best,
Clifford
_______________________________________________ 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
