Disk is filling up with large files. How can I clean?

Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or
similar). The number 16665 is called the file node.

To get a list of file nodes for a specific database you can run:

SELECT oid::regclass::text, relfilenode FROM pg_class;

The /16384/ in the path represents the database. To decipher that you can
run:

SELECT datname, oid FROM pg_database;

Once you have all that information, you know which database to connect to
and which tables are big. Then you can DROP/DELETE/TRUNCATE or so.

Useful functions in that context are pg_relation_size(), pg_table_size(),
pg_total_relation_size() and some more. Check out the manual for more
information.

Example:
SELECT oid::regclass::text, relfilenode, pg_relation_size(oid) as size FROM
pg_class ORDER BY size DESC;

On Wed, Oct 9, 2024 at 10:10 AM Mikael Petterson <
mikaelpetterson@hotmail.com> wrote:

On Oct 9, 2024, at 5:52 AM, Torsten Förtsch <tfoertsch123@gmail.com> wrote:

Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or similar). The number 16665 is called the file node.

To get a list of file nodes for a specific database you can run:

SELECT oid::regclass::text, relfilenode FROM pg_class;

The /16384/ in the path represents the database. To decipher that you can run:

SELECT datname, oid FROM pg_database;

Once you have all that information, you know which database to connect to and which tables are big. Then you can DROP/DELETE/TRUNCATE or so.

Mikael, if you’re unaware of VACUUM FULL (as opposed to just VACUUM), you should read about that too.

Hope that helps,
Philip

On Wed, Oct 9, 2024 at 9:02 AM Philip Semanchuk <
philip@americanefficient.com> wrote:

On Oct 9, 2024, at 5:52 AM, Torsten Förtsch <tfoertsch123@gmail.com>

wrote:

Filenames like 16665, 16665.1, 16665.2 etc all represent the same table

(or similar). The number 16665 is called the file node.

To get a list of file nodes for a specific database you can run:

SELECT oid::regclass::text, relfilenode FROM pg_class;

The /16384/ in the path represents the database. To decipher that you

can run:

SELECT datname, oid FROM pg_database;

Once you have all that information, you know which database to connect

to and which tables are big. Then you can DROP/DELETE/TRUNCATE or so.

Mikael, if you’re unaware of VACUUM FULL (as opposed to just VACUUM), you
should read about that too.

VACUUM FULL with an almost-full disk probably isn't the wisest idea.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

On 10/8/24 04:06, Mikael Petterson wrote:

Hi,
I find our disk is filling up.

Query the pg_stat_activity view:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

and look for rows that have with state showing 'idle in transaction'.

Report back what you find.

You should also check out this post:

https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/

What can I do to clean up. We are using pgsql 12.
Br,
//Mikael

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Oct 9, 2024 at 4:10 AM Mikael Petterson <mikaelpetterson@hotmail.com>
wrote:

Hi,

I find our disk is filling up.

sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print
$9 ": " $5 }'

...

Those files only add up to about 30GB. That's pretty small these days :
time for a bigger disk? Or perhaps the space is elsewhere: probably want to
do something like
sudo find / -xdev -maxdepth 2 -exec du -sh {} \; | grep -E 'G|M' | sort -g

Cheers,
Greg

Another possibility is orphaned files.
See https://www.dbi-services.com/blog/can-there-be-orphaned-data-files-in-postgresql/
and https://github.com/bdrouvot/pg_orphaned
--
Sergey

On Wed, 9 Oct 2024 at 19:02, Greg Sabino Mullane <htamfids@gmail.com> wrote:

On Wed, Oct 9, 2024 at 4:10 AM Mikael Petterson <mikaelpetterson@hotmail.com> wrote:

Hi,

I find our disk is filling up.

sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

...

Those files only add up to about 30GB. That's pretty small these days : time for a bigger disk? Or perhaps the space is elsewhere: probably want to do something like
sudo find / -xdev -maxdepth 2 -exec du -sh {} \; | grep -E 'G|M' | sort -g

Cheers,
Greg

--
Sergey