CODEWITHSUNDEEP
postgresqlazure-database-postgresql
Wouter
Wouter

Reputation: 371

How to create database template on azure postgresql service

While using the database service for postgresql om azure, it looks like it is not possible to create a custom template database.

What I want to achieve, is that a regular account, csn create new databases with a specific extension enabled. The creation can be delegated, but the enabling of the extension fails in my test for all but the initial database admin account.

Upvotes: 1

Views: 914

Answers (1)

Nik Larin - Azure Data
Nik Larin - Azure Data

Reputation: 30

I've just tried the following in Azure Database for PostgreSQL and it worked (please bear with me as I walk you through my steps in Azure).

  1. In Azure CLI (bash) followed steps in Quick Start for Azure PostgreSQL to create a new resource group > create a PG server in it > create a new DB (originaldb) on that PG server. All worked just fine.
  2. Then I enabled a earthdistance and cube (pre-req for earthdistance) extensions for orginaldb (the DB I created in step 1).
  3. Then I used CLI to create another DB (dbclone) using orginaldb as a template: CREATE DATABASE dbclone TEMPLATE originaldb; It worked just fine and cube and earthdistance extensions are enabled in dbclone DB.
  4. Now on to trying it with another PG user: I created a PG user (user1) and granted this user DB creation privilege.
  5. Then I logged on to my server as user1 and created another DB from CLI using the same command: CREATE DATABASE dbclone2 TEMPLATE originaldb; It worked too and I see that cube and earthdistance extensions are enabled in dbclone2 database.

Is that what you're trying to do? Are you hitting errors following the same steps or you're trying to do something different?

Upvotes: 1

Related Questions