[Support] jj9987 - PostgreSQL

[CryPt00n]

The user oc_admin is auto generated, my db "root" user is nextcloud. I´ve set the password for this one the same like in the v11 docker.

 

I´ll try to reset it for oc_admin

 

Shouldnt the password of oc_admin also get backed up and imported with my commands above?

 

EDIT: Works, thank you!

Edited November 18, 2022 by CryPt00n
Added feedback

[Eddyall]

Hello, having a bit of an issue with having a stable Szurubooru (image DB viewer) working lately while it's been good for the past while. Seems to happen when the Szurubooru becomes sizable but pretty sure that's not the root cause of the issue.

 

For context, I have multiple dockers running off the one postgres11 instance: Grocy, Firefly-iii, Mediawiki, nextcloud and two set of Szurubooru one for music and the other for images. Now because Szurubooru comes in 2 dockers, one for the API and one for the web client. I've not been able to find a way to change the port used by the API so I had to set one at br0 with it's own IP address. The problematic one is the one for images which is the bigger one but also the one on br0.

 

ATM the API docker for the image Szurubooru will just close if I try to run it on br0 but if I set it back to the bridge as the only Szurubooru running, seems to just work. Any insight on why it being on br0 breaks something or how to check how much resources a docker can use on Unraid would be appreciated.

Logs don't show much, just that the API can't connect to Postgres on the right IP/port while it could fine yesterday.

[AmokK]

On 11/17/2022 at 10:20 PM, CryPt00n said:

Not quite sure if i restored it the right way

 

Used for Backup

pg_dumpall -U nextcloud -s > bak.sql

 

Used for restore

psql -U nextcloud postgres < bak.sql

 

 

 

Currently running PostgresQL 13.9 for nextcloud database, I would like to upgrade to PostgresQL 15.1, but I'm struggling with theses commands, what is the exact syntax to use ? (I'm running command in PostgresQL container console)

My database is named "nextclouddb", I can access it using adminer.

[CryPt00n]

11 hours ago, AmokK said:

 

Currently running PostgresQL 13.9 for nextcloud database, I would like to upgrade to PostgresQL 15.1, but I'm struggling with theses commands, what is the exact syntax to use ? (I'm running command in PostgresQL container console)

My database is named "nextclouddb", I can access it using adminer.

 

Hi, i don´t know how it would work with adminer, should just export from the old one and import to the new. But this is how i migrated:

 

 

I´ve used the same port for the new database, so i switch with powering on and off between them. Just don´t wanted to change the nextcloud config.

 

-shut down nextcloud & postgres and do a backup of your appdata folders

 

-install target postgres docker

IMPORTANT: You need to create a user with the same credentials used by nextcloud after starting the docker (you can find it in the nextcloud config) or add it to the postgres docker template (easiest way)

 

-start target postgres docker (if you did not entered the user credentials for nextcloud to the template, you have to create the user now)

-shut down target postgres docker

-start source postgres docker

 

-backup Database

run in source docker:

pg_dumpall -U YOUR-NEXTCLOUD-DB-USER -s > /var/lib/postgresql/data/bak.sql

 

-in unraid, move it to your target appdata folder (e.g. from /mnt/user/appdata/postgres13 to /mnt/user/appdata/postgres15)

-stop source docker

 

-import Database
run in target docker:

psql -U YOUR-NEXTCLOUD-DB-USER -d nextclouddb < /var/lib/postgresql/data/bak.sql

 

 

Database should be imported, you can now start nextcloud again and test it

Edited November 25, 2022 by CryPt00n

[AmokK]

@CryPt00n

Thanks for the detailed answer !

First, I was referring to adminer not to use it to backup/migrate, but to point out that database and credentials are valid, as it can be accessed by adminer.

I tried your method, but when trying to backup database in source container, i get this error :

/ # pg_dumpall -U nextcloud -s > /var/lib/postgresql/data/bak.sql
pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
pg_dumpall: error: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

Nextcloud db user is really "nextcloud", no error on that, like I said, database is accessed by the same user by adminer with no problem, and I can see all entries.

I don't know what I did wrong in my setup to end up with this result 

[jj9987]

2 minutes ago, AmokK said:

@CryPt00n

Thanks for the detailed answer !

First, I was referring to adminer not to use it to backup/migrate, but to point out that database and credentials are valid, as it can be accessed by adminer.

I tried your method, but when trying to backup database in source container, i get this error :

/ # pg_dumpall -U nextcloud -s > /var/lib/postgresql/data/bak.sql
pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
pg_dumpall: error: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

Nextcloud db user is really "nextcloud", no error on that, like I said, database is accessed by the same user by adminer with no problem, and I can see all entries.

I don't know what I did wrong in my setup to end up with this result 

pg_dumpall tries to dump the whole database instance (all databases) and needs root/superuser permissions. If you want to dump a single database, use pg_dump.

Edited November 26, 2022 by jj9987

[AmokK]

@jj9987 Thanks !

I'm not very confident with command lines, sorry if I make beginner mistakes 

So I tried this :

/ # pg_dump -U nextcloud -s > /var/lib/postgresql/data/bak.sql
pg_dump: error: connection to database "nextcloud" failed: FATAL:  database "nextcloud" does not exist

I don't know how the command arguments work, but it seems I'm not using it correctly ! "-U" is for username (nextcloud) but the command seems to understand "nextcloud" database, and skips the username part ?

[Doublemyst]

Hi, are you planing to add postgres sql 15 (seems to be in stable now) as an app?

[jj9987]

1 hour ago, Doublemyst said:

Hi, are you planing to add postgres sql 15 (seems to be in stable now) as an app?

Added the template for postgres 15.

 

On 11/26/2022 at 11:02 AM, AmokK said:

@jj9987 Thanks !

I'm not very confident with command lines, sorry if I make beginner mistakes 

So I tried this :

/ # pg_dump -U nextcloud -s > /var/lib/postgresql/data/bak.sql
pg_dump: error: connection to database "nextcloud" failed: FATAL:  database "nextcloud" does not exist

I don't know how the command arguments work, but it seems I'm not using it correctly ! "-U" is for username (nextcloud) but the command seems to understand "nextcloud" database, and skips the username part ?

Postgres CLI tools default to the same database name as the user you are trying to connect with. Maybe your database has a different name?

[AmokK]

21 minutes ago, jj9987 said:

Added the template for postgres 15.

 

Postgres CLI tools default to the same database name as the user you are trying to connect with. Maybe your database has a different name?

My database is indeed named "nextclouddb". What command should I use then?

Edited January 1, 2023 by AmokK

[ptchernegovski]

Have got postgresql15 installed. Have filled in the superuser, and password. But when I try to login via console, or connect with pgadmin4, it says the password is incorrect. I have tried a few times.
Am I just being dense, or could there be a reason?

[Kilrah]

35 minutes ago, ptchernegovski said:

Have got postgresql15 installed. Have filled in the superuser, and password. But when I try to login via console, or connect with pgadmin4, it says the password is incorrect. I have tried a few times.
Am I just being dense, or could there be a reason?

Did you ever run the container before setting these up, or change them afterwards? The db/user/pw are used only on first run to set things up, any later change has no effect.

[ptchernegovski]

On 2/9/2023 at 10:03 PM, Kilrah said:

Did you ever run the container before setting these up, or change them afterwards? The db/user/pw are used only on first run to set things up, any later change has no effect.

Yes, from the install

[AB3DC]

On 2/9/2023 at 3:26 AM, ptchernegovski said:

Have got postgresql15 installed. Have filled in the superuser, and password. But when I try to login via console, or connect with pgadmin4, it says the password is incorrect. I have tried a few times.
Am I just being dense, or could there be a reason?

 

I have the same issue with postgresql15 and pgadmin4. I then installed postgresql14 and it works fine.

[ptchernegovski]

8 hours ago, AB3DC said:

 

I have the same issue with postgresql15 and pgadmin4. I then installed postgresql14 and it works fine.

Thank you! I have done the same, and working fine now!

[gilladur]

On 11/25/2022 at 10:41 PM, CryPt00n said:

 

Hi, i don´t know how it would work with adminer, should just export from the old one and import to the new. But this is how i migrated:

 

 

I´ve used the same port for the new database, so i switch with powering on and off between them. Just don´t wanted to change the nextcloud config.

 

-shut down nextcloud & postgres and do a backup of your appdata folders

 

-install target postgres docker

IMPORTANT: You need to create a user with the same credentials used by nextcloud after starting the docker (you can find it in the nextcloud config) or add it to the postgres docker template (easiest way)

 

-start target postgres docker (if you did not entered the user credentials for nextcloud to the template, you have to create the user now)

-shut down target postgres docker

-start source postgres docker

 

-backup Database

run in source docker:

pg_dumpall -U YOUR-NEXTCLOUD-DB-USER -s > /var/lib/postgresql/data/bak.sql

 

-in unraid, move it to your target appdata folder (e.g. from /mnt/user/appdata/postgres13 to /mnt/user/appdata/postgres15)

-stop source docker

 

-import Database
run in target docker:

psql -U YOUR-NEXTCLOUD-DB-USER -d nextclouddb < /var/lib/postgresql/data/bak.sql

 

 

Database should be imported, you can now start nextcloud again and test it

Thank you for your very clear explanation.

I've followed it and it seems all fine regarding nextcloud being able to connect to the V15 if it's active.
Nevertheless, I can't login into the webUI into my Nextcloud - it says that the User or Password is wrong.
If I switch back to V14 everything works again.
Any idea?

[jj9987]

Just now, gilladur said:

Thank you for your very clear explanation.

I've followed it and it seems all fine regarding nextcloud being able to connect to the V15 if it's active.
Nevertheless, I can't login into the webUI into my Nextcloud - it says that the User or Password is wrong.
If I switch back to V14 everything works again.
Any idea?

PostgreSQL upgrades aren't as easy as increasing the version. To do it properly, you have three options:

1) Dump all data from the old version, import it into the new version.

2) Go through the pg_upgrade process, which requires you to have binaries for both versions, but can be done in-place.

3) Using replication.

 

Follow the official documentation on how to do each one of them, whichever suits you best: https://www.postgresql.org/docs/current/upgrading.html

In case of containerized PostgreSQL, dump/import is probably the easiest to do, but will cause downtime.

[gilladur]

Hi jj9987,
I've used the Dump all data from the old version and imported it into the new V15 docker - the same way CryPt00n explained it.

According to the logs all connections seem to work. Still can't login in the web interface.

Could it be, that the password encryption changed between V14 and V15?

 

Edit: I've checked the tables in adminer and unfortunately they are there but empty - somehow the data is not imported properly with the command
 

psql -U nextcloud -d nextcloud < /var/lib/postgresql/data/bak.sql

if you wonder, my database is named only nextcloud

Edited May 23, 2023 by gilladur

[gilladur]

I've dived a bit deeper in the dump_all command could it be that the option -s is incorrect?

# pg_dump -U nextcloud -s > /var/lib/postgresql/data/bak.sql

according to the postgres documentation:

Quote

-s
--schema-only

Dump only the object definitions (schema), not data.

 

This would explain why no data is in the tables after import and therefore I can't login with my login data or?
 

Edit: dumping the Database without -s did the trick - Nextcloud is now running fine under V15
 

pg_dumpall -U nextcloud > /var/lib/postgresql/data/bak14.sql

==>
 

Edited May 24, 2023 by gilladur

[ZerkerEOD]

I am running into an issue, out of no where my Postgresql container has started changing my appdata/postgresql directory owner to UNKNOWN and permissions of 600. It happens every time I try to run the system. I can change the owership and permissions to 777, but as soon as the docker tries to start it resets everything back to UNKNOWN 600. Any help would be great. I tried adding the PUID and PGID to 99 and 100. The docker won't even start so I can't poke around in that.

[ZerkerEOD]

On 11/8/2021 at 9:05 PM, Jase said:

Hi, I too have just stumbled across this permission issue. When the docker container is restarted, the 'postgres' app data folder loses the correct permissions. I'm able to correct this by running chmod -R u-x,go-rwx,go+u,ugo+X '/mnt/user/appdata/postgres' then chown -R nobody:users '/mnt/user/appdata/postgres' but after I restart the container, the permissions are gone. Any help would be appreciated as this was not an issue before. 

Did you get this fixed by chance? This is happening to me every time my container tries to start and then it crashes.

[jj9987]

15 hours ago, ZerkerEOD said:

I am running into an issue, out of no where my Postgresql container has started changing my appdata/postgresql directory owner to UNKNOWN and permissions of 600. It happens every time I try to run the system. I can change the owership and permissions to 777, but as soon as the docker tries to start it resets everything back to UNKNOWN 600. Any help would be great. I tried adding the PUID and PGID to 99 and 100. The docker won't even start so I can't poke around in that.

Postgres doesn't change the permissions, unless it is run the first time (when it needs to initialize the data directory) even then it sets them to 775. 600 might not be enough for PostgreSQL data directory anyway.

https://github.com/docker-library/postgres/blob/master/14/bullseye/docker-entrypoint.sh

 

The owner can be unknown, because it is configured for the user ID that Postgres image runs as within the container. Just because host doesn't know the UID, doesn't mean it's broken. But it needs to match the userID, that the postgres process in Docker runs as.
PUID and PGID don't do anything, they are specific to Linuxserver images, not Docker in general.

 

It feels like something else is changing the permissions.

[ZerkerEOD]

3 minutes ago, jj9987 said:

Postgres doesn't change the permissions, unless it is run the first time (when it needs to initialize the data directory) even then it sets them to 775. 600 might not be enough for PostgreSQL data directory anyway.

https://github.com/docker-library/postgres/blob/master/14/bullseye/docker-entrypoint.sh

 

The owner can be unknown, because it is configured for the user ID that Postgres image runs as within the container. Just because host doesn't know the UID, doesn't mean it's broken. But it needs to match the userID, that the postgres process in Docker runs as.
PUID and PGID don't do anything, they are specific to Linuxserver images, not Docker in general.

 

It feels like something else is changing the permissions.

It was the docker container or some process with the container. I removed the container completely and brought it back and it did it. I could change my permissions to 777 owner nobody:users and it would stay that was for hours until I manually started the postgresql container, then it instantly failed and the permissions were changed and the owner was unknown and the docker image refused to fully start up and would crash and burn. Looks like I found some other issue that the postgresql appdata CANNOT run from a user share even if it is set to only use a specific drive. Changed the mount point from /mnt/user/appdata/postgresql to /mnt/vmstorage/appdata/postgresql and it booted right up. Something about how unraid handles the file system and postgresql docker image not playing nice. Not sure why it ran for months without issue and then nothing but issues, but oh well.

[HHUBS]

Hello, I am getting this error below:

 

WARNING:  database "example" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE example REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.3 (Debian 15.3-1.pgdg120+1))
Type "help" for help.

 

Can anyone help me please? I have search google but I can't understand. Below are some info that might help:

 

Unraid version: 16.12.0

PostgreSQL version: 15.3

 

Thanks in advance. 😉

 

[LoneStar]

On 6/17/2023 at 11:26 AM, HHUBS said:

Hello, I am getting this error below:

 

WARNING:  database "example" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE example REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.3 (Debian 15.3-1.pgdg120+1))
Type "help" for help.

 

Can anyone help me please? I have search google but I can't understand. Below are some info that might help:

 

Unraid version: 16.12.0

PostgreSQL version: 15.3

 

Thanks in advance. 😉

 

Login to Postgres15 using adminer / pgadmin4 / psql and execute on each existing database:

ALTER DATABASE <dbname> REFRESH COLLATION VERSION;