On Sun, May 7, 2023 at 6:45 AM Max Pyziur <p...@brama.com> 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 > p...@brama.com > > 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 -- @osm_washington www.snowandsnow.us OpenStreetMap: Maps with a human touch
_______________________________________________ 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
