On Sat, Nov 4, 2017 at 10:45 AM, Mark Fletcher <markf@xxxxxxxxxxxxxx> wrote:
While trying to track down my logical decoding problem, I noticed that my pg_logical/snapshots directory has ~5000 .snap files and is growing at a rate of about 4 files a minute. The earliest file is from yesterday afternoon, dating to the time I took our site down and added a column to a large table.
This is with Postgres 9.6.5. After the directory got to 8000 files, I decided it wasn't going to stop on its own. Here is my setup, and what I ended up doing:
We run 6 logical streaming processes. First, I stopped and restarted those processes. That did not clean up the pg_logical/snapshots directory. Then I restarted Postgres itself. That also had no effect. Finally, I destroyed all 6 logical slots. That was the thing that caused Postgres to clean up the snapshots directory. I then recreated the slots and everything is back to normal (normal meaning around 20 .snap files in that directory that get rotated regularly).
Going back through my backups, it's clear that this problem began right after I added a column to a table. So maybe that caused something to be held that shouldn't be held?
Thanks,
Mark
