jj9987
-
Posts
26 -
Joined
-
Last visited
Content Type
Profiles
Forums
Downloads
Store
Gallery
Bug Reports
Documentation
Landing
Posts posted by jj9987
-
-
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?
-
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?
-
On 11/26/2023 at 8:57 AM, Aedankerr said:
I HAVE THE SAME ISSUE!!!
PostgreSQL has a process, that you must follow for major version upgrades.
-
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?
-
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.
-
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. -
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 existI 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?
-
1
-
-
2 minutes ago, AmokK said:
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 2Nextcloud 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.
-
1
-
-
10 hours ago, CryPt00n said:
Not quite sure if i restored it the right way
Used for Backup
pg_dumpall -U nextcloud -s > bak.sqlUsed for restore
psql -U nextcloud postgres < bak.sqlSet up the docker with same username/password and port configured.
Receive this in logs then
2022-11-17 22:15:13.463 CET [274] FATAL: password authentication failed for user "oc_admin" 2022-11-17 22:15:13.463 CET [274] DETAIL: User "oc_admin" does not have a valid SCRAM secret. Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"It's a password failure error. Have you tried resetting the password?
-
Just now, CryPt00n said:
Hi, i´m using Postgresql version 11 for nextcloud and want to upgrade to Version 14. already tried pg_dumpall and psql commands to migrate but nextcloud is not working then. Database got imported to the new container. Any ideas? What is the recommended way?
What are the errors you are seeing?
-
1 hour ago, Snack_Ears said:
I had to recreate my my docker image and after reinstalling the postgres14 container, I get this in the logs when attempting to start the container. No containers are able to connect to Postgres and all connections are refused. I'm at a loss on how to fix this or what is causing it. Any help would be greatly appreciated.
text error warn system array login
2022-07-05 23:07:28.736 MDT [1] LOG: starting PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-07-05 23:07:28.737 MDT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-07-05 23:07:28.737 MDT [1] LOG: listening on IPv6 address "::", port 5432
2022-07-05 23:07:28.743 MDT [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-07-05 23:07:28.748 MDT [26] LOG: database system was interrupted; last known up at 2022-07-05 23:06:49 MDT
2022-07-05 23:07:28.761 MDT [26] LOG: database system was not properly shut down; automatic recovery in progress
2022-07-05 23:07:28.764 MDT [26] LOG: redo starts at 1/34FC9FD8
2022-07-05 23:07:28.764 MDT [26] LOG: invalid record length at 1/34FCA028: wanted 24, got 0
2022-07-05 23:07:28.764 MDT [26] LOG: redo done at 1/34FC9FD8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2022-07-05 23:07:28.782 MDT [1] LOG: database system is ready to accept connectionsPostgreSQL Database directory appears to contain a database; Skipping initialization
These logs are normal, nothing alarming. It says it is ready to accept connections, so could it be something else? Wrong IP perhaps?
-
1 hour ago, HHUBS said:
Hello,
I have pgsql install before. I have removed it and start new. I also cleaned up the appdata of it. But I get this error(below pic) for the new installation and I can't start the app.
I go to directory /var/lib/ but there is no postgresql directory on it. Where can I find it? How to fix this?
Howdy
Docker works by mounting host paths to container paths. These are called volumes.
Check where /var/lib/postgresql/data is mounted to. Latest version of the template defaults to /mnt/cache/appdata/postgresql<version>, e.g. /mnt/cache/appdata/postgresql14. That directory either should not exist, be empty or only contain PostgreSQL own files.
-
Just now, spacezmonkey said:
Hello, are you referring to how i connect it?
Port 5432 is not WebUI, it is PostgreSQL TCP port. This container only runs the database engine itself.
If you want a webUI, you need to find and deploy some other tool.
-
3 hours ago, spacezmonkey said:
Hello , I install postgres 11 and unable to login suing ip address.
when i check the log, there is "invalid length of startup packet" . I tried different version, all of them give me same error.. may I know how should i preceed.
What version of psql are you using to connect to it? What error is psql giving? Server logs do not give very much information what the error is.
-
8 hours ago, Eddyall said:
Hello, recently made the error of deleting my Postgres11 docker instead of the pgAdmin 4 docker to which I lost the password for, I got the two dockers working again except I don't have my data it seems. I had two database for Szurubooru, one might be lost (lame but oh well) but the other shouldn't have been affected by me fumbling around with reinstalling the postgres docker. Post above for avlec were the solution basicly. However I'm not too sure about how to link/add the second database so the postgres docker can see it. Any simple way to add it back from pgAdmin? Or even not so simple ways
Edit: Suggestion on how to set up auto backup/snapshot for the docker image so I can more easily fix that kind of screw up in the future also welcome.Howdy. Just deleting the Docker won't wipe your data, unless you specifically also delete the appdata folder (whether it is on the array or cache drive). So if the appdata is still there and you recreate the container (has to be the same major version of PostgreSQL), all the databases and the data should still be there. As for recovery, standard data recovery applies, but it is much harder to do with databases, since it is not just one file. And if part of it is missing, basically all is gone.
You can add additional databases when you log in as superuser `postgres` and run `CREATE DATABASE <name>;`. Security-wise I'd also recommend to have different users with the necessary permissions (e.g. one user can only access one database etc).
For auto-backups, you can run a cron job, that runs `pg_dump` or `pg_dumpall`. Alternatively you could use pgBackRest, but there is no template for it at this moment, it needs additional configuration and is more complicated.
---
Made some changes to the templates:
- BREAKING CHANGE: Database volume paths now include version tag, e.g. `/mnt/cache/appdata/postgresql10` so you can run multiple instances with different versions at the same time.
- BREAKING CHANGE: PostgreSQL 10 template got changed, this could have broken stuff on old users. I am not familiar with the template changes on older versions.
- Added PostgreSQL 12, 13 and 14 templates.
- Container names changed to lowercase and includes version, e.g. `postgresql14`. This should make it easier for using in custom networks.
- Brought the templates up to date with current schema version.
Hopefully it is more user-friendly now. Feedback welcome. Sorry, it's been too long since I updated them.
-
1
-
-
Just now, AmokK said:
Thank you for your answer, I understand now why it can't work automatically, as it is a major postgres update.
I will stick to the 13.4 version for the moment, as the database upgrade procedure seems a little bit complicated, but I will study it thoroughly and try it later
The upgrade is sorta complicated, you need both old and new binaries available, which the official docker images do not provide. Backup and restore might be more convenient and easier.
-
1
-
-
On 8/20/2021 at 3:46 AM, Team_Dango said:
I have an instance of Postgres11 set up and working with one application. Now I am looking to install a second application (Joplin) that also needs its own PostgreSQL database. What is the best way to do this? Having two apps share one database feels like a bad idea. Can I create a second database within one instance of Postgres11, or do I need to install a second instance?
Thanks!
It's absolutely okay to use one database instance with multiple databases for multiple apps. You will just have to create the new databases (and preferably also new roles) through the psql shell.
6 minutes ago, AmokK said:Hi !
I'm using a Postgres container to get my Nextcloud Database running, I followed some tutorials here and there when I set it all up, all was working great.
This morning, Postgres was not running, checked the logs :
My container was using the postgres:latest tag, so I guess it auto-updated to the latest 14 version.
Switched back to tag postgres:13.4 , all is working.
Question is, what should I do ? Leave it as is and stay on the 13.4 version for a while (or forever ?), or seek a solution to update database to 14.0 so it could be compatible ?
i'm a bit of a noob with this, maybe I didn't understand the problem correctly, but any reference site or procedure to follow could help me, and any advice is welcome
Thanks for your help !!
Postgres upgrades can't be done by simply changing the version tags, they often have changes that require additional steps (e.g. pg_upgrade). So definitely don't use the latest tag. I changed the templates long time ago, haven't added newer templates to the repo though.
Here's the information on how to do it: https://www.postgresql.org/docs/14/upgrading.html
-
1
-
-
I started to use unRAID because it was a complete package. It includes well-tested software that was designed to be working together and do it reliably. Plus it came with community support.
I would love to see VM snapshots being added in 2020. Would make testing and creating new base VMs (with some initial configuration) so much more convenient.
-
Happy birthday! Unraid has really improved over the years and awesome features just keep coming and coming. Keep up the good work!
-
Postgres 11 has some breaking changes apparently. Made a not-so-smart move to configure this to latest tag.
I'll fix this to PostgreSQL 10 and create another template for Postgres 10.
-
On 26/02/2018 at 2:21 AM, ucliker said:
I really like this setup but I have a question. Under volumes, it says I have 170 unused volumes? Is there a way to tell what these volumes contain?
It shows all the Docker volumes, that you can usually see with "docker volume list". By default these get mounted to /var/lib/docker/volumes/[volume id]/_data and are used by the container internally. More on Docker volumes.
-
Application: Docker Registry @ https://github.com/docker/distribution-library-image
DockerHub: https://registry.hub.docker.com/_/registry/
GitHub: https://github.com/juusujanar/unraid-templates/blob/master/DockerRegistry.xml
This template uses the official Docker Registry image to quickly set up your own private repository for storing Docker images.
If you have any issues/questions/suggestions regarding this Docker or the template, feel free to contact here.
-
Application: Portainer @ https://portainer.io/
DockerHub:https://hub.docker.com/r/portainer/portainer/
GitHub: https://github.com/juusujanar/unraid-templates/blob/master/Portainer.xmlThis template uses the official Portainer Docker image to quickly set up a Portainer instance. It is used for graphical Docker management and gives more information than unRAID itself.
If you have any issues/questions/suggestions regarding this Docker or the template, feel free to contact here.
-
Application: Redis @ https://redis.io/
DockerHub: https://hub.docker.com/_/redis/
GitHub: https://github.com/juusujanar/unraid-templates/blob/master/Redis.xml
This template uses the official Redis Docker image to quickly set up a Redis cache instance according to your configuration.
If you have any issues/questions/suggestions regarding this Docker or the template, feel free to contact here.
[Support] jj9987 - PostgreSQL
in Docker Containers
Posted
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