Skip to main content

Create a PostgreSQL server in BlueXP workload factory

Contributors netapp-rlithman
PDFs

Creating a new PostgreSQL server, or database host, in BlueXP workload factory for Databases requires an FSx for ONTAP file system deployment and resources for Active Directory.

About this task

You'll need AWS account credentials and automate permissions.

Before you begin, learn about the available storage deployment types for the database host configuration, Active Directory deployment, workload factory modes of operation, and the requirements to complete this operation.

FSx for ONTAP file system deployments

Creating a new PostgreSQL server requires an FSx for ONTAP file system as the storage backend. You can use an existing FSx for ONTAP file system or create a new file system. If you select an existing FSx for ONTAP file system as your database server storage backend, we create a new storage VM for the PostgreSQL workloads.

FSx for ONTAP file systems supports standalone deployments for PostgreSQL. Workload factory creates a Single Availability Zone FSx for ONTAP file system in addition to separate volumes and LUNs for data, log, and tempdb files.

Workload factory operation modes

Workload factory offers three operational modes depending on how comfortable you are with letting workload factory manage your AWS resources.

Basic mode: in this mode of operation, you don't need to associate any AWS account credentials in workload factory. You can copy or download a partially filled YAML template from the Codebox to be completed outside workload factory.

Read mode: in this mode of operation, you provide AWS account credentials with read permissions which lets you complete the Quick create or Advanced create form and then copy or download it. You can also redirect to CloudFormation from workload factory with the completed form details. And you will be able to manage the deployed database server in workload factory.

Automate mode: in this mode of operation, you provide AWS account credentials with automate permissions which lets you create and manage AWS resources within workload factory.

Before you begin

Ensure you have the following prerequisites before you create a new database host.

Credentials and permissions

You must have AWS account credentials and automate mode permissions to create a new database host in workload factory.

Create a database server

You can use the Advanced create deployment mode to complete this task in workload factory with Automate mode permissions. You can also use the following tools available in the Codebox: REST API, AWS CLI, and AWS CloudFormation. Learn how to use Codebox for automation.

Steps

  1. Log in using one of the console experiences.

  2. In the Databases tile, select Deploy database host and then select PostgreSQL from the dropdown menu.

  3. Select Advanced create.

  4. For Deployment model, Standalone instance is the default.

  5. Under AWS settings, provide the following:

    1. AWS credentials: Select AWS credentials with automate permissions to deploy the new database host.

      AWS credentials with automate permissions let workload factory deploy and manage the new database host from your AWS account within workload factory.

      AWS credentials with read permissions let workload factory generate a CloudFormation template for you to use in the AWS CloudFormation console.

      If you don't have AWS credentials associated in workload factory and you want to create the new server in workload factory, follow Option 1 to go to the Credentials page. Manually add the required credentials and permissions for automate mode for Database workloads.

      If you want to complete the create new server form in workload factory so you can download a complete YAML file template for deployment in AWS CloudFormation, follow Option 2 to ensure you have the required permissions to create the new server within AWS CloudFormation. Manually add the required credentials and permissions for read mode for Database workloads.

      Optionally, you can download a partially completed YAML file template from the Codebox to create the stack outside workload factory without any credentials or permissions. Select CloudFormation from the dropdown in the Codebox to download the YAML file.

    2. Region & VPC: Select a Region and VPC network.

      Ensure security groups for an existing interface endpoint allow access to HTTPS (443) protocol to the selected subnets.

      AWS Service interface endpoints (SQS, FSx, EC2, CloudWatch, Cloud Formation, SSM) and S3 gateway endpoint are created during deployment if not found.

      VPC DNS attributes EnableDnsSupport and EnableDnsHostnames are modified to enable resolve endpoint address resolution if not already set to true.

    3. Availability zones: Select availability zones and subnets.

      For single instance deployments

      1. In the Cluster configuration - Node 1 field, select an availability zone from the Availability zone dropdown menu and a subnet from the Subnet dropdown menu.

    4. Security group: Select an existing security group or create a new security group.

      Two security groups get attached to the SQL nodes (EC2 instances) during new server deployment.

      1. A workload security group is created to allow ports and protocols required for PostgreSQL.

      2. For an existing FSx for ONTAP file system, the security group associated with it is added automatically to the PostgreSQL node which allows communication to the file system. When a new FSx for ONTAP system is created, a new security group is created for the FSx for ONTAP file system and the same security group also gets attached to SQL node.

  6. Under Application settings, provide the following:

    1. Select the Operating system from the dropdown menu.

    2. Select the PostgreSQL version from the dropdown menu.

    3. Database server name: Enter the database cluster name.

    4. Database credentials: Enter a user name and password for a new service account or use existing service account credentials in the Active Directory.

  7. Under Connectivity, select a key pair to connect securely to your instance.

  8. Under Infrastructure settings, provide the following:

    1. DB Instance type: Select the database instance type from the dropdown menu.

    2. FSx for ONTAP system: Create a new FSx for ONTAP file system or use an existing FSx for ONTAP file system.

      1. Create new FSx for ONTAP: Enter user name and password.

        A new FSx for ONTAP file system may add 30 minutes or more of installation time.

      2. Select an existing FSx for ONTAP: Select FSx for ONTAP name from the dropdown menu, and enter a user name and password for the file system.

        For existing FSx for ONTAP file systems, ensure the following:

        • The routing group attached to FSx for ONTAP allows routes to the subnets to be used for deployment.

        • The security group allows traffic from the subnets used for deployment, specifically HTTPS (443) and iSCSI (3260) TCP ports.

    3. Snapshot policy: Enabled by default. Snapshots are taken daily and have a 7-day retention period.

      The snapshots are assigned to volumes created for PostgreSQL workloads.

    4. Data drive size: Enter the data drive capacity and select the capacity unit.

    5. Provisioned IOPS: Select Automatic or User-provisioned. If you select User-provisioned, enter the IOPS value.

    6. Throughput capacity: Select the throughput capacity from the dropdown menu.

      In certain regions, you may select 4 GBps throughput capacity. To provision 4 GBps of throughput capacity, your FSx for ONTAP file system must be configured with a minimum of 5,120 GiB of SSD storage capacity and 160,000 SSD IOPS.

    7. Encryption: Select a key from your account or a key from another account. You must enter the encryption key ARN from another account.

      FSx for ONTAP custom encryption keys aren't listed based on service applicability. Select an appropriate FSx encryption key. Non-FSx encryption keys will cause server creation failure.

      AWS-managed keys are filtered based on service applicability.

    8. Tags: Optionally, you can add up to 40 tags.

    9. Simple Notification Service: Optionally, you can enable the Simple Notification Service (SNS) for this configuration by selecting an SNS topic for Microsoft SQL Server from the dropdown menu.

      1. Enable the Simple Notification Service.

      2. Select an ARN from the dropdown menu.

    10. CloudWatch monitoring: Optionally, you can enable CloudWatch monitoring.

      We recommend enabling CloudWatch for debugging in case of failure. The events that appear in the AWS CloudFormation console are high-level and don't specify the root cause. All detailed logs are saved in the C:\cfn\logs folder in the EC2 instances.

      In CloudWatch, a log group is created with the name of the stack. A log stream for every validation node and SQL node appear under the log group. CloudWatch shows script progress and provides information to help you understand if and when deployment fails.

    11. Resource rollback: This feature isn't currently supported.

  9. Summary

    1. Estimated cost: Provides an estimate of charges that you might incur if you deployed the resources shown.

  10. Click Create to deploy the new database host.

    Alternatively, you can save the configuration.

What's next

You can manually configure users, remote access, and databases on the deployed PostgreSQL server.