Cannot run stored procedures for postgreSQL in azure

Harditya Sarvaiya 1 Reputation point
2020-09-01T13:11:15.027+00:00

I have to migrate my PostgreSQL from on-premise to the Azure cloud. I have stored procedures in the on-premise database. But I cannot find a way to run those on Azure Database for Postgresql. There is no option for PostgreSQL as a source for dataflows or stored procedures inside the ADF. Is there any workaround for this?

Azure Database for PostgreSQL
{count} votes

1 answer

Sort by: Most helpful
  1. Navtej Singh Saini 4,171 Reputation points
    2020-09-02T18:30:27.773+00:00

    @Harditya Sarvaiya

    ADF does not support a built-in PostgreSQL stored procedure. As a workaround, you can consider using an Azure Function with ADF that runs the logic you want.

    Don't have exact working example for this but will work mostly like:
    module.exports = async function (TableName, LastModifiedDate) {
    var pg = require('pg');
    //const config = "postgres://<username>:<password>@<postgres servername>:5432/<database>";

            const config = {  
                                host: 'servername.postgres.database.azure.com',  
                                user: 'user@servername',       
                                password: <password>,  
                                database: '',  
                                port: 5432,  
                                ssl: true  
            };  
              
            var client = new pg.Client(config);  
            const query = ' UPDATE mytable  
          SET [Value] = @LastModifiedtime   
    WHERE [TableName] = @TableName';  
            context.log(query);  
            client.connect();  
            client.query(query);  
            await client.end();  
            context.log('insert completed successfully!');        
        };  
    

    There are other methods to migrate you can look into like using DMS or Export/Import

    Hope this helps.

    Thank you
    Navtej S

    No comments