CODEWITHSUNDEEP
postgresqlazureazure-devopsazure-powershellazure-pipelines-release-pipeline
Ryanman
Ryanman

Reputation: 880

How can I deploy a new version of an Azure PostgreSQL Database backed up in VSTS?

The Problem

My CI/CD workflow is set up pretty well in VSTS and Azure right now. The missing piece is also updating my database after pull requests or checkins to master.

What I've done so far

I have my DB backup scripts in source control. Right now I have the extreme luxury of a small, uncomplicated database. The stopgap measure I've been taking so far has been before a checkin to master:

  1. Cancelling connections to the DB in PGAdmin
  2. Dropping the Existing DB in powershell, using the postgres utility dropdb
  3. Creating a new blank DB in powershell, using createdb
  4. Loading the DB with a psql command (since for some godforsaken reason pg_restore absolutely refuses to work with any archive files I create)

The Questions

First, how would I refer to my backup artifact in my drop location through any powershell scripting measure in VSTS? By the time we get to the "Release" part of the flow, my app has been archived into a .zip file. I've played around with a lot of different tasks and can't seem to find out how to do this.

Second, since my releases are being handled by hosted Azure agents - even if I DO figure out how refer to my database dumps, I can't imagine that these agents have access to postgres utilities like pg_restore or psql. Is my only solution here to spin up my own VM as my release agent and install the utilities before executing the powershell? Or is there some combination of tasks available in VSTS or Azure that I'm missing?

Thanks for your help!

Upvotes: 0

Views: 837

Answers (1)

starian chen-MSFT
starian chen-MSFT

Reputation: 33728

First, you can extract the zip file through Extract files task and the value of System.DefaultWorkingDirectory variable is the directory to which artifacts are downloaded during the deployment of a release.

Secondly, the Hosted agent doesn’t include PostgreSQL and you can’t install on it, so you need to setup a private agent. (Setup the requirements of PostgreSQL on this agent machine manually)

Upvotes: 1

Related Questions