DEV Community

Discussion on: Clean Sentry On-Premise Database

View post
Collapse
 
c80609a profile image
scout • • Edited

My solution was used before I found this pull request.

To checkout volume:

$ cd /var/lib/docker/volumes/sentry-postgres/_data/base/12407
$ ls -AlhtS

    total 13G
    -rw------- 1 999 docker 1.0G Nov 24 11:51 20250
    -rw------- 1 999 docker 1.0G Nov 23 11:58 20250.1
    -rw------- 1 999 docker 1.0G Nov 22 12:53 20250.10
    -rw------- 1 999 docker 1.0G Nov 21 12:05 20250.2
    -rw------- 1 999 docker 1.0G Nov 20 12:12 20250.3
    -rw------- 1 999 docker 1.0G Nov 19 12:18 20250.4
    -rw------- 1 999 docker 1.0G Nov 18 12:25 20250.5
    -rw------- 1 999 docker 1.0G Nov 17 12:32 20250.6
    -rw------- 1 999 docker 1.0G Nov 16 12:51 20250.7
    -rw------- 1 999 docker 1.0G Nov 15 12:45 20250.8
    -rw------- 1 999 docker 1.0G Nov 14 12:49 20250.9
    -rw------- 1 999 docker 507M Nov 14 12:54 20250.11
    -rw------- 1 999 docker 237M Nov 23 12:53 20252
    <...>
Enter fullscreen mode Exit fullscreen mode

To connect to docker:

docker exec -it sentry_onpremise_postgres_1 bash
Enter fullscreen mode Exit fullscreen mode

To enter console:

psql -U postgres
Enter fullscreen mode Exit fullscreen mode

To find out all tables with TOAST tables:

SELECT oid::regclass, reltoastrelid::regclass, pg_relation_size(reltoastrelid) AS toast_size FROM pg_class WHERE relkind = 'r' AND reltoastrelid <> 0 ORDER BY 3 DESC;

                    oid                     |      reltoastrelid      | toast_size
--------------------------------------------+-------------------------+------------
 nodestore_node                             | pg_toast.pg_toast_20247 | 12020846080
 pg_rewrite                                 | pg_toast.pg_toast_2618  |      385024
 pg_statistic                               | pg_toast.pg_toast_2619  |      212992
 sentry_groupedmessage                      | pg_toast.pg_toast_16900 |       81920
 sentry_apikey                              | pg_toast.pg_toast_16542 |           0
 sentry_authidentity                        | pg_toast.pg_toast_16605 |           0
 sentry_authprovider                        | pg_toast.pg_toast_16616 |           0
 <...>
Enter fullscreen mode Exit fullscreen mode

To cleanup:

DELETE FROM nodestore_node WHERE timestamp < '2021-11-23 00:00:00';
Enter fullscreen mode Exit fullscreen mode

PROFIT.

Collapse
 
michabbb_76 profile image
Michael Bladowski •

why a "truncate" after the delete ?