YugabyteDB is a distributed SQL database compatible with PostgreSQL, designed for high availability across multiple regions. For database administrators, extracting schema details—such as tablespace definitions, roles, and users—is essential for tasks like backups, migrations, and audits. In YugabyteDB, tablespaces are used to control data placement across regions or zones, helping optimize for low latency, high availability, and data locality.
In the previous tip, Extract Tablespace DDL in YugabyteDB using ysql_dumpall, we explored how ysql_dumpall can be used to export all databases in a YugabyteDB cluster into a single plain-text SQL script. Unlike ysql_dump, this utility also includes global objects shared across databases—like roles and tablespaces.
If you’re specifically looking to extract tablespace definitions,
ysql_dumpall offers a straightforward way to do that. But there’s also a simple alternative—using SQL to pull the tablespace DDL directly from the Postgres pg_tablespace system catalog using:
SELECT spcname, spcoptions FROM pg_tablespace WHERE spcname NOT IN ('pg_default', 'pg_global');
Example:
yugabyte=# \x
Expanded display is on.
yugabyte=# SELECT spcname, spcoptions FROM pg_tablespace WHERE spcname NOT IN ('pg_default',
'pg_global');
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------
spcname | us_east_2_ts
spcoptions | {"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}]}"}
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------
spcname | us_east_1_ts
spcoptions | {"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}]}"}
-[ RECORD 3 ]------------------------------------------------------------------------------------------------------------------------------
spcname | us_west_2_ts
spcoptions | {"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}]}"}
Hold on a second—what’s in spcoptions doesn’t exactly look like DDL… because it’s not! We’ll need to do a little creative massaging to transform this metadata into proper CREATE TABLESPACE commands. 😄
Something like this:
SELECT 'CREATE TABLESPACE ' || spcname || ' WITH (' || REPLACE(REPLACE(REPLACE(RIGHT(spcoptions::TEXT, -2), '\', ''), '={', '=''{'), '"}', ''')') || ';' FROM pg_tablespace WHERE spcoptions IS NOT NULL ORDER BY spcname;
Example:
yugabyte=# SELECT 'CREATE TABLESPACE ' || spcname || ' WITH (' || REPLACE(REPLACE(REPLACE(RIGHT(spcoptions::TEXT, -2), '\', ''), '={', '=''{'), '"}', ''')') || ';' FROM pg_tablespace WHERE spcname NOT IN ('pg_default', 'pg_global') ORDER BY spcname;
CREATE TABLESPACE us_east_1_ts 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_east_2_ts 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_west_2_ts 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}]}');
Have Fun!
