[ISSUE] Problem accessing my databases after postgres was upgraded

[ISSUE] Problem accessing my databases after postgres was upgraded

[Dishayloo]

[-- Attachment #1: Type: text/plain, Size: 1024 bytes --]

New issue by Dishayloo on void-packages repository

https://github.com/void-linux/void-packages/issues/38306

Description:
I regularly update my system with xbps-install -Suv. I noticed postgres wasn't working anymore and realized that I now have postgresql14 installed and the old postgres-Installation is gone. So I configured the new postgres to run with runit (which works) and wanted to convert my old data-dir. This is not working, apparently as the old postgres-Binaries are missing.

Here is my console attempt:

```
$ sudo su postgres
$ pg_upgrade -c -d /var/lib/postgresql/data -b /usr/lib/postgresql/ -D /var/lib/postgresql14/data

Prüfen von »/usr/lib/postgresql/postgres« fehlgeschlagen: keine reguläre Datei
Fehlgeschlagen, Programm wird beendet
```

The output is in german, but it basically means: check for »/usr/lib/postgresql/postgres« failed: no regular file; Failed, program is terminated
This is as the old postgres-binaries are not present anymore - or I don't know where they are.

Re: Problem accessing my databases after postgres was upgraded

[Duncaen]

[-- Attachment #1: Type: text/plain, Size: 544 bytes --]

New comment by Duncaen on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1196640785

Comment:
For the next time, you can use `LANG_ALL=C` for English error messages.

There is not enough information, what was the previous version, at least the bin path (`-b`) seems wrong, not sure about the other ones. The bin paths should probably be something like `/usr/lib/psql13/bin` or `/usr/lib/psql13/`, not sure if you also need to define the new binpath or if the PATH handling is enough for that.

Re: Problem accessing my databases after postgres was upgraded

[Duncaen]

[-- Attachment #1: Type: text/plain, Size: 542 bytes --]

New comment by Duncaen on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1196640785

Comment:
For the next time, you can use `LC_ALL=C` for English error messages.

There is not enough information, what was the previous version, at least the bin path (`-b`) seems wrong, not sure about the other ones. The bin paths should probably be something like `/usr/lib/psql13/bin` or `/usr/lib/psql13/`, not sure if you also need to define the new binpath or if the PATH handling is enough for that.

Re: Problem accessing my databases after postgres was upgraded

[Duncaen]

[-- Attachment #1: Type: text/plain, Size: 650 bytes --]

New comment by Duncaen on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1196640785

Comment:
For the next time, you can use `LC_ALL=C` for English error messages. (Not sure if postgres uses localized data so maybe its not the best idea, maybe `C.UTF-8` would be better).

There is not enough information, what was the previous version, at least the bin path (`-b`) seems wrong, not sure about the other ones. The bin paths should probably be something like `/usr/lib/psql13/bin` or `/usr/lib/psql13/`, not sure if you also need to define the new binpath or if the PATH handling is enough for that.

Re: Problem accessing my databases after postgres was upgraded

[Duncaen]

[-- Attachment #1: Type: text/plain, Size: 469 bytes --]

New comment by Duncaen on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1196640785

Comment:
There is not enough information, what was the previous version, at least the bin path (`-b`) seems wrong, not sure about the other ones. The bin paths should probably be something like `/usr/lib/psql13/bin` or `/usr/lib/psql13/`, not sure if you also need to define the new binpath or if the PATH handling is enough for that.

Re: Problem accessing my databases after postgres was upgraded

[Dishayloo]

[-- Attachment #1: Type: text/plain, Size: 320 bytes --]

New comment by Dishayloo on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1196695686

Comment:
The previous version was 9.6 and seems to be deinstalled now. The directory /usr/lib/ only has psql14 and postgresql as subdirs (regarding postgres), but postgresql is empty.

Re: Problem accessing my databases after postgres was upgraded

[Chocimier]

[-- Attachment #1: Type: text/plain, Size: 596 bytes --]

New comment by Chocimier on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1196887239

Comment:
Upgrading major version requires some manual steps described at <https://www.postgresql.org/docs/current/upgrading.html>. That's why versions 12-14 were provided in numbered packages for last two years.
If you choose method that requires presence of old binaries, you can get them from cache (<https://docs.voidlinux.org/xbps/advanced-usage.html#downgrading>)  or build with xbps-src.

There was no clean way to remove long unsupported 9.6, sorry.

Re: Problem accessing my databases after postgres was upgraded

[Dishayloo]

[-- Attachment #1: Type: text/plain, Size: 900 bytes --]

New comment by Dishayloo on void-packages repository

https://github.com/void-linux/void-packages/issues/38306#issuecomment-1197054438

Comment:
Thanks, these are helpful hints. I found my own solution though in the meantime. I found an older VM at work I could install Postgres 9.6 on, copy my datadir over and exported my databases with pg_dump and reimported them into psql14. So that solved it for me, but hopefully this will help others running into the same problems.

And I actually wasn't aware I was riding an outdated psql-installation and had the chance to port over to newer numbered versions. So the removal took me by surprise. I should have been more attentive to the installation, so that I would've noted it and did somethign then were was more time. But in the end I could find a solution and now know about the possibility for downgrading, which may come in handy in the future.

Re: [ISSUE] [CLOSED] Problem accessing my databases after postgres was upgraded

[Dishayloo]

[-- Attachment #1: Type: text/plain, Size: 1027 bytes --]

Closed issue by Dishayloo on void-packages repository

https://github.com/void-linux/void-packages/issues/38306

Description:
I regularly update my system with xbps-install -Suv. I noticed postgres wasn't working anymore and realized that I now have postgresql14 installed and the old postgres-Installation is gone. So I configured the new postgres to run with runit (which works) and wanted to convert my old data-dir. This is not working, apparently as the old postgres-Binaries are missing.

Here is my console attempt:

```
$ sudo su postgres
$ pg_upgrade -c -d /var/lib/postgresql/data -b /usr/lib/postgresql/ -D /var/lib/postgresql14/data

Prüfen von »/usr/lib/postgresql/postgres« fehlgeschlagen: keine reguläre Datei
Fehlgeschlagen, Programm wird beendet
```

The output is in german, but it basically means: check for »/usr/lib/postgresql/postgres« failed: no regular file; Failed, program is terminated
This is as the old postgres-binaries are not present anymore - or I don't know where they are.