root ownership of pg_stat_tmp causes postgres stats to fail #878
Comments
|
Same error as #319 |
|
Great point. It's the same error. That issue is closed so hopefully this is useful. The proposed temporary fix in #319 to remove the file (to presumably reset it), did not work for me. Interestingly, nor has changing the ownership "after the fact" i.e., doing so in the running container. Finally, per the "user notes" in the documentation, I am using a So all in all, I'm not sure what's going on here. |
|
Hmm, if you could get a minimal reproducer that'd help with probing around. It could also be related to something with Docker for Mac docker run postgres$ docker run -d --name postgres -e POSTGRES_PASSWORD=pass postgres
Unable to find image 'postgres:latest' locally
latest: Pulling from library/postgres
a330b6cecb98: Pull complete
3b0b899b4747: Pull complete
cc0b2671a552: Pull complete
1a7c7505993a: Pull complete
02cdead79556: Pull complete
0d8fbe9259d6: Pull complete
974e6d476aa7: Pull complete
e9abf0d5d0bc: Pull complete
38a9de11c706: Pull complete
a3864ed531fa: Pull complete
de957ee6c50c: Pull complete
a8eba1185eab: Pull complete
67aed56271be: Pull complete
Digest: sha256:97e5e91582e89514277912d4b7c95bceabdede3482e32395bcb40099abd9c506
Status: Downloaded newer image for postgres:latest
4b229ee6e57cfa2ecf41d81bae18c6fa82c17ac9f246b4a98bd80557131f9ca8
$ docker logs postgres 2>&1 | grep WARNING
root@4b229ee6e57c:/# ls -al /var/lib/postgresql/data
total 64
drwx------ 19 postgres postgres 4096 Sep 9 18:27 .
drwxr-xr-x 1 postgres postgres 18 Sep 3 12:56 ..
drwx------ 5 postgres postgres 41 Sep 9 18:27 base
drwx------ 2 postgres postgres 4096 Sep 9 18:28 global
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_commit_ts
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_dynshmem
-rw------- 1 postgres postgres 4782 Sep 9 18:27 pg_hba.conf
-rw------- 1 postgres postgres 1636 Sep 9 18:27 pg_ident.conf
drwx------ 4 postgres postgres 68 Sep 9 18:27 pg_logical
drwx------ 4 postgres postgres 36 Sep 9 18:27 pg_multixact
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_notify
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_replslot
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_serial
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_snapshots
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_stat
drwx------ 2 postgres postgres 63 Sep 9 18:28 pg_stat_tmp
drwx------ 2 postgres postgres 18 Sep 9 18:27 pg_subtrans
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_tblspc
drwx------ 2 postgres postgres 6 Sep 9 18:27 pg_twophase
-rw------- 1 postgres postgres 3 Sep 9 18:27 PG_VERSION
drwx------ 3 postgres postgres 60 Sep 9 18:27 pg_wal
drwx------ 2 postgres postgres 18 Sep 9 18:27 pg_xact
-rw------- 1 postgres postgres 88 Sep 9 18:27 postgresql.auto.conf
-rw------- 1 postgres postgres 28156 Sep 9 18:27 postgresql.conf
-rw------- 1 postgres postgres 36 Sep 9 18:27 postmaster.opts
-rw------- 1 postgres postgres 94 Sep 9 18:27 postmaster.pid |
|
If I don't give you exactly what you need, let me know. The docker-compose file (there are 2 other containers that aren't show)version: "3"
services:
db:
build:
context: .
dockerfile: Dockerfile
command:
- "postgres"
# logging-related
- "-c"
- "log_destination=stderr"
- "-c"
- "log_statement=ddl" # all | mod | ddl | none
- "-c"
- "log_min_error_statement=INFO"
restart: always
ports:
- "5432:5432"
env_file:
- .env
volumes:
# map where to host the data with where postgres writes the data
- "./pgdata:/var/lib/postgresql/data"
# map the directory that initializes the db with where my scripts exist
# includes init.sql that calls schema/main.sql
- "./scripts:/docker-entrypoint-initdb.d"
- "./schema:/schema"
The dockerfile# the postgres docker image
FROM postgres:13
ENV PG_MAJOR 13
ENV PGTAP_MAJOR 1
ENV PGTAP_VERSION 1.1.0
ENV PLPERL_VERSION 11
RUN apt-get update && apt-get install -y --no-install-recommends \
postgresql-$PG_MAJOR-pgtap
RUN apt-get install -y postgresql-plperl-$PG_MAJOR
The logs with WARNING |
|
Yeah I'm not able to reproduce from your docker-compose.yml and Dockerfile. Seems likely to be something with your host's environment docker-compose up$ docker-compose up -d
Creating network "root_default" with the default driver
Building db
Step 1/7 : FROM postgres:13
13: Pulling from library/postgres
Digest: sha256:97e5e91582e89514277912d4b7c95bceabdede3482e32395bcb40099abd9c506
Status: Downloaded newer image for postgres:13
---> 346c7820a8fb
Step 2/7 : ENV PG_MAJOR 13
---> Running in b4e7565103a5
Removing intermediate container b4e7565103a5
---> d97754a1a90f
Step 3/7 : ENV PGTAP_MAJOR 1
---> Running in 67c304d54e21
Removing intermediate container 67c304d54e21
---> 447ba4e7b527
Step 4/7 : ENV PGTAP_VERSION 1.1.0
---> Running in 61db95a72c13
Removing intermediate container 61db95a72c13
---> 9d0c192ae11c
Step 5/7 : ENV PLPERL_VERSION 11
---> Running in 6c0ac1b8740b
Removing intermediate container 6c0ac1b8740b
---> 08d9a07d11e3
Step 6/7 : RUN apt-get update && apt-get install -y --no-install-recommends postgresql-$PG_MAJOR-pgtap
---> Running in abc0cddbc8d6
Get:1 http://security.debian.org/debian-security buster/updates InRelease [65.4 kB]
Get:2 http://deb.debian.org/debian buster InRelease [122 kB]
Get:3 http://deb.debian.org/debian buster-updates InRelease [51.9 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease [110 kB]
Get:5 http://security.debian.org/debian-security buster/updates/main amd64 Packages [303 kB]
Get:6 http://deb.debian.org/debian buster/main amd64 Packages [7,907 kB]
Get:7 http://deb.debian.org/debian buster-updates/main amd64 Packages [15.2 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 Packages [235 kB]
Fetched 8,809 kB in 4s (2,094 kB/s)
Reading package lists...
Reading package lists...
Building dependency tree...
Reading state information...
Recommended packages:
pgtap-doc libtap-parser-sourcehandler-pgtap-perl libtap-harness-archive-perl
The following NEW packages will be installed:
postgresql-13-pgtap
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 59.1 kB of archives.
After this operation, 1,676 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-13-pgtap all 1.1.0-5.pgdg100+1 [59.1 kB]
debconf: delaying package configuration, since apt-utils is not installed
Fetched 59.1 kB in 0s (162 kB/s)
Selecting previously unselected package postgresql-13-pgtap.
(Reading database ... 11788 files and directories currently installed.)
Preparing to unpack .../postgresql-13-pgtap_1.1.0-5.pgdg100+1_all.deb ...
Unpacking postgresql-13-pgtap (1.1.0-5.pgdg100+1) ...
Setting up postgresql-13-pgtap (1.1.0-5.pgdg100+1) ...
Processing triggers for postgresql-common (226.pgdg100+1) ...
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Removing intermediate container abc0cddbc8d6
---> b2d8ecfd27f5
Step 7/7 : RUN apt-get install -y postgresql-plperl-$PG_MAJOR
---> Running in 0a073b46622e
Reading package lists...
Building dependency tree...
Reading state information...
The following NEW packages will be installed:
postgresql-plperl-13
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 88.8 kB of archives.
After this operation, 382 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-plperl-13 amd64 13.4-1.pgdg100+1 [88.8 kB]
debconf: delaying package configuration, since apt-utils is not installed
Fetched 88.8 kB in 0s (231 kB/s)
Selecting previously unselected package postgresql-plperl-13.
(Reading database ... 11812 files and directories currently installed.)
Preparing to unpack .../postgresql-plperl-13_13.4-1.pgdg100+1_amd64.deb ...
Unpacking postgresql-plperl-13 (13.4-1.pgdg100+1) ...
Setting up postgresql-plperl-13 (13.4-1.pgdg100+1) ...
Processing triggers for postgresql-common (226.pgdg100+1) ...
debconf: unable to initialize frontend: Dialog
debconf: (TERM is not set, so the dialog frontend is not usable.)
debconf: falling back to frontend: Readline
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Removing intermediate container 0a073b46622e
---> 516095f3d458
Successfully built 516095f3d458
Successfully tagged root_db:latest
WARNING: Image for service db was built because it did not already exist. To rebuild this image you must use `docker-compose build` or `docker-compose up --build`.
Creating root_db_1 ... donedocker logs$ docker logs root_db_1
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.
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 ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /var/lib/postgresql/data -l logfile start
waiting for server to start....2021-09-10 16:51:03.928 UTC [47] LOG: starting PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-09-10 16:51:03.930 UTC [47] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-09-10 16:51:03.940 UTC [48] LOG: database system was shut down at 2021-09-10 16:51:03 UTC
2021-09-10 16:51:03.945 UTC [47] LOG: database system is ready to accept connections
done
server started
/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2021-09-10 16:51:04.163 UTC [47] LOG: received fast shutdown request
waiting for server to shut down....2021-09-10 16:51:04.166 UTC [47] LOG: aborting any active transactions
2021-09-10 16:51:04.173 UTC [47] LOG: background worker "logical replication launcher" (PID 54) exited with exit code 1
2021-09-10 16:51:04.174 UTC [49] LOG: shutting down
2021-09-10 16:51:04.193 UTC [47] LOG: database system is shut down
done
server stopped
PostgreSQL init process complete; ready for start up.
2021-09-10 16:51:04.320 UTC [1] LOG: starting PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-09-10 16:51:04.321 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2021-09-10 16:51:04.322 UTC [1] LOG: listening on IPv6 address "::", port 5432
2021-09-10 16:51:04.331 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-09-10 16:51:04.337 UTC [66] LOG: database system was shut down at 2021-09-10 16:51:04 UTC
2021-09-10 16:51:04.354 UTC [1] LOG: database system is ready to accept connections
$ docker exec -it root_db_1 bash
root@916a6b064a72:/# ls -al /var/lib/postgresql/data/
total 64
drwx------ 19 postgres root 4096 Sep 10 16:51 .
drwxr-xr-x 1 postgres postgres 18 Sep 3 12:56 ..
drwx------ 5 postgres postgres 41 Sep 10 16:51 base
drwx------ 2 postgres postgres 4096 Sep 10 16:51 global
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_commit_ts
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_dynshmem
-rw------- 1 postgres postgres 4782 Sep 10 16:51 pg_hba.conf
-rw------- 1 postgres postgres 1636 Sep 10 16:51 pg_ident.conf
drwx------ 4 postgres postgres 68 Sep 10 16:51 pg_logical
drwx------ 4 postgres postgres 36 Sep 10 16:51 pg_multixact
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_notify
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_replslot
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_serial
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_snapshots
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_stat
drwx------ 2 postgres postgres 42 Sep 10 16:51 pg_stat_tmp
drwx------ 2 postgres postgres 18 Sep 10 16:51 pg_subtrans
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_tblspc
drwx------ 2 postgres postgres 6 Sep 10 16:51 pg_twophase
-rw------- 1 postgres postgres 3 Sep 10 16:51 PG_VERSION
drwx------ 3 postgres postgres 60 Sep 10 16:51 pg_wal
drwx------ 2 postgres postgres 18 Sep 10 16:51 pg_xact
-rw------- 1 postgres postgres 88 Sep 10 16:51 postgresql.auto.conf
-rw------- 1 postgres postgres 28156 Sep 10 16:51 postgresql.conf
-rw------- 1 postgres postgres 127 Sep 10 16:51 postmaster.opts
-rw------- 1 postgres postgres 94 Sep 10 16:51 postmaster.pid |
|
You have identified a different set of permissions associated with the db container (your output has the permission set correctly throughout). I agree that it has something to do with the interaction of the initializing script and the host (mac) system. Clearly the initializing script's coverage of the permissions is complete at first (per your findings). What is different about |
|
To reproduce you just need to wait 30+ minutes till it actually starts to access this file and then you'll constantly get: Compose config: postgres:
image: postgres:13-alpine
environment:
POSTGRES_DB: "backend"
POSTGRES_PASSWORD: "postgres"
POSTGRES_USER: "backend"
volumes:
- ./.pgdata:/var/lib/postgresql/data:delegated
ports:
- 5432:5432 |
|
Sounds like this is probably a quirk of the macOS filesystem sharing (which has traditionally never got along very well with PostgreSQL); I'd suggest using a named volume so the storage stays inside the Linux VM instead, which will probably help. |
|
system preferences -> security & privacy -> Full Disk Access -> add Docker. It helps me |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
postgres v12 and v13
Thank you for making this official image powerful to work with.
The initialization script changes the ownership of many of the files to postgres (per my superuser), however, it does not include
pg_statandpg_stat_tmpdirectories where the owner remainsroot. Is that because these directories are created after the script has executed?Notwithstanding, the missing permissions seems to be causing the following error:
The permissions for the data files:
And the contents of directory mentioned in the error:
root@a8dfd4bbf9f9:/# ls -l /var/lib/postgresql/data/pgdata/pg_stat_tmp total 40 -rw------- 1 root root 3014 Sep 9 16:05 db_0.stat -rw------- 1 root root 21068 Sep 9 16:05 db_13395.stat -rw------- 1 postgres postgres 1151 Sep 9 16:05 global.stat... so able, initially to write to it, but not subsequently access it?
I'm new to working with docker. So, I have not figured out how to run a "patch" at the right point in time i.e., after the files have been created, but before the postgres server fires up.
The text was updated successfully, but these errors were encountered: