Re: pg_logical/snapshots directory

On Sat, Nov 4, 2017 at 10:45 AM, Mark Fletcher <markf(at)corp(dot)groups(dot)io> 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