– Azure Database for PostgreSQL is a service that provides a fully managed instance of community PostgreSQL in the Azure cloud for any application sizing. It’s the same open-source software that is used in on-premises servers, with Azure taking care of maintenance and security obligations.
– The PostgreSQL layer is based on the popular open-source PostgreSQL Community Edition has its own query language called pgsql. This language is a variant of the standard relational query language, SQL. Azure Database for PostgreSQL allows you to work with popular frameworks and languages such as Ruby on Rails, Python with Django, Java with Spring Boot, .NET, and Node.js.
– Azure Database for PostgreSQL give options four noticeable benefits: High availability, with an SLA of up to 99.99% uptime, AI-powered performance optimization, Automated hardware and software maintenance, Advanced data and server security.
– Data is automatically encrypted and backed up. Options such as Advanced Threat Protection make it simple to address potential threats without the need to be a security expert or manage advanced security monitoring systems.
– Azure Database for PostgreSQL takes backups of the data files and the transaction log. Depending on the supported maximum storage size, we either take full and differential backups (4-TB max storage servers) or snapshot backups (up to 16-TB max storage servers). These backups allow you to restore a server to any point-in-time within your configured backup retention period. The default backup retention period is seven days.
– These backup files cannot be exported. The backups can only be used for restore operations in Azure Database for PostgreSQL. You can use pg_dump to copy a database.
– Azure Database for PostgreSQL provides the flexibility to choose between locally redundant or geo-redundant backup storage in the General Purpose and Memory Optimized tiers. When the backups are stored in geo-redundant backup storage, they are not only stored within the region in which your server is hosted, but are also replicated to a paired data center.
– Azure Database for PostgreSQL provides up to 100% of your provisioned server storage as backup storage at no additional cost. Any additional backup storage used is charged in GB per month.
*Using Private Link:
– Private Link allows you to create private endpoints for Azure Database for PostgreSQL – Single server to bring it inside your Virtual Network (VNet). The private endpoint exposes a private IP within a subnet that you can use to connect to your database server just like any other resource in the VNet.
– Disable all Azure service traffic to Azure Database for PostgreSQL Single server via the public endpoint by setting Allow Azure Services to OFF. Ensure no IP addresses or ranges are allowed to access the server either via firewall rules and connection security settings. Only allow traffic to the Azure Database for PostgreSQL Single server using the Private IP address of the VM.
1/Create VNet and VM For Installing pgAdmin4 Manage Tool:
– Deploy VM Ubuntu Using For pgadmin4 Tool Installed
– SSH connect to Azure VM and install pgadmin4 tool:
Install required packages:
# sudo apt-get install build-essential libssl-dev libffi-dev libgmp3-dev virtualenv python-pip libpq-dev python-dev
Add PostgreSQL APT repository
# sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main” >> /etc/apt/sources.list.d/pgdg.list’
# sudo apt install wget ca-certificates
# wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add Update and install pgadmin4 tool
# sudo apt update
# sudo apt install pgadmin4
– Configure pgAdmin4-Web:
# sudo /usr/pgadmin4/bin/setup-web.sh
– Create NSG rule for accessing pgadmin4 portal from internet only for public IP of admin computer
2/Create an Azure Database for PostgreSQL server by using the Azure portal:
3/Configure Private Link On Azure Database for PostgreSQL server:
– Before configure private link on VM pgadmin4 try to resolve server name of PostgreSQL server and return public IP of server instance
– Click Add new private endpoint
– On pgadmin4 VM use nslookup to resolve server name of PostgreSQL server will return private IP of db-subnet after configure private link
4/ Deny Public Network Access in Azure Database for PostgreSQL and add server to pgadmin4:
– Change Deny public network access to Yes > Save
– On pgadmin4 portal > Add New Server
Click Save > Add Azure Postgres Server to pgadmin4 successful using private link
5/ Configure Logging On Azure Postgres Server:
– Create Log Analytics Workspace
– Send logs to Azure Monitor (Log Analytics Workspace)
– Click on Add diagnostic setting
– Check metric CPU usage and number of active connection on postgresql server