Force WAL cleanup on running instance
Hi, I am building a docker image with a postgresql 12.2 instance and while doing so and importing a dump and running some update scripts wal size is increasing. When finished I don't need all those wal files anymore and tried to force the daemon to clean them up and tried this: select pg_switch_wal(); CHECKPOINT; and did wait for a minute. Sometimes it works and wal files are cleaned and moved away so my image size is way smaller - but it does not happen always in that minute. So is there a way to tell postgres to force the housekeeping of the wal stuff via a statement / command line tool? In a "normal" running instance it just takes care of itself and it will happen sooner or later and it doesn't really matter when that will happen - but with my docker image which is automatically build it would be nice to have a deterministic way of trigger that to reduce the final size image. Is there some way to trigger this? kind regards Torsten --
Re: Force WAL cleanup on running instance
Am Mittwoch, den 11.03.2020, 08:42 -0600 schrieb Michael Lewis: > I don't know the answer to your stated question. I am curious if you > have > set wal_level = minimal and if not, if that would be appropriate for > your > use case and might render your concern a non-issue. Hi Micheal, I am already running with "wal_level = minimal" set. kind regards Torsten
Re: Force WAL cleanup on running instance
Am Mittwoch, den 11.03.2020, 15:45 + schrieb Simon Riggs: > The size of the task varies, so sometimes takes longer than 60s, > depending > upon your hardware. Yes that's what I am observing and why I am asking if there is some select statement or command which triggers that task and returns after it has finished - as I can't predict how long that task may run. kind regards Torsten --
Re: Force WAL cleanup on running instance
Am Mittwoch, den 11.03.2020, 11:46 -0500 schrieb Jerry Sievers: > If your site can afford a restart after the bulk load, > > 1. Clean shutdown. > 2. pg_resetwal > 3. Start > > That should leave you with a very small N WAL files, perhaps just 1, > though I've not run it lately to reverify. Thanks - that worked like a charm and is exactly what I was looking for, of cause I can afford that, it's shutdown after the init anyway - so after running pg_resetwal this was left: root@53e0d45ce5d7:/var/lib/postgresql/data/pg_wal# ls -lh total 17M -rw--- 1 postgres postgres 16M Mär 11 17:23 00010012 drwx-- 2 postgres postgres 4,0K Mär 11 17:13 archive_status Nice one :) thanks. kind regards Torsten
Re: Re: Different execution plan between PostgreSQL 8.2 and 12.5
But you should do that according to the documentation: ... After restoring a backup, it is wise to run ANALYZE on each database so the query optimizer has useful statistics. ...
running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
Hi, I am running that one (official docker image) PostgreSQL 13.11 (Debian 13.11-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit and one of my nightly jobs reported that error yesterday when running an "ANALYZE": FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »pg_statistic_relid_att_inh_index« Detail: Schlüssel »(starelid, staattnum, stainherit)=(2609, 4, f)« existiert bereits. which should translate to something like: ERROR: duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index" > DETAIL: Key (starelid, staattnum, stainherit)=(2609, 4, f) already exists. Anyone an idea what's wrong? Maybe (not?) related but sometimes the analyze does fail with: ERROR: attempted to delete invisible tuple Both errors are only happening here and there - so I don't have a reproducer, but still I am curious what is wrong here with me running an "ANALYZE" after my data import. thanks for insights :) kind regards Torsten
Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
Am Mittwoch, dem 06.09.2023 um 10:21 +0200 schrieb Laurenz Albe: > You are lucky that the corrupted table is one that holds data that > can be rebuilt. It is a test instance / container anyway which is deleted afterwards and can be setup again as often as I want. But how is that corruption happening - I mean it is a docker image, freshly fetched from the registry. After that I am starting a container from that image, (re)importing data (different tests => different data so the cycle of delete data / import data / analyze the data happens quite often) and running my tests. The OS does not report anything which would relate nor does any other tool / system fail nor does postgresl itself fail on any other table here - it always fails only on that analyze part. That happens all in about 8-10 minutes for the whole process - what is causing that corruption in that short timeframe here? regards Torsten
Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
Am Mittwoch, dem 06.09.2023 um 12:04 +0200 schrieb Erik Wienhold: > > I am running that one (official docker image) > > > > PostgreSQL 13.11 (Debian 13.11-1.pgdg110+1) on x86_64-pc-linux-gnu, > > compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit > > Have you also tried with 13.12? Yes, but it did also happen on previous versions before 13.11 / 13.12 sometimes (I just ignored it until now because it happens so rarely). > > > Does the unique constraint violation always occur for the same row? > OID 2609 > is pg_description. As I don't have a reproducer yet (I did not track stats but lets say it runs fine for 100 / 200 times and 1 or 2 of those are failing with those mentioned analyze errors - it may even be less than that) I can't tell you if it fails always over that OID - I need to wait for it to happen again, I will report here if it is the same - may take some time ;). Torsten
Re: running ANALYZE results in => duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index"
Am Mittwoch, dem 06.09.2023 um 20:42 +0200 schrieb Imre Samu: > Maybe you have to increase the "*--stop-timeout" value That is totally unrelated in my case, it is an anonymous volume anyway which gets created on start and deleted afterwards. Torsten
could not open file "base/XX/XX": Interrupted system call
Hi, I am running the postgres docker image with that version: 2023-09-20 10:36:32.478 CEST [1] LOG: starting PostgreSQL 13.12 (Debian 13.12-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit 2023-09-20 10:36:32.564 CEST [1] LOG: listening on IPv4 address "0.0.0.0", port 5432 2023-09-20 10:36:32.564 CEST [1] LOG: listening on IPv6 address "::", port 5432 And this is happening for some queries: 2023-09-20 10:38:25.076 CEST [47] ERROR: could not open file "base/16386/17328": Interrupted system call 2023-09-20 10:38:41.897 CEST [49] ERROR: could not open file "base/16386/68359": Interrupted system call I can enter the container and view the files above with e.g. strings $file | less and I can create a md5sum from that file without an error. What can I do about that EINTR on open, how to fix it? The host (6.1.53-060153-generic #202309130436 SMP PREEMPT_DYNAMIC) and the docker daemon (Version: 24.0.6) do have nothing suspicious in there dmesg / logs. Anyone an idea how to debug / progress with that one? Torsten
Re: could not open file "base/XX/XX": Interrupted system call
Am Mittwoch, dem 20.09.2023 um 13:11 +0200 schrieb Laurenz Albe: > Is that an NFS mount? What are the mount options? > > Yours, > Laurenz Albe No mount at all. Just the docker container filesystem using the overlay2 storage driver, there is nothing externally mounted in that container, the data is already in the image (via docker COPY) from which the container gets created (and that data directory was created in the image build process via pg_restore from the dump file). The whole container / image just involves overlay2 fs which itself is sitting on ext4. Torsten
Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20
Am Sonntag, dem 01.12.2024 um 21:59 +0100 schrieb Alexander Farber: > But I am yet not sure how to enable listening at localhost again Instead of using a TCP/IP connection, why not use the unix socket to connect to your database [1]? [1] https://jdbc.postgresql.org/documentation/use/#unix-sockets
Re: could not open file "base/XX/XX": Interrupted system call
Am Donnerstag, dem 26.12.2024 um 13:48 +0200 schrieb Slava Shpitalny: > Do you happen to have any updates on this issue? Hi, unfortunately not, the only thing I found is this in "man 2 open": ... EINTR While blocked waiting to complete an open of a slow device (e.g., a FIFO; see fifo(7)), the call was interrupted by a signal handler; see signal(7). ... To me this reads that postgres should handle that error code (e.g. try again n times before failing) - but maybe it does that already, I did not verify all open calls, if or how postgres does handle EINTR. The only thing I did change is, that I use a volume (an anonymous one in my usecase) again, because with that, I never encountered that error - if I omit that volume and write directly to the containers fs (which would be sufficient for me), I still had that error. kind regards Torsten
