Postgresql – Azure DBaaS storage

azurepostgresql

Does Azure Database for PostgreSQL has its storage auto-expandable?
Say, we have allocated 100G of space for the DB instance. Would it expand on its own.

Where can we see the server log storage capacity for Azure Database for PostgreSQL?

Best Answer

Regarding your first question: It is not auto-expandable.

Reaching the storage limit

The server is marked read-only when the amount of free storage reaches less than 5 GB or 5% of provisioned storage, whichever is less. For example, if you have provisioned 100 GB of storage, and the actual utilization goes over 95 GB, the server is marked read-only. Alternatively, if you have provisioned 5 GB of storage, the server is marked read-only when the free storage reaches less than 250 MB.

When the server is set to read-only, all existing sessions are disconnected and uncommitted transactions are rolled back. Any subsequent write operations and transaction commits fail. All subsequent read queries will work uninterrupted.

You can either increase the amount of provisioned storage to your server or start a new session in read-write mode and drop data to reclaim free storage. Running SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE; sets the current session to read write mode. In order to avoid data corruption, do not perform any write operations when the server is still in read-only status.

We recommend that you set up an alert to notify you when your server storage is approaching the threshold so you can avoid getting into the read-only state. For more information, see the documentation on how to set up an alert.

Regarding your 2nd Question: The server log storage is in the same bucket as the database files. You can configure the server logging to meet your requirements. See here

Storage

The storage you provision is the amount of storage capacity available to your Azure Database for PostgreSQL server. The storage is used for the database files, temporary files, transaction logs, and the PostgreSQL server logs. The total amount of storage you provision also defines the I/O capacity available to your server.

More info here

Related Solutions

SQL Azure Pool Security – Security Per Database

You can restrict user per database. I will explain here using sql authentication and you can do the same if you have AD set up.

I have 3 database and 'taiobmdjamshed' is the server admin and have access to all the databases.

I create security1 login with db_owner privilege to security1 database and a user at master database.

CREATE LOGIN security1 
    WITH PASSWORD = 'S<<MM12345!@@!' 
GO

Create a user in master database.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

Make a user in security1 database and make it a member of db_owner role.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'security1'
GO

If connecting to the server using security1 login and try to access security2 database this is what will happen because security1 login does not have a corresponding user or privilege in security2 database.

This link explains more in details.

SQL Server – Resolve SQLVDI Error When Backing Up to Azure Storage BLOB

Crikey, this is one of those "no idea how I fixed it" things.

Before I went to lunch, I could reliably generate the error in question by running the provided T-SQL, yet when I came back from lunch the BACKUP command completed fine!

The main thing I remember changing was on the Azure side, where I created a SAS (Shared Access Signature).

This is supposedly not required for SQL 2014 as it uses an actual Azure Credential to connect to the storage instead. I actually created the SAS for an instance of SQL Server 2016 that I want backing up to the same container, and that may have opened the access pathway to the container for SQL Server 2014 too!

Many thanks to Sean Gallardy for recommending ProcMon, which showed a whole ton more error log information than the "ErrorCode=(5)Access is denied" message did.