Introducing CockroachDB and Building a Failover Cluster with It on Ubuntu 16.04
- Transfer
- Tutorial
Preface from the translator: CockroachDB is a fairly young open-source relational database management system (Apache 2.0 license), originally created to be distributed (with horizontal scaling out of the box) and fault-tolerant . Its authors from Cockroach Labs, established in 2015, set out to "combine the richness of SQL functionality with the horizontal availability familiar to NoSQL solutions." This guide was written by one of the employees of the development company and published on the website of the cloud provider DigitalOcean in order to introduce IT specialists to this DBMS and demonstrate its use.
CockroachDB is an open source distributed DBMS (SQL) that provides data consistency, scalability and survivability.
Setting up CockroachDB is simple: install it on several servers ( nodes ) and combine them into a single whole for collaboration ( cluster ). All cluster nodes act "symmetrically" and offer access to the same data. If the storage for data needs to be increased, then with the architecture used, it is enough to create new nodes and attach to the cluster.
Details on how this works are described in the official documentation for the CockroachDB scalability model .(Note: if one of the nodes reaches the limit on the amount of stored data, CockroachDB will try to redistribute the data to other nodes where free space is still available. The replication factor is determined by the replication zones settings .)
Since this guide describes an unsafe installation without using SSL encryption, it is not recommended for use in production. For details about safe installation, you can refer to a more complete article, which describes the creation of certificates , and then a directory with them is added as a parameter at the start of each node.
Before you begin, you will need:
Find out the internal and external IP addresses of each server. Later in the manual, they will use the type designations
Each node in the cluster must have an executable file
We connect via SSH to the server, after which we download and install the latest version of the binary in the user's home directory
Verify that the DBMS executable is available by recognizing its version:
If after these steps the system does not find the command
Repeat this process on the other two servers that will become cluster nodes (
The first CockroachDB node -
To start the cluster, run the
It will start the node without SSL encryption (
Now that the node (and the cluster) has started to work, you can see information about it through the Admin UI administrative interface control panel integrated into CockroachDB to obtain information about the cluster. Go to the address
The fact that the node has been started successfully is visible in the interface:
When you hover the mouse over a notifying exclamation mark (!) In the NODE block , it will give an explanation: the cluster has a low level of replication ( Low Replication ), because you do not have enough nodes. With one node that is running now, the data will not be restored in the event of a fall (about how many nodes are required for stable operation, read below in paragraph 5) .
The situation will be corrected in the next stage, when we add two additional servers as two nodes of the cluster. Having three nodes, CockroachDB guarantees the availability of three copies of all data, ensuring their recovery in the event of a fall of one of the nodes.
Run the
Run the same command on the third server (
Go to the administrative interface (Admin UI) of any node (for example
All nodes are interconnected and have access to the same data.
Any record of data to any node means its presence in all other nodes of the cluster. The easiest way to demonstrate this is to use the sample data generation from CockroachDB and view the result using the built-in SQL client.
On the first node,
A database for the experiments appears
On the second node,
It is easy to see that the data created on one node (the database
CockroachDB guarantees data availability and data integrity in the event of a server failure. The DBMS remains stable in case of
To demonstrate this, delete one node from the cluster and see if data is still available. Then (in paragraph 6) we will again connect the node to the cluster and make sure that it will receive all the changes that occurred during its failure.
On the second node,
The answer will be 200 lines. You can exit the SQL client by clicking on+.
Now remove this node from the cluster and make sure that the data remains on other nodes. To do this, on the node,
Go to another node (for example
The same 200 lines are available after disconnecting one of the nodes.
Now demonstrate the correct response of CockroachDB to the resumption of host availability. To do this, we first delete some of the data, then return the disconnected node to the cluster, and then check that the data on it will be relevant.
On one of the working nodes (for example
Remaining 133 lines. Return to the node excluded from the cluster (
Run the SQL client here and check the number of rows in the table
The output should again display 133 . Thus, the offline node received changes upon returning to the cluster.
To delete all previously generated data, run in
To use CockroachDB from an application, you need:
The following is a general example - other data might be needed for your application.
Select and install the driver from the list of PostgreSQL compatible clients for your application.
The connection string must point to port 26257 and the IP address of any of the cluster nodes. Note that firewall must allow connections on this port.
For example, a connection in PHP / PDO for a user
Numerous examples of using PostgreSQL client drivers for different programming languages are available in the CockroachDB documentation .
The created cluster of three nodes helped demonstrate the basic functions of the CockroachDB DBMS and the ability to connect applications to it.
Since CockroachDB is actively developing, one day in your control panel you will see a message about the availability of a new version of the product ( There is a newer version of CockroachDB available ). By clicking the Update button , a link to the updated binary file will be available, the download and installation of which at the moment requires manual intervention.
For horizontal scaling of DBMS installation, i.e. adding new nodes, you need to repeat the steps that were performed for the second and third nodes: just install the executable file
Before running CockroachDB in production, please review the recommended settings . The main link to the official product documentation (in English) is www.cockroachlabs.com/docs .
Updated (May 11): The final release of CockroachDB 1.0 has already taken place . Its official announcement states that the DBMS is used in production by Baidu and Heroic Labs.
Introduction
CockroachDB is an open source distributed DBMS (SQL) that provides data consistency, scalability and survivability.
Setting up CockroachDB is simple: install it on several servers ( nodes ) and combine them into a single whole for collaboration ( cluster ). All cluster nodes act "symmetrically" and offer access to the same data. If the storage for data needs to be increased, then with the architecture used, it is enough to create new nodes and attach to the cluster.
Details on how this works are described in the official documentation for the CockroachDB scalability model .(Note: if one of the nodes reaches the limit on the amount of stored data, CockroachDB will try to redistribute the data to other nodes where free space is still available. The replication factor is determined by the replication zones settings .)
Please note that at the time of writing this manual [and its translation - approx. trans.] CockroachDB had beta status, so it is recommended that you use this document as a way to familiarize yourself with the technology, and not to deploy the product for use in mission-critical software.
The manual does not provide secure access to the cluster administrative interface; everyone can contact him, knowing the correct URL. If you intend to use this configuration in production, be sure to block access to port 8080 by firewall rules.
Since this guide describes an unsafe installation without using SSL encryption, it is not recommended for use in production. For details about safe installation, you can refer to a more complete article, which describes the creation of certificates , and then a directory with them is added as a parameter at the start of each node.
Training
Before you begin, you will need:
- Three servers with Ubuntu 16.04 with 2+ GB of RAM and the Private Networking [for DigitalOcean] option enabled . All of them should be in one region. As their names later in this article will be used:
cockroach-01,cockroach-02,cockroach-03. - An administrator user must be added on each server (not root, but with sudo privileges).
- All servers must have TCP traffic allowed on two ports. If UFW is configured as a firewall, you will need to configure it accordingly:
- 26257 - for interaction between nodes and with the application (
sudo ufw allow 26257/tcp); - 8080 - for Admin UI (
sudo ufw allow 8080/tcp) administrative interface .
- 26257 - for interaction between nodes and with the application (
- Optional: install and configure NTP on each server. (If you deploy a DBMS for a short test, this is not necessary.)
Find out the internal and external IP addresses of each server. Later in the manual, they will use the type designations
cockroach_01_public_ipand cockroach_01_private_ip. To find out the internal IP in DigitalOcean, go to the control panel and look at the Private IP field in the upper block of information.1. Install CockroachDB
Each node in the cluster must have an executable file
cockroach. The following describes the installation of CockroachDB on the first server ( cockroach-01), by analogy with which it is necessary to carry out operations on the remaining nodes. We connect via SSH to the server, after which we download and install the latest version of the binary in the user's home directory
cockroach:$ ssh sammy@cockroach_01_public_ip
$ wget https://binaries.cockroachdb.com/cockroach-latest.linux-amd64.tgz?s=do
$ tar -xf cockroach-latest.linux-amd64.tgz?s=do --strip=1 cockroach-latest.linux-amd64/cockroach
$ sudo mv cockroach /usr/local/bin
Verify that the DBMS executable is available by recognizing its version:
$ cockroach version
Build Tag: v1.0-rc.1-dirty
Build Time: 2017/05/01 18:33:34
Distribution: CCL
Platform: linux amd64
Go Version: go1.8.1
C Compiler: gcc 6.3.0
Build SHA-1: 2d4d1ab5c42efb5accf73c9876e6ffd934fdc9e6
Build Type: release
If after these steps the system does not find the command
cockroach, make sure that the file has actually been downloaded, unpacked and moved. Repeat this process on the other two servers that will become cluster nodes (
cockroach-02and cockroach-03). After that, you can configure the cluster itself.2. Configure the first node
The first CockroachDB node -
cockroach-01- will start the cluster. But there is nothing special in its configuration: it just starts as one DBMS server, to which others then join. To start the cluster, run the
cockroach-01following command:cockroach start --insecure --background --advertise-host=cockroach_01_private_ipIt will start the node without SSL encryption (
--insecure), return the command line for further work ( --background) and raise the node to communicate with other nodes through the internal IP ( --advertise-host). The above cockroach_01_private_ipshould be replaced with the real internal IP of the first server.Please note that when starting the node, you can set it with a number of additional flags that change the server behavior (for example, the directory in which the data is stored). All these flags are described in official documentation (in English).
Now that the node (and the cluster) has started to work, you can see information about it through the Admin UI administrative interface control panel integrated into CockroachDB to obtain information about the cluster. Go to the address
http://cockroach_01_public_ip:8080(now public IP is already used). The fact that the node has been started successfully is visible in the interface:
When you hover the mouse over a notifying exclamation mark (!) In the NODE block , it will give an explanation: the cluster has a low level of replication ( Low Replication ), because you do not have enough nodes. With one node that is running now, the data will not be restored in the event of a fall (about how many nodes are required for stable operation, read below in paragraph 5) .
The situation will be corrected in the next stage, when we add two additional servers as two nodes of the cluster. Having three nodes, CockroachDB guarantees the availability of three copies of all data, ensuring their recovery in the event of a fall of one of the nodes.
3. Adding the second and third nodes to the cluster
Run the
cockroach-02command on the server cockroachin the same way as it was done for the first node in the previous step, but with the only difference. In the parameters of the DBMS, we indicate that it is necessary to join the first node through the internal IP address. In the command below replace both variables with IP ( cockroach_02_private_ipand cockroach_01_private_ip):$ cockroach start --insecure --background \
--advertise-host=cockroach_02_private_ip \
--join=cockroach_01_private_ip:26257
Run the same command on the third server (
cockroach-03), specifying its internal IP there. Attach it to the first node too:$ cockroach start --insecure --background \
--advertise-host=cockroach_03_private_ip \
--join=cockroach_01_private_ip:26257
Go to the administrative interface (Admin UI) of any node (for example
http://cockroach_03_public_ip:8080) and make sure that the cluster now consists of 3 nodes: All nodes are interconnected and have access to the same data.
4 (optional). Demonstration of data transfer between nodes
Any record of data to any node means its presence in all other nodes of the cluster. The easiest way to demonstrate this is to use the sample data generation from CockroachDB and view the result using the built-in SQL client.
On the first node,
cockroach-01generate data:$ cockroach gen example-data | cockroach sqlA database for the experiments appears
startrek. Now you can start the SQL client and see the list of databases in the cluster:$ cockroach sql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| pg_catalog |
| startrek |
| system |
+--------------------+
Please note that CockroachDB works with its own SQL dialect, which has extensions to the SQL standard that are different from those offered by other DBMSs.
On the second node,
cockroach-02you can run the same commands:$ cockroach sql> SHOW DATABASES;It is easy to see that the data created on one node (the database
startrek) is available on other nodes. You can view the list of databases existing in the cluster on the DATABASES tab of the Admin UI interface on any of the nodes (for example, http://cockroach_01_public_ip:8080/#/databases/).5 (optional). Removing a node from a cluster
CockroachDB guarantees data availability and data integrity in the event of a server failure. The DBMS remains stable in case of
(n-1)/2node failure , where nis the total number of nodes in the cluster. Thus, in our example with three nodes, the fall of one node is possible (without losing any data). To demonstrate this, delete one node from the cluster and see if data is still available. Then (in paragraph 6) we will again connect the node to the cluster and make sure that it will receive all the changes that occurred during its failure.
On the second node,
cockroach-02run the SQL client and count the number of rows in the table quotes:$ cockroach sql> SELECT COUNT(*) FROM startrek.quotes;The answer will be 200 lines. You can exit the SQL client by clicking on
Now remove this node from the cluster and make sure that the data remains on other nodes. To do this, on the node,
cockroach-02complete the CockroachDB process with the command:$ cockroach quitGo to another node (for example
cockroach-03), start the SQL client and check the number of rows in the same table:$ cockroach sql SELECT COUNT(*) FROM startrek.quotes;The same 200 lines are available after disconnecting one of the nodes.
6 (optional). Reconnecting a node to a cluster
Now demonstrate the correct response of CockroachDB to the resumption of host availability. To do this, we first delete some of the data, then return the disconnected node to the cluster, and then check that the data on it will be relevant.
On one of the working nodes (for example
cockroach-03), delete part of the data from the table quotes:
> DELETE FROM startrek.quotes WHERE episode > 50;
> SELECT COUNT(*) FROM startrek.quotes;
Remaining 133 lines. Return to the node excluded from the cluster (
cockroach-02) and run it again:$ cockroach start --insecure --background \
--advertise-host=cockroach_02_private_ip \
--join=cockroach_01_private_ip:26257
Run the SQL client here and check the number of rows in the table
quotes$ cockroach sql
> SELECT COUNT(*) FROM startrek.quotes;
The output should again display 133 . Thus, the offline node received changes upon returning to the cluster.
To delete all previously generated data, run in
cockroach sql:
> DROP TABLE quotes;
> DROP TABLE episodes;
> DROP DATABASE startrek;
7 (optional). Application Connection
To use CockroachDB from an application, you need:
- driver supported by the application (CockroachDB works with drivers for PostgreSQL);
- connection string.
The following is a general example - other data might be needed for your application.
Select and install the driver from the list of PostgreSQL compatible clients for your application.
Please note that although CockroachDB supports the PostgreSQL protocol, the syntax of its SQL language is different and therefore this DBMS is not a ready-made replacement for PostgreSQL.
The connection string must point to port 26257 and the IP address of any of the cluster nodes. Note that firewall must allow connections on this port.
For example, a connection in PHP / PDO for a user
sammyto a database bankon a local machine ( localhost) would look like this:PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=disable',
'sammy', null, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => true,
));
Numerous examples of using PostgreSQL client drivers for different programming languages are available in the CockroachDB documentation .
Conclusion
The created cluster of three nodes helped demonstrate the basic functions of the CockroachDB DBMS and the ability to connect applications to it.
Since CockroachDB is actively developing, one day in your control panel you will see a message about the availability of a new version of the product ( There is a newer version of CockroachDB available ). By clicking the Update button , a link to the updated binary file will be available, the download and installation of which at the moment requires manual intervention.
For horizontal scaling of DBMS installation, i.e. adding new nodes, you need to repeat the steps that were performed for the second and third nodes: just install the executable file
cockroachand run it with a connection to the cluster.Before running CockroachDB in production, please review the recommended settings . The main link to the official product documentation (in English) is www.cockroachlabs.com/docs .
Updated (May 11): The final release of CockroachDB 1.0 has already taken place . Its official announcement states that the DBMS is used in production by Baidu and Heroic Labs.