What's the recommended way to seed Azure PostgreSQL flexible server?

Question

Hello,

I'm deploying an Azure PostgreSQL flexible server database. I want to seed this database with an existing SQL dump that creates one table with some data.

My original approach was to create a deploymentScript with Bicep that:

1. Downloads the SQL dump from Azure Blob Storage into the ACL
2. Seeds the Postgres database with the pg_restore command

However, I noticed that the ACL instance that runs the deployment script doesn't have pg_restore installed. This means I would either have to install it or use a custom container image.

I found an alternative on the Azure GitHub: https://github.com/Azure/azure-postgresql/tree/master/arm-templates/ExampleWithStartupScript

There they seed the database using an Ubuntu VM.

So what is the recommended way to seed my database?

Answer 1

Hello,

I solved the problem using an Azure deployment script. I install postgresql-client in the Alpine Linux container in which the script runs.

   resource createPostgresTable 'Microsoft.Resources/deploymentScripts@2020-10-01' = {  
     name: 'createPostgresTable'  
     location: location  
     kind: 'AzureCLI'  
     identity: {  
       type: 'UserAssigned'  
       userAssignedIdentities: {  
         '${managedIdentityId}': {}  
       }  
     }  
     properties: {  
       azCliVersion: '2.37.0'  
       retentionInterval: 'P1D'  
       environmentVariables: [  
         {  
           name: 'administratorLogin'  
           value: postgresDatabase.properties.administratorLogin  
         }  
         {  
           name: 'PGPASSWORD'  
           value: administratorLoginPassword  
         }  
         {  
           name: 'postgresDatabaseName'  
           value: postgresDatabase.name  
         }  
       ]  
       cleanupPreference: 'OnSuccess'  
       forceUpdateTag: currentTime  
       scriptContent: '''  
         az login --identity  
     
         apk add --no-cache postgresql-client   
     
         psql \  
           --host=${postgresDatabaseName}.postgres.database.azure.com \  
           --username=$administratorLogin \  
           --dbname=postgres <<-EOSQL  
             CREATE TABLE IF NOT EXISTS public.pressure (  
               timestamp TIMESTAMPTZ NOT NULL,  
               asset_id BIGINT NOT NULL,  
               sensor_name VARCHAR (25) NOT NULL,  
               pressure REAL,  
               PRIMARY KEY (timestamp, asset_id)  
           );  
         EOSQL  
       '''  
     }  
   }