How to dump YSQL schema, global objects, and database structure

Environment

• YugabyteDB Version - 2024.2.3 or later

Process

This process outlines how to obtain a schema capture from a YugabyteDB Anywhere cluster for analysis, without transferring row data.

• Additional Information: This procedure covers three capture types: cluster-wide global objects (roles, tablespaces), the structure of every database in a single pass, and a schema-only dump of all available databases.

Resolution

Overview

ysql_dump and ysql_dumpall are the YSQL equivalents of PostgreSQL's pg_dump and pg_dumpall. Use ysql_dumpall --globals-only for cluster-wide objects, ysql_dumpall --no-data to capture every database's structure in one command, and ysql_dump --schema-only when only one database's schema is needed. Run all commands as the yugabyte OS user on a database node.

Steps

1. Find the ysql_dumpall binaries.

find /home/yugabyte -name "ysql_dumpall"

Example output:

/home/yugabyte/yb-software/yugabyte-2025.2.4.0-b122-linux-x86_64/postgres/bin/ysql_dumpall

NOTE: The path shown reflects a YBA-managed install under /home/yugabyte/yb-software/. Depending on installation configuration settings, installs may place the binaries elsewhere. Adjust the search root if /home/yugabyte returns nothing.

NOTE: If the command returns multiple versions, one line per installed release, pick the binary matching the version currently running on the cluster. Confirm the running version through the YBA UI, or by running ysqlsh -h <node_ip> -c "SELECT version();". In most cases this is the most recently installed version.

Set the paths as variables for the commands below.

Substitute <ysql_dump_path> and <ysql_dumpall_path> with the values found here.

2. Dump the global objects (roles, tablespaces).

<ysql_dumpall_path> -h <node_ip> --globals-only --binary-upgrade --quote-all-identifiers > globals_dump.sql

• --globals-only: dumps only cluster-wide objects, roles and tablespaces. Skips per-database schema and data.
• --binary-upgrade: includes internal object IDs required for a binary-compatible restore.
• --quote-all-identifiers: wraps every identifier in double quotes. Prevents case-folding mismatches on restore.

3. Dump the database structure. Two approaches, depending on whether you need every database or just one.

          dump every database's structure in one command:

<ysql_dumpall_path> -h <node_ip> --no-data --quote-all-identifiers --binary-upgrade --with-statistics > all_databases_dump.sql

• --no-data: dumps structure only, no row data.
• --quote-all-identifiers: wraps every identifier in double quotes. Prevents case-folding mismatches on restore.
• --binary-upgrade: includes internal object IDs required for a binary-compatible restore.
• --with-statistics: includes planner statistics. The restored database does not need an immediate ANALYZE.

4. Confirm the output files exist and are non-empty.

ls -la globals_dump.sql all_databases_dump.sql

Open the file and check for CREATE ROLE or CREATE TABLE statements matching the cluster's objects.

5. Capture the exact build for version matching.

SELECT version();

6. Attach the following to the support ticket:
   • globals_dump.sql from step 2
   • all_databases_dump.sql from step 3
   • Output of SELECT version(); from step 5

Additional Information

• Common issues might include:
  • No output file created — check that the binary path from step 1 is correct and executable.
  • Connection refused — confirm the node IP from hostname -i matches the node YSQL is bound to, and that port 5433 is reachable.
  • Missing custom roles or tablespaces in a single-database dump — ysql_dump only captures objects inside one database. Run step 2 to capture globals separately.

SUPPORT-1116