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

Before creating a PostgreSQL server from BlueXP workload factory, learn about the available storage deployment types for the database host configuration, 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 have two PostgreSQL server deployment models: High Availability (HA) or single instance. Different resources are created for the FSx for ONTAP file system depending on the FSx for ONTAP deployment model you select.

  • High Availability (HA) deployment: A Multiple Availability Zone FSx for NetApp ONTAP file system is deployed when a new FSx for ONTAP file system is selected for HA deployment. Separate volumes and LUNs are created for data, log, and tempdb files for an HA deployment. An additional volume and LUN are created for Quorum or witness disk for Windows cluster. HA deployment configures Streaming replication between the primary and secondary PostgreSQL servers.

  • Single instance deployment: A Single Availability Zone FSx for ONTAP file system is created when a new PostgreSQL server is created. In addition, separate volumes and LUNs are created for data, log, and tempdb files.

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 read/write mode permissions to create a new database host in workload factory.

Create a database server

You can use Quick create or Advanced create deployment modes 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, AWS CloudFormation, and Terraform. Learn how to use Codebox for automation.

Note When using Terraform from Codebox, the code you copy or download hides fsxadmin and vsadmin passwords. You'll need to re-enter the passwords when you run the code. You'll need to include the following permissions for the user account in addition to Automate mode permissions: iam:TagRole and iam:TagInstanceProfile. Learn how to use Terraform from Codebox.
Quick create
Note In Quick create, HA is the default deployment model, Windows 2016 is the default Windows version, and SQL 2019 Standard Edition is the default SQL version.
Steps

  1. Log in using one of the console experiences.

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

  3. Select Quick create.

  4. Under Landing zone, provide the following:

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

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

      AWS credentials with read-only 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 read/write 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-only 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, CloudFormation, SSM) and the S3 gateway endpoint are created during deployment if not found.

      VPC DNS attributes EnableDnsSupport and EnableDnsHostnames are modified to enable endpoint address resolution if they aren't already set to true.

    3. Availability zones: Select availability zones and subnets.

      Note HA deployments are only supported on Multiple Availability Zone (MAZ) FSx for ONTAP configurations.

      Subnets should not share the same route table for high availability.

      1. In the Cluster configuration - Node 1 field, select the primary availability zone for the MAZ FSx for ONTAP configuration from the Availability zone dropdown menu and a subnet from the primary availability zone from the Subnet dropdown menu.

      2. In the Cluster configuration - Node 2 field, select the secondary availability zone for the MAZ FSx for ONTAP configuration from the Availability zone dropdown menu and a subnet from the secondary availability zone from the Subnet dropdown menu.

  5. Under Application settings, enter a user name and password for Database credentials.

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

  7. Under Infrastructure settings, provide the following:

    1. 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.

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

  8. Summary:

    1. Preview default: Review the default configurations set by Quick create.

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

  9. Click Create.

    Alternatively, if you want to change any of these default settings now, create the database server with Advanced create.

    You can also select Save configuration to deploy the host later.

Advanced create
Steps

  1. Log in using one of the console experiences.

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

  3. Select Advanced create.

  4. Under Deployment model, select Standalone instance or High availability (HA).

  5. Under Landing zone, 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-only 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 read/write 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-only 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

      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.

      For HA deployments

      1. In the Cluster configuration - Node 1 field, select the primary availability zone for the MAZ FSx for ONTAP configuration from the Availability zone dropdown menu and a subnet from the primary availability zone from the Subnet dropdown menu.

      2. In the Cluster configuration - Node 2 field, select the secondary availability zone for the MAZ FSx for ONTAP configuration from the Availability zone dropdown menu and a subnet from the secondary availability zone 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 a new FSx for ONTAP file system, a new security group is created and attached to the SQL node. 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.

  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.