How to Backup and Restore Babelfish T-SQL Databases

Babelfish has specific versions of pg_dump to correctly handle Babelfish database roles and structures. These specialized tools are bbf_dumpall and bbf_dump, which include additional code to correctly backup and restore databases using standard PostgreSQL restore tools.

Important: You can only restore a database on a Babelfish cluster that is the same major and minor version as the source cluster, and the target database must not exist.

For detailed documentation, see the Babelfish dump and restore wiki.

For Bash scripts to help run backup and restore of Babelfish T-SQL databases see my repo article How to Backup and Restore Babelfish T-SQL Databases.

The following is a quick start guide from the article How to Backup and Restore Babelfish T-SQL Databases.

Prerequisites

The easiest way to use the bbf_dump commands is to launch an Amazon EC2 instance with Amazon Linux 2023 (AL2023) using the Amazon EC2 console, as the tools come pre-built. Otherwise, you would need to compile them from source.

For development, I highly recommend using Cursor IDE with the Remote - SSH extension to connect to your EC2 instance. This provides a full IDE experience with integrated chat capabilities to interact with files and run scripts directly against the EC2 instance.

Setup Development Environment

1. Connect to EC2 with Cursor Remote-SSH

1. Install Cursor IDE from cursor.com
2. Install the "Remote - SSH" extension
3. Configure SSH connection to your EC2 instance
4. Connect and enjoy a full IDE experience on your remote server

Manual Commands (Alternative)

If you prefer running commands manually:

Backup Commands

# Source environment
source ~/db_config.sh

# Backup roles
bbf_dumpall --database $PGDATABASE \
            --host=$PGHOST \
            --port=$PGPORT \
            --username $PGUSER \
            --bbf-database-name=northwind \
            --roles-only \
            --quote-all-identifiers \
            --verbose \
            --no-role-passwords \
            -f northwind-roles-$(date +%Y%m%d).sql

# Backup database
bbf_dump --dbname=$PGDATABASE \
         --host=$PGHOST \
         --port=$PGPORT \
         --username $PGUSER \
         --bbf-database-name=northwind \
         --quote-all-identifiers \
         --verbose \
         --file=northwind-db-$(date +%Y%m%d).tar \
         --format=tar

Restore Commands

# Apply roles
psql --host=$TARGET_PGHOST \
     --port=$PGPORT \
     --dbname=$PGDATABASE \
     --username $PGUSER \
     --single-transaction \
     --file northwind-roles-20240127.sql

# Restore database
pg_restore --host=$TARGET_PGHOST \
           --port=$PGPORT \
           -d $PGDATABASE \
           -U $PGUSER \
           --verbose \
           northwind-db-20240127.tar

Security Best Practices

1. Store credentials securely: Never hardcode passwords in scripts. Use environment variables or AWS Secrets Manager.

2. Secure backup files: Ensure backup files are stored with appropriate permissions and consider encryption for sensitive data.

3. Network security: Use VPC security groups to restrict database access to authorized EC2 instances only.

4. Audit access: Enable CloudTrail and RDS logging to track database access and operations.

Troubleshooting

• Version compatibility: Ensure source and target Babelfish clusters are the same major.minor version
• Database exists error: The target database must not exist before restore
• Permission errors: Verify the user has sufficient privileges for backup/restore operations
• Network connectivity: Ensure EC2 instance can reach both source and target RDS clusters

Enjoy, Bill Ramos, Senior Principal Data Architect, Caylent