PostgresDB PaaS
Setup Postgres Service on Azure Portal
An Azure Database for PostgreSQL server is created with a configured set of compute and storage resources. The server is created within an Azure resource group.
Prerequisites
The following prerequisites are necessary for setting up Postgres Database on Azure.
- A Microsoft account is required to work with Azure Cloud Platform. Sign up for a Microsoft account, if you do not have one.
- Ensure that you have an Azure subscription with a billing account.
PostgreSQL Database
Azure Database for PostgreSQL is a managed service that you use to run, manage, and scale highly available PostgreSQL databases in the cloud. To set up Postgres Database, refer to the official Azure Documentation.
Setup Postgres
Step 1: To set up Postgres, navigate to Azure Console, search for Azure Database for PostgreSQL flexible servers, and click it.
Step 2: Click Create.
PostgreSQL Basics
This section provides basic information about your Postgres database
Step 1: Enter the required details for the following mandatory fields.
- Subscription
- Resource Group
- Server Name
- Region
- PostgreSQL Version
- Admin Username
- Password
- Confirm Password
(contd.)
(contd.)
| Category | Description |
|---|---|
| Subscription | The name of your Subscription(Account) in which the PostgreSQL database is to be set up. |
| Resource Group | Name of the Group under which the PostgreSQL database should be available |
| Server Name | A unique name that identifies your Azure Database for PostgreSQL server. |
| Region | Name of the Region in which the cluster is to be setup. |
| PostgreSQL Version | Infoworks recommends using PostgreSQL Version 13. |
| Workload Type | Select the environment (Development or Production) for your Postgres DB, whichever it will be used for. If the database instance is set up for a critical application, depending upon the usage and the size of data which will be ingested, select the appropriate Size (Small/Medium or Large). |
| Authentication Method | Select the type of Authentication you want to set up. Infoworks supports PostgreSQL authentication only. |
| Admin Username/ Password | Your own login account to use when you connect to the server. The admin login name can't be azure_superuser, azure_pg_admin, admin, administrator, root, guest, or public. It can't start with pg_. Set a strong password using uppercase letters, English lowercase letters, numbers, and non-alphanumeric characters |
Step 2: Click Next: Networking>.
Networking
Networking in the below mentioned scenario describes securing the database and accessing it privately without exposing it to the outside world.
Step 1: Fill the required details for setting up the networking.
- Connectivity Method
- Subscription
- Virtual Network
- Subnet
- Private DNS Zone
(contd.)
(contd.)
| Category | Description |
|---|---|
| Connectivity Method | Choose whether the database should be accessible over a public or private network. Infoworks recommends to set up connectivity over private network |
| Subscription | The name of your Subscription (account) in which the PostgreSQL database is to be set up. |
| Virtual Network | Select the Virtual Network to be associated with the database |
| Subnet | Select the appropriate subnet within the VNet. |
| Private DNS Zone | Create a new Zone or select an existing DNS Zone. This will create a FQDN for the Postgres database. |
Step 2: Click Next: Security (preview).
Security Preview
The storage used for database and backup is encrypted by default with service managed keys. Data Encryption additionally encrypts your databases, backups, and logs at rest without any changes to your application.
Step 1: Keep the default settings and click Next: Tags >.
Tags
Tags are name/value pairs that enable you to categorize resources and view consolidated billing by applying the same tag to multiple resources and resource groups.
Step 1: Add the tags as per your organization standards.
Step 2: Click Next: Review + Create >.
Review + Create
Verify the details and click Review + Create to complete the configurations and deploy the PostgreSQL Database.
(contd.)
High Availability
High Availability makes sure that the database service is always available and connectivity remains intact even in a scenario where one instance of the database is down or unavailable.
Infoworks recommends enabling High Availability to avoid downtime in case of an outage on the Postgres Machines.
Enabling High Availability
Enabling High Availability for a New Instance
Step 1: To enable High Availability for a new instance of PostgreSQL, navigate to Azure Console, search for Azure Database for PostgreSQL flexible servers, and click it.
Step 2: Click Create.
Step 3: Under the Basics tab, click Enable High Availability checkbox.
Enabling High Availability for an Existing Instance
Step 1: To enable High Availability for an existing instance of PostgreSQL, open an existing Postgres instance from the Azure portal,
Step 2: Under the Settings section, click High Availability, and click it.
Step 3: Select Enable High Availability checkbox.
Backup and Restore
Backup and restore functionality makes sure that no data is lost in case of any outages. A daily backup is automatically saved by Azure which can be restored in case of any discrepancies in the datasets stored in Postgres.
This is an automated feature that does not require any additional configuration to be made.
User Permissions
For Infoworks to use Postgres PaaS, considering the variables set during the installation, the given Postgres user (set in POSTGRESDB_USERNAME) needs to have the LOGIN Role. It must also have all privileges on a given database (set in INFOWORKS_POSTGRESDB_DATABASE_NAME). For more information, refer to Infoworks Installation on Azure Kubernetes Service (AKS).
To set the privileges:
xxxxxxxxxxCREATE USER <POSTGRESDB_USERNAME>;GRANT ALL PRIVILEGES ON DATABASE <INFOWORKS_POSTGRESDB_DATABASE_NAME> TO <POSTGRESDB_USERNAME>;Verifying Connection from PostgresDB to Infoworks
Verify that applications can access the PostgreSQL DB from Infoworks AKS cluster.
Use the following procedure as a reference only.
Step 1: Log in to your Bastion host where Kubernetes cluster is accessible.
Step 2: Create a pod with PostgreSQL image in the respective namespace.
xxxxxxxxxxkubectl run postgres --image postgres --restart=Never --namespace <namespace> sleep 3000Step 3: Run the exec command to run bash command inside the pod.
xxxxxxxxxxkubectl exec -it postgres --namespace <namespace> -- bashStep 4: Get the connection string from MongoDB Atlas Connect. For example, consider the below given command.
xxxxxxxxxxroot@postgres:/# psql --host <postgres-db-name>.postgres.database.azure.com --port 5432 --username <username> --dbname <database_name>Step 5: Provide the password.
Password for user <username>:psql (15.1 (Debian 15.1-1.pgdg110+1), server 13.7)SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)Type "help" for help.postgres=>The MongoDB and Infoworks are successfully connected. In case of any errors, refer to official Postgres Documentation.
Deleting Postgres Pod
Once the connection between Postgres and Infoworks is successful. If you wish to delete Postgres pod, execute the following command.
xxxxxxxxxxkubectl delete pods postgres --namespace <namespace>