Hi,

I wanted to ask if the Azure team could copy AWS & provide scale ups and scale downs during maintenance windows without any downtime following the AZ replica switchover method for Azure postgres sql.

Shuaib

Latest version available in the docs is 2023-03-01-preview.
https://learn.microsoft.com/en-us/azure/templates/microsoft.dbforpostgresql/flexibleservers?pivots=deployment-language-bicep

Deployments with later version however success without any issues:

resource postgre 'Microsoft.DBforPostgreSQL/flexibleServers@2023-06-01-preview' = {
  name: 'postgre-${name}-${environment}-${region}-${instance}'
  location: location
...
}

Some options available in the later versions are not documented, such as properties.network.publicNetworkAccess

Hello,

I just deployed a new Azure Database for PostgreSQL resource. When configuring the deployment, I selected the latest PostgreSQL version (v16). However, when I log into the database with Azure Cloud Shell, I'm shown:

psql (14.8, server 16.0)
WARNING: psql major version 14, server major version 16.
         Some psql features might not work.

I skimmed through the docs, but only find references to "Major Version Upgrades".

How do I line up the psql version and the server version?

When creating the server in the portal there is an extra option in the UI to add a security setting in order to allow azure services to connect to the server, as showing below

I couldn't find the equivalent option through the ARM template.

Hi,

PostgreSQL 10.x versions have a disclosed security vulnerability that was fixed in PostgreSQL 10.6 according to https://www.postgresql.org/about/news/1905/.

We see the PostgreSQL version in Azure is still 10.5. When can we expect our 10.x servers to be upgraded to 10.6 to close the security hole?

Thanks

When I run the Azure function I have this error :

2019-11-12T17:18:10.653 [Information] Executing 'Functions.EventHubTrigger1' (Reason='', Id=3d0bb39e-8661-47b6-9ba7-0e3ea3821109)
2019-11-12T17:18:10.700 [Error] Executed 'Functions.EventHubTrigger1' (Failed, Id=3d0bb39e-8661-47b6-9ba7-0e3ea3821109)
Result: Failure
Exception: Error: Cannot find module 'pg'
Stack: Error: Cannot find module 'pg'
at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15)
at Function.Module._load (internal/modules/cjs/loader.js:562:25)
at Module.require (internal/modules/cjs/loader.js:692:17)
at require (internal/modules/cjs/helpers.js:25:18)
at module.exports (D:\home\site\wwwroot\EventHubTrigger1\index.js:2:14)
at WorkerChannel.invocationRequest (D:\Program Files (x86)\SiteExtensions\Functions\2.0.12858\32bit\workers\node\worker-bundle.js:16506:26)
at ClientDuplexStream.WorkerChannel.eventStream.on (D:\Program Files (x86)\SiteExtensions\Functions\2.0.12858\32bit\workers\node\worker-bundle.js:16386:30)
at ClientDuplexStream.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)

Do you know where it could come from (knowing that I did install pg before running the function) ?

Thank you.

We are working on a solution based on PostgreSQL in Azure that will need multi-tenancy support, in which case our customers will want to use their own KEK for their data. We can give them separate database, but the current encryption KEK is at the server level. So we're wondering if it's possible to have different KEK for different databases in the same PostgreSQL server?

In a Flexible Server with azure.enable_temp_tablespaces_on_local_ssd enabled, after a failure occurred and the server was started in another AZ, I saw an event where "CREATE TEMPORARY TABLE" returned "No such file or directory". .

Since we are using a local SSD, it would be natural for the DB objects in temp_tablespace to be lost after an event such as starting on a new, different server, but it doesn't seem like a natural behavior to be unable to execute "CREATE TEMPORARY TABLE". .
Isn't it a bug?

I recently moved to a new location (with new IP address), and I am using the same machine with unchanged configuration. I have attempted to connect with the same computer from my office, and it connects just fine.

When attempting to connect to our postgres database via pgadmin I am getting the following error:
FATAL: no pg_hba.conf entry for host "<ipv4-address>", user "<user>", database "postgres", SSL on

I have added the new IP address to Connection Security in the Azure portal, but I am still unable to connect. I thought it might be a temporary hiccup, so I have tried to add and remove the IP-address several times and waiting a day before attempting to connect, but the result is the same.

Any advice on how to proceed? Currently working from home due to the COVID-19 situation and the Postgres database is crucial to my workflow.

Potential ARM Template error: Not sure if the ARM Template is incorrect or it actual uses a different spelling.

ARM Template sample is using: "minimumTlsVersion"

The REST API Docs & Policy Aliases uses "minimalTlsVersion" and PowerShell uses New-AzPostgreSqlServer -MinimalTlsVersion

MinimalTlsVersionEnum

Hello,

I am trying to upgrade a postgres flexible-server from v11 to a newer version put I always get (InternalServerError) An unexpected error occured while processing the request. Tracking ID: '')

More specifically:

• I have created a v11 postgres flexible-server
• Activated the PG_BUFFERCACHE , PG_STAT_STATEMENTS, PLPGSQL and POSTGIS extensions
• Migrated from a v11 postgres single-server
• Made attempts to upgrade the flexible server from 11 to 12, 13, 14 and 15 from the portal and from the az-cli but all attempts failed with the InternalServerError error.

Hi,

The latest version of the Timescale extension on Azure Postgres is 1.1.1 (as per documentation here; https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions#postgres-10-extensions).

Newer versions of Timescale have some critically important features for our use case. Notably the continuous aggregate functions (available since 1.3) would be most useful for our management dashboards. Is there a plan to make later versions of Timescale available this year?

Also, Azure Postgres only makes the Timescale extension available on Postgres version 9 or 10. Is there a plan to enable Timescale on Postgres 11?

Thank you,
Aiden.

When I changed the "postgis.gdal_enabled_drivers" parameter to "ENABLE_ALL" from the Portal "Server Parameters" and "Save", I get the following error:
An error occurred updating server parameters for {servername} An unexpected error occured while processing the request.

Also checking the postgres logs give me this:
2019-01-28 20:00:47 UTC-5c424f3f.5c-ERROR: unrecognized configuration parameter "postgis.gdal_enabled_drivers" 2019-01-28 20:00:47 UTC-5c424f3f.5c-STATEMENT: ALTER SYSTEM SET "postgis.gdal_enabled_drivers" = 'ENABLE_ALL'

I am working to document the operation procedures around PostgreSQL in Azure (PaaS version). Is there any documentation describing how and when Microsoft will patch PostgreSQL in case of for ex. 0-day detected? Have few customers talking about that and this would help me to be precise about the procedure. Thanks in advance!

It appears that it's not possible to create a read replica in a different resource group to the master postgres instance.

This is unfortunate because it reduces the resilience of the postgres deployment: if the resource group is accidentally deleted we lose both the primary postgres server and the read replicas at one time.

Is this correct? If not , how can a read replica be deployed in a separate resource group and region to the primary?

The notification is not sent to the client who is listening to a channel.

Code to show the error: https://github.com/robsonpeixoto/azure-postgresql-listen-notify-bug

Affected users:

• commanded/eventstore#126
• elixir-ecto/postgrex#375
• subzerocloud/pg-amqp-bridge#13

Products that will be affected:

• graphile/crystal#92

As mentioned in the below chart, storage auto-grow feature is available in Single Server but not Flexible Server, what is the plan to enable that feature for Flexible Server? We're new customers and we'd like to use Flexible Server based on Azure's recommendation.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-compare-single-server-flexible-server#comparison-table

Hello,

I deployed a new Azure Database for PostgreSQL server and database. When I try to connect to the database using the Connect button in the Databases blade, the default bash command that Azure Cloud Shell tries fails every time.

Default: bold chars below need to be removed from the command.

psql "host=<your-server-name>.postgres.database.azure.com port=5432 dbname=<your-database-name> user=<your-admin-name>@<your-domain.com> password=\'\$\(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken\)\' sslmode=require"

Should be:

psql "host=<your-server-name>.postgres.database.azure.com port=5432 dbname=<your-database-name> user=<your-admin-name>@<your-domain.com> password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken) sslmode=require"

When applying the pg_restore command, I kept getting an issue stating that the database name was not set. It seems that the following command was not doing anything:

export PGDATABASE=$TARGET_DATABASE_NAME

I updated the pg_restore call to the following which ended up fixing the issue:

pg_restore -v --no-owner --host="$TARGET_SERVER_NAME.postgres.database.azure.com" --port=5432 --username="$TARGET_USERNAME@$TARGET_SERVER_NAME" --dbname=$TARGET_DATABASE_NAME "$SOURCE_DATABASE_NAME.dump"

Is there any reason as to why this issue would come up?

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

We're evaluating a solution to store large volume of data in Azure DB for PostgreSQL permanently, one concern we have is the storage limit of 16TB. Do we plan to increase that limit? Does Azure support on-demand limit increase? AFAIK, GCP PostgreSQL has a storage limit of 64TB that would give us much more confidence https://cloud.google.com/sql/docs/postgres/instance-settings#storage-capacity-2ndgen

I have an Azure Database for Postgres instance that I wanted to roll back a few hours to grab some rows that I'd deleted. Using these instructions I tried to roll back one hour, a few hours, and a few days, but in all cases I'm seeing that the new database that's created from the Restore event has data in it that's newer than the selected point in time.

Is there some special step that I need to take to make this work as expected?

In case it helps, the Restore screen says that it has 35 days of backups.

I found if we need to enable the customer key encryption for the PostgreSQL database, the "serverKeyName" must be in this format: "KeyvaultName_keyName_keyVersion".

which makes it impossible to create the keyvault key and enable the customer-key encryption at the same within one ARM template, cause we cannot use the ARM function "reference" to reference the key we created and extract the dynamically generated version number. The "reference" function cannot be used in variables definition, resource name and types.

Questions:
How to create the keyvault key and enable the customer-key encryption at the same time within one ARM template?

Thanks.

The az postgres server create example in the following document does not work:
https://github.com/Azure/azure-postgresql/blob/master/samples/django-python/0.md#create-an-azure-postgresql-database-server

The error one gets indicates that the --performance-tier option is not valid. It may have been valid earlier, I guess.

Client: az v2.0.19 on mac

We would like to have an option to store PostgreSQL backups and snapshots in an Azure Storage account.

Hello,

Something definitely changed since yesterday. For the past few days I've been putting together an article and video showcasing Azure Database for PostgreSQL. I've taken many screenshots and walked through deployments 3-4x. Today, installing the VECTOR extension no long works.

Steps to reproduce:

1. Fresh install in a new Resource Group
2. Create a new database
3. Install AZURE_AI, AZURE_STORAGE and VECTOR extensions and Save
4. Click Connect next to the new database
5. Create a new Azure Cloud Shell Storage Account
6. Fix the Incorrectly escaped characters in the default Cloud Shell command
7. Try to create a new table that has a vector column

   CREATE TABLE tbltest (
       id bigserial primary key,
       text_content text,
       vector_content vector(3)
   );
   

   • Receive the error:

   ERROR:  type "vector" does not exist
   LINE 4:     vector_content vector(3)
   

   • This worked fine on previous days!

Running SHOW azure.extensions; yields:

       azure.extensions        
-------------------------------
 AZURE_AI,AZURE_STORAGE,VECTOR
(1 row)

Troubleshooting tried:

1. Uninstalled/reinstalled extensions
2. Uninstalled/reinstalled extensions one-at-a-time
3. Ran the following:

testdb=> SHOW azure.extensions;
       azure.extensions        
-------------------------------
 AZURE_AI,AZURE_STORAGE,VECTOR
(1 row)

testdb=> SELECT oid, extname, extversion FROM pg_extension;
  oid  | extname | extversion 
-------+---------+------------
 14126 | plpgsql | 1.0
(1 row)

testdb=> CREATE TABLE tbltest (
    id bigserial primary key,
    text_content text,
    vector_content vector(3)
);

ERROR:  type "vector" does not exist
LINE 4:     vector_content vector(3)
                           ^
testdb=> CREATE EXTENSION vector;
CREATE EXTENSION

testdb=> SELECT oid, extname, extversion FROM pg_extension;
  oid  | extname | extversion 
-------+---------+------------
 14126 | plpgsql | 1.0
 25107 | vector  | 0.5.1
(2 rows)

testdb=> CREATE TABLE tbltest (
    id bigserial primary key,
    text_content text,
    vector_content vector(3)
);
CREATE TABLE

Seems that the azure.extensions VECTOR isn't working, at least not for me.

Thoughts?

Here is an example list of steps in order to add an Entra group with a database schema select grant. While some are achievable using tools like terraform or bicep, it feels like Azure could make this process more accessible and automated. Additionally, the existence of functions like pgaadauth_create_principal only in the cloud, makes the automation testing more difficult.

1. In Azure Portal, Azure Database for PostgreSQL flexible server | Databases create test database
2. In Azure Portal, Azure Database for PostgreSQL flexible server | Authentication | Add Microsoft Entra Admins create MyAdAdmin user, using an existing MyAdAdmin AAD group
3. Connect to the postgres database using MyAdAdmin (due to MicrosoftDocs/azure-docs#102693 (comment))

   POSTGRES_USER=MyAdAdmin
   export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)
   PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER postgres
   

   and create a MyAdReader user, using an existing MyAdReader AAD group

   SELECT * FROM pgaadauth_create_principal('MyAdReader', false, false);
               pgaadauth_create_principal             
   ---------------------------------------------------
   Created role for "MyAdReader"
   (1 row)
   SELECT * FROM pgaadauth_list_principals(false);
      rolname  | principaltype |               objectid               |               tenantid               | ismfa | isadmin 
   ------------------------------------+---------------+--------------------------------------+--------------------------------------+-------+---------
    MyAdAdmin  | group         | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx1 | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |     0 |       1
    MyAdReader | group         | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx2 | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |     0 |       0
   (2 rows)  
   

4. As the regular database Admin (not MyAdAdmin) connect to test database

   POSTGRES_USER=Admin
   PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER test
   

   and create a schema

   CREATE SCHEMA "myschema";
   CREATE TABLE "myschema"."mytable" (mycolumn text);
   INSERT INTO "myschema"."mytable" (mycolumn) VALUES ('Hello, world!');
   

   and add grants to MyAdReader

   GRANT USAGE ON SCHEMA "myschema" TO "MyAdReader";
   GRANT SELECT ON ALL TABLES IN SCHEMA "myschema" TO "MyAdReader";
   

5. As MyAdReader connect to test database

   POSTGRES_USER=MyAdReader
   export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query "[accessToken]" -o tsv)
   PGSSLMODE=require psql -h $POSTGRES_HOST --port=$POSTGRES_PORT --username=$POSTGRES_USER test
   

   and verify the grant

   SELECT * FROM "myschema"."mytable";
      mycolumn    
   ---------------
   Hello, world!
   (1 row)
   

Microsoft Azure SQL Database has this handy page that shows the Recommended Alert Rules to monitor Azure SQL Databases: https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-metrics-alerts?view=azuresql#recommended-alert-rules

Could something similar be created for Azure Database for PostgreSQL Flexible Server?

Thanks in advance!

We deploy PostgreSQL servers for our customers using ARM templates and the public/private implementation for flexible server is very confusing. For single servers we use service endpoints to secure public servers and private endpoints to secure the private ones. With flexible there is no way to restrict the public servers to be allowed from VNET only, IP whitelisting doesn't make any sense. Of course we could use the private access mode to secure the traffic, but the implementation is very complicated. Dedicated subnet and private DNS zone requirements make this task impossible to automate. We can't create subnets or private DNS zones in our customer's subscription just for this purpose.

Please consider to remove the dedicated subnet requirement and implement public DNS support instead of private DNS for private access mode like in AKS.

Hello,
the script exports and imports the tables and contents, but my Devs define separate roles for each schema (schema_reader & schema_writer) so they can make better defined security.
Would it be possible to include in this script to also get the users, roles, schema/table-owners to the new one?
Thanks
Sebastian

We are using Azure postgresql flexiable and we have observed instances in which a change (update) of a record(s) get back to it's previous value randomly.

For example, in a table that has a column named "value", when I change it from A to B, it gets changed/commited but after a while (maybe an hour), we see that the value of that record is revereted back to "A".

This is so weird and we have no clue what's happening.

Azure flexiable server is deployed with a sha1 certitifcate for TLS connectivtiy.

https://learn.microsoft.com/en-us/answers/questions/1199915/certificates-do-not-conform-to-algorithm

sha1 certitifcates has been unsupported and depreaced since 2021
(https://learn.microsoft.com/en-us/lifecycle/announcements/sha-1-signed-content-retired)

Can this be upgraded to sha256?

Hello, I am trying to create an Azure AD role in postgres server with the pipeline. I login with the Azure AD Admin (configured in the ARM template). This Azure AD admin is a AAD group in the same tenant. However when I am trying to run the query CREATE ROLE "[email protected]" WITH LOGIN PASSWORD 'Azure Object ID' IN ROLE azure_ad_user
with pipeline , it throws below error. The same query when run through the Azure Data Studio with Azure AD admin login (same), works well. The pipeline service principal has the graph permissions too. Has anyone come across this kind of scenario in Postgres?

Please help. The goal is to create a Azure AD user through pipeline.

Will it be possible to include the queries from https://github.com/pgexperts/pgx_scripts/tree/master/bloat to the notebook?