A PostgreSQL-Compatible, Distributed SQL Cheat Sheet: The Basics
In this blog post, we’ll highlight how all the basic commands you end up using in the first few minutes after installing PostgreSQL are identical in YugabyteDB. We’ll cover connecting to the database, creating users, databases, schemas, and calling external files from the SQL shell. In the next blog post in this series we’ll tackle querying data to demonstrate that if you know how to query data in PostgreSQL, you already know how to do it in YugabyteDB.
First things first, for those of you who might be new to either distributed SQL or YugabyteDB…
What Is Distributed SQL?
Distributed SQL databases are becoming popular with organizations interested in moving data infrastructure to the cloud or cloud native environments. This is often motivated by the desire to reduce TCO or move away from the horizontal scaling limitations of monolithic RDBMS like Oracle, PostgreSQL, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:
- They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures, and triggers.
- Smart distributed query execution so that query processing is pushed closer to the data as opposed to data being pushed over the network and thus slowing down query response times.
- Should support automatic and transparent distributed data storage. This includes indexes which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures high performance and high availability.
- Distributed SQL systems should also provide for strongly consistent replication and distributed ACID transactions.
For a deeper discussion about what Distributed SQL is, check out, “What is Distributed SQL?”
YugabyteDB is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. YugabyteDB is PostgreSQL wire compatible, cloud native, offers deep integration with GraphQL projects, plus supports advanced RDBMS features like stored procedures, triggers, and UDFs.
Got questions? Make sure to ask them in our YugabyteDB Slack channel. Ok, let’s dive in…
YugabyteDB is only slightly more involved than getting PostgreSQL up and running. At the end of the day it should only take a few minutes or less depending on your environment. Let’s look at a few scenarios:
Single Node Installation on Mac
$ wget https://downloads.yugabyte.com/yugabyte-188.8.131.52-darwin.tar.gz $ tar xvfz yugabyte-184.108.40.206-darwin.tar.gz && cd yugabyte-220.127.116.11/ $ ./bin/yugabyted start
Single Node Installation on Linux
$ wget https://downloads.yugabyte.com/yugabyte-18.104.22.168-linux.tar.gz $ tar xvfz yugabyte-22.214.171.124-linux.tar.gz && cd yugabyte-126.96.36.199/ $ ./bin/post_install.sh $ ./bin/yugabyted start
Note: If you want to run 3 local nodes instead of a single node for either the Mac or Linux setups, just tweak the last command so it reads:
./bin/yb-ctl --rf 3 create
3 Node Installation on Google Kubernetes Engine
$ helm repo add yugabytedb https://charts.yugabyte.com $ helm repo update $ kubectl create namespace yb-demo $ helm install yb-demo yugabytedb/yugabyte --namespace yb-demo --wait
For more information on other installation types and prerequisites, check out the Quickstart Docs.
Connecting to a YugabyteDB Cluster
Assuming you are in the YugabyteDB install directory, simply execute the following to get to a YSQL shell:
$ ./bin/ysqlsh ysqlsh (11.2-YB-188.8.131.52-b0) Type "help" for help. yugabyte=#
Connecting on GKE
Assuming you are connected to the Kubernetes cluster via the Google Cloud Console, execute the following.
$ kubectl exec -n yb-demo -it yb-tserver-0 -- ysqlsh -h yb-tserver-0.yb-tservers.yb-demo ysqlsh (11.2-YB-184.108.40.206-b0) Type "help" for help. yugabyte=#
Check out the documentation for more information about YugabyteDB’s PostgreSQL-compatible YSQL API.
Connecting via JDBC
Assuming we are using the PostgreSQL JDBC driver to connect to YugabyteDB, the construction of the connect string will be identical to PostgreSQL. For example here’s a snippet for setting up a connection to YugabyteDB using the PostgreSQL driver in Spring.
spring.datasource.url=jdbc:postgresql://220.127.116.11:5433/northwind spring.datasource.username=yugabyte spring.datasource.password=password spring.datasource.driver-class-name=org.postgresql.Driver spring.jpa.database=POSTGRESQL spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect spring.jpa.hibernate.ddl-auto=none
Note: In the example above we assume YugabyteDB’s YSQL API is being accessed at 18.104.22.168 on the default port 5433, using the default user “yugabyte” with the password “password”. For more information about YugabyteDB connectivity options check out the Drivers section of the documentation.
Setting Up Users in YugabyteDB
Creating roles/users, and assigning them privileges and passwords is going to be the same in YugabyteDB as it is in PostgreSQL.
Create a Role with Privileges
CREATE ROLE felix LOGIN;
Create a Role with a Password
CREATE USER felix2 WITH PASSWORD ‘password’;
Create a Role with a Password That Will Expire in the Future
CREATE ROLE felix3 WITH LOGIN PASSWORD 'password' VALID UNTIL '2020-09-30';
Change a User’s Password
ALTER ROLE felix WITH PASSWORD 'newpassword';
List All the Users
For more information about how YugabyteDB handles, users, permissions, security, and encryption check out the Secure section of the documentation.
Creating Databases and Schemas in YugabyteDB
Creating databases and schemas in YugabyteDB is identical to how it is done in PostgreSQL.
Create a Database
CREATE DATABASE northwind;
Switch to a Database
Describe the Database
\dt List of relations Schema | Name | Type | Owner --------+------------------------+-------+---------- public | categories | table | yugabyte public | customer_customer_demo | table | yugabyte public | customer_demographics | table | yugabyte public | customers | table | yugabyte public | employee_territories | table | yugabyte public | employees | table | yugabyte public | order_details | table | yugabyte public | orders | table | yugabyte public | products | table | yugabyte public | region | table | yugabyte public | shippers | table | yugabyte public | suppliers | table | yugabyte public | territories | table | yugabyte public | us_states | table | yugabyte (14 rows)
Create a Schema
CREATE SCHEMA nonpublic;
Create a Schema for a Specific User
CREATE SCHEMA AUTHORIZATION felix;
Create Objects and Load Data from External Files
If you have DDL or DML scripts that you want to call from within the YSQL shell, the process is the same in YugabyteDB as it is in PostgreSQL. You can find the scripts used in the examples below in the
~/yugabyte-2.3.x.x/share directory. For information about the sample data sets that ship by default with YugabyteDB, check out the Sample Datasets documentation.
Call an External File to Create Objects
Call an External File to Load Data into the Objects
Stay tuned for part 2 in this series where we’ll dive into querying data from a YugabyteDB cluster using familiar PostgreSQL syntax.