These messages are dated yesterday. You should, I think, stop the service, backup the service file, edit it to have the path as previously described, and re-start the service.
You have a mis-match of versions and you need to have the service and the initdb using the same version.
Edited:
You might then want to post the new status output so that we can see if the edit helped.
The postgresql-old-upgrade package’s descriptions reads
Older PostgreSQL for migrating major versions with pg_upgrade
This looks like the OP cannot dump the old data with the new Postgres version he has installed now. Anyway, pg_upgrade is simpler and should do - at least it did for me.
The OP said
@jofi - to me that sounds like they have already dumped the data, but of course maybe you are right.
I used to run a lot of Postgres, even clusters for work.
The in place pg_upgrade for every Postgres major version is the recommended way to go. Minor versions no problem. But major versions, every time. I would get process down if you have important data. It’s also much quicker.
Seems like you have given up on that, and want to export/import? Not as resilient. But works most the time, especially with simple DBs only used as a basic transactional database.
But I’m confused what or where this is headed now.
There is a mismatch of the PGROOT path. You initialised a different path for your data. But your service is configured with the default path. If your setup ever did work you changed the PGROOT path and most likely you did this:
And this isn’t the way since this will be lost on updates. Instead use
# systemctl edit unit --drop-in=drop_in_name
to overwrite the PGROOT setting.
Moreover, you don’t need any dump to upgrade (it’s for making backups). The better way is to use pg_upgrade.
Thanks, but I have in /var/lib/postgres/data/postgresql.conf and this had worked in the past.
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
# NEU 11.01.2021 UND 04.09.2025 data_directory definiert
data_directory = '/mnt/Daten/Technik/PostgreSQL/data'
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
Nevertheless I have
[quote="MaMicha, post:14, topic:183753"]
Mine `/usr/lib/systemd/system/postgresql.service` is the same:
``
```
Environment=PGROOT=/var/lib/postgres
```
[/quote]
Now what?
You need to fix the mismatch between the path used in initdb and in the service. People have suggested a few ways to fix this, but as far as I can tell you have not tried any of them. I don’t think i have anything else to suggest. Good luck.
Then you didn’t run Postgres as systemd service, which is possible, too. Or rather you did which is why there is an old version under the default path so now with the new version this fails to start.
Thank you @jofi
How can I find out that? I’d prefer to run it as systemd service. How to set up this?
The systemd service tells you there is an old database under ${PGROOT}/data and this is why it doesn’t start.
This is the systemd service’s PGROOT, so the old database is under /var/lib/postgres/data. In all your posts you never touched this path, so there was no change there, so it has the old version. The underlying problem is configuring the systemd service with a default location and at the same time setting the Postgres data location via the Postgres configuration to something else. Your setup was screwed before the upgrade (whether or how it worked who knows doesn’t matter) and the upgrade attempt only reveals this. Please read about it and notice the tip box (with green background)
If you change the root to something other than
/var/lib/postgres, you will have to edit the service file. If the root is underhome, make sure to setProtectHometo false.
I don’t have my DB at the default location and all i did was to edit the service file - i can’t recall anything else like modifying the Postgres configuration.
Thank you @jofi,
this is my actual /var/lib/postgres:
[XYZ data]# cd /var/lib/postgres
[XYZ postgres]# ls
data
[XYZ postgres]# ls -l
insgesamt 4
drwx------ 19 postgres postgres 4096 4. Sep 09:25 data
[XYZ postgres]# cd data
[XYZ data]# ls -l
insgesamt 120
drwx------ 5 postgres postgres 4096 4. Sep 09:25 base
drwx------ 2 postgres postgres 4096 4. Sep 09:25 global
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_commit_ts
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_dynshmem
-rw------- 1 postgres postgres 5711 4. Sep 09:25 pg_hba.conf
-rw------- 1 postgres postgres 2640 4. Sep 09:25 pg_ident.conf
drwx------ 4 postgres postgres 4096 4. Sep 09:25 pg_logical
drwx------ 4 postgres postgres 4096 4. Sep 09:25 pg_multixact
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_notify
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_replslot
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_serial
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_snapshots
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_stat
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_stat_tmp
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_subtrans
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_tblspc
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_twophase
-rw------- 1 postgres postgres 3 4. Sep 09:25 PG_VERSION
drwx------ 4 postgres postgres 4096 4. Sep 09:25 pg_wal
drwx------ 2 postgres postgres 4096 4. Sep 09:25 pg_xact
-rw------- 1 postgres postgres 88 4. Sep 09:25 postgresql.auto.conf
-rw------- 1 postgres postgres 30837 4. Sep 10:24 postgresql.conf
And this is the actual /mnt/Daten/Technik/PostgreSQL incl the old_data 16 an 17:
[XYZ data]# cd /mnt/Daten/Technik/PostgreSQL
[XYZ PostgreSQL]# ls -l
insgesamt 12
drwx------ 19 postgres postgres 4096 16. Dez 14:25 data
drwx------ 20 postgres postgres 4096 5. Feb 2025 data16
drwx------ 19 postgres postgres 4096 16. Dez 14:40 data17
[XYZ PostgreSQL]# cd data
[XYZ data]# ls -l
insgesamt 120
drwx------ 5 postgres postgres 4096 16. Dez 14:25 base
drwx------ 2 postgres postgres 4096 16. Dez 14:25 global
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_commit_ts
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_dynshmem
-rw------- 1 postgres postgres 5721 16. Dez 14:25 pg_hba.conf
-rw------- 1 postgres postgres 2681 16. Dez 14:25 pg_ident.conf
drwx------ 4 postgres postgres 4096 16. Dez 14:25 pg_logical
drwx------ 4 postgres postgres 4096 16. Dez 14:25 pg_multixact
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_notify
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_replslot
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_serial
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_snapshots
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_stat
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_stat_tmp
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_subtrans
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_tblspc
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_twophase
-rw------- 1 postgres postgres 3 16. Dez 14:25 PG_VERSION
drwx------ 4 postgres postgres 4096 16. Dez 14:25 pg_wal
drwx------ 2 postgres postgres 4096 16. Dez 14:25 pg_xact
-rw------- 1 postgres postgres 88 16. Dez 14:25 postgresql.auto.conf
-rw------- 1 postgres postgres 32364 16. Dez 14:25 postgresql.conf
What would you do now. I’d like to run the systemd.service and have the data in /mnt/Daten/Technik/PostgreSQL.
Should I edit the file /usr/lib/systemd/system/postgresql.service and modify
Environment=PGROOT=/var/lib/postgres
to
Environment=PGROOT=/mnt/Daten/Technik/PostgreSQL
Adapting your procedure to my local situation, my code would be:
$ sudo -i
# su postgres
[postgres]$ cd /mnt/Daten/Technik/PostgreSQLpostgres
[postgres]$ mv data olddata # SKIP, already done!!!
[postgres]$ rm -r data # Kill the old one
[postgres]$ mkdir data
[postgres]$ mkdir /mnt/Daten/Technik/PostgreSQL/data
[postgres]$ chown postgres:postgres /mnt/Daten/Technik/PostgreSQL/data # ADDED to your process; OK?
[postgres]$ mkdir tmp
[postgres]$ cd tmp
[postgres]$ initdb -D /mnt/Daten/Technik/PostgreSQLpostgres/data
[postgres]$ pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /mnt/Daten/Technik/PostgreSQLpostgres/olddata -D /mnt/Daten/Technik/PostgreSQLpostgres/data # pgsql-17 is present actually
Would you agree?
This seems to be a typo. Other than this, yes /mnt/Daten/Technik/PostgreSQL would be your new PGROOT. I think the pg_upgrade may fail as i mentioned earlier because the change in check sums. In this case
pg_checksums --disable --pgdata /mnt/Daten/Technik/PostgreSQL/data
pg_upgrade ...
pg_checksums --enable --pgdata /mnt/Daten/Technik/PostgreSQL/data
Thank yo @jofi,
I modified the file /usr/lib/systemd/system/postgresql.service to
Environment=PGROOT=/mnt/Daten/Technik/PostgreSQL
rebooted and now I have:
$ systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
Active: active (running) since Thu 2025-12-18 16:28:47 CET; 22s ago
Invocation: ecd0bfa5c81844ed8037d6c48caf025d
Docs: man:postgres(1)
Process: 944 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
Main PID: 948 (postgres)
Tasks: 9 (limit: 37444)
Memory: 30.1M (peak: 30.6M)
CPU: 139ms
CGroup: /system.slice/postgresql.service
├─948 /usr/bin/postgres -D /mnt/Daten/Technik/PostgreSQL/data
├─950 "postgres: io worker 0"
├─951 "postgres: io worker 1"
├─952 "postgres: io worker 2"
├─953 "postgres: checkpointer "
├─954 "postgres: background writer "
├─956 "postgres: walwriter "
├─957 "postgres: autovacuum launcher "
└─958 "postgres: logical replication launcher "
Dez 18 16:28:46 XYZ systemd[1]: Starting PostgreSQL database server...
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.045 CET [948] LOG: starting PostgreSQL 18.1 on x>
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.045 CET [948] LOG: listening on IPv6 address "::>
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.045 CET [948] LOG: listening on IPv4 address "12>
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.047 CET [948] LOG: listening on Unix socket "/ru>
Dez 18 16:28:47 XYZ postgres[955]: 2025-12-18 16:28:47.055 CET [955] LOG: database system was shut down>
lines 1-27...skipping...
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
Active: active (running) since Thu 2025-12-18 16:28:47 CET; 22s ago
Invocation: ecd0bfa5c81844ed8037d6c48caf025d
Docs: man:postgres(1)
Process: 944 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
Main PID: 948 (postgres)
Tasks: 9 (limit: 37444)
Memory: 30.1M (peak: 30.6M)
CPU: 139ms
CGroup: /system.slice/postgresql.service
├─948 /usr/bin/postgres -D /mnt/Daten/Technik/PostgreSQL/data
├─950 "postgres: io worker 0"
├─951 "postgres: io worker 1"
├─952 "postgres: io worker 2"
├─953 "postgres: checkpointer "
├─954 "postgres: background writer "
├─956 "postgres: walwriter "
├─957 "postgres: autovacuum launcher "
└─958 "postgres: logical replication launcher "
Dez 18 16:28:46 XYZ systemd[1]: Starting PostgreSQL database server...
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.045 CET [948] LOG: starting PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.1 20251112, 64-bit
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.045 CET [948] LOG: listening on IPv6 address "::1", port 5432
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.045 CET [948] LOG: listening on IPv4 address "127.0.0.1", port 5432
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.047 CET [948] LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
Dez 18 16:28:47 XYZ postgres[955]: 2025-12-18 16:28:47.055 CET [955] LOG: database system was shut down at 2025-12-16 14:25:11 CET
Dez 18 16:28:47 XYZ postgres[948]: 2025-12-18 16:28:47.063 CET [948] LOG: database system is ready to accept connections
What to do as my next step?
Well, stop the Postgres service, switch to the postgres user, cd to the temporary directory and run the pg_upgrade command. If it fails with the check sum error i already posted the commands. Good luck.
Sorry, I had the server running yesterday, but without my data:
$ sudo -i
[XYZ ~]# su postgres
[postgres@XYZ root]$ psql -l
Liste der Datenbanken
Name | Eigentümer | Kodierung | Locale-Provider | Sortierfolge | Zeichentyp | Locale | ICU-Regeln | Zugriffsprivilegien
-----------+------------+-----------+-----------------+--------------+------------+--------+------------+-----------------------
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 Zeilen)
So I tried again today:
$ sudo -i
# su postgres
# systemctl stop postgresql
# systemctl status postgresql # result: Dead!
[postgres]$ cd /mnt/Daten/Technik/PostgreSQL
[postgres]$ mv data olddata # SKIP, already done!!!
[postgres]$ rm -r data # Kill the old one
[postgres]$ mkdir /mnt/Daten/Technik/PostgreSQL/data
[postgres]$ chown postgres:postgres /mnt/Daten/Technik/PostgreSQL/data
[postgres]$ mkdir tmp
[postgres]$ cd tmp
$ sudo -i
[XYZ ~]# su postgres
[postgres@XYZ root]$ initdb -D /mnt/Daten/Technik/PostgreSQL/data
SUCCESS, Server can be started ...
exit
exit
[myName@XYZ qqtmp]$ pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /mnt/Daten/Technik/PostgreSQL/data17 -D /mnt/Daten/Technik/PostgreSQL/data
konnte Zugriffsrechte von Verzeichnis »/mnt/Daten/Technik/PostgreSQL/data« nicht lesen: Keine Berechtigung
Fehlgeschlagen, Programm wird beendet
[myName@XYZ qqtmp]$ sudo -i
[XYZ ~]# pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /mnt/Daten/Technik/PostgreSQL/data17 -D /mnt/Daten/Technik/PostgreSQL/data
pg_upgrade: kann nicht als root ausgeführt werden
Fehlgeschlagen, Programm wird beendet
[XYZ ~]# su postgres
[postgres@XYZ root]$ pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /mnt/Daten/Technik/PostgreSQL/data17 -D /mnt/Daten/Technik/PostgreSQL/data
Sie müssen Lese- und Schreibzugriff im aktuellen Verzeichnis haben.
Fehlgeschlagen, Programm wird beendet
[postgres@XYZ root]$ cd /mnt/Daten/qqtmp
[postgres@XYZ qqtmp]$ pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /mnt/Daten/Technik/PostgreSQL/data17 -D /mnt/Daten/Technik/PostgreSQL/data
Sie müssen Lese- und Schreibzugriff im aktuellen Verzeichnis haben.
Fehlgeschlagen, Programm wird beendet
Why is pgupgrade not running?
Sie müssen Lese- und Schreibzugriff im aktuellen Verzeichnis haben.
Appears to translate to
You must have read and write access in the current directory.
The pg_upgrade manual page says
pg_upgrade requires write permission in the current directory.
So I guess the postgres user does not have write access in /mnt/Daten/qqtmp - surely you can find a directory in which it can both read and write? Maybe the postgres user’s home directory, or /tmp, or something.
Thanks for translating.
You just need sudo chown (-R) postgres:postgres on the data directories, no?
(With 770 on directories, 660 files.)
My data dirs in the past were in places all over.
I think the actual data directories are probably okay, it is just that the pg_upgrade command is being issued in another directory. For instance, they tried running it in /mnt/Daten/qqtmp and we have no idea who actually owns that directory.
Thank you. The directory /mnt/Daten/qqtmp is owned by myself and I have write permission there. My guess is, the temporary directory must not be part of the data-directory (e.g. mnt/Daten/?
I could solve it by choosing /tmp as temporary directory. Doing this, the solutions (1) from @Phemisters and (2)@jofi worked. In general, this has been done:
$ pamac checkupdates
3 Aktualisierungen verfügbar:
postgresql 17.5-5 -> 18.1-1 extra
postgresql-libs 17.5-5 -> 18.1-1 extra
postgresql-old-upgrade 16.9-2 -> 17.7-1 extra
$ pamac update
$ sudo -i
# mv /mnt/Daten/Technik/PostgreSQL/data /mnt/Daten/Technik/PostgreSQL/data17
# rm -r /mnt/Daten/Technik/PostgreSQL/data # Remove the old and create a new data-dir
# mkdir /mnt/Daten/Technik/PostgreSQL/data
# chown postgres:postgres /mnt/Daten/Technik/PostgreSQL/data
# exit
$ sudo -i
# su postgres
[postgres@XYZ root]$ cd /tmp # IMPORTANT, do not use a directory within /mnt/Daten/...
[postgres@XYZ tmp]$ initdb -D /mnt/Daten/Technik/PostgreSQL/data --locale=C.UTF-8 --encoding=UTF8 --data-checksums
...
SUCCESS: You can start the Server now using:
pg_ctl -D /mnt/Daten/Technik/PostgreSQL/data -l logdatei start
[postgres@XYZ tmp]$ exit
exit
[XYZ ~]# exit
Abgemeldet
$ cd /tmp
$ sudo -i
[XYZ ~]# su postgres
[postgres@XYZ root]$ cd /tmp
[postgres@XYZ tmp]$ pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /mnt/Daten/Technik/PostgreSQL/data17 -D /mnt/Daten/Technik/PostgreSQL/data # pgsql-17 is present actually
Running consistency testing
-------------------------------
Running Upgrade
-------------------
...
Upgrade finished
---------------------
[postgres@XYZ tmp]$exit
exit
$ systemctl start postgresql.service
Thank you for your support!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.