[Support] jj9987 - PostgreSQL


Recommended Posts

My latest install of the Postrgres 15 docker won't start. I have the following logs each time I try to start it even though it "installed successfuly"

 

Quote

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
2023-09-24 13:52:07.410 EDT [38] LOG: could not link file "pg_wal/xlogtemp.38" to "pg_wal/000000010000000000000001": Function not implemented
2023-09-24 13:52:07.413 EDT [38] FATAL: could not open file "pg_wal/000000010000000000000001": No such file or directory
child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/postgresql/data"
running bootstrap script ...

 

I had it working but nuked the previous image since I am just learning Postgres and had databases that Datagrip couldn't drop for some reason. Any help would be much appreciated!

Edited by audiocycle
additionnal info
Link to comment
On 6/14/2023 at 10:42 AM, ZerkerEOD said:

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.

 

Wow thanks, not sure I would have figured this out. I had it installed and working initially and somewhere in the process I switched to location to /mnt/user/share to properly use the FUSE system and it stopped working but I never thought that could be it.

 

Link to comment
  • 4 weeks later...

I've tried to follow the install instructions for Invidious but I am unable to access the WebUI. I am getting the following error in the logs (seems to repeat for all tables):

Unhandled exception in spawn: permission denied for table users (PQ::PQError)
  from lib/pg/src/pq/connection.cr:202:22 in 'handle_error'
  from lib/pg/src/pq/connection.cr:220:65 in 'handle_async_frames'
  from lib/pg/src/pq/connection.cr:162:7 in 'read'
  from lib/pg/src/pq/connection.cr:414:31 in 'perform_query'
  from lib/db/src/db/statement.cr:80:7 in 'query:args'
  from lib/db/src/db/pool_statement.cr:29:30 in 'query:args'
  from lib/db/src/db/query_methods.cr:61:12 in 'begin'
  from src/invidious/jobs.cr:37:15 in '->'
  from /usr/share/crystal/src/fiber.cr:146:11 in 'run'
  from ???

 

Please let me know what info/files I can attach for more info. 

Link to comment
11 hours ago, VelcroBP said:

I've tried to follow the install instructions for Invidious but I am unable to access the WebUI. I am getting the following error in the logs (seems to repeat for all tables):

Unhandled exception in spawn: permission denied for table users (PQ::PQError)
  from lib/pg/src/pq/connection.cr:202:22 in 'handle_error'
  from lib/pg/src/pq/connection.cr:220:65 in 'handle_async_frames'
  from lib/pg/src/pq/connection.cr:162:7 in 'read'
  from lib/pg/src/pq/connection.cr:414:31 in 'perform_query'
  from lib/db/src/db/statement.cr:80:7 in 'query:args'
  from lib/db/src/db/pool_statement.cr:29:30 in 'query:args'
  from lib/db/src/db/query_methods.cr:61:12 in 'begin'
  from src/invidious/jobs.cr:37:15 in '->'
  from /usr/share/crystal/src/fiber.cr:146:11 in 'run'
  from ???

 

Please let me know what info/files I can attach for more info. 

Seems like the user, that is connecting to the database, does not have the correct permissions.

What have you done so far? How did you configure the database and the users?

Link to comment
  • 3 weeks later...
  • 3 weeks later...

My docker image got updated and now when I try to start it up I get this:

 

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/postgresql/data" or run initdb
with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.


** Press ANY KEY to close this window ** 

 

Link to comment
18 hours ago, Nex said:

My docker image got updated and now when I try to start it up I get this:

 

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/postgresql/data" or run initdb
with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.


** Press ANY KEY to close this window ** 

 

What update did you do? What tag are you using?

Link to comment
  • 2 weeks later...

02:28:22 PM

PostgreSQL Database directory appears to contain a database; Skipping initialization

12/18/2023

02:28:22 PM

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.408 GMT [1] LOG: starting PostgreSQL 15.5 (Debian 15.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.408 GMT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.408 GMT [1] LOG: listening on IPv6 address "::", port 5432

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.422 GMT [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.438 GMT [30] LOG: database system was shut down at 2023-12-18 14:28:19 GMT

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.459 GMT [1] LOG: database system is ready to accept connections

12/18/2023

02:33:22 PM

2023-12-18 14:33:22.535 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:33:27 PM

2023-12-18 14:33:27.023 GMT [28] LOG: checkpoint complete: wrote 48 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.416 s, sync=0.053 s, total=4.488 s; sync files=13, longest=0.005 s, average=0.005 s; distance=209 kB, estimate=209 kB

12/18/2023

02:38:22 PM

2023-12-18 14:38:22.053 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:38:26 PM

2023-12-18 14:38:26.834 GMT [28] LOG: checkpoint complete: wrote 48 buffers (0.3%); 0 WAL file(s) added, 0 removed, 1 recycled; write=4.713 s, sync=0.049 s, total=4.782 s; sync files=15, longest=0.004 s, average=0.004 s; distance=245 kB, estimate=245 kB

12/18/2023

02:43:22 PM

2023-12-18 14:43:22.935 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:43:27 PM

2023-12-18 14:43:27.413 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.414 s, sync=0.039 s, total=4.479 s; sync files=12, longest=0.004 s, average=0.004 s; distance=218 kB, estimate=243 kB

12/18/2023

02:48:22 PM

2023-12-18 14:48:22.489 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:48:26 PM

2023-12-18 14:48:26.971 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.414 s, sync=0.050 s, total=4.483 s; sync files=12, longest=0.016 s, average=0.005 s; distance=229 kB, estimate=241 kB

12/18/2023

02:53:23 PM

2023-12-18 14:53:23.067 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:53:27 PM

2023-12-18 14:53:27.847 GMT [28] LOG: checkpoint complete: wrote 48 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.714 s, sync=0.049 s, total=4.781 s; sync files=15, longest=0.004 s, average=0.004 s; distance=227 kB, estimate=240 kB

12/18/2023

02:58:23 PM

2023-12-18 14:58:23.900 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:58:28 PM

2023-12-18 14:58:28.369 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.413 s, sync=0.039 s, total=4.470 s; sync files=12, longest=0.004 s, average=0.004 s; distance=232 kB, estimate=239 kB

12/18/2023

03:03:23 PM

2023-12-18 15:03:23.430 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:03:27 PM

2023-12-18 15:03:27.896 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.412 s, sync=0.037 s, total=4.467 s; sync files=12, longest=0.005 s, average=0.004 s; distance=224 kB, estimate=238 kB

12/18/2023

03:08:23 PM

2023-12-18 15:08:23.996 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:08:28 PM

2023-12-18 15:08:28.966 GMT [28] LOG: checkpoint complete: wrote 49 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.715 s, sync=0.188 s, total=4.970 s; sync files=15, longest=0.019 s, average=0.013 s; distance=241 kB, estimate=241 kB

12/18/2023

03:13:24 PM

2023-12-18 15:13:24.012 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:13:28 PM

2023-12-18 15:13:28.480 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.412 s, sync=0.038 s, total=4.468 s; sync files=12, longest=0.004 s, average=0.004 s; distance=210 kB, estimate=238 kB

12/18/2023

03:18:24 PM

2023-12-18 15:18:24.565 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:18:29 PM

2023-12-18 15:18:29.032 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.412 s, sync=0.038 s, total=4.468 s; sync files=12, longest=0.004 s, average=0.004 s; distance=234 kB, estimate=238 kB

12/18/2023

03:23:24 PM

2023-12-18 15:23:24.132 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:23:29 PM

2023-12-18 15:23:29.090 GMT [28] LOG: checkpoint complete: wrote 50 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.914 s, sync=0.028 s, total=4.959 s; sync files=15, longest=0.004 s, average=0.002 s; distance=239 kB, estimate=239 kB

12/18/2023

03:28:24 PM

2023-12-18 15:28:24.190 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:28:28 PM

2023-12-18 15:28:28.658 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.414 s, sync=0.038 s, total=4.469 s; sync files=12, longest=0.004 s, average=0.004 s; distance=230 kB, estimate=238 kB

12/18/2023

03:33:24 PM

2023-12-18 15:33:24.758 GMT [28] LOG: checkpoint starting: time

 

 

 

 

Had next cloud runing for a couple of weeks fine with postgres. Then I wanted to add my first user.  Try to log in and next cloud wont respond and then this error message in postgres,   Only thing I can think of if there might of been an update from 15.4 to 15.5.  Not sure, as last time I had to delete the lot an start over.  and I gave up on nextcloud.  Help please!

 

Link to comment
12 hours ago, Nummy said:

02:28:22 PM

PostgreSQL Database directory appears to contain a database; Skipping initialization

12/18/2023

02:28:22 PM

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.408 GMT [1] LOG: starting PostgreSQL 15.5 (Debian 15.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.408 GMT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.408 GMT [1] LOG: listening on IPv6 address "::", port 5432

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.422 GMT [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.438 GMT [30] LOG: database system was shut down at 2023-12-18 14:28:19 GMT

12/18/2023

02:28:22 PM

2023-12-18 14:28:22.459 GMT [1] LOG: database system is ready to accept connections

12/18/2023

02:33:22 PM

2023-12-18 14:33:22.535 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:33:27 PM

2023-12-18 14:33:27.023 GMT [28] LOG: checkpoint complete: wrote 48 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.416 s, sync=0.053 s, total=4.488 s; sync files=13, longest=0.005 s, average=0.005 s; distance=209 kB, estimate=209 kB

12/18/2023

02:38:22 PM

2023-12-18 14:38:22.053 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:38:26 PM

2023-12-18 14:38:26.834 GMT [28] LOG: checkpoint complete: wrote 48 buffers (0.3%); 0 WAL file(s) added, 0 removed, 1 recycled; write=4.713 s, sync=0.049 s, total=4.782 s; sync files=15, longest=0.004 s, average=0.004 s; distance=245 kB, estimate=245 kB

12/18/2023

02:43:22 PM

2023-12-18 14:43:22.935 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:43:27 PM

2023-12-18 14:43:27.413 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.414 s, sync=0.039 s, total=4.479 s; sync files=12, longest=0.004 s, average=0.004 s; distance=218 kB, estimate=243 kB

12/18/2023

02:48:22 PM

2023-12-18 14:48:22.489 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:48:26 PM

2023-12-18 14:48:26.971 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.414 s, sync=0.050 s, total=4.483 s; sync files=12, longest=0.016 s, average=0.005 s; distance=229 kB, estimate=241 kB

12/18/2023

02:53:23 PM

2023-12-18 14:53:23.067 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:53:27 PM

2023-12-18 14:53:27.847 GMT [28] LOG: checkpoint complete: wrote 48 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.714 s, sync=0.049 s, total=4.781 s; sync files=15, longest=0.004 s, average=0.004 s; distance=227 kB, estimate=240 kB

12/18/2023

02:58:23 PM

2023-12-18 14:58:23.900 GMT [28] LOG: checkpoint starting: time

12/18/2023

02:58:28 PM

2023-12-18 14:58:28.369 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.413 s, sync=0.039 s, total=4.470 s; sync files=12, longest=0.004 s, average=0.004 s; distance=232 kB, estimate=239 kB

12/18/2023

03:03:23 PM

2023-12-18 15:03:23.430 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:03:27 PM

2023-12-18 15:03:27.896 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.412 s, sync=0.037 s, total=4.467 s; sync files=12, longest=0.005 s, average=0.004 s; distance=224 kB, estimate=238 kB

12/18/2023

03:08:23 PM

2023-12-18 15:08:23.996 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:08:28 PM

2023-12-18 15:08:28.966 GMT [28] LOG: checkpoint complete: wrote 49 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.715 s, sync=0.188 s, total=4.970 s; sync files=15, longest=0.019 s, average=0.013 s; distance=241 kB, estimate=241 kB

12/18/2023

03:13:24 PM

2023-12-18 15:13:24.012 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:13:28 PM

2023-12-18 15:13:28.480 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.412 s, sync=0.038 s, total=4.468 s; sync files=12, longest=0.004 s, average=0.004 s; distance=210 kB, estimate=238 kB

12/18/2023

03:18:24 PM

2023-12-18 15:18:24.565 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:18:29 PM

2023-12-18 15:18:29.032 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.412 s, sync=0.038 s, total=4.468 s; sync files=12, longest=0.004 s, average=0.004 s; distance=234 kB, estimate=238 kB

12/18/2023

03:23:24 PM

2023-12-18 15:23:24.132 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:23:29 PM

2023-12-18 15:23:29.090 GMT [28] LOG: checkpoint complete: wrote 50 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.914 s, sync=0.028 s, total=4.959 s; sync files=15, longest=0.004 s, average=0.002 s; distance=239 kB, estimate=239 kB

12/18/2023

03:28:24 PM

2023-12-18 15:28:24.190 GMT [28] LOG: checkpoint starting: time

12/18/2023

03:28:28 PM

2023-12-18 15:28:28.658 GMT [28] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.414 s, sync=0.038 s, total=4.469 s; sync files=12, longest=0.004 s, average=0.004 s; distance=230 kB, estimate=238 kB

12/18/2023

03:33:24 PM

2023-12-18 15:33:24.758 GMT [28] LOG: checkpoint starting: time

 

 

 

 

Had next cloud runing for a couple of weeks fine with postgres. Then I wanted to add my first user.  Try to log in and next cloud wont respond and then this error message in postgres,   Only thing I can think of if there might of been an update from 15.4 to 15.5.  Not sure, as last time I had to delete the lot an start over.  and I gave up on nextcloud.  Help please!

 

 

That's normal PostgreSQL log at default level (LOG: indicates informative message). What's the exact error you are seeing?

Link to comment
On 12/17/2023 at 1:58 PM, Xerazal said:

I think the last update overwrote file perms, but not sure. this is how it looks for me in my appdata folder. 

 

 

Screenshot 2023-12-17 065627.png

 

User `postgres` is configured with ID 999 in the container. The docker entrypoint script configures the postgres data directories to be owned by that user, if container is started as root (but postgres is run as postgres user).
https://github.com/docker-library/postgres/blob/master/15/bookworm/docker-entrypoint.sh#L59

Link to comment
  • 4 weeks later...
On 12/4/2023 at 2:22 AM, Nex said:

My docker image got updated and now when I try to start it up I get this:

 

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/postgresql/data" or run initdb
with an argument other than "/var/lib/postgresql/data".
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.


** Press ANY KEY to close this window ** 

 


It could be that the data is partly stored on cache disk and yet to be move to the data disk, what I've tried is to do a quick sync up between those two directories.

```

rsync -a /mnt/disk1/appdata/postgresql15/ /mnt/cache/appdata/postgresql15/

```

Then restart the container and it's started as expected.

 

Note the disk number in the above command, yours could be different, and make sure to make a copy of those directory for backup first.

Link to comment
  • 4 weeks later...

i am on postgres 15.  i am having the same ownership problem.  the container works fine with my other containers useing it as a database.

 

however i can not view it appdata in krusader and it will not back up with appdata backup since it will not let it open its appdata folder.

 

i have the appdata to mnt/cache in the template not user share.

 

can someone help me?  thank you!

Link to comment
  • 3 weeks later...
On 2/9/2024 at 5:23 PM, knights_of_pine said:

i am on postgres 15.  i am having the same ownership problem.  the container works fine with my other containers useing it as a database.

 

however i can not view it appdata in krusader and it will not back up with appdata backup since it will not let it open its appdata folder.

 

i have the appdata to mnt/cache in the template not user share.

 

can someone help me?  thank you!

I think this is because it's now outside the share/fuse appdata location.

I'd try changing the appdata backup Appdata source(s) to use mnt/cache rather than mnt/user/appdata or whatever share location you're using for appdata.

Link to comment
  • 4 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.