Question
Crestone Technology
MX
Last activity: 26 Jun 2018 16:30 EDT
How to import a DB table with a Pega BLOB from Postgres to Oracle
Summary
I am migrating our Pega environments. Our work history table is far too large to migrate via the Migrate Work wizard (1.5 million records). I am hoping that someone may have done a similar export/conversion/import and may have advice and examples.
Source Environment: Pega 7.2.1 on Postgres (Pega Cloud)
Target Environment: Pega 7.2.1 on Oracle (On-Premise installation)
Process we expect to do the following, but we are open to suggestions.
- export from Postgres to CSV
-- (perform transformations on export as needed)
- import to Oracle via SQL*Loader
-- (perform transformations on import as needed)
Challenge: the BLOB. The most difficult piece seems to be how to handle the BLOB (pzPVStream)
In the CSV table dump we get from Postgres from the Cloud team, the BLOB column appears as a string of Hexadecimal text, always beginning with "\x5a6c423700000". I will freely confess my ignorance on the subject; I have never exported/imported BLOB DB data. Can anyone provide suggestions how I might approach this?
Attached is a small CSV file. It is an export of some sample records in the format we receive them from the Cloud team.
Cheers,
Elliott
Accepted Solution
Crestone Technology
MX
The answer turned out to be to ask the Cloud team to use a different format for their export. Our DBA talked with them directly and worked this out.
Cheers,
Elliott
JPMorgan Chase & Company
US
Hi,
As of my understanding this is fine, this will save you a great deal of time when importing them into other DBMses, since pretty much everything understands CSV. Keep the schemas consistent for each instance so you don't need to handle different schemas for different instances.
Accepted Solution
Crestone Technology
MX
The answer turned out to be to ask the Cloud team to use a different format for their export. Our DBA talked with them directly and worked this out.
Cheers,
Elliott