WiltonDB Backup GUI tool
On this page:
- GUI Backup tool overview
- Create example database in SSMS
- Backup from WiltonDB
- Restore DB under different name
- Backup and restore with Babelfish for Aurora PostgreSQL
In PostgreSQL, pg_dump utility is used to back up a single database into a file (or a set of files). In Babelfish, when multi-db mode is enabled, then multiple "logical" T-SQL databases live inside a single "physical" PostgreSQL database.
Modified PostgreSQL version (that is used in Babelfish) implements additional option --bbf-database-name that allows to back up one single selected logical DB.
WiltonDB Backup tool uses these modified pg_dump and pg_restore utilities under the hood, they are included with the distribution.
Backup tool requires Microsoft Visual C++ Redistributable to be installed (direct download link: VC_redist.x64.exe).
Note: backed up DB can only be restored to the same major Babelfish version. Thus a backup done from WiltonDB version 3.3 can be restored back to an instance of WiltonDB 3.3 or Babelfish for Aurora PostgreSQL version 3.x.
Note 2: this article is about GUI Windows tool used to perform backups manually. To do this on Linux or to automate backup/restore see Scripting backup and restore operations.
Open a connection to WiltonDB from SSMS and run the following SQL snippet in master database:
create database mydb1
create database mydb2
use mydb2
create table mytab1(name1 nvarchar(42))
create table mytab2(name2 nvarchar(42))
insert into mytab1 values('foo')
insert into mytab1 values('bar')
insert into mytab1 values('baz')
This creates two logical T-SQL databases and two tables inside the second created database.
Download the latest version of Backup tool, unzip the directory and run wdb_backup.exe:
DB connection dialog is displayed:
To perform a backup WiltonDB Backup tool uses pg_dump under the hood, that connects to DB using PostgreSQL protocol (default port 5432). The "Connect DB" field is only necessary for initial connection, all subsequent backup and restore activity uses the Babelfish physical database (babelfishpg_tsql.database_name readonly system property).
Fill the connect form, test the connection to ensure it is correct and press "Load DB names" button to read the list of names of logical database to choose from for backing up.
On this form you can choose a DB name from the loaded list, the destination directory and the name of the resulting backup file.
Backup tool uses --format=directory option to produce a directory with a single compressed text file for every DB table (and also additional Table Of Contents - TOC file). This backup directory is written under the chosen destination directory. After that this directory is packed into a .zip file and deleted.
Choose mydb2 DB from the list and press "Run backup" button:
If you unzip the resulting mydb2.zip file you can see 10 files there:
toc.dat is a table of contents file, other 9 files contain the data of backed up tables. While we have ony 2 tables mytab1 and mytab2 in our example DB, additional 7 Babelfish catalog tables from sys schema are also included into backup:
When backup is restored it may be necessary to restore the logical DB under different name. For example, restore mydb2 into mydb2_backed_up_last_thursday DB. Such functionality is not natively supported in pg_dump or pg_restore, besides that
Babelfish (as of version 3.3) does not support renaming logical DBs.
To work around this limitation Backup tool uses pgdump_toc_rewrite library that allows to change name of a logical DB in backup files. To do so, after the backup file is unzipped, Backup tool rewrites the name of logical DB in TOC file (and also in dump files of Babelfish catalog tables) before calling pg_restore on these file. It also creates appropriate DB roles automatically for a specified DB name.
DB name is only rewritten in the temporary unzipped directory, original .zip backup file renames intact and can be restored multiple times under different names.
Open the "Restore" tab of Backup tool and choose mydb2.zip file in file system:
"Postgres DB" on this form is the name of the physical Babelfish DB. And "Restore into DB" is the name of the destination logical DB. If we try to restore to the same WiltonDB instance using the same mydb2 name an error about "DB already exists" will occur:
Lets choose different destination DB name and run the restore:
We can check in SSMS that restored DB is available and that table data is there:
Backup tool supports backup/restore with both WiltonDB locally and Babelfish for Aurora PostgreSQL in a cloud. When restoring backups the same major version of Babelfish must be used, for WiltonDB 3.3 it is "Aurora PostgreSQL (Compatible with PostgreSQL 15.x)":
Please refer to AWS documentation about creating Babelfish DB instance on Aurora. To be able to work with multiple logical databases "Multiple databases" options must be enabled:
After Babelfish instance is up and running, Backup tool can be used with Aurora the same way it is used with WiltonDB:
