# pkg upgrade sqlite3 mysteriously wants to install postgresql13-client



## putney (Jan 14, 2022)

I'm guessing I've screwed up my package database (?) somehow. I've tried searching for similar problems but come up blank.

Of course, letting pkg install postgresql13-client would also remove postgres12-server and replace it with postgres13-server. I don't really want to version-chase postgres (though I will if I have to).

All tips gratefully received.


```
# pkg info -d sqlite3
sqlite3-3.35.5_3,1:
    libedit-3.1.20210216,1
# pkg info -d libedit
libedit-3.1.20210216,1:
# pkg upgrade sqlite3
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Checking integrity... done (0 conflicting)
The following 2 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
    postgresql13-client: 13.5

Installed packages to be UPGRADED:
    sqlite3: 3.35.5_3,1 -> 3.35.5_4,1

Number of packages to be installed: 1
Number of packages to be upgraded: 1

The process will require 12 MiB more space.

Proceed with this action? [y/N]:
# uname -a
FreeBSD nuck.lan 12.2-RELEASE-p12 FreeBSD 12.2-RELEASE-p12 GENERIC  amd64
#
```


----------



## SirDice (Jan 14, 2022)

Don't upgrade individual packages, upgrade the whole lot at the same time. Make sure everything is up to date before installing something new. Installing a new package will upgrade any of its dependencies if they're out of date.


----------



## putney (Jan 14, 2022)

Thanks. That's were I started - a plain "pkg upgrade" said it would install postgresql13-client, and replace postgres12-server. I was trying to produce a minimal case to see where the dependency on postgresql13-client was. But the examples I found appear to suggest that even if there is a genuine dependency somewhere, there are cases where pkg says it's making the change, but "pkg info -d" doesn't explain it. I'm confused (not an unusual situation!).


----------



## obsigna (Jan 14, 2022)

Try to install databases/postgresql12-client before anything else. Or perhaps even better force re-install the srver. This should drag-in the client as well: `pkg install -f postgresql12-server`

The client is a dependency of the server. My best educated guess is, that the pkg tool somehow finds out that the client dependency is missing, and adds the latest postgresql-client to the list of pkgs to be installed. Then it finds out, that the server (which triggered the dependency fault) is of the wrong version for the shiny new client, and only now, pkg wants to update this as well.

I just updated databases/sqlite3 on one of my machines, which got both of postgresql11 - client and server - installed and pkg left this untouched.


----------



## putney (Jan 14, 2022)

Nice idea, but no joy:


```
# pkg install -f postgresql12-server
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
#
# pkg install -f postgresql12-client
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Checking integrity... done (0 conflicting)
The most recent versions of packages are already installed
#
```

If I understand you, you're suggesting that 'pkg upgrade foo' may try to fix dependency problems unrelated to the particular package 'foo' you've asked it upgrade? 

My only postgres database is for davical-php74. The installed version has a dependency (via php) on postgresql12-client, and there's a php74 update that may want to replace that with something depending on postgres13-client if that's now the default postgres version in packages. 

Is it likely that if I want to continue using davical & php from packages, I'm stuck with changing postgres versions every year when the packages are all rebuilt against a different postgres?


----------



## SirDice (Jan 14, 2022)

putney said:


> Is it likely that if I want to continue using davical & php from packages, I'm stuck with changing postgres versions every year when the packages are all rebuilt against a different postgres?


Default versions change all the time. So yes, this can and will happen again in the future. 

See /usr/ports/UPDATING:

```
20211024:
  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 12 to 13.
  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 12.8, your
  files are named according to this.

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

  Now update PostgreSQL:

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

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

    Portupgrade users:
    # portinstall databases/postgresql13-server databases/postgresql13-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/data13 -U postgres"
  # su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data12/ -B /usr/local/bin/ -D /var/db/postgres/data13/ -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
```

If you build from ports (or set up your own repository) you can control the default versions yourself. In this case you could set:

```
DEFAULT_VERSIONS+= pgsql=12
```
To keep on PostgreSQL 12 and switch to 13 when you're better prepared.


----------



## putney (Jan 15, 2022)

Thanks again. I'll probably just put in the prep time and move everything to FreeBSD 13 and Postgres 13. 
My current understanding of my 12 and 12 installation...

My pkg database and/or installation does seem to be slightly broken. Davical has an installation script that creates/updates its postgres database, and that uses Perl - and in my case 'pkg -d upgrade <anything>' reports 

```
DBG(1)[43266]> cannot find local packages that provide library libpq.so.5 required for p5-DBD-Pg
DBG(1)[43266]> cannot find remote packages that provide library libpq.so.5 required for p5-DBD-Pg
```

which I guess is what leads it to try to pull in the current postgres-client13. The p5-DBD packages are installed, and libpq.so.5 is present; it was installed by postgresql12-client according to 'pkg which'. And postgresql12-client is still installed.

I already tried a forced reinstall of postgresql12-client as suggested by obsigna, but it didn't change anything.


----------

