Postgres SQL is support in Hana Database ?

[former_member250641]

Hi Expert,

I have existing PostgreSQL DB now our requirement to move the database to Hana ,

So can we directly used the PostgreSQL Script in Hana to create the same structure of database ?

Thanks,

Sumit

[kapil_tilwani · Accepted solution]

Hi Sumit,

It all boils down to what you would really like to do. If reading from Information_schema is required, it is perfectly doable - SAP HANA has metadata exposing system views which would give you the desired results. All you may need to do is create a wrapper (read view) on these tables and voila - for eg., INFORMATION_SCHEMA.TABLES can point to SYS.M_CS_TABLES with appropriate field aliasing as well.

The challenge you may most likely face would be in areas where you are using DB-specific functionality - e.g., partitioning, tablespaces, view-updates, materialised views.

In summary, you would definitely need to go through an entire DB migration but I would say it should not be overly complicated.

Regards,

Kapil

[lucas_oliveira]

Hi Sumit,

Well looks like you faced your first migration issue and an old classic: databases don't implement SQL standards only, they do their own thing. COPY function is an example which is not on the SQL Standard. HANA on the other hand, does not have that feature (it sure have other import/export features).

So for the COPY function, I'm sure you can verify on the Postgres documentation how generate insert statements instead of COPY. Of course, you can still bump into differences such as: data types, imperative logic, etc.

So what I see here is possibly a database migration project (unless you're dealing with a fairly simple database *and* you're not super tied to Postgres specifics) . If that's the case, then using a migration tool (like SAP Data Services) to support the process is a good idea (at least for the data migration). Procedures, functions and complex types defined will demand their migration time and effort accordingly.

BRs,

Lucas de Oliveira

[former_member250641]

Hi Lucas,

Thanks for your reply .

Our PostgreSQL is not quit simple . As there are some predefined (Inbuild) Schema like information_schema is used to create function /Procedure or trigger , So my concern how we can handle that like to create the same in Hana?

Thanks,

Sumit

[lucas_oliveira]

Hi Sumit,

One thing that it's important to stress here is before going any further: db migrations are *not* straightforward. There's no magic button you can push to make it happen.

You'll need to evaluate exactly what you have on postgres that needs to go to HANA and specially how to adapt the current scenario to HANA. For example: maybe on your Postgres system you have many databases with many schemas on each database. In that case, it seems to fit into an MDC scenario where each HANA tenant would match to a specific Postgres database. Of course, this is an example not necessarily the reality.

Another detail here is to keep up with the application side expectations. In other words, application migration (or more likely adaptation). That's just as hard as a db migration and depends strongly on what you use at the front-end (how tied you are to postgres). In short, a successful migrated DB does not mean you can simply aim the application at the new database and hope it will work.

Again, this kind of study/preparation and decisions needs to be done by whoever is driving the migration taking into account the applications used therein. No magic.

As per schema information_schema is part of postgres architecture and I'm not sure what is its counter part on HANA (if exists).

BRs,

Lucas de Oliveira

[kapil_tilwani]

Hi Sumit,

Yes, you can. SAP HANA is ANSI-SQL compliant - same as PostgreSQL. But it all boils down to how much of the base ANSI-SQL you have really used.

PostgreSQL has extended functionalities to use it is as on ORDBMS, Procedure polymorphism, etc., which are different in SAP HANA. Some examples of small but important differences exist like there is no varchar(max), varchar is not unicode, procedures do not have polymorphism.

But, if you have been using PostgreSQL largely as a data-store, it should be a smooth migration. But beware, once you have migrated to SAP HANA though, the performance is quite addictive. 🙂

Regards,

Kapil

[former_member250641]

Hi Kapil ,

Thanks for your reply .

But I facing issue to create the schema, table and function using existing SQL script .

One more thing can we create the historical function like copy_to_log , and all the Postgress SQL function in Hana ?

Thanks,

Sumit