> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nx1cloud.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Ingest a PostgreSQL database

> Deploy a PostgreSQL database in the cloud and ingest its datasets into NexusOne.

PostgreSQL is one of the database vendors supported on NexusOne. In this guide, you'll deploy
a PostgreSQL database instance in a public cloud using Terraform, add datasets into the database,
and ingest the database into NexusOne.

## Prerequisites

* An existing AWS, Azure, or Google Cloud account
* Appropriate NexusOne permission: `nx1_ingest`, `nx1_monitor`, `nx1_s3_admin`, `airflow_user`,
  `superset_user`, `spark_sql`, and `trino_admin`
* Command line tools from [AWS](https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html),
  [Azure](https://learn.microsoft.com/en-us/cli/azure/install-azure-cli?view=azure-cli-latest),
  or [Google Cloud](https://docs.cloud.google.com/sdk/docs/install-sdk) installed
* [psql](https://www.postgresql.org/download/) command line tool installed
* Terraform installed

## Authenticate to your public cloud

Use your public cloud's command line tool to authenticate to your cloud account.
This allows Terraform to interact with your cloud account.

<Tabs>
  <Tab title="AWS">
    The following AWS command requests that you enter the following AWS credentials:

    * Access key ID
    * Secret access key
    * Default region

    ```bash theme={null}
    # User account login
    aws configure
    ```

    To create an access key ID and secret access key, refer to
    [How IAM users can manage their own access keys](https://docs.aws.amazon.com/IAM/latest/UserGuide/access-key-self-managed.html) in the AWS documentation.
  </Tab>

  <Tab title="Azure">
    The following Azure command opens your default web browser and requests for your Azure credentials.
    After successfully signing in, the command line asks you to select your Azure subscription and tenant.

    ```bash theme={null}
    # User account login
    az login
    ```
  </Tab>

  <Tab title="Google">
    The following Google cloud command opens your default web browser and requests for the following:

    * Your user credentials
    * Permissions to what the Google Auth Library can access

    ```bash theme={null}
    # User account login
    gcloud auth application-default login
    ```
  </Tab>
</Tabs>

## Define a Terraform and cloud provider

After authenticating to your cloud account using the command line tool, create the following
Terraform file.

<CodeGroup>
  ```terraform aws_provider.tf theme={null}
  # Terraform provider
  terraform {
    required_providers {
      aws = {
        source  = "hashicorp/aws"
        version = ">=6.32.0"
      }
    }
  }

  # AWS provider
  provider "aws" {
    region = "us-west-2"
  }
  ```

  ```terraform azure_provider.tf theme={null}
  # Terraform provider
  terraform {
    required_providers {
      azurerm = {
        source  = "hashicorp/azurerm"
        version = ">=4.59.0"
      }
    }
  }

  # Azure provider
  provider "azurerm" {
    features {}
    subscription_id = "" # Enter your subscription ID
  }
  ```

  ```terraform google_provider.tf theme={null}
  # Terraform provider
  terraform {
    required_providers {
      google = {
        source = "hashicorp/google"
        version = ">=7.19.0"
      }
    }
  }

  # Google provider
  provider "google" {
    region = "us-central1"
    project = ""  # Enter your project ID
  }
  ```
</CodeGroup>

The defined cloud provider automatically uses the credentials you defined when interacting
with the cloud provider's command line tool.

## Define a PostgreSQL instance

This step defines Terraform resources required in this tutorial for deploying the PostgreSQL
database. Create the following Terraform file.

<CodeGroup>
  ```terraform aws_postgres.tf theme={null}
  # Create the database instance
  resource "aws_db_instance" "postgres" {
    allocated_storage   = 10
    engine              = "postgres"
    instance_class      = "db.t3.micro"
    username            = "psqladmin"
    password            = "NexusPostgres!"
    publicly_accessible = true
    skip_final_snapshot = true
  }

  # Output the database address
  output "database_address" {
    value = aws_db_instance.postgres.address
  }
  ```

  ```terraform azure_postgres.tf theme={null}
  # Deployment boundary for all USA resources
  resource "azurerm_resource_group" "usa" {
    name     = "usa-resources"
    location = "Central US"
  }

  # Create the database instance
  resource "azurerm_postgresql_flexible_server" "postgres" {
    name                   = "postgrestutorial"
    resource_group_name    = azurerm_resource_group.usa.name
    location               = azurerm_resource_group.usa.location
    version                = "17"
    administrator_login    = "psqladmin"
    administrator_password = "NexusPostgres!"
    authentication {
      password_auth_enabled = true
    }
    storage_mb = 32768
    sku_name   = "B_Standard_B1ms"
  }

  # Allow access to the database
  resource "azurerm_postgresql_flexible_server_firewall_rule" "allow_all" {
    name             = "allow-all"
    server_id        = azurerm_postgresql_flexible_server.postgres.id
    start_ip_address = "0.0.0.0"
    end_ip_address   = "255.255.255.255"
  }

  # Output the database address
  output "database_address" {
    value = azurerm_postgresql_flexible_server.postgres.fqdn
  }
  ```

  ```terraform google_postgres.tf theme={null}
  # Create the database instance
  resource "google_sql_database_instance" "postgres" {
    database_version    = "POSTGRES_17"
    deletion_protection = false

    settings {
      tier    = "db-custom-2-8192"
      edition = "ENTERPRISE"
      ip_configuration {
        authorized_networks {
          name  = "allow-all"
          value = "0.0.0.0/0"
        }
      }
    }
  }

  # Create a user for the database
  resource "google_sql_user" "psqladmin" {
    name     = "psqladmin"
    instance = google_sql_database_instance.postgres.name
    password = "NexusPostgres!"
  }

  # Output the database public IP address
  output "database_public_ip_address" {
    value = google_sql_database_instance.postgres.public_ip_address
  }
  ```
</CodeGroup>

<Warning>
  * Azure expects a globally unique PostgreSQL Flexible Server name. If you experience
    a *ServerNameAlreadyExists* error when creating the resource, then use a unique name.
  * Public access to the database is for demonstration purposes only.
</Warning>

## Deploy the PostgreSQL instance

After defining the PostgreSQL Terraform resource specific to your cloud provider, take the following
steps to deploy the Terraform resources.

1. Initialize the Terraform configuration.

   ```bash theme={null}
   terraform init
   ```

2. Create the PostgreSQL instance.

   ```bash theme={null}
   terraform apply --auto-approve
   ```

Once the resources are successfully created, Terraform outputs the database address.

## Add datasets to the database

To ingest datasets from the database into NexusOne, these datasets need to exist first.
Take the following steps to add datasets to the database:

1. Authenticate to the database using the psql command line tool.

   <Tabs>
     <Tab title="AWS">
       ```bash theme={null}
       psql \
       --host=<database_address> \
       --username=psqladmin \
       --dbname=postgres
       ```
     </Tab>

     <Tab title="Azure">
       ```bash theme={null}
       psql \
       --host=<database_address> \
       --username=psqladmin \
       --dbname=postgres
       ```
     </Tab>

     <Tab title="Google">
       ```bash theme={null}
       psql \
       --host=<database_public_ip> \
       --username=psqladmin \
       --dbname=postgres
       ```
     </Tab>
   </Tabs>

   <Note>PostgreSQL creates a [default database](https://www.postgresql.org/docs/current/creating-cluster.html)
   named `postgres`, which stores default schemas.</Note>

2. When prompted, enter your password, `NexusPostgres!`.

3. Create a new table inside the default `public` schema.

   ```sql theme={null}
   CREATE TABLE public.test (
     id SERIAL PRIMARY KEY,
     name TEXT
   );
   ```

4. Insert data into the table.

   ```sql theme={null}
   INSERT INTO public.test (name)
   VALUES ('Nexus'), ('Cognitive');
   ```

5. View all rows in the `test` table.

   ```sql theme={null}
   SELECT * FROM public.test;
   ```

## Ingest the database into NexusOne

Now that a dataset is available on the PostgreSQL database, you can ingest the database
into NexusOne using the following steps:

1. Log in to NexusOne.
2. From the NexusOne homepage, navigate to **Ingest > Database > From Table**.
3. Enter the table and schema name:
   * **Source Schema**: `public`
   * **Source Table**: `test`
4. Add connection details:
   * **Database URL**: `jdbc:postgresql://<database_URL_or_public_IP>:5432/postgres`
     <Note>
       * Port 5432 is PostgreSQL's default port number.

       * PostgreSQL creates a [default database](https://www.postgresql.org/docs/current/creating-cluster.html)
         named `postgres`, which stores default schemas.
     </Note>
   * **Username**: `psqladmin`
   * **Password**: `NexusPostgres!`
5. Add ingest details:
   * **Name**: `ingest_database`
   * **Schema**: `postgres_schema`
   * **Table**: `postgres_table`
   * **Schedule**: `Run Once`
   * **Mode**: `append`
   * **Tags**: Don't add any tags.
6. After configuring all fields, click **Ingest** to submit the job.
   Wait for a few minutes until you see a success message appear with an
   option to click **View Jobs**.

## Monitor job

When you ingest the database, this creates an Airflow job. To monitor the status of the job,
use the following steps:

1. Click **View Jobs**, or navigate to the NexusOne homepage and then click **Monitor**.
2. Find your job name, `ingest_database`, in the list, and watch its current status.
3. Wait for a few minutes, and then refresh your browser until the status changes to `Completed`.

## Visualize your dataset

Use the following steps to visualize your dataset:

1. On the NexusOne homepage, click **Discover** to launch Superset.
2. Hover your mouse over **SQL**, and then select **SQL Lab**.
3. Enter the following command in the query box:

```sql theme={null}
SELECT * FROM postgres_schema.postgres_table
```

<Card img="https://mintcdn.com/nexusone-4c77570d/NX-7c1xGMzLi7QfY/images/tutorials/ingest/database/visualize-postgres-v4-1-2.png?fit=max&auto=format&n=NX-7c1xGMzLi7QfY&q=85&s=d14caedf8eddc308b56595cf20f50012" width="1000" height="502" data-path="images/tutorials/ingest/database/visualize-postgres-v4-1-2.png">
  Visualize your dataset
</Card>

## Delete the table and Terraform resources

After completing the tutorial, delete the created table and PostgreSQL instance
using the following steps:

1. Delete the table.

   ```sql theme={null}
   DROP TABLE public.test;
   ```

2. Destroy the Terraform resources.

   ```terraform theme={null}
   terraform destroy --auto-approve
   ```

## Additional resources

* To get an overview of what database ingestion is, refer to [Data ingestion overview](/documentation/data-pipeline/overview/ingest#how-database-ingestion-works).
* For more information about roles or permissions, refer to [Govern Overview](/documentation/govern/overview).