Effortless Database Installation: Unveiling the Compatibility of YugabyteDB and PostgreSQL

Jimmy Guerrero

VP Developer Relations

In this blog post, we’ll show you how the basic commands you use to install PostgreSQL are also applicable when installing YugabyteDB. We’ll cover connecting to the database, creating users, databases, schemas, and executing external files from the SQL shell.

Installing YugabyteDB

Installing YugabyteDB 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-
$ tar xvfz yugabyte- && cd yugabyte-
$ ./bin/yugabyted start

Single Node Installation on Linux

$ wget https://downloads.yugabyte.com/yugabyte-
$ tar xvfz yugabyte- && cd yugabyte-
$ ./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

A Three 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 our YugabyteDB Quickstart Guide.

Connect to a YugabyteDB Cluster

Connect Locally

Assuming you are in the YugabyteDB install directory, simply execute the following to get to a YSQL shell:

$ ./bin/ysqlsh

ysqlsh (11.2-YB-
Type "help" for help.


Connect 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-
Type "help" for help.


Check out our documentation for more information about our YSQL API.

Connect 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.


Note: In the example above we assume that YugabyteDB’s YSQL API is being accessed at 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 our documentation site.

Set Up Users in YugabyteDB

Creating roles/users and assigning them privileges and passwords is the same in YugabyteDB as it is in PostgreSQL.

Create a Role with Privileges


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 our documentation site.

Create Databases and Schemas in YugabyteDB

Creating databases and schemas in YugabyteDB is identical to how it is done in PostgreSQL.

Create a Database


Switch to a Database

\c northwind;

Describe the Database


                 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 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 our Sample Datasets documentation.

Call an External File to Create Objects

\i 'northwind_ddl.sql';

Call an external file to create objects, PostgreSQL YugabyteDB Compatibility tutorial

Call an External File to Load Data into the Objects

\i 'northwind_data.sql';

postgresql and yugabytedb compatibility, Call an external file to load data into the objects

Got questions? Make sure to ask them in our YugabyteDB Slack channel.

Jimmy Guerrero

VP Developer Relations

Related Posts

Explore Distributed SQL and YugabyteDB in Depth

Discover the future of data management.
Learn at Yugabyte University
Get Started
Browse Yugabyte Docs
Explore docs
PostgreSQL For Cloud Native World
Read for Free