search

bill-ramos-rmoswi / babelfish_postgresql_towel

Bill Ramos's knowledge base for tips, tricks, and best practices for migrating SQL Server solutions to Babelfish for PostgreSQL and the open-source WiltonDB for Windows
Apache License 2.0
1 stars 1 forks source link

Query Option for backing up database from PostgreSQL #6

Open Aravind2601 opened 6 days ago

Aravind2601 commented 6 days ago

@bill-ramos-rmoswi we have the below query in sql which needs to updated to support in babelfish for the database migration. But I could see the database backup option postgresql using the Pg_dump and pg_restore .Its like the dump and restore option are utility which we not able to use it in query i think. Question: Is there any other option to replace in query itself for the database backup in postgresql. SQL Query: BACKUP DATABASE employee TO DISK = 'c:\temp\OKB\employee.bak' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup of SQLTestDB';

Aravind2601 commented 5 days ago

@bill-ramos-rmoswi any update on above query ?

bill-ramos-rmoswi commented 4 days ago

Hi @Aravind2601, What's needed is to automate the WiltonDB Backup Restore tool using a Windows automation program like Power Automate.

staticlibs commented 3 days ago

@Aravind2601 , I think @bill-ramos-rmoswi is right about the automation, just wanted to share some details on this topic:

  1. I assume you need to backup a single logical T-SQL DB. Otherwise, if you backing up the whole physical Babelfish DB or the whole DB clusted, the details below do not apply.

  2. backup in Babelfish needs to be done on Postgres connection with pg_dump. Getting all schema details on TDS connection (in addition to all data that can be expored with bulk copy API) is possible in theory, but there is no tooling for it. Also there is currently no way to get consistent export over TDS when exporting live DB is being witten to (pg_dump uses serializable isolation level so does not have this problem).

  3. there is no way to trigger backups directly from T-SQL code. I suggest to have a Powershell ot Python backup script that is triggered by some kind of scheduler. If necessary, the scheduling can be driven from DB - pg_agent can be adapted to do this.

  4. to perform a backup of a single logical DB it is not neccessary to use wdb_backup or any other additional tools - it is enough to invoke pg_dump (and pg_dumpall if users/roles are necessary) with correct parameters.

  5. to performs a restore of a logical DB additional tools are basically required. When using pg_restore/psql Babelfish can only restore logical DB under the same name it was imported with (rejected related enhancement suggestion). This limitation makes restores almost unusable, for example DB cannot be restored in to the same instance from where it was backed up without dropping it first.

  6. wdb_backup supports rewriting logical DB name when performing the restore. Just it is not necessary to run the GUI for this, all the rewriting logic lives in pgdump_toc_rewrite command line utility, so it can be easily scripted from Powershell/Python.

  7. pgdump_toc_rewrite is not yet foolproof, in some cases Babelfish catalogs can be rewritten incorrectly. I am going to look into this (and into adding support for Babelfish Aurora 4.x to it) after SSPI-supported update of WiltonDB is out. If you meet any problems with it - please add the details to #16.

  8. when writing backup/restore scripts I suggest to refer to Babelfish Dump and Restore (bbf_dump/restore utilities referred there are the same as pg_dump/restore included with WiltonDB) and Scripting backup and restore operations articles.

Aravind2601 commented 2 days ago

@staticlibs and @bill-ramos-rmoswi Thanks for the response!!