Обсуждение: 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)?
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:
Did you vacuum afterwards?
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.
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:
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:
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