Uploading a new custom image does not remove the old image

[Mike Pontillo]

For context, see the thread here:

https://lists.ubuntu.com/archives/maas-devel/2017-January/002357.html

Summarizing:

Doing a "maas-region db_vacuum_lobjects” doesn’t seem to clean out the overwritten image it seems like. Below I’m doing a du -hs of /var/lib/postgresql before and after an image upload as well as after the vacuum.

13G /var/lib/postgresql
Uploading
19G /var/lib/postgresql
Cleanup database
vacuumdb: vacuuming database "maasdb"
Database vacuumed successfully.
19G /var/lib/postgresql

It seems though that the database is not shrinking, while iterating and making a new windows image over and over again, uploading it with:

maas admin boot-resources create name=windows/win2012r2 architecture=amd64/generic filetype=ddtgz content@=myimage.raw.tgz

Running `vacuumlo`, followed by a `VACUUM FULL` and a `REINDEX TABLE` also had no effect.

maasdb=# select id, sha256, total_size, content from maasserver_largefile order by id;
 id | sha256 | total_size | content
----+------------------------------------------------------------------+------------+---------
  3 | c982d550cc03da0fec01f8059596f8334d9135adc5b5600f9d86b0ae003fbffe | 7069136 | 18775
  6 | db99dbac5ffdd56cffc4295785817748e389f9c5fa9110c835e4b8d3d2ba3d1a | 7103248 | 18778
  8 | 233f693406cce4582d642f786902ddb9f5280b762db7373624902edc16094e3a | 307768 | 18780
  9 | 6ea59948ad694f29c849395f4689442942066acb7f5e4595b4fd90d4d4b8c17d | 281340 | 18781
 10 | 12c895ac0ec41d1ced3f7d81b36e27f70f023cd3350b798106e7f401d53ecfb6 | 514912 | 18782
 11 | 3ff850786b9514ca17815c9975b91d0987e4d50363b300261db3dbe2f02045df | 487132 | 18783
 12 | 8d4e6c2b4188baac94fe627c8b4ffc1569a2b71b8a2574aa1aadf52f2a0ba932 | 374692 | 18784
 13 | 556a3192d070a40cb930e4dd2ab2aaa5c2e49b4884348e90ced185b25af2b814 | 4418461053 | 18806
 14 | 8b1fda76f7ad259c54f9368a4487522dac8f386d63a6d2504cf93be6eadb2e62 | 4496433400 | 18819
 15 | cde4daf9f6ba691a43b2a8760a057109ba25a057d17884c955607daf43220e49 | 4430168602 | 18843
 16 | 576bed34e75a61e061183f8c2b39479c483a57c0f0fd1218d13940bc9b2ea23d | 187719680 | 18857
 17 | 9002e787a4e5f3d8cd8722719e0502f2206ec3c22d23c58021f4605fe64c8116 | 37669738 | 18858
 18 | 4b7e166571168a615dea2aa83c2faaa8e5cd501544eda6d0197af825c91e314e | 7332736 | 18859
 19 | e92149d78744623bf8fa4a7b12d8a723e8ec993ac938da2925175f4bc399d634 | 40058210 | 18860
 20 | 63bf66548c5e6a243d3fbecfeef4734056aec2eccde5af7565ccdfe83f84feea | 37670057 | 18861
 21 | 06eb302764703c8a5a8d99022badd0677c0b3ce67d04831d016dbf4edc3610eb | 37685703 | 18862
 22 | 714a6903bab25c38ad66a1723f0a8ec033883a68433944d42622b0decc5fce0a | 7298592 | 18863
 23 | 4d5f762ff60b58efabde51d88573a05f813b2619f80d57d87aefc4314b372799 | 40068223 | 18864
 24 | 7a517586b2f9798d8ed6d47e5f1426caf0f7996ea21e836303e86c3e539349eb | 37687745 | 18865
 25 | b7313ab3a0a06bd59699124b14dda5a0479dcfd0c6078ca21c09afe3df5fba66 | 4607439486 | 18880
(20 rows)

maasdb=# select largefile_id, filename, filetype, extra from maasserver_bootresourcefile order by largefile_id;
 largefile_id | filename | filetype | extra
--------------+---------------------+----------------+----------------------------------------------------------------------------------------------------------
            3 | boot-kernel | boot-kernel | {"kpackage": "linux-generic"}
            3 | boot-kernel | boot-kernel | {"kpackage": "linux-generic"}
            6 | boot-kernel | boot-kernel | {"kpackage": "linux-lowlatency"}
            6 | boot-kernel | boot-kernel | {"kpackage": "linux-lowlatency"}
            8 | shim-signed.tar.xz | archive.tar.xz | {"src_package": "shim-signed", "src_release": "xenial", "src_version": "1.19~16.04.1+0.8-0ubuntu2"}
            9 | grub2-signed.tar.xz | archive.tar.xz | {"src_package": "grub2-signed", "src_release": "xenial", "src_version": "1.66.7+2.02~beta2-36ubuntu3.7"}
           10 | grub2.tar.xz | archive.tar.xz | {"src_package": "grub2", "src_release": "xenial", "src_version": "2.02~beta2-36ubuntu3.7"}
           11 | grub2.tar.xz | archive.tar.xz | {"src_package": "grub2", "src_release": "xenial", "src_version": "2.02~beta2-36ubuntu3.7"}
           12 | syslinux.tar.xz | archive.tar.xz | {"src_package": "syslinux", "src_release": "xenial", "src_version": "3:6.03+dfsg-11ubuntu1"}
           13 | root-dd | root-dd | ""
           14 | root-dd | root-dd | ""
           15 | root-dd | root-dd | ""
           16 | squashfs | squashfs | {}
           16 | squashfs | squashfs | {}
           16 | squashfs | squashfs | {}
           16 | squashfs | squashfs | {}
           16 | squashfs | squashfs | {}
           16 | squashfs | squashfs | {}
           17 | boot-initrd | boot-initrd | {"kpackage": "linux-lowlatency"}
           18 | boot-kernel | boot-kernel | {"kpackage": "linux-lowlatency-hwe-16.04-edge"}
           19 | boot-initrd | boot-initrd | {"kpackage": "linux-lowlatency-hwe-16.04-edge"}
           20 | boot-initrd | boot-initrd | {"kpackage": "linux-lowlatency"}
           21 | boot-initrd | boot-initrd | {"kpackage": "linux-generic"}
           22 | boot-kernel | boot-kernel | {"kpackage": "linux-generic-hwe-16.04-edge"}
           23 | boot-initrd | boot-initrd | {"kpackage": "linux-generic-hwe-16.04-edge"}
           24 | boot-initrd | boot-initrd | {"kpackage": "linux-generic"}
           25 | root-dd | root-dd | ""
(27 rows)

... but from the above, the cause seems to be that overwriting a custom image does not remove the previous boot resource file, or large object. So that could be why the vacuuming isn't working.

[Tim Chen (timchen119)]

One important symptom found here:
 - Upload and importing custom images to MAAS server with same filename with different SHA256 will continue grow the `maasserver_largefile` table (and pg_largeobject) but only show one entry on `maasserver_bootresource`

so I feel it's more a MAAS bug than postgres.

Current workaround is to remove the last uploaded image whenever we upload a custom image, thus the entries in `maasserver_largefile` table (and pg_largeobject) will be dropped,
and after a while the postgres auto vacuum and vacuumlo will be triggered from MAAS and release the allocated space to the system.

Following MAAS CLI command will get the MAAS image list (with logged user `admin`):

$ maas admin boot-resources read

And we will get a image entry id , for example following `pc-carson-sanbernardino-daily` image is 32.
....
    {
        "id": 32,
        "type": "Uploaded",
        "subarches": "generic",
        "name": "pc-carson-sanbernardino-daily",
        "resource_uri": "/MAAS/api/2.0/boot-resources/32/",
        "title": "pc-carson-sanbernardino-daily",
        "architecture": "amd64/generic"
    }

Then delete the image,

$ maas admin boot-resource delete 32

Wait a while and the image(s) will be released automatically by MAAS (and postgres) and the disk space will return.

[Paul Larson (pwlars)]

I'm currently running something like this every day on our MaaS:
time sudo -u postgres vacuumlo -v maasdb
time sudo maas-region db_vacuum_lobjects
time sudo -u postgres psql maasdb -c 'vacuum full verbose maasserver_largefile'
sudo du -sh /var/lib/postgresql/

The sudo maas-region db_vacuum_lobjects often takes up to an hour for us, but this has enabled us to keep the size of everything under /var/lib/postgresql down to about 20-30GB. It sometimes grows a little higher than that, but comes back down after running this.

[Lee Trager (ltrager)]

A fix has landed in master for this. We want to test it for a few days in master before backporting it to 2.3 and 2.4.