Test Yugabyte SQL (YSQL) API Beta
Attention
This page documents an earlier version. Go to the latest version.Follow the instructions below to test YugabyteDB’s PostgreSQL-compatible YSQL API.
psql is a command line shell for interacting with PostgreSQL. For ease of use, YugabyteDB ships with a version of psql in its bin directory.
1. Create a new cluster
- Destroy any existing cluster.
$ ./bin/yb-ctl destroy
- Create a new cluster with YSQL API enabled. Note the additional option
enable_postgres
passed to the create cluster command.
You can do this as shown below.
$ ./bin/yb-ctl --rf 3 create --enable_postgres
- Check status of the cluster and confirm that the special
postgres
node is now running. This is the node we will connect postgres clients to. Internally, this node will use the various tserver nodes to distribute the data across the entire cluster.
$ ./bin/yb-ctl status
2019-01-15 22:18:40,387 INFO: Server is running: type=master, node_id=1, PID=12818, admin service=http://127.0.0.1:7000
2019-01-15 22:18:40,394 INFO: Server is running: type=master, node_id=2, PID=12821, admin service=http://127.0.0.2:7000
2019-01-15 22:18:40,401 INFO: Server is running: type=master, node_id=3, PID=12824, admin service=http://127.0.0.3:7000
2019-01-15 22:18:40,408 INFO: Server is running: type=tserver, node_id=1, PID=12827, admin service=http://127.0.0.1:9000, cql service=127.0.0.1:9042, redis service=127.0.0.1:6379, pgsql service=127.0.0.1:5433
2019-01-15 22:18:40,415 INFO: Server is running: type=tserver, node_id=2, PID=12830, admin service=http://127.0.0.2:9000, cql service=127.0.0.2:9042, redis service=127.0.0.2:6379, pgsql service=127.0.0.2:5433
2019-01-15 22:18:40,422 INFO: Server is running: type=tserver, node_id=3, PID=12833, admin service=http://127.0.0.3:9000, cql service=127.0.0.3:9042, redis service=127.0.0.3:6379, pgsql service=127.0.0.3:5433
- Run psql to connect to the service.
You can do this as shown below.
$ ./bin/psql -h 127.0.0.1 -p 5433 -U postgres
psql (10.3, server 10.4)
Type "help" for help.
postgres=#
- Destroy any existing cluster.
$ ./bin/yb-ctl destroy
- Create a new cluster with YSQL API enabled. Note the additional option
enable_postgres
passed to the create cluster command.
You can do this as shown below.
$ ./bin/yb-ctl --rf 3 create --enable_postgres
- Check status of the cluster and confirm that the special
postgres
node is now running. This is the node we will connect postgres clients to. Internally, this node will use the various tserver nodes to distribute the data across the entire cluster.
$ ./bin/yb-ctl status
2019-01-15 22:18:40,387 INFO: Server is running: type=master, node_id=1, PID=12818, admin service=http://127.0.0.1:7000
2019-01-15 22:18:40,394 INFO: Server is running: type=master, node_id=2, PID=12821, admin service=http://127.0.0.2:7000
2019-01-15 22:18:40,401 INFO: Server is running: type=master, node_id=3, PID=12824, admin service=http://127.0.0.3:7000
2019-01-15 22:18:40,408 INFO: Server is running: type=tserver, node_id=1, PID=12827, admin service=http://127.0.0.1:9000, cql service=127.0.0.1:9042, redis service=127.0.0.1:6379, pgsql service=127.0.0.1:5433
2019-01-15 22:18:40,415 INFO: Server is running: type=tserver, node_id=2, PID=12830, admin service=http://127.0.0.2:9000, cql service=127.0.0.2:9042, redis service=127.0.0.2:6379, pgsql service=127.0.0.2:5433
2019-01-15 22:18:40,422 INFO: Server is running: type=tserver, node_id=3, PID=12833, admin service=http://127.0.0.3:9000, cql service=127.0.0.3:9042, redis service=127.0.0.3:6379, pgsql service=127.0.0.3:5433
- Run psql to connect to the service.
You can do this as shown below.
$ ./bin/psql -h 127.0.0.1 -p 5433 -U postgres
psql (10.3, server 10.4)
Type "help" for help.
postgres=#
1. Create a new cluster
- Destroy any existing cluster.
$ ./yb-docker-ctl destroy
- Create a new cluster with YSQL API enabled. Note the additional option
enable_postgres
passed to the create cluster command. Also note that this requires at least version1.1.2.0-b10
of YugabyteDB.
$ ./yb-docker-ctl create --rf 3 --enable_postgres
- Check status of the cluster
$ ./yb-docker-ctl status
ID PID Type Node URL Status Started At
ca16705b20bd 5861 tserver yb-tserver-n3 http://192.168.64.7:9000 Running 2018-10-18T22:02:52.12697026Z
0a7deab4e4db 5681 tserver yb-tserver-n2 http://192.168.64.6:9000 Running 2018-10-18T22:02:51.181289786Z
921494a8058d 5547 tserver yb-tserver-n1 http://192.168.64.5:9000 Running 2018-10-18T22:02:50.187976253Z
0d7dc9436033 5345 master yb-master-n3 http://192.168.64.4:7000 Running 2018-10-18T22:02:49.105792573Z
0b25dd24aea3 5191 master yb-master-n2 http://192.168.64.3:7000 Running 2018-10-18T22:02:48.162506832Z
feea0823209a 5039 master yb-master-n1 http://192.168.64.2:7000 Running 2018-10-18T22:02:47.163244578Z
- Run psql to connect to the service.
You can do this as shown below.
$ docker exec -it yb-tserver-n1 /home/yugabyte/postgres/bin/psql -h yb-tserver-n1 -p 5433 -U postgres
psql (10.4)
Type "help" for help.
postgres=#
## 1. Create a new cluster
- Destroy any existing cluster.
$ kubectl delete -f yugabyte-statefulset.yaml
- Create a new cluster with YSQL API enabled.
First uncomment the following flags in the YAML file.
# To support postgres functionality, uncomment the following flags.
# - "--start_pgsql_proxy"
# - "--pgsql_proxy_bind_address=$(POD_IP):5433"
Recreate the cluster.
$ kubectl apply -f yugabyte-statefulset.yaml
- Check cluster status
Run the command below to see that we now have two services with 3 pods each - 3 yb-master
pods (yb-master-1,yb-master-2,yb-master-3) and 3 yb-tserver
pods (yb-tserver-1,yb-tserver-2,yb-tserver-3) running. Roles played by these pods in a YugabyteDB cluster (aka Universe) is explained in detail here.
$ kubectl get pods
NAME READY STATUS RESTARTS AGE
yb-master-0 0/1 ContainerCreating 0 5s
yb-master-1 0/1 ContainerCreating 0 5s
yb-master-2 1/1 Running 0 5s
yb-tserver-0 0/1 ContainerCreating 0 4s
yb-tserver-1 0/1 ContainerCreating 0 4s
yb-tserver-2 0/1 ContainerCreating 0 4s
Eventually all the pods will have the Running
state.
$ kubectl get pods
NAME READY STATUS RESTARTS AGE
yb-master-0 1/1 Running 0 13s
yb-master-1 1/1 Running 0 13s
yb-master-2 1/1 Running 0 13s
yb-tserver-0 1/1 Running 1 12s
yb-tserver-1 1/1 Running 1 12s
yb-tserver-2 1/1 Running 1 12s
- Initialize the YSQL API
$ kubectl exec -it yb-tserver-0 bash -- -c "YB_ENABLED_IN_POSTGRES=1 FLAGS_pggate_master_addresses=yb-master-0.yb-masters.default.svc.cluster.local:7100,yb-master-1.yb-masters.default.svc.cluster.local:7100,yb-master-2.yb-masters.default.svc.cluster.local:7100 /home/yugabyte/postgres/bin/initdb -D /tmp/yb_pg_initdb_tmp_data_dir -U postgres"
- Run psql to connect to the service.
$ kubectl exec -it yb-tserver-0 /home/yugabyte/postgres/bin/psql -- -U postgres -d postgres -h yb-tserver-0 -p 5433
psql (10.4)
Type "help" for help.
postgres=#
2. Create a table
Create a database called ‘sample’.
postgres=> CREATE DATABASE sample;
Connect to the database we just created.
postgres=> \c sample
You are now connected to database "sample" as user "postgres".
sample=>
Create a table named ‘stock_market’ which can store stock prices at various timestamps for different stock ticker symbols.
sample=> CREATE TABLE stock_market (
stock_symbol text,
ts text,
current_price float,
PRIMARY KEY (stock_symbol, ts)
);
3. Insert data
Let us insert some data for a few stock symbols into our newly created ‘stock_market’ table. You can copy-paste these values directly into your psql shell.
INSERT INTO stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 09:00:00',157.41);
INSERT INTO stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 10:00:00',157);
INSERT INTO stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 09:00:00',170.63);
INSERT INTO stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 10:00:00',170.1);
INSERT INTO stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 09:00:00',972.56);
INSERT INTO stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 10:00:00',971.91);
4. Query the table
Query all the values we have inserted into the table.
sample=> SELECT * FROM stock_market ORDER BY stock_symbol ASC, ts DESC;
stock_symbol | ts | current_price
--------------+---------------------+---------------
AAPL | 2017-10-26 10:00:00 | 157
AAPL | 2017-10-26 09:00:00 | 157.41
FB | 2017-10-26 10:00:00 | 170.1
FB | 2017-10-26 09:00:00 | 170.63
GOOG | 2017-10-26 10:00:00 | 971.91
GOOG | 2017-10-26 09:00:00 | 972.56
(6 rows)