0

Can someone pls help me to automate build and release pipelines in Azure DevOps for Azure DB for PostgreSQL database (Single Server) so that I can create a Database and run different scripts in that database for creating/altering tables, functions, indexes etc. ?

I googled and found nothing in Microsoft documentation for this purpose but I did find it can be done using Zapier

As per organizational policies I can not use Zapier or any third-party tools/sites.

Is it doable only using Microsoft build and release tasks in Azure DevOps, can someone pls guide me with any steps for this purpose ?

S2S2
  • 8,322
  • 5
  • 37
  • 65

1 Answers1

1

Database DevOps is difficult because you have to manipulate existing objects, not simply replace them like you do for application deployments. To do this, you have to add a tool that manages your Data Definition Language queries. Or you can build one. We did that a long time ago. I don't recommend it. Tons of work, lots of issues.

For PostgreSQL, I'd suggest you start testing Flyway. It works really well with Azure DevOps. I have a short video you can use to see it in action. Flyway is open source, so getting started with it is license free. You can install the software, but it also runs through containers, so it makes it really simple to implement through the Azure DevOps agents. The concept is pretty simple. It acts as a marshalling tool to run your DDL in the correct order, like a manifest. Then, it marks the database so it knows which scripts it has already run. You go from there.

Grant Fritchey
  • 2,645
  • 19
  • 21
  • 1
    In addition I would mention that connecting to a database from Azure DevOps agents requires some networking. You can ether whitelist dynamically the agents IP or use self hosted agents which have a static IP or integrate to your vnets. https://learn.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops&tabs=browser#install – bursson Sep 28 '21 at 15:48
  • Exactly. Although if you're all in Azure, it's a lot easier. – Grant Fritchey Sep 29 '21 at 12:35