Start Now

Download our new eBook, Distributed SQL Databases for Dummies, to discover more about distributed SQL and YugabyteDB!

Download for Free

Distributed SQL Tips and Tricks – April 3, 2020

Dorian Hoxha

Welcome to this week’s tips and tricks blog where we recap some distributed SQL questions from around the Internet. We’ll also review upcoming events, new documentation, and blogs that have been published since the last post. Got questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stackoverflow. Ok, let’s dive right in:

How can I save the results of YSQL commands to a file?

It is often useful to save the results from ysqlsh queries to a file so they can be reviewed at a later time. This can be done easily by using the \o filename command. For example:

$ ./bin/ysqlsh
ysqlsh (11.2-YB-
Type "help" for help.

yugabyte=# \o results.txt
yugabyte=# \l
yugabyte=# SELECT 1;
yugabyte=# \q

We can then read the generated file using the cat command:

$ cat results.txt
                               	List of databases
  	Name   	|  Owner   | Encoding | Collate |	Ctype	|   Access privileges   
 postgres    	| postgres | UTF8 	| C   	| en_US.UTF-8 |
 system_platform | postgres | UTF8 	| C   	| en_US.UTF-8 |
 template0   	| postgres | UTF8 	| C   	| en_US.UTF-8 | =c/postgres      	+
             	|      	|      	|     	|         	| postgres=CTc/postgres
 template1   	| postgres | UTF8 	| C   	| en_US.UTF-8 | =c/postgres      	+
             	|      	|      	|     	|         	| postgres=CTc/postgres
 yb_demo     	| yugabyte | UTF8 	| C   	| en_US.UTF-8 | =Tc/yugabyte     	+
             	|      	|      	|     	|         	| yugabyte=CTc/yugabyte+
             	|      	|      	|     	|         	| user_read=c/yugabyte
 yugabyte    	| postgres | UTF8 	| C   	| en_US.UTF-8 |
(6 rows)

(1 row)

How can I specify multiple hosts for the YSQL client to use?

The YSQL API is built on top of a PostgreSQL query layer and makes use of the same client drivers. Most PostgreSQL clients (including ysqlsh) are built on top of PostgreSQL’s libpq library which supports the ability to specify multiple hosts on connection settings. When done this way, each host will be tried in sequential order until a connection is successful. In the example below we see an example of this behavior on a cluster with 3 yb-tservers using ysqlsh.

First we create a cluster with replication factor 3:

$ ./bin/yb-ctl start --replication_factor 3
Creating cluster.
Waiting for cluster to be ready.
| Node Count: 3 | Replication Factor: 3                                                        	|
| JDBC            	: jdbc:postgresql://                              	|
| YSQL Shell      	: bin/ysqlsh                                                             	|
| YCQL Shell      	: bin/cqlsh                                                              	|
| YEDIS Shell     	: bin/redis-cli                                                          	|
| Web UI          	:                                                 	|
| Cluster Data    	: /home/guru/yugabyte-data                                               	|


For more info, please use: yb-ctl status

Looking at the page, we see the IPs of the 3 processes:

see IPs tablet servers yugabytedb distributed sql database

We can see in the UI that the IPs of the YugabyteDB tablet servers are:


Now, let’s test to see if we can successfully connect to the first host at

$ ./bin/ysqlsh --host=
ysqlsh (11.2-YB-
Type "help" for help.


Now let’s find and kill the yb-tserver listening on by searching for its process id (PID) and then issuing a kill command:

$ ps aux | grep -i yb-tserver | grep -i
guru  	2680  3.8  0.5 1193072 81732 pts/0   Sl   20:27   0:21 /home/guru/Desktop/yugabyte/yugabyte- --fs_data_dirs /home/guru/yugabyte-data/node-1/disk-1 --webserver_interface --rpc_bind_addresses --v 0 --version_file_json_path=/home/guru/Desktop/yugabyte/yugabyte- --webserver_doc_root /home/guru/Desktop/yugabyte/yugabyte- --tserver_master_addrs=,, --yb_num_shards_per_tserver=2 --redis_proxy_bind_address= --cql_proxy_bind_address= --local_ip_for_outbound_sockets= --use_cassandra_authentication=false --ysql_num_shards_per_tserver=2 --enable_ysql=true --pgsql_proxy_bind_address=

$ kill -9 2680
$ ps aux | grep -i yb-tserver | grep -i

After killing the process we are unable to connect to this tablet server:

$ ./bin/ysqlsh --host=
ysqlsh: could not connect to server: Connection refused
    Is the server running on host "" and accepting
    TCP/IP connections on port 5433?

Then we retry again by listing all hosts:

$ ./bin/ysqlsh --host=,,
ysqlsh (11.2-YB-
Type "help" for help.


The client fails to connect to the first host and tries the next one until a connection is successful. In this case a successful connection was established on the tablet server running on

When should I use yb-ctl? Can I use it to monitor the status of a cluster created using manual deployment?

yb-ctl is a CLI for creating and managing YugabyteDB clusters on a single local machine. Such local clusters are meant for development and functional testing purposes only. It internally orchestrates yb-tserver and yb-master servers for RF1 (default) and RF3 configurations.

For performance testing and production environments, a multi-node YugabyteDB cluster running on multiple host machines is needed. There are many different ways to deploy such clusters including the ability to deploy manually using the yb-tserver and yb-master servers directly. Once a multi-node cluster is created without using yb-ctl (using say the manual deployment approach), then that cluster cannot be monitored or managed with yb-ctl. In other words, yb-ctl and other deployment approaches cannot be mixed and matched.

How can I extract the date (yyyy/mm/dd) from a timestamp using YSQL?

PostgreSQL and thus YSQL provide a vast array of functions to manipulate timestamps. A list with examples can be found on the PostgreSQL documentation site. A request that often comes up is extracting a date from a timestamp. This can be easily achieved by casting the timestamp column as a date:

yugabyte=# CREATE TABLE user_login(name TEXT, login_time TIMESTAMP);
yugabyte=# SELECT name, login_time::date FROM user_login;
 name | login_time
 john | 2019-11-11
 bill | 2020-10-22
 jane | 2020-04-01
(3 rows)

Looking a little deeper, we can see that Bill actually logged in from the future! We can also select only users that logged in from the future:

yugabyte=# SELECT name, login_time::date FROM user_login WHERE login_time > (now() at time zone 'utc');
 name | login_time
 bill | 2020-10-22
(1 row)

In YugabyteDB, is there an equivalent to MySQL’s group_concat function?

MySQL provides a group_concat function which returns a string result with the concatenated non-NULL values from a group. The same results can be achieved by using the string_agg function available in YSQL as illustrated in the example below:

yugabyte=# CREATE TABLE student_grades(name TEXT, grade TEXT);

yugabyte=# INSERT INTO student_grades(name,grade) VALUES ('bill', 'A'), ('john', 'B-'), ('bill', 'A'), ('john', 'C-'), ('jane', 'F');

yugabyte=# SELECT name, string_agg(grade,',') AS grades FROM student_grades GROUP BY name ORDER BY name;

 name | grades
 bill | A,A
 jane | F
 john | B-,C-
(3 rows)

In this example, the string_agg function helps easily identify the student with the worst grades.

New Blogs, Tutorials, and Videos

New Blogs

New Videos

Upcoming Events

We Are Hiring!

Yugabyte is growing fast and we’d like you to help us keep the momentum going! Check out our currently open positions:

Our team consists of domain experts from leading software companies such as Facebook, Oracle, Nutanix, Google, and LinkedIn. We have come a long way in a short time but we cannot rest on our past accomplishments. We need your ideas and skills to make us better at every function that is necessary to create the next great software company. All while having tons of fun and blazing new trails!

Get Started

Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.

High performance distributed SQL by Yugabyte

Related Posts

Dorian Hoxha

Related Posts

Learn More to Accelerate Your Retail Business

Ready to dive deeper into distributed SQL, YugabyteDB, and Yugabyte Cloud?
Learn at Yugabyte University
Learn More
Browse Yugabyte Docs
Read More
Join the Yugabyte Community
Join Now