Use PostgreSQL Command Line Tools for YugabyteDB

YugabyteDB is a distributed SQL database built to scale out while maintaining compatibility with the PostgreSQL ecosystem. That compatibility is not just limited to SQL syntax—it extends to PostgreSQL’s rich set of client tools as well.

If you’re coming from the PostgreSQL world, this means you can use some of the tools you already know and trust to interact with YugabyteDB. But even if you’re new to PostgreSQL, there are good reasons to adopt these tools in your YugabyteDB workflows.

In today’s YugabyteDB tip, we’ll explore several of the PostgreSQL client applications that are compatible with YugabyteDB.

Note: For the examples below, I’ll be connecting to a YugabyteDB cluster running version 2024.2.2.1. I’ll store a node’s IP address in an environment variable. Since PostgreSQL tools default to port 5432, you’ll need to specify the correct port when connecting to YugabyteDB. In my case, I’m using YugabyteDB’s default port: 5433. Also, I ran all of the commands below from an install from PostgreSQL 15.

The first tool is psql—the PostgreSQL interactive terminal and the predecessor to ysqlsh, YugabyteDB’s interactive terminal.

				
					[postgres@cloud-server-0 ~]$ export yugabyte_server=XXX.XX.XXX.XXX #IP Hidden

[postgres@cloud-server-0 ~]$ psql -h $yugabyte_server -p 5433 -U yugabyte -c "SELECT 'I can connect to Yugabyte...' \"connected?\";"
          connected?
------------------------------
 I can connect to Yugabyte...
(1 row)

The next tool is pg_ready – it’s used to check the connection status of a PostgreSQL server.

				
					[postgres@cloud-server-0 ~]$ /usr/pgsql-15/bin/pg_isready -h $yugabyte_server -p 5433
yugabytedb.tech:5433 - accepting connections

The next tool is createdb which allows us to create a new PostgreSQL database.

				
					[postgres@cloud-server-0 ~]$ createdb -h $yugabyte_server -p 5433 -U yugabyte -e my_new_db
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE my_new_db;

[postgres@cloud-server-0 ~]$ psql -h $yugabyte_server -p 5433 -U yugabyte -c "SELECT datname FROM pg_database ORDER BY datname;"
     datname
-----------------
 my_new_db
 postgres
 system_platform
 template0
 template1
 yugabyte
(6 rows)

The next tool is createuser – which allows us to define a new PostgreSQL user account.

				
					[postgres@cloud-server-0 ~]$ createuser -h $yugabyte_server -p 5433 -U yugabyte -s -e my_new_role
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE my_new_role SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

[postgres@cloud-server-0 ~]$ psql -h $yugabyte_server -p 5433 -U yugabyte -c "SELECT usename FROM pg_user ORDER BY usename;"
   usename
-------------
 my_new_role
 postgres
 yugabyte
( rows)

Finally, here are the counterparts to createdb and createuser: dropdb and dropuser, respectively.

				
					[postgres@cloud-server-0 ~]$ dropdb -h $yugabyte_server -p 5433 -U yugabyte -e my_new_db
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE my_new_db;

[postgres@cloud-server-0 ~]$ dropuser -h $yugabyte_server -p 5433 -U yugabyte -e my_new_role
SELECT pg_catalog.set_config('search_path', '', false);
DROP ROLE my_new_role;

Remember, I ran all of the commands above from an install from PostgreSQL 15. But all of these commands, and more, are included with your YugabyteDB installation—just check the postgres/bin directory on your TServers!

In fact, I frequently use pg_isready from the YugabyteDB installation directory when starting a cluster with yugabyted, to confirm that the first node is up and ready before adding other nodes.

Have Fun!