Support - I broke my PostgreSQL Installation

[nr458h]

What type of hardware are you using: Virtual machine
What YunoHost version are you running: 12.1.27
How are you able to access your server: The webadmin
SSH
Direct access via physical keyboard/screen
Are you in a special context or did you perform specific tweaking on your YunoHost instance ?: i tried to reinstall immich

Describe your issue

Some weeks ago during an update my immich installation broke - the backup didn’t work so tried to reinstall the app and ran into several issues. The language and the format weren’t correct etc. So I tried to repair it with help from the internet by removing legacy parts off installed postgres-installation which hat no dependencies

I followed also this thread as well:

After installing Joplin : "Error: Postgres version not supported"

It was : $ sudo pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log 15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log I don’t have the right version apparently : postgresql-15

Now I’m still not able to reinstall my immich instance as well my paperless server is not reachable anymore.

If I can provide specific logs or terminal output I’m very greatful for your help
Thanks in advance

Share relevant logs or error messages

https://paste.yunohost.org/raw/duhopuyoci

[otm33]

Hello @nr458h
This is going to be a bit tricky since we don’t know what has been modified or deleted, but we’ll see what we can do.

1. Check which port each installed version of PostgreSQL is listening on your server:

sudo pg_lsclusters

postgresql 15 should listen on 5432.

2. Then connect to the version using 5432 port and look for a paperless database

sudo -u postgres psql -p 5432 -c "\l"

3. Backup your database

sudo -u postgres -p 5432 pg_dump -d paperless* -F p -f /tmp/paperless_bak.sql

• *database name may be different: see the output of step 2

4. You may now have to change paperless_ngx user password:

sudo -u postgres psql -p 5432 -d paperless* -c "ALTER USER paperless_ngx WITH PASSWORD '<password set in /etc/yunohost/apps/paperless_ngx/settings.yml:db_password';"

It should be the same for immich.

[nr458h]

Thanks for the quick response.

1. running the commant shows that postgres 16/17 are installed as well, the output is:

Ver Cluster Port   Status  Owner     Data directory                      Log file
15   main    5499  online  postgres  /var/lib/postgresql/15/main  /var/log/postgresql/postresql-15-main.log
16   main    5498  online  postgres  /var/lib/postgresql/16/main  /var/log/postgresql/postresql-16-main.log
17   main    5432  online  postgres  /var/lib/postgresql/17/main  /var/log/postgresql/postresql-17-main.log

So if I run the second step I get an output but only after switching to root with su, with sudo I get an error.
Two questions remain for me:

1. how do I change the port?
2. My sorting and my typset is set to de_DE.UTF-8 what got me in trouble in the first place - should I switch it to en and how do I do that?

Thanks in advance

[otm33]

@nr458h

Ver Cluster Port   Status  Owner     Data directory                      Log file
15   main    5499  online  postgres  /var/lib/postgresql/15/main  /var/log/postgresql/postresql-15-main.log
16   main    5498  online  postgres  /var/lib/postgresql/16/main  /var/log/postgresql/postresql-16-main.log
17   main    5432  online  postgres  /var/lib/postgresql/17/main  /var/log/postgresql/postresql-17-main.log

Check if there is an existing paperless database on one of the three psql versions installed.

@nr458h

how do I change the port?

NB: Before changing port, check which databases are available on each version of psql. Changing port will break apps if you can’t update this param in their settings.

For example, for psql 15

sudo nano /etc/postgresql/15/main/postgresql.conf

edit value of port key

sudo systemctl restart postgresql@15-main

@nr458h

My sorting and my typset is set to de_DE.UTF-8 what got me in trouble in the first place - should I switch it to en and how do I do that?

It worked before with de_DE, didn’t it ?

[nr458h]

Thank you for your help - while paperless is running again after restarting postgresql the version 16 is shown as down.
Also the installation-script for immich is failing - it seems the postgresql 16 installation is somewhat broken …
https://paste.yunohost.org/raw/uyiwicisuj
Any idea how to continue?

[otm33]

Try to restart it sudo pg_ctlcluster 16 main start, then check its status.

[nr458h]

sudo pg_ctlcluster 16 main start
.../16/main is not accessible or does not exist

.../postgresql# ls
15 

I think postgresql 16 wasn’t properly setup by immich, there is no folder. Force reinstall doesn’t help.
Do I need to create the folder by myself?

[otm33]

@nr458h

16 main 5498 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postresql-16-main.log

But how did it disappear ?? Did you manually remove it ?

@nr458h

Do I need to create the folder by myself?

Immich is meant to install it…

[nr458h]

@otm33

But how did it disappear ?? Did you manually remove it ?

Not intentonaly. Before asking for help I followed this instructions … After installing Joplin : "Error: Postgres version not supported" - #12 by blodarn
But as far as I can tell - I only uninstalled postgresql-16 and didn’t remove the data.

In the logs I saw that immich is forcing a reinstall of postgresql-16 as well and then aborts because there is no response from the set port.
How can I fix that - or is this an upstream (immich) problem?

[otm33]

I would try to reinstall immich It seems very curious to me that immich is trying port 5498 again if PostgreSQL 16 has been uninstalled…

If it fails again, you can try installing manually postgresql 16

sudo apt install postgresql-16
sudo pg_createcluster 16 main --start

and then reinstall immich

[nr458h]

Thanks man for your patients and your support. But after running your commands it still not working - now I do have a folder in postgresql/ → 16 but this folder is empty ???

[otm33]

sudo pg_lsclusters
sudo pg_dropcluster 16 main --stop
sudo pg_createcluster 16 main --start
sudo pg_lsclusters

The previous uninstallation of postgresql may have been incomplete ?

[nr458h]

Thanks - it seemed to work - the installation process is still runing.

[otm33]

Soooooo… ??

[nr458h]

Thanks @otm33 it worked out everything. I marked the solution.

The problem were the following:

• wrong port in first place for postgresql-15
• the other postgresql instances were somewhat broken/not correctly installed by the application with the marked solution it is possible to fix the postgresql installation, afterwards it is nessesary to restart. If the expected port durring an installation differs from the actuall port you can change the port with the commands from the linked thread.