Rqlite
What: rqlite is a lightweight, distributed relational database built on SQLite.
Installation
Linux and macOS
Check the official binaries: https://github.com/rqlite/rqlite/releases
Installation on Raspberry Pi
Note: There are no rqlite binaries for the RPi, however it is possible to compile it quite easily. You only need to compile it on *one* RPi, after that you can simply copy the resulting binaries to other RPis of your cluster.
Compilation
- as root
- install latest version of golang for ARMv6 architecture (https://golang.org/dl/):
cd /usr/src wget https://dl.google.com/go/go1.10.2.linux-armv6l.tar.gz tar -C /usr/local -xzvf go1.10.2.linux-armv6l.tar.gz export PATH=$PATH:/usr/local/go/bin
- prepare environment for building rqlite (as root or regular user)
cd mkdir go cd go mkdir bin pkg src export GOPATH=$PWD
- build rqlite (this will take a while, monitor with (h)top if you like)
go get -u -t github.com/rqlite/rqlite/... # yes you need the '...' at the end cd $GOPATH/src/github.com/rqlite/rqlite/cmd/rqlite go build -v . sudo cp rqlite /usr/local/bin cd $GOPATH/src/github.com/rqlite/rqlite/cmd/rqlited go build -v . cp rqlited /usr/local/bin
That's it!
Installation on other nodes
You just need to copy the binaries rqlite and rqlited to /usr/local/bin.
A simple 3 nodes cluster
This is assuming that you have working rqlite and rqlited on 3 different machines.
Note: According to the documentation you need at least 3 nodes to test a fault tolerant cluster. In practice, to be fault tolerant, you need N/2 + 1 nodes up and running where N is the number of nodes in the cluster, to be fault tolerant.
Create initial leader
To create a cluster you must first launch a node that can act as the initial leader. Do this as follows on host1:
rqlited -node-id 1 -http-addr host1:4001 -raft-addr host1:4002 ~/node
Note: host1 needs to resolve to an IP reachable by the other nodes. You can also directly used an IP, which can be useful for running rqlite with a VPN.
Create subsequent node N
To join a second node to this leader, execute the following command on hostN:
rqlited -node-id 2 -http-addr hostN:4001 -raft-addr hostN:4002 -join http://host1:4001 ~/node
or
rqlited -http-addr 10.0.0.2:4001 -raft-addr 10.0.0.2:4002 -join http://10.0.0.10:4001 ~/node
Note: hostN needs to resolve to an IP reachable by the other nodes. You can also directly used an IP, which can be useful for running rqlite with a VPN.
Testing the cluster!
on leader/host1
- make sure rqlited is running (see above)
- create some entries
rqlite -H host1
# to create a table:
host1> CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, name TEXT)
# to view tables in db
host1> .tables
# to make a entry into the db
host1> INSERT INTO foo(name) VALUES("fiona")
# to display the entry on that table
host1> SELECT * FROM foo
on hostN
- make sure rqlited is running (see above)
- create some entries
rqlite -H host1 # YES it's host1!
# to view tables in db
host1> .tables
# to make a entry into the db
host1> INSERT INTO foo(name) VALUES("anotherentry")
# to display the entry on that table
host1> SELECT * FROM foo
More information
The documentation of rqlite has more information and details about specific commands and tweaks: https://github.com/rqlite/rqlite/tree/master/DOC
Use rqlite with your sqlalchemy application
install pyrqlite
git clone https://github.com/rqlite/pyrqlite.git pip3 install ./pyrqlite
install sqlalchemy-rqlite
git clone https://github.com/rqlite/sqlalchemy-rqlite.git cd sqlalchemy-rqlite sudo python3 ./setup.py install
insert into your app:
(replace IP & PORT with yours)
registry.register("rqlite.pyrqlite", "sqlalchemy_rqlite.pyrqlite", "dialect")
app.config['SQLALCHEMY_DATABASE_URI'] = 'rqlite+pyrqlite://localhost:4001/'