Posts tagged: data

Today I learned how to VACUUM a sqlite database and cut its size in about half. It’s a database that I have had running for quite awhile and has some decent traffic on it.

Why is it important to do a VACUUM? In short its becuase the file system gets fragmented with as data is updated. On delete the files are removed from the database and marked as available for reuse in the filesystem, but the space is not reclaimed.

To VACUUM a database, run the following sql command. You can do it right form the sqlite shell by running sqlite3.

You will need about double the current size of the database as free space to do the VACUUM, you need space for a full copy, journaling or write ahead logs, and the existing database.

VACUUM;

The docs are fantastic for vacuum.

I recently se tup minio object storage in my homelab for litestream sqlite backups. The litestream quickstart made it easy to get everything up and running on localhost, but I hit a wall when dns was involved to pull it from a different machine.

Here is what I got to work #

First I had to configure the Key ID and Secret Access Key generated in the minio ui.

❯ aws configure
AWS Access Key ID [****************VZnD]:
AWS Secret Access Key [****************xAm8]:
Default region name [us-east-1]:
Default output format [None]:

Then set the the s3 signature_version to s3v4.

aws configure set default.s3.signature_version s3v4

Now when I have minio running on https://my-minio-endpoint.com I can use the aws cli to access the bucket.

Note that https://my-minio-endpoint.com resolves to the bucket endpoint (default 9000) not the ui (default 9001).

aws --endpoint-url https://my-minio-endpoint.com s3 ls my_bucket

Now Configuring Litestream #

Litestream also accepts the endpoint argument via config. I could not get it to work just with the ui.

Note the aws configure step above is not required for litestream, only the aws cli.

dbs:
  - path: /path/to/database.db
    replicas:
      - url: s3://my_bucket/
        endpoint: https://my-minio-endpoint.com
        region: us-east-1
        access-key-id: ****************VZnD
        secret-access-key: ************************************xAm8

Now run a litestream replication.

litestream replicate -config litestream.yml
# or put the config in /etc/litestream.yml and just run replicate
litestream replicate