pkg update but don't update PostgreSQL from 13 to 15 as recommended

I

i.am.the.problem

Hi,

I need to update the packages on a FreeBSD system but I don't have the capacity to look into what would be effected by upgrading PostgreSQL 13 at the moment.

Is it possible to upgrade all packages apart from PostgreSQL? What issues might I face?

Code: 
# pkg upgrade
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Checking for upgrades (22 candidates): 100%
Processing candidates (22 candidates): 100%
Checking integrity... done (4 conflicting)
  - postgresql15-contrib-15.4 conflicts with postgresql13-contrib-13.12 on /usr/local/bin/oid2name
  - postgresql15-server-15.4 conflicts with postgresql13-server-13.12 on /usr/local/bin/initdb
  - postgresql15-client-15.4 conflicts with postgresql13-client-13.12 on /usr/local/bin/clusterdb
  - postgresql15-client-15.4 conflicts with postgresql13-client-13.12 on /usr/local/bin/clusterdb
Checking integrity... done (0 conflicting)
The following 15 package(s) will be affected (of 0 checked):

Installed packages to be REMOVED:
        postgresql13-client: 13.12
        postgresql13-contrib: 13.12
        postgresql13-server: 13.12

New packages to be INSTALLED:
        postgresql15-client: 15.4
        postgresql15-contrib: 15.4
        postgresql15-server: 15.4

Installed packages to be UPGRADED:
        apache24: 2.4.57_1 -> 2.4.58_1
        gdal: 3.6.2_3 -> 3.7.2_1
        harfbuzz: 8.2.1 -> 8.2.2
        openvpn: 2.6.6 -> 2.6.8_1
        postgis33: 3.3.2_3 -> 3.3.4
        py39-psycopg2: 2.9.5 -> 2.9.8
        py39-urllib3: 1.26.16_1,1 -> 1.26.18,1
        sudo: 1.9.14p3 -> 1.9.15p2

Installed packages to be REINSTALLED:
        pkg-1.20.8

Number of packages to be removed: 3
Number of packages to be installed: 3
Number of packages to be upgraded: 8
Number of packages to be reinstalled: 1

The process will require 20 MiB more space.

Proceed with this action? [y/N]: n
 
It's possible to lock a package at the current version, see pkg-lock().

So, I think you can do:
pkg lock postgresql13-client postgresql13-contrib postgresql13-server

But I'm unsure if that will help here since it's actually wanting to remove these packages and then replace them with other packages.

You might have to identify the package that, when updated, needs PostgreSQL 15. Maybe your postgis33 package? And then lock that instead.

Edit: Yes, looks like at least postgis33 v3.3.5 has a runtime dependency on databases/postgresql15-server and databases/postgresql15-contrib, see:

FreshPorts -- databases/postgis33: Geographic objects support for PostgreSQL databases

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial...
www.freshports.org www.freshports.org

Edit 2: You can also run pkg rquery '%dn-%dv' postgis33 to list the dependencies.
 
So I need to upgrade from 13 to 15. Is that as simple using pg_dumpall while version 13 is running, upgrade to 15 then, restoring the dump?

Or would I be better investigating pg_upgrade?
 
Seems I have got myself into a right mess.

Code: 
pg_dumpall > all.sql
pg_dump: error: query failed: ERROR:  could not access file "$libdir/postgis-3": No such file or directory
pg_dump: error: query was: SELECT
a.attnum,
a.attname,
a.atttypmod,
a.attstattarget,
a.attstorage,
t.typstorage,
a.attnotnull,
a.atthasdef,
a.attisdropped,
a.attlen,
a.attalign,
a.attislocal,
pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,
a.attgenerated,
CASE WHEN a.atthasmissing AND NOT a.attisdropped THEN a.attmissingval ELSE null END AS attmissingval,
a.attidentity,
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions,
CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
array_to_string(a.attoptions, ', ') AS attoptions
FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = '9900445'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2
ORDER BY a.attnum
pg_dumpall: error: pg_dump failed on database "gis", exiting

Now I can't install postgis again as it will try to pull in postgresql 15 again.
 
All packages that have a dependency on PostgreSQL are now built against the new default, i.e. version 15.
If you build packages yourself you can keep PostgreSQL 13 the default; that's what I'm doing until I find the time to upgrade our PostgreSQL cluster as well as everything that uses it... PostgreSQL 13 will be supported until the end of 2025, so no need to hurry.
 
i.am.the.problem said:
So I need to upgrade from 13 to 15.
Click to expand...
Code: 
20230908:
  AFFECTS: users of databases/postgresql* and other software using PostgreSQL to run
  AUTHOR: kbowling@FreeBSD.org

  The default version of PostgreSQL has been switched from 13 to 15.
  The upgrade procedure can use up twice the space the databases
  currently needs. If you have a big amount of stored data take a
  closer look at the manpage of pg_upgrade for avoidance and/or
  speedup of the upgrade.

  The upgrade instructions consider a basic usage and do not match
  complex scenarios like replication, sharding, or similar.

  Upgrade instructions:

  First stop your PostgreSQL, create PostgreSQL-binaries and backup your data.
  If you have another Version of PostgreSQL installed, for example 13, your
  files are named according to this.

  # service postgresql stop
  # pkg create postgresql13-server postgresql13-contrib
  # mkdir /tmp/pg-upgrade
  # tar xf postgresql13-server-13.12.pkg -C /tmp/pg-upgrade
  # tar xf postgresql13-contrib-13.12.pkg -C /tmp/pg-upgrade
  # pkg delete -f databases/postgresql13-server databases/postgresql13-contrib databases/postgresql13-client

  Now update PostgreSQL:

    pkg user:
    # pkg install databases/postgresql15-server databases/postgresql15-contrib
    # pkg upgrade

    Portmaster users:
    # portmaster databases/postgresql15-server databases/postgresql15-contrib
    # portmaster -a

    Portupgrade users:
    # portinstall databases/postgresql15-server databases/postgresql15-contrib
    # portupgrade -a

  After installing the new PostgreSQL version you need to convert
  all your databases to new version:

  # su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data15 -U postgres"
  # su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data13/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres "

  Now the migration is finished. You can start PostgreSQL again with:

  # service postgresql start

  ATTENTION:
  1) The default user changed from "pgsql" to "postgres" in 11. The migration steps above now assume
     the "postgres" database user and FreeBSD user.
  2) See the updating entry 20190829 if you are updating from a release prior to 11.
  3) If you use non-default initdb options, you have to adjust the initdb-command accordingly
 
I had to delete /usr/local/bin/clusterdb to get pkg to resume mid-flight after bombing out..
It failed on this file and I took a chance and deleted it after pkg pointed me at it..
 
Alain De Vos said:
Can you explain the reason why not update postgres ?
Click to expand...
The machine is, among other things, an openstreet map tile server. At the time I set it up I compiled and install mapnik, mod_tile and renderd from their source code rather than via FreeBSD's ports or packages.

I thought by leaving postgres and postgis alone I'd be able to keep it working as is.

What I ended up doing was installing postgis33 from the out of date ports tree still on the machine. Then used pg_dumpall, update postgres/postgis and restoring the dump.
 
You must log in or register to reply here.
Back
Top