LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

How to Setup Replicated Highly Available SQLite with rqlite

Tags: , , , , Date/Time: June 3, 2021 @ 12:00 pm, by raindog308

Replicated Highly Availably SQLite with rqliteIf you need a highly available database configuration where the loss of one node doesn’t result in loss of service and changes on one node are replicated to other nodes, you would probably reach for MariaDB, PostgreSQL, or one of the big boys like Oracle or SQL Server.  Probably the last thing that would cross your mind is sqlite.  After all, sqlite is not even multi-user, much less multi-node.

That’s very true for sqlite, but there’s a project called rqlite that adds both replication and high availability to SQLite, as well as other features such an an http(s) API, encrypted traffic, read-only nodes, and even hot backups.  It’s kind of the ultimate LowEndDatabase :-)

In this tutorial we’ll setup a 3-node rqlite cluster.

Setting Up the VPSes

I’m going to use three VPSes for the cluster.  rqlite uses the RAFT consensus algorithm for high availability and replication, which means that you need to size your clusters in odd numbers – 3, 5, etc.

I’ll be using the following config, in which I’ve replace the real IPs with fake numbers:

1.1.1.1 rqlite1.lowend.party
2.2.2.2 rqlite2.lowend.party
3.3.3.3 rqlite3.lowend.party

To install rqlite, I’ll grab the latest release from Github and set it up in /usr/local:

# cd /usr/local
# curl -L https://github.com/rqlite/rqlite/releases/download/v5.3.0/rqlite-v5.3.0-linux-amd64.tar.gz -o rqlite-v5.3.0-linux-amd64.tar.gz
# tar xvfz rqlite-v5.3.0-linux-amd64.tar.gz
# ln -s rqlite-v5.3.0-linux-amd64 rqlite
# cd rqlite

I did this on all three nodes.

Startup rqlite the First Time

The first time you start rqlite, it won’t have any configuration information about the nodes, so we’ll start rqlite1 as the cluster leader and then manually join the other nodes.  In future starts/restarts, rqlite will know its config and we can start things in any order.

On rqlite1, I type:

# ./rqlited -node-id 1 -http-addr 1.1.1.1:4001 -raft-addr 1.1.1.1:4002 /usr/local/rqlite/db

The arguments break down as follows:

  • -node-id: This can be any string, as long as it’s unique in the cluster
  • -http-addr: This is the address and port I’m going to use for the HTTP interface, which is also used by the rqlite command-line client, etc.
  • -raft-addr: This is the address and port that other nodes will connect to for intra-cluster traffic.
  • /usr/local/rqlite/db: This directory (which will be crated if it doesn’t exist) will hold the database and state inforamtion.

After issuing the command, I see a lot of info go by:

            _ _ _
           | (_) |
  _ __ __ _| |_| |_ ___
 | '__/ _  | | | __/ _ \   The lightweight, distributed
 | | | (_| | | | ||  __/   relational database.
 |_|  \__, |_|_|\__\___|
         | |
         |_|

[rqlited] 2020/05/25 11:08:14 rqlited starting, version v5.3.0, commit 5f762f01ecf2ad74ea398e20258239fca89777e2, branch master
[rqlited] 2020/05/25 11:08:14 go1.14, target architecture is amd64, operating system target is linux
[store] 2020/05/25 11:08:14 opening store with node ID 1
[store] 2020/05/25 11:08:14 ensuring directory at /usr/local/rqlite/db exists
[store] 2020/05/25 11:08:14 SQLite in-memory database opened
2020-05-25T11:08:14.117-0700 [INFO]  raft: Initial configuration (index=0): []
[store] 2020/05/25 11:08:14 bootstrap needed
2020-05-25T11:08:14.117-0700 [INFO]  raft: Node at 1.1.1.1:4002 [Follower] entering Follower state (Leader: "")
[rqlited] 2020/05/25 11:08:14 no join addresses set
2020-05-25T11:08:15.752-0700 [WARN]  raft: Heartbeat timeout from "" reached, starting election
2020-05-25T11:08:15.752-0700 [INFO]  raft: Node at 1.1.1.1:4002 [Candidate] entering Candidate state in term 2
2020-05-25T11:08:15.754-0700 [INFO]  raft: Election won. Tally: 1
2020-05-25T11:08:15.754-0700 [INFO]  raft: Node at 1.1.1.1:4002 [Leader] entering Leader state
[store] 2020/05/25 11:08:15 waiting for up to 2m0s for application of initial logs
[http] 2020/05/25 11:08:15 service listening on 1.1.1.1:4001
start node 2

Wait until you see the “service listening” before proceeding.

On node2, the command is the same with the addition of the -join parameter, which tells rqlite where to join the cluster:

# ./rqlited -node-id 2 -http-addr 2.2.2.2:4001 -raft-addr 2.2.2.2:4002 -join http://1.1.1.1:4001 /usr/local/rqlite/db

Note that rqlite is instructed to join the http address, not the RAFT address.

And now on node 3:

# ./rqlited -node-id 3 -http-addr 3.3.3.3:4001 -raft-addr 3.3.3.3:4002 -join http://1.1.1.1:4001 /usr/local/rqlite/db

And if you go back to node 1 you should see messages like this:

2020/05/25 11:09:53 [DEBUG] raft-net: 1.1.1.1:4002 accepted connection from: 3.3.3.3:36882

Restarting rqlite

At this point, you could stop all the rqlited sessions and restart them without the -join parameter, or with the other two nodes specified in -join.  It’s not necessary for a human to pick a leader and manually join, so you can write startup scripts without worrying about which node is coming up first, etc.

rqlite Command Line Client

Your command line client will be at /usr/local/rqlite/rqlite.  You can use it the same way you would the sqlite3 CLI, except that you’ll need to put in the hostname (and port, if you’ve changed it from the default).  Let’s create and populate a table:

root@rqlite3:/usr/local/rqlite# ./rqlite -H rqlite3.lowend.party
Welcome to the rqlite CLI. Enter ".help" for usage hints.
rqlite3.lowend.party:4001> create table planets (from_sun integer not null primary key, name varchar(30));
rqlite3.lowend.party:4001> insert into planets (from_sun, name) values (1, 'Mercury');
1 row affected
rqlite3.lowend.party:4001> insert into planets (from_sun, name) values (2, 'Venus');
1 row affected
rqlite3.lowend.party:4001> insert into planets (from_sun, name) values (3, 'Earth');
1 row affected

Now on node 1:

root@rqlite1:/usr/local/rqlite# ./rqlite -H rqlite1.lowend.party
Welcome to the rqlite CLI. Enter ".help" for usage hints.
rqlite1.lowend.party:4001> select * from planets;
+----------+---------+
| from_sun | name    |
+----------+---------+
| 1        | Mercury |
+----------+---------+
| 2        | Venus   |
+----------+---------+
| 3        | Earth   |
+----------+---------+
rqlite1.lowend.party:4001>

We can see that replication is working.

Testing rqlite High Avilability

Let's crash node 1 by hitting control-C in its rqlited session.  Looking then on rqlite2, we see:

2020-05-25T11:09:52.883-0700 [WARN]  raft: Heartbeat timeout from "1.1.1.1:4002" reached, starting election
2020-05-25T11:09:52.883-0700 [INFO]  raft: Node at 2.2.2.2:4002 [Candidate] entering Candidate state in term 3
2020-05-25T11:09:52.885-0700 [ERROR] raft: Failed to make RequestVote RPC to {Voter 1 1.1.1.1:4002}: dial tcp 1.1.1.1:4002: connect: connection refused
2020/05/25 11:09:53 [DEBUG] raft-net: 2.2.2.2:4002 accepted connection from: 3.3.3.3:36882
2020-05-25T11:09:53.627-0700 [INFO]  raft: Duplicate RequestVote for same term: 3
2020-05-25T11:09:54.288-0700 [WARN]  raft: Election timeout reached, restarting election
2020-05-25T11:09:54.288-0700 [INFO]  raft: Node at 2.2.2.2:4002 [Candidate] entering Candidate state in term 4
2020-05-25T11:09:54.290-0700 [ERROR] raft: Failed to make RequestVote RPC to {Voter 1 1.1.1.1:4002}: dial tcp 1.1.1.1:4002: connect: connection refused
2020-05-25T11:09:54.293-0700 [INFO]  raft: Election won. Tally: 2
2020-05-25T11:09:54.293-0700 [INFO]  raft: Node at 2.2.2.2:4002 [Leader] entering Leader state
2020-05-25T11:09:54.293-0700 [INFO]  raft: Added peer 1, starting replication
2020-05-25T11:09:54.293-0700 [INFO]  raft: Added peer 3, starting replication

Then let’s restart node1:

            _ _ _
           | (_) |
  _ __ __ _| |_| |_ ___
 | '__/ _  | | | __/ _ \   The lightweight, distributed
 | | | (_| | | | ||  __/   relational database.
 |_|  \__, |_|_|\__\___|
         | |
         |_|

[rqlited] 2020/05/25 11:10:43 rqlited starting, version v5.3.0, commit 5f762f01ecf2ad74ea398e20258239fca89777e2, branch master
[rqlited] 2020/05/25 11:10:43 go1.14, target architecture is amd64, operating system target is linux
[store] 2020/05/25 11:10:43 opening store with node ID 1
[store] 2020/05/25 11:10:43 ensuring directory at /usr/local/rqlite/db exists
[store] 2020/05/25 11:10:43 SQLite in-memory database opened
2020-05-25T11:10:43.144-0700 [INFO]  raft: Initial configuration (index=6): [{Suffrage:Voter ID:1 Address:1.1.1.1:4002} {Suffrage:Voter ID:2 Address:2.2.2.2:4002} {Suffrage:Voter ID:3 Address:3.3.3.3:4002}]
[store] 2020/05/25 11:10:43 no bootstrap needed
[rqlited] 2020/05/25 11:10:43 no join addresses set
2020-05-25T11:10:43.144-0700 [INFO]  raft: Node at 1.1.1.1:4002 [Follower] entering Follower state (Leader: "")
2020-05-25T11:10:44.825-0700 [WARN]  raft: Heartbeat timeout from "" reached, starting election
2020-05-25T11:10:44.825-0700 [INFO]  raft: Node at 1.1.1.1:4002 [Candidate] entering Candidate state in term 3
2020-05-25T11:10:44.829-0700 [INFO]  raft: Node at 1.1.1.1:4002 [Follower] entering Follower state (Leader: "")
2020/05/25 11:10:46 [DEBUG] raft-net: 1.1.1.1:4002 accepted connection from: 2.2.2.2:47180
[store] 2020/05/25 11:10:46 waiting for up to 2m0s for application of initial logs
[http] 2020/05/25 11:10:46 service listening on 1.1.1.1:4001
2020/05/25 11:10:47 [DEBUG] raft-net: 1.1.1.1:4002 accepted connection from: 2.2.2.2:47182

on node2:

2020/05/25 11:10:44 [DEBUG] raft-net: 2.2.2.2:4002 accepted connection from: 1.1.1.1:46620 2020-05-25T11:10:44.829-0700 [WARN]  raft: Rejecting vote request from 1.1.1.1:4002 since we have a leader: 2.2.2.2:4002 2020-05-25T11:10:46.479-0700 [INFO]  raft: pipelining replication to peer {Voter 1 1.1.1.1:4002}

You don’t really have to worry about all the “rejecting vote request”, “suffrage”, etc. info – it’s just there for informational purposes.  The point is that if you test your queries again, you’ll see that all three nodes are in perfect sync.  rqlite2 is now the leader, but if it were to die, one of the other two nodes would immediately hold an election per the RAFT protocol and pick a new leader.

Checking Cluster Status

rqlite comes with some sqlite3 language extensions, one of which is the .status command, which prints out a lot of information.  I’ve trimmed off some of it in the output below:

rqlite2.lowend.party:4001> .status
build:
  branch: master
  build_time: 2020-05-13T13:00:25-0400
  commit: 5f762f01ecf2ad74ea398e20258239fca89777e2
  version: v5.3.0
http:
  addr: 2.2.2.2:4001
  redirect: 3.3.3.3:4001
node:
  uptime: 4m15.059332819s
  start_time: 2020-05-25T11:19:45.205084102-07:00
leader:
    addr: 3.3.3.3:4002
    node_id: 3
  raft:
    latest_configuration: [{Suffrage:Voter ID:1 Address:1.1.1.1:4002} {Suffrage:Voter ID:2 Address:2.2.2.2:4002} {Suffrage:Voter ID:3 Address:3.3.3.3:4002}]
    num_peers: 2
  dir: /usr/local/rqlite/db
  metadata:
    1:
      api_addr: 1.1.1.1:4001
    2:
      api_addr: 2.2.2.2:4001
    3:
      api_addr: 3.3.3.3:4001
  nodes: [map[addr:1.1.1.1:4002 id:1] map[addr:2.2.2.2:4002 id:2] map[addr:3.3.3.3:4002 id:3]]

Here you can see that this node (rqlite2) has two peers, and the entire cluster configuration (and lots of other data) is displayed.

Securing rqlite

By default, rqlite transmits unencrypted.  We can fix that by putting HTTPS in place.  We’re going to use self-signed certificates in this tutorial.

First, generate the certificate:

root@rqlite1:/usr/local/rqlite# openssl req -x509 -nodes -newkey rsa:4096 -keyout key.pem -out cert.pem -days 3650
Generating a RSA private key
................................................................++++
....................................................................................................................................++++
writing new private key to 'key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:Oregon
Locality Name (eg, city) []:Portland
Organization Name (eg, company) [Internet Widgits Pty Ltd]:raindog
Organizational Unit Name (eg, section) []:raindog
Common Name (e.g. server FQDN or YOUR name) []:raindog
Email Address []:raindog308@raindog308.com

Now shut down all nodes and restart them with additional HTTPS parameters.  Here is rqlite1’s startup, with the new parameters bolded:

./rqlited -node-id 1 -http-addr 1.1.1.1:4001 -raft-addr 1.1.1.1:4002 -http-no-verify -node-encrypt -node-cert /usr/local/rqlite/cert.pem -node-key /usr/local/rqlite/key.pem /usr/local/rqlite/db

Backing Up rqlite

rqlite can do hot backups via the .backup command:

root@rqlite3:/usr/local/rqlite# ./rqlite -H rqlite3.lowend.party
Welcome to the rqlite CLI. Enter ".help" for usage hints.
rqlite3.lowend.party:4001> .backup bak.sqlite3
backup file written successfully
rqlite3.lowend.party:4001> 
EOF (CTRL+D)
root@rqlite3:/usr/local/rqlite# ls -l bak.sqlite3 
-rw-r--r-- 1 root root 8192 May 25 12:24 bak.sqlite3

This file is just a normal sqlite3 file:

root@rqlite1:/usr/local/rqlite# sqlite3 bak.sqlite3 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE planets (from_sun integer not null primary key, name varchar(30));
sqlite> 

From this I could create a dump file and then restore it:

root@rqlite3:/usr/local/rqlite# echo ".dump" | sqlite3 bak.sqlite3 > restore.dump
root@rqlite3:/usr/local/rqlite# cat restore.dump 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE planets (from_sun integer not null primary key, name varchar(30));
INSERT INTO planets VALUES(1,'Mercury');
INSERT INTO planets VALUES(2,'Venus');
INSERT INTO planets VALUES(3,'Earth');
INSERT INTO planets VALUES(4,'Mars');
COMMIT;
root@rqlite1:/usr/local/rqlite# ./rqlite -H rqlite3.lowend.party
Welcome to the rqlite CLI. Enter ".help" for usage hints.
rqlite3.lowend.party:4001> drop table planets;
1 row affected
rqlite3.lowend.party:4001> .restore restore.dump
last inserted ID: 4
rows affected: 1
database restored successfully

More Info

There’s more to rqlite, including APIs and clients for many programming languages.  Visit the Github project page for full details.

I'm Andrew, techno polymath and long-time LowEndTalk community Moderator. My technical interests include all things Unix, perl, python, shell scripting, and relational database systems. I enjoy writing technical articles here on LowEndBox to help people get more out of their VPSes.

1 Comment

  1. Thanks for your good post on rqlite. The version downloaded in your post is rather old though, 5.12.1 is much better.

    June 8, 2021 @ 8:17 am | Reply

Leave a Reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *