Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.0 (latest version) v1.3 (earlier version) v1.2 (earlier version) v1.1 (earlier version) v1.0 (earlier version)
  • Introduction
  • Quick Start
    • 1. Install YugabyteDB
    • 2. Create Local Cluster
    • 3. Test YCQL API
    • 4. Test YEDIS API
    • 5. Test YSQL API
    • 6. Run Sample Apps
  • Explore Core Features
    • Cloud Native
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. Observability
      • 4. Orchestration Readiness
    • Transactional
      • 1. ACID Transactions
      • 2. Secondary Indexes
      • 3. JSON Documents
    • High Performance
      • Tunable Reads
    • Planet Scale
      • 1. Global Distribution
      • 2. Auto Sharding
      • 3. Auto Rebalancing
    • PostgreSQL (Beta)
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. JOINs
      • 4. Aggregations
      • 5. Expressions
      • 6. Views
  • Develop
    • Tools
      • cqlsh
      • TablePlus
    • Client Drivers
      • C/C++
      • C#
      • Go
      • Java
      • NodeJS
      • Python
    • Learn App Dev
      • 1. SQL vs NoSQL
      • 2. Data Modeling
      • 3. Data Types
      • 4. ACID Transactions
      • 5. Aggregations
      • 6. Batch Operations
    • Ecosystem Integrations
      • Apache Kafka
      • Apache Spark
      • JanusGraph
      • KairosDB
      • Presto
      • Metabase
    • Real World Examples
      • E-Commerce App
      • IoT Fleet Management
      • Retail Analytics
  • Deploy
    • Checklist
    • Manual Deployment
      • 1. System Configuration
      • 2. Install Software
      • 3. Start YB-Masters
      • 4. Start YB-TServers
      • 5. Verify Deployment
    • Kubernetes
      • Helm Chart
      • Local SSD
    • Docker Swarm
    • Public Clouds
      • Amazon Web Services
      • Google Cloud Platform
      • Microsoft Azure
    • Pivotal Cloud Foundry
    • Enterprise Edition
      • 1. Prepare Cloud Env
      • 2. Install Admin Console
      • 3. Configure Admin Console
      • 4. Configure Cloud Providers
  • Secure
    • Security Checklist
    • Authentication
    • Authorization
      • 1. RBAC Model
      • 2. Create Roles
      • 3. Grant Permissions
    • TLS Encryption
      • 1. Prepare Nodes
      • 2. Server-Server Encryption
      • 3. Client-Server Encryption
      • 4. Connect to Cluster
  • Manage
    • Backup and Restore
      • Backing Up Data
      • Restoring Data
    • Data Migration
      • Bulk Import
      • Bulk Export
    • Change Cluster Config
    • Upgrade Deployment
    • Diagnostics Reporting
    • Enterprise Edition
      • Create Universe - Multi-Zone
      • Create Universe - Multi-Region
      • Edit Universe
      • Edit Config Flags
      • Health Checking and Alerts
      • Node Status & Actions
      • Read Replicas
      • Backup & Restore
      • Upgrade Universe
      • Delete Universe
  • Troubleshoot
    • Troubleshooting Overview
    • Cluster Level Issues
      • YCQL Connection Issues
      • YEDIS Connection Issues
    • Node Level Issues
      • Check Processes
      • Inspect Logs
      • System Stats
    • Enterprise Edition
      • Troubleshoot Universes
  • Architecture
    • Basic Concepts
      • Key Components
      • YQL Query Layer
      • DocDB Document Store
        • Sharding
        • Replication
        • Persistence
      • Acknowledgements
    • Transactions
      • Isolation Levels
      • Single Row Transactions
      • Distributed Transactions
      • Transactional IO Path
  • Comparisons
    • Apache Cassandra
    • MongoDB
    • Redis In-Memory Store
    • FoundationDB
    • Amazon DynamoDB
    • Azure Cosmos DB
    • Google Cloud Spanner
    • Apache HBase
  • API Reference
    • YCQL
      • ALTER KEYSPACE
      • ALTER ROLE
      • ALTER TABLE
      • CREATE INDEX
      • CREATE KEYSPACE
      • CREATE ROLE
      • CREATE TABLE
      • CREATE TYPE
      • DROP INDEX
      • DROP KEYSPACE
      • DROP ROLE
      • DROP TABLE
      • DROP TYPE
      • GRANT PERMISSION
      • GRANT ROLE
      • REVOKE PERMISSION
      • REVOKE ROLE
      • USE
      • INSERT
      • SELECT
      • UPDATE
      • DELETE
      • TRANSACTION
      • TRUNCATE
      • Simple Value
      • Subscript
      • Function Call
      • Operator Call
      • BLOB
      • BOOLEAN
      • MAP, SET, LIST
      • FROZEN
      • INET
      • Integer & Counter
      • Non-Integer
      • TEXT
      • Date & Time Types
      • UUID & TIMEUUID
      • JSONB
      • Date & Time Functions
    • YEDIS
      • APPEND
      • AUTH
      • CONFIG
      • CREATEDB
      • DELETEDB
      • LISTDB
      • SELECT
      • DEL
      • ECHO
      • EXISTS
      • EXPIRE
      • EXPIREAT
      • FLUSHALL
      • FLUSHDB
      • GET
      • GETRANGE
      • GETSET
      • HDEL
      • HEXISTS
      • HGET
      • HGETALL
      • HINCRBY
      • HKEYS
      • HLEN
      • HMGET
      • HMSET
      • HSET
      • HSTRLEN
      • HVALS
      • INCR
      • INCRBY
      • KEYS
      • MONITOR
      • PEXPIRE
      • PEXPIREAT
      • PTTL
      • ROLE
      • SADD
      • SCARD
      • SET
      • SETRANGE
      • SISMEMBER
      • SMEMBERS
      • SREM
      • STRLEN
      • ZRANGE
      • TSADD
      • TSCARD
      • TSGET
      • TSLASTN
      • TSRANGEBYTIME
      • TSREM
      • TSREVRANGEBYTIME
      • TTL
      • ZADD
      • ZCARD
      • ZRANGEBYSCORE
      • ZREM
      • ZREVRANGE
      • PUBSUB
      • PUBLISH
      • SUBSCRIBE
      • UNSUBSCRIBE
      • PSUBSCRIBE
      • PUNSUBSCRIBE
    • YSQL (Beta)
      • DDL Statements
        • CREATE DATABASE
        • CREATE TABLE
        • CREATE VIEW
        • DROP DATABASE
        • DROP TABLE
      • DML Statements
        • INSERT
        • SELECT
      • Datatypes
        • FLOAT
        • INTEGER
        • TEXT
      • Transactions
      • Roles and Permissions
      • Prepared Statements
      • Explain Statement
  • Admin Reference
    • yb-ctl
    • yb-docker-ctl
    • docker-compose
    • yb-master
    • yb-tserver
  • FAQs
    • Product
    • Architecture
    • Enterprise Edition
    • Cassandra Compatibility
> Quick Start >

Test Yugabyte SQL (YSQL) API Beta

Attention

This page documents an earlier version. Go to the latest version.

    • 1. Create a new cluster
    • 2. Create a table
    • 3. Insert data
    • 4. Query the table

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

  • macOS
  • Linux
  • Docker
  • Kubernetes
  • 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 version 1.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)
Quick Start
Test Yugabyte Dictionary Service (YEDIS) API
Quick Start
Run Sample Apps
Talk to Community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2019 Yugabyte, Inc. All rights reserved.