Extract Tablespace DDL in YugabyteDB using ysql_dumpall

YugabyteDB is a distributed SQL database with PostgreSQL compatibility that lets you manage data across multiple regions while ensuring high availability. It uses tablespaces to control data placement across regions or zones, enabling low latency and better data locality by letting you specify where tables and indexes are stored.

For database administrators, extracting schema information like tablespace definitions is essential for backup, migration, and auditing. While YugabyteDB provides several tools for this purpose, it’s important to understand the right tool for extracting global objects such as tablespaces.

ysql_dump is a utility in YugabyteDB designed for backing up and restoring individual databases. However, it does not capture global objects like users, roles, and tablespaces. If you need a comprehensive backup that includes these global objects, you’ll need to use ysql_dumpall.

ysql_dumpall is an extended version of ysql_dump that not only backs up the database schemas and data but also includes global objects, such as:

Users
Roles
Tablespaces
Other configuration settings

If your goal is to extract tablespace definitions specifically, ysql_dumpall provides an easy way to capture this information. The following command can be used to dump the entire schema, including tablespace DDLs:

				
					ysql_dumpall -g

Since ysql_dumpall outputs a lot of information, you can filter the relevant CREATE TABLESPACE DDL statements using standard tools like sed. Here’s how you can extract tablespace DDL from the dump:

				
					ysql_dumpall -g | sed -n '/CREATE TABLESPACE/,/;/p'

Example (also specifying database name with -l):

				
					[root@cloud-server-0 yb2]# ysql_dumpall -l yugabyte -g | sed -n '/CREATE TABLESPACE/,/;/p'
CREATE TABLESPACE us_east_2_ts OWNER yugabyte LOCATION '' WITH (replica_placement='{"num_replicas": 3, "placement_blocks":
  [{"cloud":"aws","region":"us-east-2","zone":"us-east-2-zone","min_num_replicas":1,"leader_preference":1},
   {"cloud":"aws","region":"us-east-1","zone":"us-east-1-zone","min_num_replicas":1,"leader_preference":2},
   {"cloud":"aws","region":"us-west-2","zone":"us-west-2-zone","min_num_replicas":1,"leader_preference":3}]}');
CREATE TABLESPACE us_east_1_ts OWNER yugabyte LOCATION '' WITH (replica_placement='{"num_replicas": 3, "placement_blocks":
  [{"cloud":"aws","region":"us-east-1","zone":"us-east-1-zone","min_num_replicas":1,"leader_preference":1},
   {"cloud":"aws","region":"us-east-2","zone":"us-east-2-zone","min_num_replicas":1,"leader_preference":2},
   {"cloud":"aws","region":"us-west-2","zone":"us-west-2-zone","min_num_replicas":1,"leader_preference":3}]}');
CREATE TABLESPACE us_west_2_ts OWNER yugabyte LOCATION '' WITH (replica_placement='{"num_replicas": 3, "placement_blocks":
  [{"cloud":"aws","region":"us-east-2","zone":"us-east-2-zone","min_num_replicas":1,"leader_preference":2},
   {"cloud":"aws","region":"us-east-1","zone":"us-east-1-zone","min_num_replicas":1,"leader_preference":2},
   {"cloud":"aws","region":"us-west-2","zone":"us-west-2-zone","min_num_replicas":1,"leader_preference":1}]}');

Note that the DDL includes the LOCATION keyword. While this keyword is ignored by YugabyteDB and a warning is issued, the tablespace is still created successfully.

				
					yugabyte=# DROP TABLESPACE us_west_2_ts;
DROP TABLESPACE

yugabyte=# CREATE TABLESPACE us_west_2_ts OWNER yugabyte LOCATION '' WITH (replica_placement='{"num_replicas": 3, "placement_blocks":
yugabyte'#   [{"cloud":"aws","region":"us-east-2","zone":"us-east-2-zone","min_num_replicas":1,"leader_preference":2},
yugabyte'#    {"cloud":"aws","region":"us-east-1","zone":"us-east-1-zone","min_num_replicas":1,"leader_preference":2},
yugabyte'#    {"cloud":"aws","region":"us-west-2","zone":"us-west-2-zone","min_num_replicas":1,"leader_preference":1}]}');
WARNING:  LOCATION not supported yet and will be ignored
LINE 1: CREATE TABLESPACE us_west_2_ts OWNER yugabyte LOCATION '' WI...
                                                      ^
HINT:  See https://github.com/yugabyte/yugabyte-db/issues/6569. React with thumbs up to raise its priority
CREATE TABLESPACE

If you’d prefer to exclude the LOCATION part from the CREATE TABLESPACE DDL, here’s a revised command:

				
					ysql_dumpall -g | sed -n '/CREATE TABLESPACE/,/;/p' | sed 's/LOCATION [^ ]*//'

Have Fun!