Introducing CockroachDB and creating a failover cluster with it on Ubuntu 16.04
Preface from the translator: CockroachDB is a fairly young open-source relational database (Apache 2.0 license), originally created to be distributed (with horizontal scaling out of the box) and fault tolerant . Its authors from Cockroach Labs, created in 2015, set themselves the goal of “combining the richness of SQL functionality with the horizontal availability common to NoSQL solutions”. This manual was written by one of the employees of the company-developer and published on the website of the cloud provider DigitalOcean in order to acquaint IT specialists with this DBMS and demonstrate its use.
CockroachDB is an open source distributed database management system (SQL) that provides data consistency, scalability and survival.
Setting up CockroachDB is simple: install it on multiple servers ( nodes ) and combine them into a single unit for collaboration ( cluster ). All cluster nodes operate "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 on the CockroachDB scalability model . (Approx. Translation: when one of the nodes reaches a limit on the amount of stored data, CockroachDB will try to redistribute data to other nodes where free space is still available. Replication factor is determined by the replication zones settings.)
Since this guide describes an insecure installation without using encryption with SSL, it is not recommended for use in production. For more details on a secure installation, you can refer to a more complete article that describes how to create certificates , and then add a directory with them as a parameter when you start each of the nodes.
Before you begin, you will need:
Find out the internal and external IP addresses of each server. Further in the manual for them will be used the notation of the type
Each cluster node must have an executable
We connect via SSH to the server, then download and install the latest version of the
Check that the executable DBMS file is available by learning its version:
If after these steps the system does not find the
Repeat these operations on two other servers that will become cluster nodes (
The first CockroachDB node,
To start the cluster, execute the following command on
It will start the node without SSL encryption (
Now that the node (and the cluster) has started working, you can view information about it through the Admin UI admin interface control panel built into CockroachDB for information about the cluster. Visit
The fact that the node has been successfully launched can be seen in the interface:
A notification exclamation mark (!) In the NODE block when you hover the mouse cursor 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 (see how many nodes are required for stable operation, see below, in clause 5) .
The situation will be corrected in the next stage, when we add two additional servers as two cluster nodes. Having three nodes, CockroachDB guarantees the availability of three copies of all data, ensuring their recovery in the event of a drop in one of the nodes.
Run the
Run the same command on the third server (
Log into the administrative interface (Admin UI) of any node (for example,
All nodes are interconnected and have access to the same data.
Any data record in any node means their presence in all other nodes of the cluster. The easiest way to demonstrate this is to use test data generation from CockroachDB and view the result using the embedded SQL client.
On the first
A database for
On the second node
It is easy to see that the data created on one node (the
CockroachDB guarantees data availability and integrity in the event of server failure. The DBMS remains stable in the event of a failure of
To demonstrate this, remove one node from the cluster and see if the data is still available. Then (in clause 6), we will rejoin the node to the cluster and make sure that it receives all the changes that occurred during its failure.
On the second node of
The answer will be 200 lines. You can exit the SQL client by pressing <Ctrl> + <c>.
Now remove this node from the cluster and make sure that the data remains on the other nodes. To do this, on the
Go to another node (for example,
The same 200 lines are available after disconnecting one of the nodes.
Now we’ll demonstrate the correct CockroachDB response to node availability. To do this, we first delete part 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,
133 lines left. Return to the node excluded from the cluster (
Start the SQL client here and check the number of rows in the
The output should again show 133 . Thus, the offline node received changes upon returning to the cluster.
To remove all previously generated data, run in
To use CockroachDB from within the application, you need:
The following is a general example - your application may need other data.
Select and install a driver from the PostgreSQL compliant client list for your application.
The connection string must point to port 26257 and the IP address of any of the nodes in the cluster. Note that the firewall must allow connections to this port.
For example, a connection in PHP / PDO for the
Numerous examples of using PostgreSQL client drivers for different programming languages ​​are available in the CockroachDB documentation .
The created cluster of three nodes helped to demonstrate the basic functions of the CockroachDB DBMS and the ability to connect an application 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 the Update button, a link to the updated binary file will be available, download and installation of which currently requires manual intervention.
For horizontal scaling of the DBMS installation, i.e. adding new nodes, you need to repeat the steps that were performed for the second and third nodes: it is enough to install the
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 . In its official announcement it is stated that the DBMS is used in production by Baidu and Heroic Labs.
Introduction
CockroachDB is an open source distributed database management system (SQL) that provides data consistency, scalability and survival.
Setting up CockroachDB is simple: install it on multiple servers ( nodes ) and combine them into a single unit for collaboration ( cluster ). All cluster nodes operate "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 on the CockroachDB scalability model . (Approx. Translation: when one of the nodes reaches a limit on the amount of stored data, CockroachDB will try to redistribute data to other nodes where free space is still available. Replication factor is determined by the replication zones settings.)
Please note that at the time of creation of this manual [and its translation - approx. trans.] CockroachDB was in beta status, so it is recommended that you use this document as a way of becoming familiar with the technology, and not for deploying the product for use in critical software.
The manual does not provide secure access to the cluster administrative interface; anyone can access it, knowing the correct URL. If you are going to use this configuration in production, do not forget to close access to port 8080 by the firewall rules.
Since this guide describes an insecure installation without using encryption with SSL, it is not recommended for use in production. For more details on a secure installation, you can refer to a more complete article that describes how to create certificates , and then add a directory with them as a parameter when you start each of the nodes.
Training
Before you begin, you will need:
- Three servers with Ubuntu 16.04 with 2+ GB of RAM and the option included Private Networking [for DigitalOcean]. All of them must be in the same region. The following names will be used as their names in the article:
cockroach-01,cockroach-02,cockroach-03. - Each server must have an admin user added (not root, but with sudo privileges).
- All servers must have TCP traffic on two ports. If UFW is configured as a firewall, you need to configure it accordingly:
- 26257 - for interaction between nodes and with the application (
sudo ufw allow 26257/tcp); - 8080 - for the Admin UI administrative interface (
sudo ufw allow 8080/tcp).
- 26257 - for interaction between nodes and with the application (
- Optional: install and configure NTP on each server. (If you deploy a DBMS for short testing, this is not necessary.)
Find out the internal and external IP addresses of each server. Further in the manual for them will be used the notation of the type
cockroach_01_public_ip and 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. Installing CockroachDB
Each cluster node must have an executable
cockroach file. The following describes the installation of CockroachDB on the first server ( cockroach-01 ), by analogy with which it is necessary to perform operations on the other nodes.We connect via SSH to the server, then download and install the latest version of the
cockroach binary in the user's home directory: $ 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 Check that the executable DBMS file is available by learning 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
cockroach command, make sure that the file was actually downloaded, unpacked and moved.Repeat these operations on two other servers that will become cluster nodes (
cockroach-02 and cockroach-03 ). After that, you can configure the cluster itself.2. Configure the first node
The first CockroachDB node,
cockroach-01 , will launch a cluster. But there is nothing special about its configuration: it simply runs as one DBMS server, to which the others join.To start the cluster, execute the following command on
cockroach-01 : cockroach start --insecure --background --advertise-host=cockroach_01_private_ip It 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 via the internal IP ( --advertise-host ). The above cockroach_01_private_ip must be replaced with the real internal IP of the first server.Please note that when the node starts, you can set it with a number of additional flags that change the behavior of the server (for example, the directory in which data is stored). All these flags are described in the official documentation (in English).
Now that the node (and the cluster) has started working, you can view information about it through the Admin UI admin interface control panel built into CockroachDB for information about the cluster. Visit
http://cockroach_01_public_ip:8080 (now public IP is used).The fact that the node has been successfully launched can be seen in the interface:
A notification exclamation mark (!) In the NODE block when you hover the mouse cursor 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 (see how many nodes are required for stable operation, see below, in clause 5) .
The situation will be corrected in the next stage, when we add two additional servers as two cluster nodes. Having three nodes, CockroachDB guarantees the availability of three copies of all data, ensuring their recovery in the event of a drop in one of the nodes.
3. Adding the second and third nodes to the cluster
Run the
cockroach-02 command on the server cockroach-02 in 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_ip and 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 Log into 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 data record in any node means their presence in all other nodes of the cluster. The easiest way to demonstrate this is to use test data generation from CockroachDB and view the result using the embedded SQL client.
On the first
cockroach-01 node, generate the data: $ cockroach gen example-data | cockroach sql A database for
startrek experiments will startrek . Now you can run 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 SQL standard extensions that differ from those offered by other DBMSs.
On the second node
cockroach-02 you can run the same commands: $ cockroach sql > SHOW DATABASES; It is easy to see that the data created on one node (the
startrek database) is available on other nodes. You can view the list of existing databases in the cluster on the Admin UI tab DATABASES tab 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 integrity in the event of server failure. The DBMS remains stable in the event of a failure of
(n-1)/2 nodes, where n is the total number of nodes in the cluster. Thus, in our example with three nodes, it is possible for a single node to fall (without losing any data).To demonstrate this, remove one node from the cluster and see if the data is still available. Then (in clause 6), we will rejoin the node to the cluster and make sure that it receives all the changes that occurred during its failure.
On the second node of
cockroach-02 launch the SQL client and count the number of rows in the quotes table: $ cockroach sql > SELECT COUNT(*) FROM startrek.quotes; The answer will be 200 lines. You can exit the SQL client by pressing <Ctrl> + <c>.
Now remove this node from the cluster and make sure that the data remains on the other nodes. To do this, on the
cockroach-02 node, complete the CockroachDB process with the command: $ cockroach quit Go to another node (for example,
cockroach-03 ), run 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). Reattach node to cluster
Now we’ll demonstrate the correct CockroachDB response to node availability. To do this, we first delete part 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 quotes table: > DELETE FROM startrek.quotes WHERE episode > 50; > SELECT COUNT(*) FROM startrek.quotes; 133 lines left. 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 Start the SQL client here and check the number of rows in the
quotes table. $ cockroach sql > SELECT COUNT(*) FROM startrek.quotes; The output should again show 133 . Thus, the offline node received changes upon returning to the cluster.
To remove 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 within the application, you need:
- driver supported by the application (CockroachDB works with drivers for PostgreSQL);
- connection string.
The following is a general example - your application may need other data.
Select and install a driver from the PostgreSQL compliant client list 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 nodes in the cluster. Note that the firewall must allow connections to this port.
For example, a connection in PHP / PDO for the
sammy user to the bank database on the 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 to demonstrate the basic functions of the CockroachDB DBMS and the ability to connect an application 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 the Update button, a link to the updated binary file will be available, download and installation of which currently requires manual intervention.
For horizontal scaling of the DBMS installation, i.e. adding new nodes, you need to repeat the steps that were performed for the second and third nodes: it is enough to install the
cockroach executable file and run it with 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 . In its official announcement it is stated that the DBMS is used in production by Baidu and Heroic Labs.
Source: https://habr.com/ru/post/327640/
All Articles