IBM Cloud Docs
Getting started with Databases for PostgreSQL

Getting started with Databases for PostgreSQL

This tutorial guides you through the steps to quickly start by using Databases for PostgreSQL by provisioning an instance, setting up pgAdmin, setting your Admin password, and setting up logging and monitoring.

Follow these steps to complete the tutorial:

Follow these steps to complete the tutorial:

Follow these steps to complete the tutorial:

Follow these steps to complete the tutorial:

Before you begin

Step 1: Provision through the console

  1. Log in to the IBM Cloud console.

  2. Click the Databases for PostgreSQL service in the catalog.

  3. In Service details, configure the following:

    • Service name - The name can be any string and is the name that is used on the web and in the CLI to identify the new deployment.
    • Resource group - If you are organizing your services into resource groups, specify the resource group in this field. Otherwise, you can leave it at default. For more information, see Managing resource groups.
    • Location - The deployment's public cloud region or Satellite location.

  4. Resource allocation - Specify the initial RAM, disk, and cores for your databases. The minimum sizes of memory and disk are selected by default. With dedicated cores, your resource group is given a single-tenant host with a minimum reserve of CPU shares. Your deployments are then allocated the number of cores that you specify. Once provisioned, disk cannot be scaled down.

  5. In Service configuration, configure the following:

    • Database version Set only at deployment - The deployment version of your database. To ensure optimal performance, run the preferred version. The latest minor version is used automatically. For more information, see Database Versioning Policy.
    • Encryption - If you use Key Protect, an instance and key can be selected to encrypt the deployment's disk. If you do not use your own key, the deployment automatically creates and manages its own disk encryption key.
    • Endpoints Set only at deployment - Configure the Service Endpoints on your deployment.

    After you configure the appropriate settings, click Create to start the provisioning process. The Databases for PostgreSQL Resource list page opens.

  6. Click Create. The Cloud Databases Resource list page opens.

  7. When your instance has been provisioned, click the instance name to view more information.

Step 1: Provision through the CLI

You can provision a Databases for PostgreSQL instance through the CLI. If you don't already have it, you need to install the IBM Cloud CLI.

  1. Log in to IBM Cloud with the following command:

    ibmcloud login

    If you use a federated user ID, it's important that you switch to a one-time passcode (ibmcloud login --sso), or use an API key (ibmcloud --apikey key or @key_file) to authenticate. For more information about how to log in using the CLI, see General CLI (ibmcloud) commands under ibmcloud login.

  2. Create a Databases for PostgreSQL instance.

    Select one of the following methods:

    • To create an instance from the CLI on the Enterprise plan, run the following command:

      ibmcloud resource service-instance-create <INSTANCE_NAME> <SERVICE_NAME> <SERVICE_PLAN_NAME> <LOCATION> <SERVICE_ENDPOINTS_TYPE> <RESOURCE_GROUP>

    The fields in the command are described in the table that follows.

    Basic command format fields
    Field Description Flag
    NAME Required The instance name can be any string and is the name that is used on the web and in the CLI to identify the new deployment.
    SERVICE_NAME Required Name or ID of the service. For Databases for PostgreSQL, use databases-for-postgresql.
    SERVICE_PLAN_NAME Required Standard plan (standard)
    LOCATION Required The location where you want to deploy. To retrieve a list of regions, use the ibmcloud regions command.
    SERVICE_ENDPOINTS_TYPE Configure the Service Endpoints of your deployment, either public or private. The default value is public.
    RESOURCE_GROUP The Resource group name. The default value is default. -g
    --parameters JSON file or JSON string of parameters to create service instance -p

    You see a response like:

    Creating service instance INSTANCE_NAME in resource group default of account    USER...
OK
Service instance INSTANCE_NAME was created.

Name:                INSTANCE_NAME
ID:                  crn:v1:bluemix:public:databases-for-postgresql:us-east:a/   40ddc34a846383BGB5b60e:dd13152c-fe15-4bb6-af94-fde0af5303f4::
GUID:                dd13152c-fe15-4bb6-af94-fde0af56897
Location:            LOCATION
State:               provisioning
Type:                service_instance
Sub Type:            Public
Service Endpoints:   public
Allow Cleanup:       false
Locked:              false
Created at:          2023-06-26T19:42:07Z
Updated at:          2023-06-26T19:42:07Z
Last Operation:
                     Status    create in progress
                     Message   Started create instance operation

  3. To check provisioning status, use the following command:

    ibmcloud resource service-instance <INSTANCE_NAME>

    When complete, you see a response like:

    Retrieving service instance INSTANCE_NAME in resource group default under account USER's Account as USER...
OK

Name:                  INSTANCE_NAME
ID:                    crn:v1:bluemix:public:databases-for-postgresql:us-east:a/40ddc34a953a8c02f109835656860e:dd13152c-fe15-4bb6-af94-fde0af5303f4::
GUID:                  dd13152c-fe15-4bb6-af94-fde5654765
Location:              <LOCATION>
Service Name:          databases-for-postgresql
Service Plan Name:     standard
Resource Group Name:   default
State:                 active
Type:                  service_instance
Sub Type:              Public
Locked:                false
Service Endpoints:     public
Created at:            2023-06-26T19:42:07Z
Created by:            USER
Updated at:            2023-06-26T19:53:25Z
Last Operation:
                       Status    create succeeded
                       Message   Provisioning PostgreSQL with version 12 (100%)

  4. (Optional) Deleting a service instance Delete an instance by running a command like this one:

    ibmcloud resource service-instance-delete <INSTANCE_NAME>

Connect to your database with the CLI

Find the appropriate commands to connect to your database from the CLI in Cloud Databases CLI Reference and Connecting with psql.

The ibmcloud cdb deployment-connections command handles everything that is involved in creating a CLI connection. For example, to connect to a deployment named "example-postgres", use a command like:

ibmcloud cdb deployment-connections example-postgres --start

The command prompts for the admin password and then runs the psql CLI to connect to the database. To install the Cloud Databases plug-in, see Connecting with psql documentation here.

The --parameters parameter

The service-instance-create command supports a -p flag, which allows JSON-formatted parameters to be passed to the provisioning process. Some parameter values are Cloud Resource Names (CRNs), which uniquely identify a resource in the cloud. All parameter names and values are passed as strings.

For example, if a database is being provisioned from a particular backup and the new database deployment needs a total of 9 GB of memory across three members, then the command to provision 3 GBs per member looks like:

ibmcloud resource service-instance-create databases-for-postgresql <SERVICE_NAME> standard us-south \
-p \ '{
  "backup_id": "crn:v1:blue:public:databases-for-postgresql:us-south:a/54e8ffe85dcedf470db5b5ee6ac4a8d8:1b8f53db-fc2d-4e24-8470-f82b15c71717:backup:06392e97-df90-46d8-98e8-cb67e9e0a8e6",
  "members_memory_allocation_mb": "3072"
}'

Step 1: Provision through the resource controller API

Follow these steps to provision by using the resource controller API.

  1. Obtain an IAM token from your API token.

  2. You need to know the ID of the resource group that you would like to deploy to. This information is available through the IBM Cloud CLI.

    Use a command like:

    ibmcloud resource groups

  3. You need to know the region that you would like to deploy into.

    To list all of the regions that deployments can be provisioned into from the current region, use the Cloud Databases CLI plug-in.

    The command looks like:

    ibmcloud cdb regions --json

    Once you have all the information, provision a new resource instance with the IBM Cloud resource controller.

    curl -X POST \
  https://resource-controller.cloud.ibm.com/v2/resource_instances \
  -H 'Authorization: Bearer <>' \
  -H 'Content-Type: application/json' \
    -d '{
    "name": "my-instance",
    "target": "blue-us-south",
    "resource_group": "5g9f447903254bb58972a2f3f5a4c711",
    "resource_plan_id": "databases-for-postgresql-standard"
  }'

    The parameters name, target, resource_group, and resource_plan_id are all required.

List of Additional Parameters

  • backup_id- A CRN of a backup resource to restore from. The backup must be created by a database deployment with the same service ID. The backup is loaded after provisioning and the new deployment starts up that uses that data. A backup CRN is in the format crn:v1:<...>:backup:<uuid>. If omitted, the database is provisioned empty.

  • version - The version of the database to be provisioned. If omitted, the database is created with the most recent major and minor version.

  • disk_encryption_key_crn - The CRN of a KMS key (for example, Hyper Protect Crypto Services or Key Protect), which is then used for disk encryption. A KMS key CRN is in the format crn:v1:<...>:key:<id>.

  • backup_encryption_key_crn - The CRN of a KMS key (for example, Hyper Protect Crypto Services or Key Protect), which is then used for backup encryption. A KMS key CRN is in the format crn:v1:<...>:key:<id>.

    To use a key for your backups, you must first enable the service-to-service delegation.

  • members_memory_allocation_mb - Total amount of memory to be shared between the database members within the database. For example, if the value is "6144", and there are three database members, then the deployment gets 6 GB of RAM total, giving 2 GB of RAM per member. If omitted, the default value is used for the database type is used.

  • members_disk_allocation_mb - Total amount of disk to be shared between the database members within the database. For example, if the value is "30720", and there are three members, then the deployment gets 30 GB of disk total, giving 10 GB of disk per member. If omitted, the default value for the database type is used.

  • members_cpu_allocation_count - Enables and allocates the number of specified dedicated cores to your deployment. For example, to use two dedicated cores per member, use "members_cpu_allocation_count":"2". If omitted, the default value "Shared CPU" uses compute resources on shared hosts.

  • service-endpoints - The Service Endpoints supported on your deployment, public or private.

Step 1: Provision through Terraform

Use Terraform to manage your infrastructure through the ibm_database Resource for Terraform.

Using APIs

Use the Cloud Databases API to work with your Databases for PostgreSQL instance. The resource controller API is used to provision an instance.

Step 2: Set the Admin password

The admin user

When you provision a Databases for PostgreSQL deployment, an Admin user is automatically created.

Set the admin password before using it to connect.

When you provision a new deployment in IBM Cloud, you are automatically given an admin user to access and manage PostgreSQL. Once you set the admin password, use it to connect to your deployment.

When admin creates a resource in a database, like a table, admin owns that object. Resources that are created by admin are not accessible by other users, unless you expressly grant permissions to them.

The biggest difference between the admin user and any other users you add to your deployment is the pg_monitor and pg_signal_backend roles. The pg_monitor role provides a set of permissions that makes the admin user appropriate for monitoring the database server. The pg_signal_backend role provides the admin user the ability to send signals to cancel queries and connections that are initiated by other users. It is not able to send signals to processes owned by superusers.

You can also use the admin user to grant these two roles to other users on your deployment.

To expose the ability to cancel queries to other database users, grant the pg_signal_backend role from the admin user. Use a command like:

GRANT pg_signal_backend TO joe;

To allow the user joe to cancel backends, grant pg_signal_backend to all the users with the ibm-cloud-base-user role with a command like:

GRANT pg_signal_backend TO "ibm-cloud-base-user";

This privilege allows the user or users to terminate any connections to the database.

To set up a specific monitoring user, mary, use a command like:

GRANT pg_monitor TO mary;

Grant pg_signal_backend to all the users with the ibm-cloud-base-user role with a command like:

GRANT pg_monitor TO "ibm-cloud-base-user";

Setting the Admin Password in the UI

Set your Admin Password through the UI by selecting your instance from the Resource List in the IBM Cloud Dashboard. Then, select Settings. Next, select Change Database Admin Password.

Set the Admin password through the CLI

Use the cdb user-password command from the IBM Cloud CLI Cloud Databases plug-in to set the admin password.

For example, to set the admin password for a deployment named example-deployment, use the following command:

ibmcloud cdb user-password example-deployment admin <newpassword>

Set the Admin password through the API

The Foundation Endpoint that is shown in the Overview Deployment Details section of your service provides the base URL to access this deployment through the API. Use it with the Set specified user's password endpoint to set the admin password.

curl -X PATCH `https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/users/admin` \
-H `Authorization: Bearer <>` \
-H `Content-Type: application/json` \
-d `{"password":"newrootpasswordsupersecure21"}` \

Set the Admin password through Terraform

To set the Admin password, use the API:

The Foundation Endpoint that is shown in the Overview Deployment Details section of your service provides the base URL to access this deployment through the API. Use it with the Set specified user's password endpoint to set the admin password.

curl -X PATCH `https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/users/admin` \
-H `Authorization: Bearer <>` \
-H `Content-Type: application/json` \
-d `{"password":"newrootpasswordsupersecure21"}` \

You can also use the CLI:

Use the cdb user-password command from the IBM Cloud CLI Cloud Databases plug-in to set the admin password.

For example, to set the admin password for a deployment named example-deployment, use the following command:

ibmcloud cdb user-password example-deployment admin <newpassword>

To set the Admin password through the UI, follow these steps:

Set your Admin password through the UI by selecting your instance from the Resource List in the IBM Cloud Dashboard. Then, select Settings. Next, select Change Database Admin password.

Step 3: Set up pgAdmin

pgAdmin runs as a server and you connect to it through a browser. When the server is started, it runs on localhost, at default http://127.0.0.1:53113/browser/.

When you first open pgAdmin, you get a prompt for setting a primary password. This password is different from your instance's password as it is used specifically for pgAdmin to store passwords to your PostgreSQL servers or PostgreSQL instance.

The Dashboard panel has a Welcome screen. From the Quick links, click Add new server.

On your instance's Overview page, there is an Endpoints panel with all the relevant connection information.

Back in pgAdmin, provide pgAdmin with the information it needs to connect to your instance.

First, complete the Connection information,

  • For Host name/address, use the Hostname of your instance.
  • For the Port, use the Port of your instance.
  • The Maintenance database remains postgres.
  • For Username and Password, use the admin credentials that you set after provisioning your instance. You can choose for pgAdmin to save the password.
  • The Role and Service fields can be left empty.

Then, configure the SSL settings.

  • Copy the certificate information from the Endpoints panel in your instance's Dashboard overview page.
  • Save the certificate to a file. (You can use the name that is provided in the download, or your own file name.)
  • Set the SSL mode field to Verify-Full.
  • In the Root certificate field, select the file where you saved your instance's certificate.

Back on the General tab, give your instance a name and add any comments that you want to describe or identify your instance in pgAdmin.

If the Connect now? field is checked, pgAdmin attempts to connect to your instance when you click the Save button.

Use pgAdmin

Once pgAdmin connects, your instance appears in the Servers list and you get a Dashboard with information and statistics.

In the list of databases in the Browser, there is both the postgres database, which you are connected to, and the ibmclouddb database, which is the default database for all Databases for PostgreSQL deployments. Click ibmclouddb to connect to it and expand the information about it.

Use pgAdmin to view, administer, and manage your data and databases in your Databases for PostgreSQL instance. For more information, see pgAdmin documentation.

Administrative features that require a superuser are not available through pgAdmin because there is no superuser access available to users of a Databases for PostgreSQL deployment.

Step 4: Set up context-based restrictions

Context-based restrictions give account owners and administrators the ability to define and enforce access restrictions for IBM Cloud® resources based on the context of access requests. Access to Cloud Databases resources can be controlled with context-based restrictions and Identity and Access Management (IAM) policies.

To set up context-based restrictions for your Databases for PostgreSQL instance, follow the steps at Protecting Cloud Databases resources with context-based restrictions.

Step 5: Connect IBM Cloud Monitoring through the console

You can use IBM Cloud Monitoring to get operational visibility into the performance and health of your applications, services, and platforms. IBM Cloud Monitoring provides administrators, DevOps teams, and developers full stack telemetry with advanced features to monitor and troubleshoot, define alerts, and design custom dashboards.

For more information about how to use Monitoring with Databases for PostgreSQL, see Monitoring integration.

Step 5: Connect IBM Cloud Monitoring through the CLI

You can use IBM Cloud Monitoring to get operational visibility into the performance and health of your applications, services, and platforms. IBM Cloud Monitoring provides administrators, DevOps teams, and developers full stack telemetry with advanced features to monitor and troubleshoot, define alerts, and design custom dashboards.

For more information about how to use Monitoring with Databases for PostgreSQL, see Monitoring integration.

You cannot connect IBM Cloud Monitoring by using the CLI. Use the console to complete this task. For more information, see Monitoring integration.

Step 5: Connect IBM Cloud Monitoring through the API

You can use IBM Cloud Monitoring to get operational visibility into the performance and health of your applications, services, and platforms. IBM Cloud Monitoring provides administrators, DevOps teams, and developers full stack telemetry with advanced features to monitor and troubleshoot, define alerts, and design custom dashboards.

For more information about how to use Monitoring with Databases for PostgreSQL, see Monitoring integration.

You cannot connect IBM Cloud Monitoring by using the CLI. Use the console to complete this task. For more information, see Monitoring integration.

Step 5: Connect IBM Cloud Monitoring through Terraform

You can use IBM Cloud Monitoring to get operational visibility into the performance and health of your applications, services, and platforms. IBM Cloud Monitoring provides administrators, DevOps teams, and developers full stack telemetry with advanced features to monitor and troubleshoot, define alerts, and design custom dashboards.

For more information about how to use Monitoring with Databases for PostgreSQL, see Monitoring integration.

You cannot connect IBM Cloud Monitoring by using the CLI. Use the console to complete this task. For more information, see Monitoring integration.

Step 6: Connect IBM Cloud Activity Tracker Event Routing

IBM Cloud Activity Tracker Event Routing allows you to view, manage, and audit service activity to comply with corporate policies and industry regulations. Activity Tracker Event Routing records user-initiated activities that change the state of a service in IBM Cloud. Use Activity Tracker Event Routing to track how users and applications interact with the Databases for PostgreSQL service.

To get up and running with Activity Tracker Event Routing, see Getting started with Activity Tracker Event Routing.

Activity Tracker Event Routing can have only one instance per location. To view events, you must access the web UI of the Activity Tracker Event Routing service in the same location where your service instance is available.

For more information about events specific to Databases for PostgreSQL, see Activity tracking events.

Events are formatted according to the Cloud Auditing Data Federation (CADF) standard. For further details of the information they include, see CADF standard.

Step 6: Connect IBM Cloud® Activity Tracker through the CLI

IBM Cloud Activity Tracker allows you to view, manage, and audit service activity to comply with corporate policies and industry regulations. Activity Tracker records user-initiated activities that change the state of a service in IBM Cloud. Use Activity Tracker to track how users and applications interact with the Databases for PostgreSQL service.

To get up and running with Activity Tracker, see Getting Started with Activity Tracker.

Activity Tracker can have only one instance per location. To view events, you must access the web UI of the Activity Tracker service in the same location where your service instance is available. For more information, see Launch the web UI.

For more information about events specific to Databases for PostgreSQL, see Activity tracking events.

Events are formatted according to the Cloud Auditing Data Federation (CADF) standard. For further details of the information they include, see CADF standard.

You cannot connect Activity Tracker by using the CLI. Use the console to complete this task. For more information, see Activity tracking events.

Step 6: Connect IBM Cloud® Activity Tracker through the API

IBM Cloud Activity Tracker allows you to view, manage, and audit service activity to comply with corporate policies and industry regulations. Activity Tracker records user-initiated activities that change the state of a service in IBM Cloud. Use Activity Tracker to track how users and applications interact with the Databases for PostgreSQL service.

To get up and running with Activity Tracker, see Getting Started with Activity Tracker.

Activity Tracker can have only one instance per location. To view events, you must access the web UI of the Activity Tracker service in the same location where your service instance is available. For more information, see Launch the web UI.

For more information about events specific to Databases for PostgreSQL, see Activity tracking events.

Events are formatted according to the Cloud Auditing Data Federation (CADF) standard. For further details of the information they include, see CADF standard.

You cannot connect Activity Tracker by using the API. Use the console to complete this task. For more information, see Activity tracking events.

Step 6: Connect IBM Cloud® Activity Tracker through Terraform

IBM Cloud Activity Tracker allows you to view, manage, and audit service activity to comply with corporate policies and industry regulations. Activity Tracker records user-initiated activities that change the state of a service in IBM Cloud. Use Activity Tracker to track how users and applications interact with the Databases for PostgreSQL service.

To get up and running with Activity Tracker, see Getting Started with Activity Tracker.

Activity Tracker can have only one instance per location. To view events, you must access the web UI of the Activity Tracker service in the same location where your service instance is available. For more information, see Launch the web UI.

For more information about events specific to Databases for PostgreSQL, see Activity tracking events.

Events are formatted according to the Cloud Auditing Data Federation (CADF) standard. For further details of the information they include, see CADF standard.

You cannot connect Activity Tracker by using the API. Use the console to complete this task. For more information, see Activity tracking events.

Next steps