Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1syTO7-0009OF-P3 for pgsql-general@arkaria.postgresql.org; Wed, 09 Oct 2024 09:53:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1syTN8-000FoW-Eg for pgsql-general@arkaria.postgresql.org; Wed, 09 Oct 2024 09:52:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1syTN8-000FoL-1u for pgsql-general@lists.postgresql.org; Wed, 09 Oct 2024 09:52:46 +0000 Received: from mail-lf1-x12f.google.com ([2a00:1450:4864:20::12f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1syTN1-0008XD-7l for pgsql-general@lists.postgresql.org; Wed, 09 Oct 2024 09:52:45 +0000 Received: by mail-lf1-x12f.google.com with SMTP id 2adb3069b0e04-53995380bb3so8129727e87.1 for ; Wed, 09 Oct 2024 02:52:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728467558; x=1729072358; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=j2/Z+7BCP39x9mtSFyJJZ0BQbWCxobaXLXEcCtIsxbg=; b=mqn0M3dIsK/bnDXaFAR7dnfnfBVNcyLWLOQdKxpb6wW4lwq+qbHZrfOFRTZp5JiKwO 7YztqtRTzE3lYQPUQPPOArsOwAMuqSFjLXXbcOgndpVnBzATkkmjnCcv+J5xJFblGP83 EXCSUFDnBjytD1r66JokdvhSSEn+6vY7SPw8fjbIXddjZPYsTXOI/NwoyFt2nBeuRrIX ur58Aw6GSaMO/fa1hQj7rb/I32WgNgqSbd3gSrngMXupF56A6XovMWuQFQW1NUj/xsf1 IgIoQvfBgrTJE+EqbaKyS3B99Bv2wyt7opc3YRwUHZ+bEsGZ4ozEfZyz1HPtLHp5Cbi6 ONQQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728467558; x=1729072358; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=j2/Z+7BCP39x9mtSFyJJZ0BQbWCxobaXLXEcCtIsxbg=; b=Irybe/zHTYN32YlcnvVgvOK0JhDJJ8Z+OkFhonH9W7PzBlkQQWUjSwNqdpsBUGQ6VM 8jOrgzdlzO72RFPR2KirMBLIqKjFFt61++XRvzoA4TwpRUG6A9ji5DqNj+O7QDvO2iDB oqYoUzKHJz32CXUmk0uB1ETbBM8/2mioahozDcgimmhceZJyrQu+419QSzAD7XuEP26B jbhncOm88WgPOG8fbj0uT6c7ZwlW3dFHTuIR8CSc5bT797YE8PrzVOCm7mBnstBmUmZM gKZb67UV1Sag4KZKTtkxTOFJ149VAgovC7bc86a/ltQTdN6PbbsVjp5/yc/thZ1wQkAc HyAA== X-Gm-Message-State: AOJu0YwkhYWwx0/SzXzSaGfP0Mq2v+pRE3c9/4IWywrzTCtRXmA+tOSV GzZg7tBM+EOTrIz1rHpNJrDAhOS/HiREhYkmFn2kJzLKi5rf8BIpDSdKKKlLkt+sXpeJpdg5aXO MGp2IcH17eYZlxAeE8Piw3g9VPw== X-Google-Smtp-Source: AGHT+IHTHN/PaGZ8acEPfbnTsNW/Ve3Iu1DDCQ+xTGLcrwhdgql88wk2yztEAlgmYPlJ7XqwjbMXywIKSjQJZBH8k6g= X-Received: by 2002:a05:6512:2342:b0:539:8d46:4746 with SMTP id 2adb3069b0e04-539c4980d63mr967064e87.60.1728467558087; Wed, 09 Oct 2024 02:52:38 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Date: Wed, 9 Oct 2024 11:52:26 +0200 Message-ID: Subject: Re: Disk is filling up with large files. How can I clean? To: Mikael Petterson Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000ce443a062408361d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ce443a062408361d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM 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 }' > /var/lib/rpm/Packages: 278M > /var/lib/pgsql/12/data/base/16384/16583: 392M > /var/lib/pgsql/12/data/base/16384/16586: 1.0G > /var/lib/pgsql/12/data/base/16384/16588: 173M > /var/lib/pgsql/12/data/base/16384/16633: 106M > /var/lib/pgsql/12/data/base/16384/16644: 179M > /var/lib/pgsql/12/data/base/16384/16659: 1.0G > /var/lib/pgsql/12/data/base/16384/16662: 438M > /var/lib/pgsql/12/data/base/16384/16665: 1.0G > /var/lib/pgsql/12/data/base/16384/16667: 1.0G > /var/lib/pgsql/12/data/base/16384/16668: 1.0G > /var/lib/pgsql/12/data/base/16384/16780: 466M > /var/lib/pgsql/12/data/base/16384/16786: 182M > /var/lib/pgsql/12/data/base/16384/16788: 163M > /var/lib/pgsql/12/data/base/16384/16789: 315M > /var/lib/pgsql/12/data/base/16384/16790: 126M > /var/lib/pgsql/12/data/base/16384/16665.2: 403M > /var/lib/pgsql/12/data/base/16384/16586.7: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.6: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.9: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.8: 1.0G > /var/lib/pgsql/12/data/base/16384/16659.6: 1.0G > /var/lib/pgsql/12/data/base/16384/16659.4: 1.0G > /var/lib/pgsql/12/data/base/16384/16659.5: 1.0G > /var/lib/pgsql/12/data/base/16384/16668.3: 586M > /var/lib/pgsql/12/data/base/16384/16586.10: 458M > /var/lib/pgsql/12/data/base/16384/16659.1: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.2: 1.0G > /var/lib/pgsql/12/data/base/16384/16659.2: 1.0G > /var/lib/pgsql/12/data/base/16384/16668.1: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.3: 1.0G > /var/lib/pgsql/12/data/base/16384/16659.3: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.4: 1.0G > /var/lib/pgsql/12/data/base/16384/16665.1: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.5: 1.0G > /var/lib/pgsql/12/data/base/16384/16586.1: 1.0G > /var/lib/pgsql/12/data/base/16384/16668.2: 1.0G > /var/lib/pgsql/12/data/base/16384/16667.1: 741M > /var/lib/pgsql/12/data/base/16384/4652676: 502M > /var/lib/pgsql/12/data/base/16384/4652688: 155M > > How can I clean up? > > What can I do to clean up. We are using pgsql 12. > > Br, > > //Mikael > > > > > > --000000000000ce443a062408361d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Filenames like=C2=A016665,=C2=A016665.1,=C2=A016665.2 etc = all represent the same table (or similar). The number=C2=A016665 is called = the file node.

To get a list of file nodes for a specifi= c database you can run:

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

The /16384/ in the pa= th represents the database. To decipher=C2=A0that you can run:
SELECT datname, oid FROM pg_database;

=
Once you have all that information, you know which database to connect= to and which=C2=A0tables 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 FR= OM pg_class ORDER BY size DESC;


On Wed, Oct 9, 2024= at 10:10=E2=80=AFAM Mikael Petterson <mikaelpetterson@hotmail.com> wrote:
Hi,
=C2=A0
I find our disk is filling up.
=C2=A0
sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ prin= t $9 ": " $5 }'
/var/lib/rpm/Packages: 278M
/var/lib/pgsql/12/data/base/16384/16583: 392M
/var/lib/pgsql/12/data/base/16384/16586: 1.0G
/var/lib/pgsql/12/data/base/16384/16588: 173M
/var/lib/pgsql/12/data/base/16384/16633: 106M
/var/lib/pgsql/12/data/base/16384/16644: 179M
/var/lib/pgsql/12/data/base/16384/16659: 1.0G
/var/lib/pgsql/12/data/base/16384/16662: 438M
/var/lib/pgsql/12/data/base/16384/16665: 1.0G
/var/lib/pgsql/12/data/base/16384/16667: 1.0G
/var/lib/pgsql/12/data/base/16384/16668: 1.0G
/var/lib/pgsql/12/data/base/16384/16780: 466M
/var/lib/pgsql/12/data/base/16384/16786: 182M
/var/lib/pgsql/12/data/base/16384/16788: 163M
/var/lib/pgsql/12/data/base/16384/16789: 315M
/var/lib/pgsql/12/data/base/16384/16790: 126M
/var/lib/pgsql/12/data/base/16384/16665.2: 403M
/var/lib/pgsql/12/data/base/16384/16586.7: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.6: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.9: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.8: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.6: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.4: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.5: 1.0G
/var/lib/pgsql/12/data/base/16384/16668.3: 586M
/var/lib/pgsql/12/data/base/16384/16586.10: 458M
/var/lib/pgsql/12/data/base/16384/16659.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.2: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.2: 1.0G
/var/lib/pgsql/12/data/base/16384/16668.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.3: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.3: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.4: 1.0G
/var/lib/pgsql/12/data/base/16384/16665.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.5: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16668.2: 1.0G
/var/lib/pgsql/12/data/base/16384/16667.1: 741M
/var/lib/pgsql/12/data/base/16384/4652676: 502M
/var/lib/pgsql/12/data/base/16384/4652688: 155M
=C2=A0
How can I clean up?
=C2=A0
What can I do to clean up. We are using pgsql 12.
=C2=A0
Br,
=C2=A0
//Mikael


=C2=A0

=C2=A0

--000000000000ce443a062408361d--