Introduction
PostgreSQL is a well-known, "solid and mature" (C) SQL database management system.
RocksDB is a persistent key-value store and C/C++ library.
PgRocks is a plugin that uses DNA-like PostgreSQL data structure to link and easily migrate data between a PostgreSQL database and a RocksDB store.
In this tutorial, you'll install and configure PgRocks, and by doing so you will set up a data bridge between PostgreSQL and RocksDB.
This will require modifications in source code of both PostgreSQL and RocksDB, their compilation and installation, or re-installation, if they are already on the system.
In the end of this guide you will see examples of how the plugin works.
When you're finished, you'll basically understand how to flow data from PostgreSQL to RocksDB and back with the new plugin.
Prerequisites
Before you begin this guide you'll need the following:
Step 1 - Preparing all necessary source code trees
First, download the latest stable (postgresql-xx.x) source code for PostgreSQL.
(NB: Before selecting the version of PostgreSQL for running this tutorial, check our github repository https://github.com/kl3eo/PgRocks for the latest supported version of PG. We try to be in sync with the official PG:)
Unpack the source code tree into a new work directory, e.g. work_pgrocks:
mkdir work_pgrocks
cd work_pgrocks
wget https://ftp.postgresql.org/pub/source/vxx.x/postgresql-xx.x.tar.gz
tar zxvf postgresql-xx.x.tar.gz
In the same work directory (i.e. work_pgrocks), get the code for RocksDB and PgRocks with git:
git clone https://github.com/facebook/rocksdb
git clone https://github.com/kl3eo/PgRocks
You have three new catalogs in your work directory, postgresql-xx.x, rocksdb and PgRocks.
Further in Steps 2 and 3 you will modify the RocksDB and PostgreSQL with files from the PgRocks tree.
Step 2 - Patching the RocksDB tree, then compiling and installing the new RocksDB libraries
First, locate the file with the code for patching RocksDB. It is the file named rocksdb_get2.patch at the top level of PgRocks catalog.
Apply this patch:
cd rocksdb
git apply ../PgRocks/rocksdb_get2.patch
Before you proceed with compilation of the new RocksDB libraries, check that all the libs required for RocksDB are installed on the system. Methods of their installation depending on the system are explained at https://github.com/facebook/rocksdb/blob/master/INSTALL.md
For example, to install the required libs on Debian or Ubuntu, run the following command as root (or with sudo prefix if run from a user account):
apt-get install libgflags-dev libsnappy-dev zlib1g-dev libbz2-dev liblz4-dev libzstd-dev libnuma-dev
Now you're ready to compile and install (or possibly re-install) RocksDB. Go ahead with it:
make static_lib
make shared_lib
Everything fine, go ahead and install (as root):
make install
Optionally, you may compile and install a useful tool ldb for working with RocksDB store. Go to the tools and run these commands:
g++ -std=gnu++11 ldb.cc -o ldb -lrocksdb
cp -a ldb /usr/local/bin
cd ../..
Now you're ready to proceed to Step 3 and modify the code in PostgreSQL tree, then compile and install it.
Step 3 - Modifying the code in PostgreSQL tree, compiling and installing it
Run the following commands to overwrite the source files in PostgreSQL tree with newer files of the same name from PgRocks/src catalog:
cd postgresql-xx.x
cp -a ../PgRocks/src/* src/
Then configure the PostgreSQL source code:
./configure
This command creates a file named Makefile.global in the src catalog. You will have to open it in the editor and modify it, manually replacing only one line, the one that starts with "LIBS". Replace it with another line you will find in the file named compile at the top level of PgRocks source tree.
These lines only differ in libs required by gcc to compile the code.
If in your src/Makefile.global there is the line:
LIBS = -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm
than you have to replace it with this line:
LIBS = -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm -lsnappy -lgflags -lbz2 -llz4 -lzstd -lnuma -lrocksdb
After editing the src/Makefile.global, you are ready to compile PostgreSQL:
make
Now that the compilation is complete and you're ready to install, please make sure that no older version of PostgreSQL exists on your system.
If another version of PostgreSQL had been automatically installed (and probably never used), remove it now, e.g. on Debian and Ubuntu run the following command as root:
apt remove PostgreSQL-10
Now as root run the following command from the top level of PostgreSQL tree:
make install
Make sure the newly installed libs will be found by the linker:
ldconfig
Only if you're installing PostgreSQL for the very first time, then run the following commands, also as root:
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Start the new PostgreSQL daemon as user postgres to see if everything is fine:
su - postgres
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/psql template1
If you are able to connect to the daemon with this command, then you see this prompt:
template1=#
You're ready to make new functions visible by PostgreSQL at the next Step 4.
Step 4 - Making new internal functions visible by PostgreSQL
Open the file named pg_catalog.pg_proc.txt found at the top level of PgRocks catalog in the editor, select, copy and then paste everything into the prompt that you have opened at the previous Step. This will result in many lines of output like this:
(...)
drop table if exists jar;
DROP TABLE
delete from pg_catalog.pg_proc where proname='rocks_get_node_number';
DELETE 0
create table jar as select * from pg_catalog.pg_proc where oid = 89;
SELECT 1
(...)
If you have made mistakes while copy-pasting, repeat the whole Step 4 from beginning to end, copy and paste ALL the lines from the pg_catalog.pg_proc.txt into the prompt.
This is it, and now you are ready to start using PgRocks.
Step 5 - Creating a test suit for the plugin and running examples
In this Step you will see how the plugin works.
You will only use three of new PostgreSQL/PgRocks functions, one for writing data to RocksDB, one for reading from it, and one for closing the open connection.
-- First, create a test database as user postgres.
su - postgres
/usr/local/pgsql/bin/createdb test
-- Second, create there a table named players and populate it with test data:
/usr/local/pgsql/bin/psql test
create table players(name text, aka text, dob date, weight float, height int, last_seen timestamp);
CREATE TABLE
insert into players values ('Peter Stevens','Boss', '1956-06-30', 85.4,169,'2017-10-25 17:30');
insert into players values ('Mike Palmer','Hippy', '1988-12-06', 75.5,184,'2018-02-28 14:20');
insert into players values ('Dorothy Brown','Miss D', '1992-07-12', 64.3,172,'2018-03-15 09:25');
insert into players values ('Linda Chambers','Jamaica', '1987-03-10', 57.7,180,'2018-04-01 14:00');
insert into players values ('Claude Moulin','French', '1944-10-17', 68.1,170,'2017-09-15 12:30');
select * from players;
name | aka | dob | weight | height | last_seen
----------------+---------+------------+--------+--------+---------------------
Peter Stevens | Boss | 1956-06-30 | 85.4 | 169 | 2017-10-25 17:30:00
Mike Palmer | Hippy | 1988-12-06 | 75.5 | 184 | 2018-02-28 14:20:00
Dorothy Brown | Miss D | 1992-07-12 | 64.3 | 172 | 2018-03-15 09:25:00
Linda Chambers | Jamaica | 1987-03-10 | 57.7 | 180 | 2018-04-01 14:00:00
Claude Moulin | French | 1944-10-17 | 68.1 | 170 | 2017-09-15 12:30:00
(5 rows)
-- Third, create another table named venues and also populate it with test data:
create table venues (address text, capacity int);
insert into venues values ('Baker St. 221b', 330);
insert into venues values ('Downing St. 10', 50);
insert into venues values ('Marylebone Rd', 1000);
-- Fourth, create an empty "DNA" table v1_dna_1 (v1 stands for "version one") for RocksDB "store #1";
create table v1_dna_1 (tbl text, rev int, key bigint);
-- Fifth, pack the data from the two test table into a new RocksDB store located at /tmp/rocksdb_1 on your file system - i.e. to store#1.
For this you will use the new internal PostgreSQL/PgRocks function row_to_json_rocks, which creates a new store #1, if there is none,
and writes all rows from the given PG table into this new store as JSON data.
insert into v1_dna_1 (tbl, rev, key) select 'players', 1, row_to_json_rocks(1,players) from players;
insert into v1_dna_1 (tbl, rev, key) select 'venues', 1, row_to_json_rocks(1,venues) from venues;
select rocks_close();
Make sure to close the RocksDB connection each time after write operations calling function rocks_close.
N.B. You have inserted "1" into rev column of DNA-table. By doing this you assume that all the rows are of revision one. You won't need the data revisions yet.
The function row_to_json_rocks just used takes two arguments, (1) the number of the store and (2) the name of the table.
It returns the key of type bigint where the first digit is the number of the node (1 by default), and the other digits are epoch time in microseconds, measured at the moment of packing the record into the store.
These keys are guaranteed to be unique and as such they are assigned to the values in the RocksDB store.
You will keep these keys in the column key of the DNA table:
select * from v1_dna_1;
tbl | rev | key
---------+-----+-------------------
players | 1 | 11522687676247617
players | 1 | 11522687676247896
players | 1 | 11522687676247929
players | 1 | 11522687676247951
players | 1 | 11522687676247972
venues | 1 | 11522687676275189
venues | 1 | 11522687676275254
venues | 1 | 11522687676275269
(8 rows)
-- Sixth, drop the tables players and venues - since you keep all the data in RocksDB, you don't need the original tables any more.
drop table players;
drop table venues;
-- Seventh, take a look at our data how it is stored in RocksDB.
You will need the bigint keys printed as hexadecimal for searching with ldb:
select '0x' || LPAD(to_hex(key), 16, '0') as hex_key from v1_dna_1;
hex_key
--------------------
0x0028efd2c9ccee41
0x0028efd2c9ccef58
0x0028efd2c9ccef79
0x0028efd2c9ccef8f
0x0028efd2c9ccefa4
0x0028efd2c9cd59f5
0x0028efd2c9cd5a36
0x0028efd2c9cd5a45
(8 rows)
Quit the psql shell and look at our data packed as JSON in our RocksDB store:
ldb --db=/tmp/rocksdb_1 scan --key_hex
0x0028EFD2C9CCEE41 : {"name":"Peter Stevens","aka":"Boss","dob":"1956-06-30","weight":85.4,"height":169,"last_seen":"2017-10-25T17:30:00"}
0x0028EFD2C9CCEF58 : {"name":"Mike Palmer","aka":"Hippy","dob":"1988-12-06","weight":75.5,"height":184,"last_seen":"2018-02-28T14:20:00"}
0x0028EFD2C9CCEF79 : {"name":"Dorothy Brown","aka":"Miss D","dob":"1992-07-12","weight":64.3,"height":172,"last_seen":"2018-03-15T09:25:00"}
0x0028EFD2C9CCEF8F : {"name":"Linda Chambers","aka":"Jamaica","dob":"1987-03-10","weight":57.7,"height":180,"last_seen":"2018-04-01T14:00:00"}
0x0028EFD2C9CCEFA4 : {"name":"Claude Moulin","aka":"French","dob":"1944-10-17","weight":68.1,"height":170,"last_seen":"2017-09-15T12:30:00"}
0x0028EFD2C9CD59F5 : {"address":"Baker St. 221b","capacity":330}
0x0028EFD2C9CD5A36 : {"address":"Downing St. 10","capacity":50}
0x0028EFD2C9CD5A45 : {"address":"Marylebone Rd","capacity":1000}
You may now search the data in this store by the key:
ldb --db=/tmp/rocksdb_1 get 0x0028EFD2C9CCEF8F --key_hex
{"name":"Linda Chambers","aka":"Jamaica","dob":"1987-03-10","weight":57.7,"height":180,"last_seen":"2018-04-01T14:00:00"}
-- Eighth, get back the data into table players dropped earlier, - suppose you need it again.
You will use the counterpart function rocks_json_to_record, which also takes two arguments - (1) number of the store and (2) the key, and returns the stored record:
create table players as select d.* from v1_dna_1, rocks_json_to_record(1,v1_dna_1.key) d(name text, aka text, dob date, weight float, height int, last_seen timestamp) where v1_dna_1.tbl = 'players';
SELECT 5
select * from players;
name | aka | dob | weight | height | last_seen
----------------+---------+------------+--------+--------+---------------------
Peter Stevens | Boss | 1956-06-30 | 85.4 | 169 | 2017-10-25 17:30:00
Mike Palmer | Hippy | 1988-12-06 | 75.5 | 184 | 2018-02-28 14:20:00
Dorothy Brown | Miss D | 1992-07-12 | 64.3 | 172 | 2018-03-15 09:25:00
Linda Chambers | Jamaica | 1987-03-10 | 57.7 | 180 | 2018-04-01 14:00:00
Claude Moulin | French | 1944-10-17 | 68.1 | 170 | 2017-09-15 12:30:00
(5 rows)
-- Ninth, create a temporary table in PostgreSQL from the RocksDB store, do calculations and drop it on commit, such as:
CREATE OR REPLACE FUNCTION sql_main_sum_field(int,text,text)
RETURNS float AS $$
DECLARE
result float := 0;
BEGIN
EXECUTE format('create temp table temp_table on commit drop as select d.* from v1_dna_%s, rocks_json_to_record(%s,v1_dna_%s.key) d(%s text) where v1_dna_%s.tbl = ''%s'' ',$1,$1,$1,$3,$1,$2);
EXECUTE format('select sum(%s::float) from temp_table',$3) into result;
return result;
END;$$ LANGUAGE plpgsql;
select sql_main_sum_field(1,'venues','capacity') as total;
total
-------
1380
(1 row)
Conclusion
In this tutorial, you've explored PgRocks which is a plugin to flow data between PostgreSQL and RocksDB.
You've also seen how it is possible to enhance the PostgreSQL to create new internal functions for migrating its native formats data to external storage.
You may further explore PgRocks and take a look at a live demo you'll find at http://pgrocks.com about how PgRocks can be used for syncing PostgreSQL nodes over the network.
PostgreSQL is a well-known, "solid and mature" (C) SQL database management system.
RocksDB is a persistent key-value store and C/C++ library.
PgRocks is a plugin that uses DNA-like PostgreSQL data structure to link and easily migrate data between a PostgreSQL database and a RocksDB store.
In this tutorial, you'll install and configure PgRocks, and by doing so you will set up a data bridge between PostgreSQL and RocksDB.
This will require modifications in source code of both PostgreSQL and RocksDB, their compilation and installation, or re-installation, if they are already on the system.
In the end of this guide you will see examples of how the plugin works.
When you're finished, you'll basically understand how to flow data from PostgreSQL to RocksDB and back with the new plugin.
Prerequisites
Before you begin this guide you'll need the following:
- - a typical modern Linux system, like CentOS 7, Ubuntu 16, or Debian 9;
- - a root access (or non-root user with sudo privileges) on this system;
- - git and optionally wget installed on the system;
- - gcc compiler version 4.8 and later, and make.
Step 1 - Preparing all necessary source code trees
First, download the latest stable (postgresql-xx.x) source code for PostgreSQL.
(NB: Before selecting the version of PostgreSQL for running this tutorial, check our github repository https://github.com/kl3eo/PgRocks for the latest supported version of PG. We try to be in sync with the official PG:)
Unpack the source code tree into a new work directory, e.g. work_pgrocks:
mkdir work_pgrocks
cd work_pgrocks
wget https://ftp.postgresql.org/pub/source/vxx.x/postgresql-xx.x.tar.gz
tar zxvf postgresql-xx.x.tar.gz
In the same work directory (i.e. work_pgrocks), get the code for RocksDB and PgRocks with git:
git clone https://github.com/facebook/rocksdb
git clone https://github.com/kl3eo/PgRocks
You have three new catalogs in your work directory, postgresql-xx.x, rocksdb and PgRocks.
Further in Steps 2 and 3 you will modify the RocksDB and PostgreSQL with files from the PgRocks tree.
Step 2 - Patching the RocksDB tree, then compiling and installing the new RocksDB libraries
First, locate the file with the code for patching RocksDB. It is the file named rocksdb_get2.patch at the top level of PgRocks catalog.
Apply this patch:
cd rocksdb
git apply ../PgRocks/rocksdb_get2.patch
Before you proceed with compilation of the new RocksDB libraries, check that all the libs required for RocksDB are installed on the system. Methods of their installation depending on the system are explained at https://github.com/facebook/rocksdb/blob/master/INSTALL.md
For example, to install the required libs on Debian or Ubuntu, run the following command as root (or with sudo prefix if run from a user account):
apt-get install libgflags-dev libsnappy-dev zlib1g-dev libbz2-dev liblz4-dev libzstd-dev libnuma-dev
Now you're ready to compile and install (or possibly re-install) RocksDB. Go ahead with it:
make static_lib
make shared_lib
Everything fine, go ahead and install (as root):
make install
Optionally, you may compile and install a useful tool ldb for working with RocksDB store. Go to the tools and run these commands:
g++ -std=gnu++11 ldb.cc -o ldb -lrocksdb
cp -a ldb /usr/local/bin
cd ../..
Now you're ready to proceed to Step 3 and modify the code in PostgreSQL tree, then compile and install it.
Step 3 - Modifying the code in PostgreSQL tree, compiling and installing it
Run the following commands to overwrite the source files in PostgreSQL tree with newer files of the same name from PgRocks/src catalog:
cd postgresql-xx.x
cp -a ../PgRocks/src/* src/
Then configure the PostgreSQL source code:
./configure
This command creates a file named Makefile.global in the src catalog. You will have to open it in the editor and modify it, manually replacing only one line, the one that starts with "LIBS". Replace it with another line you will find in the file named compile at the top level of PgRocks source tree.
These lines only differ in libs required by gcc to compile the code.
If in your src/Makefile.global there is the line:
LIBS = -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm
than you have to replace it with this line:
LIBS = -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm -lsnappy -lgflags -lbz2 -llz4 -lzstd -lnuma -lrocksdb
After editing the src/Makefile.global, you are ready to compile PostgreSQL:
make
Now that the compilation is complete and you're ready to install, please make sure that no older version of PostgreSQL exists on your system.
If another version of PostgreSQL had been automatically installed (and probably never used), remove it now, e.g. on Debian and Ubuntu run the following command as root:
apt remove PostgreSQL-10
Now as root run the following command from the top level of PostgreSQL tree:
make install
Make sure the newly installed libs will be found by the linker:
ldconfig
Only if you're installing PostgreSQL for the very first time, then run the following commands, also as root:
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Start the new PostgreSQL daemon as user postgres to see if everything is fine:
su - postgres
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/psql template1
If you are able to connect to the daemon with this command, then you see this prompt:
template1=#
You're ready to make new functions visible by PostgreSQL at the next Step 4.
Step 4 - Making new internal functions visible by PostgreSQL
Open the file named pg_catalog.pg_proc.txt found at the top level of PgRocks catalog in the editor, select, copy and then paste everything into the prompt that you have opened at the previous Step. This will result in many lines of output like this:
(...)
drop table if exists jar;
DROP TABLE
delete from pg_catalog.pg_proc where proname='rocks_get_node_number';
DELETE 0
create table jar as select * from pg_catalog.pg_proc where oid = 89;
SELECT 1
(...)
If you have made mistakes while copy-pasting, repeat the whole Step 4 from beginning to end, copy and paste ALL the lines from the pg_catalog.pg_proc.txt into the prompt.
This is it, and now you are ready to start using PgRocks.
Step 5 - Creating a test suit for the plugin and running examples
In this Step you will see how the plugin works.
You will only use three of new PostgreSQL/PgRocks functions, one for writing data to RocksDB, one for reading from it, and one for closing the open connection.
-- First, create a test database as user postgres.
su - postgres
/usr/local/pgsql/bin/createdb test
-- Second, create there a table named players and populate it with test data:
/usr/local/pgsql/bin/psql test
create table players(name text, aka text, dob date, weight float, height int, last_seen timestamp);
CREATE TABLE
insert into players values ('Peter Stevens','Boss', '1956-06-30', 85.4,169,'2017-10-25 17:30');
insert into players values ('Mike Palmer','Hippy', '1988-12-06', 75.5,184,'2018-02-28 14:20');
insert into players values ('Dorothy Brown','Miss D', '1992-07-12', 64.3,172,'2018-03-15 09:25');
insert into players values ('Linda Chambers','Jamaica', '1987-03-10', 57.7,180,'2018-04-01 14:00');
insert into players values ('Claude Moulin','French', '1944-10-17', 68.1,170,'2017-09-15 12:30');
select * from players;
name | aka | dob | weight | height | last_seen
----------------+---------+------------+--------+--------+---------------------
Peter Stevens | Boss | 1956-06-30 | 85.4 | 169 | 2017-10-25 17:30:00
Mike Palmer | Hippy | 1988-12-06 | 75.5 | 184 | 2018-02-28 14:20:00
Dorothy Brown | Miss D | 1992-07-12 | 64.3 | 172 | 2018-03-15 09:25:00
Linda Chambers | Jamaica | 1987-03-10 | 57.7 | 180 | 2018-04-01 14:00:00
Claude Moulin | French | 1944-10-17 | 68.1 | 170 | 2017-09-15 12:30:00
(5 rows)
-- Third, create another table named venues and also populate it with test data:
create table venues (address text, capacity int);
insert into venues values ('Baker St. 221b', 330);
insert into venues values ('Downing St. 10', 50);
insert into venues values ('Marylebone Rd', 1000);
-- Fourth, create an empty "DNA" table v1_dna_1 (v1 stands for "version one") for RocksDB "store #1";
create table v1_dna_1 (tbl text, rev int, key bigint);
-- Fifth, pack the data from the two test table into a new RocksDB store located at /tmp/rocksdb_1 on your file system - i.e. to store#1.
For this you will use the new internal PostgreSQL/PgRocks function row_to_json_rocks, which creates a new store #1, if there is none,
and writes all rows from the given PG table into this new store as JSON data.
insert into v1_dna_1 (tbl, rev, key) select 'players', 1, row_to_json_rocks(1,players) from players;
insert into v1_dna_1 (tbl, rev, key) select 'venues', 1, row_to_json_rocks(1,venues) from venues;
select rocks_close();
Make sure to close the RocksDB connection each time after write operations calling function rocks_close.
N.B. You have inserted "1" into rev column of DNA-table. By doing this you assume that all the rows are of revision one. You won't need the data revisions yet.
The function row_to_json_rocks just used takes two arguments, (1) the number of the store and (2) the name of the table.
It returns the key of type bigint where the first digit is the number of the node (1 by default), and the other digits are epoch time in microseconds, measured at the moment of packing the record into the store.
These keys are guaranteed to be unique and as such they are assigned to the values in the RocksDB store.
You will keep these keys in the column key of the DNA table:
select * from v1_dna_1;
tbl | rev | key
---------+-----+-------------------
players | 1 | 11522687676247617
players | 1 | 11522687676247896
players | 1 | 11522687676247929
players | 1 | 11522687676247951
players | 1 | 11522687676247972
venues | 1 | 11522687676275189
venues | 1 | 11522687676275254
venues | 1 | 11522687676275269
(8 rows)
-- Sixth, drop the tables players and venues - since you keep all the data in RocksDB, you don't need the original tables any more.
drop table players;
drop table venues;
-- Seventh, take a look at our data how it is stored in RocksDB.
You will need the bigint keys printed as hexadecimal for searching with ldb:
select '0x' || LPAD(to_hex(key), 16, '0') as hex_key from v1_dna_1;
hex_key
--------------------
0x0028efd2c9ccee41
0x0028efd2c9ccef58
0x0028efd2c9ccef79
0x0028efd2c9ccef8f
0x0028efd2c9ccefa4
0x0028efd2c9cd59f5
0x0028efd2c9cd5a36
0x0028efd2c9cd5a45
(8 rows)
Quit the psql shell and look at our data packed as JSON in our RocksDB store:
ldb --db=/tmp/rocksdb_1 scan --key_hex
0x0028EFD2C9CCEE41 : {"name":"Peter Stevens","aka":"Boss","dob":"1956-06-30","weight":85.4,"height":169,"last_seen":"2017-10-25T17:30:00"}
0x0028EFD2C9CCEF58 : {"name":"Mike Palmer","aka":"Hippy","dob":"1988-12-06","weight":75.5,"height":184,"last_seen":"2018-02-28T14:20:00"}
0x0028EFD2C9CCEF79 : {"name":"Dorothy Brown","aka":"Miss D","dob":"1992-07-12","weight":64.3,"height":172,"last_seen":"2018-03-15T09:25:00"}
0x0028EFD2C9CCEF8F : {"name":"Linda Chambers","aka":"Jamaica","dob":"1987-03-10","weight":57.7,"height":180,"last_seen":"2018-04-01T14:00:00"}
0x0028EFD2C9CCEFA4 : {"name":"Claude Moulin","aka":"French","dob":"1944-10-17","weight":68.1,"height":170,"last_seen":"2017-09-15T12:30:00"}
0x0028EFD2C9CD59F5 : {"address":"Baker St. 221b","capacity":330}
0x0028EFD2C9CD5A36 : {"address":"Downing St. 10","capacity":50}
0x0028EFD2C9CD5A45 : {"address":"Marylebone Rd","capacity":1000}
You may now search the data in this store by the key:
ldb --db=/tmp/rocksdb_1 get 0x0028EFD2C9CCEF8F --key_hex
{"name":"Linda Chambers","aka":"Jamaica","dob":"1987-03-10","weight":57.7,"height":180,"last_seen":"2018-04-01T14:00:00"}
-- Eighth, get back the data into table players dropped earlier, - suppose you need it again.
You will use the counterpart function rocks_json_to_record, which also takes two arguments - (1) number of the store and (2) the key, and returns the stored record:
create table players as select d.* from v1_dna_1, rocks_json_to_record(1,v1_dna_1.key) d(name text, aka text, dob date, weight float, height int, last_seen timestamp) where v1_dna_1.tbl = 'players';
SELECT 5
select * from players;
name | aka | dob | weight | height | last_seen
----------------+---------+------------+--------+--------+---------------------
Peter Stevens | Boss | 1956-06-30 | 85.4 | 169 | 2017-10-25 17:30:00
Mike Palmer | Hippy | 1988-12-06 | 75.5 | 184 | 2018-02-28 14:20:00
Dorothy Brown | Miss D | 1992-07-12 | 64.3 | 172 | 2018-03-15 09:25:00
Linda Chambers | Jamaica | 1987-03-10 | 57.7 | 180 | 2018-04-01 14:00:00
Claude Moulin | French | 1944-10-17 | 68.1 | 170 | 2017-09-15 12:30:00
(5 rows)
-- Ninth, create a temporary table in PostgreSQL from the RocksDB store, do calculations and drop it on commit, such as:
CREATE OR REPLACE FUNCTION sql_main_sum_field(int,text,text)
RETURNS float AS $$
DECLARE
result float := 0;
BEGIN
EXECUTE format('create temp table temp_table on commit drop as select d.* from v1_dna_%s, rocks_json_to_record(%s,v1_dna_%s.key) d(%s text) where v1_dna_%s.tbl = ''%s'' ',$1,$1,$1,$3,$1,$2);
EXECUTE format('select sum(%s::float) from temp_table',$3) into result;
return result;
END;$$ LANGUAGE plpgsql;
select sql_main_sum_field(1,'venues','capacity') as total;
total
-------
1380
(1 row)
Conclusion
In this tutorial, you've explored PgRocks which is a plugin to flow data between PostgreSQL and RocksDB.
You've also seen how it is possible to enhance the PostgreSQL to create new internal functions for migrating its native formats data to external storage.
You may further explore PgRocks and take a look at a live demo you'll find at http://pgrocks.com about how PgRocks can be used for syncing PostgreSQL nodes over the network.
Комментариев нет:
Отправить комментарий