Safe to delete files?
Hi,
For my process, I needed to drop all the tables in a tablespace except one
which I truncated.
After that I would have expected to have a couple of KB max in that folder,
but there was about 200GB in it.
There were 2 sets of files (<id1>, <id1>.1 .. <id1>.99, and the same for
id2).
Tried the various options from
https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name
(with -i), to trace it back to a table but all came up empty.
Now this folder has a bit of a history spanning several postgres versions
and upgrades, and sometime in the past one of the upgrades went horribly
wrong, so my first thought was that this was possibly some leftovers from
that mishap, but the filetimes were a bit later than that.
Also hard to tell because those tables are used as write-once, read-alot so
could not base the last usage on filedate.
Normally I probably would dare to risk deleting those files, but after the
dropping and truncating, the 2 files without extension had the time of
drop/truncate and were 0 bytes in length (unfortunately I didn't check the
filesize before drop/truncating).
Are there other options to see if these files are leftovers from previous
stuff and not used by postgres (so i can safely delete them)?
Postgres 11, just one used database on it (the other one being a postgis
template), running on windows server 2012.
In replies please use reply to all...
On 4/12/19 1:11 PM, Paul van der Linden wrote:
Hi,
For my process, I needed to drop all the tables in a tablespace except
one which I truncated.
After that I would have expected to have a couple of KB max in that
folder, but there was about 200GB in it.There were 2 sets of files (<id1>, <id1>.1 .. <id1>.99, and the same for
id2).
Can you show the actual dir listing?
Tried the various options from
https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name
(with -i), to trace it back to a table but all came up empty.Now this folder has a bit of a history spanning several postgres
versions and upgrades, and sometime in the past one of the upgrades went
horribly wrong, so my first thought was that this was possibly some
leftovers from that mishap, but the filetimes were a bit later than that.
Also hard to tell because those tables are used as write-once, read-alot
so could not base the last usage on filedate.Normally I probably would dare to risk deleting those files, but after
the dropping and truncating, the 2 files without extension had the time
of drop/truncate and were 0 bytes in length (unfortunately I didn't
check the filesize before drop/truncating).Are there other options to see if these files are leftovers from
previous stuff and not used by postgres (so i can safely delete them)?Postgres 11, just one used database on it (the other one being a postgis
template), running on windows server 2012.In replies please use reply to all...
--
Adrian Klaver
adrian.klaver@aklaver.com
On 4/12/19 3:11 PM, Paul van der Linden wrote:
Hi,
For my process, I needed to drop all the tables in a tablespace except one
which I truncated.
After that I would have expected to have a couple of KB max in that
folder, but there was about 200GB in it.
Did you vacuum afterwards?
There were 2 sets of files (<id1>, <id1>.1 .. <id1>.99, and the same for id2).
Tried the various options from
https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name
(with -i), to trace it back to a table but all came up empty.Now this folder has a bit of a history spanning several postgres versions
and upgrades, and sometime in the past one of the upgrades went horribly
wrong, so my first thought was that this was possibly some leftovers from
that mishap, but the filetimes were a bit later than that.
Also hard to tell because those tables are used as write-once, read-alot
so could not base the last usage on filedate.Normally I probably would dare to risk deleting those files, but after the
dropping and truncating, the 2 files without extension had the time of
drop/truncate and were 0 bytes in length (unfortunately I didn't check the
filesize before drop/truncating).Are there other options to see if these files are leftovers from previous
stuff and not used by postgres (so i can safely delete them)?Postgres 11, just one used database on it (the other one being a postgis
template), running on windows server 2012.In replies please use reply to all...
--
Angular momentum makes the world go 'round.
It was just 99 files of 1GB each for each id, and no I didn't vacuum.
I did see disk usage dropping quite a lot after dropping those tables
though, so I expected postgres to delete all unneccesary files for all the
tables.
However when checking just now I saw that the files I was referring to were
indeed deleted somehow (don't have an autovacuum running).
So not sure how or why, but my problem is solved
On Sat, Apr 13, 2019 at 2:06 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Show quoted text
On 4/12/19 1:11 PM, Paul van der Linden wrote:
Hi,
For my process, I needed to drop all the tables in a tablespace except
one which I truncated.
After that I would have expected to have a couple of KB max in that
folder, but there was about 200GB in it.There were 2 sets of files (<id1>, <id1>.1 .. <id1>.99, and the same for
id2).Can you show the actual dir listing?
Tried the various options from
https://blog.2ndquadrant.com/postgresql-filename-to-table/ and oid2name
(with -i), to trace it back to a table but all came up empty.Now this folder has a bit of a history spanning several postgres
versions and upgrades, and sometime in the past one of the upgrades went
horribly wrong, so my first thought was that this was possibly some
leftovers from that mishap, but the filetimes were a bit later than that.
Also hard to tell because those tables are used as write-once, read-alot
so could not base the last usage on filedate.Normally I probably would dare to risk deleting those files, but after
the dropping and truncating, the 2 files without extension had the time
of drop/truncate and were 0 bytes in length (unfortunately I didn't
check the filesize before drop/truncating).Are there other options to see if these files are leftovers from
previous stuff and not used by postgres (so i can safely delete them)?Postgres 11, just one used database on it (the other one being a postgis
template), running on windows server 2012.In replies please use reply to all...
--
Adrian Klaver
adrian.klaver@aklaver.com
On 4/13/19 7:10 AM, Paul van der Linden wrote:
It was just 99 files of 1GB each for each id, and no I didn't vacuum.
I did see disk usage dropping quite a lot after dropping those tables
though, so I expected postgres to delete all unneccesary files for all
the tables.However when checking just now I saw that the files I was referring to
were indeed deleted somehow (don't have an autovacuum running).
So not sure how or why, but my problem is solved
Did you DROP the tables in a transaction and look at the the directory
previously before you issued COMMIT?
--
Adrian Klaver
adrian.klaver@aklaver.com
I did it in pgadmin, without begin commit.
On Sat, Apr 13, 2019 at 4:24 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Show quoted text
On 4/13/19 7:10 AM, Paul van der Linden wrote:
It was just 99 files of 1GB each for each id, and no I didn't vacuum.
I did see disk usage dropping quite a lot after dropping those tables
though, so I expected postgres to delete all unneccesary files for all
the tables.However when checking just now I saw that the files I was referring to
were indeed deleted somehow (don't have an autovacuum running).
So not sure how or why, but my problem is solvedDid you DROP the tables in a transaction and look at the the directory
previously before you issued COMMIT?--
Adrian Klaver
adrian.klaver@aklaver.com