iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article

💡

Replicating SQLite3 to S3 with Litestream while Rotating AWS Credentials

2022/05/14に公開

Introduction

I recently purchased a reTerminal and have been experimenting with connecting it to AWS IoT Core.^[1]
SQLite is a lightweight SQL database useful in IoT for its ease of integration into devices (allowing offline use). (The situation might be different for embedded systems.)
I decided to try replicating it to S3 with Litestream, thinking it might open up new opportunities for its use (?).

The challenge is that when issuing credentials using AWS IoT certificates and private keys, the credential expiration period is a minimum of 900 seconds and a maximum of 43200 seconds.^[2]

Using persistent credentials for hundreds of devices presents the following problems:

If using credentials for each device, the cost of issuance and management is high.
If using a single credential for all devices, the impact and cost are high when needing to revoke it due to credential leakage or other issues.

Looking at the Litestream issue Thoughts on using Litestream with time-limited AWS credentials?, it mentions wanting to add support for sending a SIGHUP to reload the config file without restarting Litestream.
Currently, this functionality is not available. Therefore, I will refresh credentials and restart Litestream when credentials are about to expire. This approach is for those who can tolerate that content written during the restart will not be replicated.

Prerequisites

The reTerminal's HSM (ATECC608A) uses an unreadable private key slot, a CSR has been issued with PKCS#11, and a certificate has been issued by AWS IoT.^[3]
Authenticating direct AWS service calls with AWS IoT Core credential provider settings have been implemented.

The reTerminal OS is Raspbian. While similar things can be done with a Raspberry Pi, it lacks an HSM, so adjustments are necessary.

$ lsb_release -a
No LSB modules are available.
Distributor ID: Raspbian
Description:    Raspbian GNU/Linux 10 (buster)
Release:        10
Codename:       buster

$ litestream version
v0.3.8

Configuration

systemd configuration

The Litestream configuration is as follows:

/etc/litestream.yml

dbs:
 - path: /home/pi/reteminal-app/.db/test.db
   replicas:
     - type: s3
       bucket: YOUR_S3_NAME
       path: replica
       region: ap-northeast-1

When Litestream is installed, it deploys a Unit definition file. In this case, since environment variables are used for credential loading, an EnvironmentFile setting will be added.

/lib/systemd/system/litestream.service

[Unit]
Description=Litestream

[Service]
Restart=always
ExecStart=/usr/bin/litestream replicate
EnvironmentFile=/home/pi/.config/sysconfig/litestream

[Install]
WantedBy=multi-user.target

In the EnvironmentFile, AWS credentials are regularly updated by the application, and Litestream is restarted by systemd.

/home/pi/.config/sysconfig/litestream

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_SESSION_TOKEN=
AWS_DEFAULT_REGION=

Source code

SQLite database and table creation code

sqlite3 test.db
CREATE TABLE HealthCheck (id INTEGER PRIMARY KEY,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
select id,datetime(created_at, '+9 hours') from HealthCheck;

The application uses Go.

Entry point code

cmd/edgego/main.go

package main

import (
	"context"
	"fmt"
	"io/ioutil"
	"os"
	"os/exec"
	"time"

	"github.com/shuntaka9576/edgego/domain"
	"github.com/shuntaka9576/edgego/logger"
)

var (
	credential                     *domain.Credentials
	SYSTEMD_LITESTREAM_CONFIG_PATH = os.Getenv("SYSTEMD_LITESTREAM_CONFIG_PATH")
)

func main() {
	credentialCh := make(chan *domain.Credentials)

	go func() {
		for {
			select {
			case credential = <-credentialCh:
				logger.Info().Msgf("get credential main expired at %s", credential.Expiration.Format(time.RFC3339))
				litestreamEnvString := fmt.Sprintf(""+
					"AWS_ACCESS_KEY_ID=\"%s\"\n"+
					"AWS_SECRET_ACCESS_KEY=\"%s\"\n"+
					"AWS_SESSION_TOKEN=\"%s\"\n"+
					"AWS_DEFAULT_REGION=ap-northeast-1",
					credential.AccessKeyId, credential.SecretAccessKey, credential.SessionToken)

				err := ioutil.WriteFile(SYSTEMD_LITESTREAM_CONFIG_PATH, []byte(litestreamEnvString), 0666)
				if err != nil {
					logger.Info().Msgf("writte error", err)
					continue
				}

				cmd := exec.Command("sudo", "systemctl", "restart", "litestream")
				logger.Info().Msgf("start restart litestream")

				err = cmd.Start()
				if err != nil {
					logger.Info().Msgf("start litestream exec error", err)
					continue
				}

				err = cmd.Wait()
				if err != nil {
					logger.Info().Msgf("restart litestream exec error", err)
					continue
				} else {
					logger.Info().Msgf("restart sucess litestream")
				}
			}
		}
	}()

	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	lotateCredentilasUseCase := &domain.LotateCredentilasUseCase{ExpiredAt: nil, UpdateAt: nil}
	go lotateCredentilasUseCase.Run(ctx, credentialCh)

	routineWriteSqlite3 := &domain.RoutineWriteSqlite3{}
	go routineWriteSqlite3.Run(ctx)

	<-ctx.Done()
}

Code to periodically update AWS credentials

We use go-curl, a binding for libcurl, to utilize PKCS#11.

Function to get credentials

package iot

import (
	"encoding/json"
	"os"

	curl "github.com/andelf/go-curl"
)

var (
	CREDENTIAL_ALIAS_ENDPOINT = os.Getenv("CREDENTIAL_ENDPOINT") +
		"/role-aliases/" +
		os.Getenv("CREDENTIAL_ROLE_ALIAS") +
		"/credentials"
	DEVICE_CERT_PATH                 = os.Getenv("DEVICE_CERT_PATH")
	DEVICE_PRIVATE_KEY_PKCS11_PHRASE = os.Getenv("DEVICE_PRIVATE_KEY_PKCS11_PHRASE")
)

type RoleAliasResponse struct {
	Credentials CredentialsResponse `json:"credentials"`
}

type CredentialsResponse struct {
	AccessKeyId     string `json:"accessKeyId"`
	SecretAccessKey string `json:"secretAccessKey"`
	SessionToken    string `json:"sessionToken"`
	Expiration      string `json:"expiration"`
}

func GetCredentialsForCert() (*RoleAliasResponse, error) {

	easy := curl.EasyInit()
	defer easy.Cleanup()

	easy.Setopt(curl.OPT_URL, CREDENTIAL_ALIAS_ENDPOINT)
	easy.Setopt(curl.OPT_SSL_VERIFYPEER, 0)
	easy.Setopt(curl.OPT_SSL_VERIFYHOST, false)
	easy.Setopt(curl.OPT_VERBOSE, 0)
	easy.Setopt(curl.OPT_SSLENGINE, "pkcs11")
	easy.Setopt(curl.OPT_SSLCERTTYPE, "PEM")
	easy.Setopt(curl.OPT_SSLKEYTYPE, "eng")
	easy.Setopt(curl.OPT_SSLCERT, DEVICE_CERT_PATH)
	easy.Setopt(curl.OPT_SSLKEY, DEVICE_PRIVATE_KEY_PKCS11_PHRASE)

	roleAliasRes := &RoleAliasResponse{}
	execFunc := func(buf []byte, userdata interface{}) bool {
		if err := json.Unmarshal(buf, roleAliasRes); err != nil {
			return false
		}

		return true
	}

	easy.Setopt(curl.OPT_WRITEFUNCTION, execFunc)
	err := easy.Perform()
	if err != nil {
		return nil, err
	}

	return roleAliasRes, nil
}

Goroutine to periodically update credentials

package domain

import (
	"context"
	"time"

	"github.com/shuntaka9576/edgego/infra/iot"
	"github.com/shuntaka9576/edgego/logger"
)

var (
	LOTATE_BUFFER_TIME_MINUTES int = 5
)

type LotateCredentilasUseCase struct {
	ExpiredAt *time.Time
	UpdateAt  *time.Time
}

func (l *LotateCredentilasUseCase) Run(ctx context.Context, credentialCh chan *Credentials) {
	go func() {
		ticker := time.NewTicker(5 * time.Second)

	LOOP:
		for {
			select {
			case <-ticker.C:

				nowTime := time.Now()

				if l.ExpiredAt == nil && l.UpdateAt == nil || l.UpdateAt.Unix() <= nowTime.Unix() {
					logger.Info().Msgf("start update credential")

					if l.ExpiredAt != nil && l.UpdateAt != nil {
						logger.Info().Msgf("now: %s", nowTime.Format(time.RFC3339))
						logger.Info().Msgf("UpdateAt: %s", l.UpdateAt.Format(time.RFC3339))
						logger.Info().Msgf("ExpiredAt: %s", l.ExpiredAt.Format(time.RFC3339))
					} else {
						logger.Info().Msgf("init credential")
					}

					res, err := iot.GetCredentialsForCert()

					if err != nil {
						logger.Warn().Msgf("get crendential error: %s", err)

						continue
					}

					parsedTime, err := time.Parse(time.RFC3339, res.Credentials.Expiration)
					if err != nil {
						logger.Warn().Msgf("parsed time error: %s", err)
						continue
					}
					updateAt := parsedTime.Add(-time.Minute * time.Duration(LOTATE_BUFFER_TIME_MINUTES))

					l = &LotateCredentilasUseCase{
						ExpiredAt: &parsedTime,
						UpdateAt:  &updateAt,
					}

					logger.Info().Msgf("updated credential success. expired at %s", l.ExpiredAt.Format(time.RFC3339))
					credentialCh <- &Credentials{
						AccessKeyId:     res.Credentials.AccessKeyId,
						SecretAccessKey: res.Credentials.SecretAccessKey,
						SessionToken:    res.Credentials.SessionToken,
						Expiration:      *l.ExpiredAt,
					}
				}
			case <-ctx.Done():
				logger.Info().Msg("Done")
				break LOOP
			}
		}
	}()
}

Code to periodically update SQLite3

The following content is written to SQLite3:

DB Field	Content to Write
id	Get the timestamp at startup, increment by 1 every 5 seconds and write (no particular intention)
create_at	Write the current date and time using SQLite3's built-in variable

Goroutine to periodically update SQLite3

package domain

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"time"

	"github.com/shuntaka9576/edgego/logger"

	_ "github.com/mattn/go-sqlite3"
)

var (
	SQLITE3_SAMPLE_DB_PATH = os.Getenv("SQLITE3_SAMPLE_DB_PATH")
	TABLE_NAME             = "HealthCheck"
)

type RoutineWriteSqlite3 struct {
}

func (l *RoutineWriteSqlite3) Run(ctx context.Context) {
	con, _ := sql.Open("sqlite3", SQLITE3_SAMPLE_DB_PATH)

	go func() {
		ticker := time.NewTicker(5 * time.Second)
		defer con.Close()
		id := time.Now().Unix()

	LOOP:
		for {
			select {
			case <-ticker.C:
				logger.Info().Msgf("sqlite3 write start")

				cmd := fmt.Sprintf("INSERT INTO %s (id) VALUES (%d);",
					TABLE_NAME,
					id,
				)
				_, err := con.Exec(cmd)
				if err != nil {
					logger.Err().Msgf("sqlite3 write err: %s", err)
					continue
				}
				id += 1
				logger.Info().Msgf("sqlite3 write success")
			case <-ctx.Done():
				logger.Info().Msg("Done")
				break LOOP
			}
		}
	}()
}

Result verification

Prerequisites

Credentials expire in 15 minutes. Credential updates and Litestream restarts occur 5 minutes before expiration (= credential updates and Litestream restarts occur every 10 minutes).
SQLite is written to once every 5 seconds.
Credential updates and SQLite writes operate on separate goroutines.
State before Go application startup:
- SQLite database and table created and empty.
- S3 is empty.
- Litestream is stopped, as it will be started by the Go application.

What we want to confirm

After running for about an hour, stop the Go application and Litestream, then verify that the number of records in the restored table from the device and S3 match.^[4]

Results

Overview of progress

Time	Target	Event
7:45:00	Manual	Go app started
7:45:03	Go app log	Startup log
7:45:08	Go app log	1st SQLite write
7:45:09	Go app log	Litestream restart success log
7:45:13	Go app log	2nd SQLite write
(omitted)	(omitted)	(omitted)
8:50:00	Manual	Go app stopped
8:50:00	Manual	Litestream stopped

Checking various logs

Looking at the logs, the time taken for a restart (unreplicated downtime) appears to be less than 1 second.

Litestream service log

 5月 14 07:45:09 raspberrypi systemd[1]: Started Litestream. 👈 Restarted from Go app
 5月 14 07:45:09 raspberrypi litestream[2239]: litestream v0.3.8 
 5月 14 07:45:09 raspberrypi litestream[2239]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 07:45:09 raspberrypi litestream[2239]: replicating to: name="s3" type="s3" bucket="YOUR_S3_NAME" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 07:45:10 raspberrypi litestream[2239]: /home/pi/auto/private-reteminal-app/.db/test.db: sync: new generation "404fe1ff630ab2f0", no generation exists
 5月 14 07:45:16 raspberrypi litestream[2239]: /home/pi/auto/private-reteminal-app/.db/test.db(s3): snapshot written 404fe1ff630ab2f0/00000001
 5月 14 07:55:14 raspberrypi litestream[2239]: signal received, litestream shutting down
 5月 14 07:55:14 raspberrypi systemd[1]: Stopping Litestream...
 5月 14 07:55:14 raspberrypi litestream[2239]: litestream shut down
 5月 14 07:55:14 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 07:55:14 raspberrypi systemd[1]: Stopped Litestream.
 5月 14 07:55:14 raspberrypi systemd[1]: Started Litestream.
 5月 14 07:55:14 raspberrypi litestream[2443]: litestream v0.3.8
 5月 14 07:55:14 raspberrypi litestream[2443]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 07:55:14 raspberrypi litestream[2443]: replicating to: name="s3" type="s3" bucket="YOURDB" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 08:05:19 raspberrypi litestream[2443]: signal received, litestream shutting down
 5月 14 08:05:19 raspberrypi systemd[1]: Stopping Litestream...
 5月 14 08:05:19 raspberrypi litestream[2443]: litestream shut down
 5月 14 08:05:19 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 08:05:19 raspberrypi systemd[1]: Stopped Litestream.
 5月 14 08:05:19 raspberrypi systemd[1]: Started Litestream.
 5月 14 08:05:19 raspberrypi litestream[2519]: litestream v0.3.8
 5月 14 08:05:19 raspberrypi litestream[2519]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 08:05:19 raspberrypi litestream[2519]: replicating to: name="s3" type="s3" bucket="YOUR_S3_NAME" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 08:15:24 raspberrypi litestream[2519]: signal received, litestream shutting down
 5月 14 08:15:24 raspberrypi systemd[1]: Stopping Litestream...
 5月 14 08:15:24 raspberrypi litestream[2519]: litestream shut down
 5月 14 08:15:24 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 08:15:24 raspberrypi systemd[1]: Stopped Litestream.
 5月 14 08:15:24 raspberrypi systemd[1]: Started Litestream.
 5月 14 08:15:24 raspberrypi litestream[2556]: litestream v0.3.8
 5月 14 08:15:24 raspberrypi litestream[2556]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 08:15:24 raspberrypi litestream[2556]: replicating to: name="s3" type="s3" bucket="YOUR_S3_NAME" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 08:25:29 raspberrypi litestream[2556]: signal received, litestream shutting down
 5月 14 08:25:29 raspberrypi systemd[1]: Stopping Litestream...
 5月 14 08:25:29 raspberrypi litestream[2556]: litestream shut down
 5月 14 08:25:29 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 08:25:29 raspberrypi systemd[1]: Stopped Litestream.
 5月 14 08:25:29 raspberrypi systemd[1]: Started Litestream.
 5月 14 08:25:29 raspberrypi litestream[2603]: litestream v0.3.8
 5月 14 08:25:29 raspberrypi litestream[2603]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 08:25:29 raspberrypi litestream[2603]: replicating to: name="s3" type="s3" bucket="YOUR_S3_NAME" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 08:35:34 raspberrypi litestream[2603]: signal received, litestream shutting down
 5月 14 08:35:34 raspberrypi systemd[1]: Stopping Litestream...
 5月 14 08:35:34 raspberrypi litestream[2603]: litestream shut down
 5月 14 08:35:34 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 08:35:34 raspberrypi systemd[1]: Stopped Litestream.
 5月 14 08:35:34 raspberrypi systemd[1]: Started Litestream.
 5月 14 08:35:34 raspberrypi litestream[2674]: litestream v0.3.8
 5月 14 08:35:34 raspberrypi litestream[2674]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 08:35:34 raspberrypi litestream[2674]: replicating to: name="s3" type="s3" bucket="shuntaka20220512" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 08:45:40 raspberrypi litestream[2674]: signal received, litestream shutting down
 5月 14 08:45:40 raspberrypi systemd[1]: Stopping Litestream...
 5月 14 08:45:40 raspberrypi litestream[2674]: litestream shut down
 5月 14 08:45:40 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 08:45:40 raspberrypi systemd[1]: Stopped Litestream.
 5月 14 08:45:40 raspberrypi systemd[1]: Started Litestream.
 5月 14 08:45:40 raspberrypi litestream[2848]: litestream v0.3.8
 5月 14 08:45:40 raspberrypi litestream[2848]: initialized db: /home/pi/auto/private-reteminal-app/.db/test.db
 5月 14 08:45:40 raspberrypi litestream[2848]: replicating to: name="s3" type="s3" bucket="shuntaka20220512" path="replica" region="ap-northeast-1" endpoint="" sync-interval=1s
 5月 14 08:50:03 raspberrypi litestream[2848]: signal received, litestream shutting down
 5月 14 08:50:03 raspberrypi systemd[1]: Stopping Litestream... 👈 Manual stop log (sudo systemctl stop litestream)
 5月 14 08:50:03 raspberrypi litestream[2848]: litestream shut down
 5月 14 08:50:03 raspberrypi systemd[1]: litestream.service: Succeeded.
 5月 14 08:50:03 raspberrypi systemd[1]: Stopped Litestream.

Go app log (partially omitted)

2022-05-14T07:45:03+09:00 INFO   start app
2022-05-14T07:45:08+09:00 INFO   start update credential
2022-05-14T07:45:08+09:00 INFO   sqlite3 write start
2022-05-14T07:45:08+09:00 INFO   init credential
2022-05-14T07:45:08+09:00 INFO   sqlite3 write success
2022-05-14T07:45:09+09:00 INFO   updated credential success. expired at 2022-05-13T23:00:09Z
2022-05-14T07:45:09+09:00 INFO   get credential main expired at 2022-05-13T23:00:09Z
2022-05-14T07:45:09+09:00 INFO   start restart litestream
2022-05-14T07:45:09+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T07:55:13+09:00 INFO   start update credential
2022-05-14T07:55:13+09:00 INFO   now: 2022-05-14T07:55:13+09:00
2022-05-14T07:55:13+09:00 INFO   sqlite3 write success
2022-05-14T07:55:13+09:00 INFO   UpdateAt: 2022-05-13T22:55:09Z
2022-05-14T07:55:13+09:00 INFO   ExpiredAt: 2022-05-13T23:00:09Z
2022-05-14T07:55:14+09:00 INFO   updated credential success. expired at 2022-05-13T23:10:14Z
2022-05-14T07:55:14+09:00 INFO   get credential main expired at 2022-05-13T23:10:14Z
2022-05-14T07:55:14+09:00 INFO   start restart litestream
2022-05-14T07:55:14+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T08:05:18+09:00 INFO   start update credential
2022-05-14T08:05:18+09:00 INFO   now: 2022-05-14T08:05:18+09:00
2022-05-14T08:05:18+09:00 INFO   sqlite3 write success
2022-05-14T08:05:18+09:00 INFO   UpdateAt: 2022-05-13T23:05:14Z
2022-05-14T08:05:18+09:00 INFO   ExpiredAt: 2022-05-13T23:10:14Z
2022-05-14T08:05:19+09:00 INFO   updated credential success. expired at 2022-05-13T23:20:19Z
2022-05-14T08:05:19+09:00 INFO   get credential main expired at 2022-05-13T23:20:19Z
2022-05-14T08:05:19+09:00 INFO   start restart litestream
2022-05-14T08:05:19+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T08:15:23+09:00 INFO   start update credential
2022-05-14T08:15:23+09:00 INFO   now: 2022-05-14T08:15:23+09:00
2022-05-14T08:15:23+09:00 INFO   sqlite3 write success
2022-05-14T08:15:23+09:00 INFO   UpdateAt: 2022-05-13T23:15:19Z
2022-05-14T08:15:23+09:00 INFO   ExpiredAt: 2022-05-13T23:20:19Z
2022-05-14T08:15:24+09:00 INFO   updated credential success. expired at 2022-05-13T23:30:24Z
2022-05-14T08:15:24+09:00 INFO   get credential main expired at 2022-05-13T23:30:24Z
2022-05-14T08:15:24+09:00 INFO   start restart litestream
2022-05-14T08:15:24+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T08:25:28+09:00 INFO   start update credential
2022-05-14T08:25:28+09:00 INFO   now: 2022-05-14T08:25:28+09:00
2022-05-14T08:25:28+09:00 INFO   sqlite3 write success
2022-05-14T08:25:28+09:00 INFO   UpdateAt: 2022-05-13T23:25:24Z
2022-05-14T08:25:28+09:00 INFO   ExpiredAt: 2022-05-13T23:30:24Z
2022-05-14T08:25:29+09:00 INFO   updated credential success. expired at 2022-05-13T23:40:29Z
2022-05-14T08:25:29+09:00 INFO   get credential main expired at 2022-05-13T23:40:29Z
2022-05-14T08:25:29+09:00 INFO   start restart litestream
2022-05-14T08:25:29+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T08:35:33+09:00 INFO   start update credential
2022-05-14T08:35:33+09:00 INFO   sqlite3 write start
2022-05-14T08:35:33+09:00 INFO   now: 2022-05-14T08:35:33+09:00
2022-05-14T08:35:33+09:00 INFO   UpdateAt: 2022-05-13T23:35:29Z
2022-05-14T08:35:33+09:00 INFO   sqlite3 write success
2022-05-14T08:35:33+09:00 INFO   ExpiredAt: 2022-05-13T23:40:29Z
2022-05-14T08:35:34+09:00 INFO   updated credential success. expired at 2022-05-13T23:50:34Z
2022-05-14T08:35:34+09:00 INFO   get credential main expired at 2022-05-13T23:50:34Z
2022-05-14T08:35:34+09:00 INFO   start restart litestream
2022-05-14T08:35:34+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T08:45:38+09:00 INFO   start update credential
2022-05-14T08:45:38+09:00 INFO   sqlite3 write start
2022-05-14T08:45:38+09:00 INFO   now: 2022-05-14T08:45:38+09:00
2022-05-14T08:45:38+09:00 INFO   UpdateAt: 2022-05-13T23:45:34Z
2022-05-14T08:45:38+09:00 INFO   ExpiredAt: 2022-05-13T23:50:34Z
2022-05-14T08:45:38+09:00 INFO   sqlite3 write success
2022-05-14T08:45:40+09:00 INFO   updated credential success. expired at 2022-05-14T00:00:40Z
2022-05-14T08:45:40+09:00 INFO   get credential main expired at 2022-05-14T00:00:40Z
2022-05-14T08:45:40+09:00 INFO   start restart litestream
2022-05-14T08:45:40+09:00 INFO   restart sucess litestream
(中略)
2022-05-14T08:49:58+09:00 INFO   sqlite3 write start
2022-05-14T08:49:58+09:00 INFO   sqlite3 write success
signal: terminated 👈 Manual stop log (kill)
make: *** [Makefile:2: start] エラー 1

Check record count

Check the number of records in the SQLite database on the device.

Check record count on device

$ sqlite3 test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> select count(*) from HealthCheck;
779

The number of sqlite3 write success entries in the logs is also 779, so they match.

Restore the replicated SQLite to a Mac and check the record count.

# Assume-role in AWS environment
$ litestream restore -o restored.db s3://YOUR_S3_NAME/replica
$ ls
restored.db
$ sqlite3 restored.db
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite> select count(*) from HealthCheck ;
779

They match.

Conclusion

As expected, replication worked flawlessly even with time-limited credentials. Litestream is fun!

Addendum

retention and snapshot-interval appear to be measured from the time of startup. In an environment like this article where restarts occur periodically, WAL file rotation and snapshots may not be created as intended unless these settings are less than the restart time.

脚注

For more details on reTerminal, refer to reTerminal arrived, so I'll try initial setup and obtaining the ATECC608A serial number ↩︎
From Authenticating direct AWS service calls with AWS IoT Core credential provider ↩︎
For certificate issuance methods, refer to [reTerminal] MQTT communication with AWS IoT while securely managing private keys using PKCS#11 ↩︎
For Litestream, check journalctl -u litestream; for the Go app, check its own logs. ↩︎

iTranslated by AI

Introduction

Prerequisites

Configuration

systemd configuration

Source code

Result verification

Prerequisites

What we want to confirm

Results

Overview of progress

Checking various logs

Check record count

Conclusion

Addendum

Discussion