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

# PostgreSQL

> Deploy PostgreSQL on Control Plane using the Template Catalog. Covers configuration, volumes, scaling, and single-replica setup with optional scheduled S3, GCS, or MinIO backups.

## Overview

PostgreSQL is a powerful open-source relational database. This template deploys a single-replica PostgreSQL instance with persistent storage and optional scheduled backups to AWS S3, GCS, or a self-hosted MinIO instance.

<Note>
  PostgreSQL on Control Plane operates as a single-replica deployment. Do not scale up the replica count, as this would result in multiple isolated instances rather than a replicated cluster. For a highly available setup, use the [PostgreSQL Highly Available](/template-catalog/templates/postgres-highly-available) template instead.
</Note>

### What Gets Created

* **Stateful Postgres Workload** — A single-replica PostgreSQL container with configurable resources.
* **Volume Set** — Persistent storage for database data, with optional autoscaling.
* **Secret** — A dictionary secret storing the database username and password, injected into the container at startup.
* **Identity & Policy** — An identity bound to the workload with `reveal` access to the database credentials secret, and cloud storage access when backup is enabled.
* **PgBouncer Workload** *(optional)* — A PgBouncer connection pooler deployed as a separate workload in front of PostgreSQL.
* **Backup Cron Workload** *(optional)* — A scheduled `pg_dump` backup job that writes compressed SQL dumps to AWS S3, GCS, or MinIO.

<Note>
  This template does not create a GVC. You must deploy it into an existing GVC.
</Note>

## Installation

This template has no external prerequisites unless backup is enabled. To install, follow the instructions for your preferred method:

<CardGroup cols={2}>
  <Card title="UI" href="/template-catalog/install-manage/ui" icon="laptop">
    Browse, install, and manage templates visually
  </Card>

  <Card title="CLI" href="/template-catalog/install-manage/cli" icon="terminal">
    Manage templates from your terminal
  </Card>

  <Card title="Terraform" href="/template-catalog/install-manage/terraform" icon={<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 128 128"><g fill-rule="evenodd"><path d="M77.941 44.5v36.836L46.324 62.918V26.082zm0 0" fill="#5c4ee5"/><path d="M81.41 81.336l31.633-18.418V26.082L81.41 44.5zm0 0" fill="#4040b2"/><path d="M11.242 42.36L42.86 60.776V23.941L11.242 5.523zm0 0M77.941 85.375L46.324 66.957v36.82l31.617 18.418zm0 0" fill="#5c4ee5"/></g></svg>}>
    Declare templates in your Terraform configurations
  </Card>

  <Card
    title="Pulumi"
    href="/template-catalog/install-manage/pulumi"
    icon={<svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" id="Pulumi-Icon--Streamline-Svg-Logos" height="24" width="24">
    <desc>
        Pulumi Icon Streamline Icon: https://streamlinehq.com
    </desc>
    <path fill="#f26e7e" d="M4.683025 13.3318c0.869125 -0.5018 0.870575 -2.1264 0.003225 -3.62865s-2.27504 -2.313275 -3.1441725 -1.811475C0.672945 8.3935 0.6715 10.0181 1.53885 11.52035c0.86735 1.502275 2.27505 2.313275 3.144175 1.81145Zm0.0052 3.2167c0.86735 1.502275 0.865925 3.126875 -0.003225 3.628675 -0.86915 0.5018 -2.2768275 -0.309225 -3.144175 -1.81145 -0.8673525 -1.50225 -0.8659075 -3.126875 0.003225 -3.628675 0.8691325 -0.5018 2.276825 0.309225 3.144175 1.81145Zm5.922875 3.4243c0.86735 1.50225 0.8659 3.126775 -0.003225 3.62875 -0.869125 0.501775 -2.27685 -0.309325 -3.1442 -1.81155 -0.867325 -1.50225 -0.865875 -3.12685 0.00325 -3.628675 0.869125 -0.5018 2.276825 0.309225 3.144175 1.811475Zm-0.001925 -6.845275c0.86735 1.50225 0.8659 3.12685 -0.003225 3.628675 -0.869125 0.5018 -2.276825 -0.309225 -3.144175 -1.811475 -0.86735 -1.50225 -0.8659 -3.12685 0.003225 -3.62865 0.869125 -0.501825 2.276825 0.3092 3.144175 1.81145Z" stroke-width="0.25"></path>
    <path fill="#8a3391" d="M22.45775 11.524125c0.86725 -1.502225 0.865925 -3.12685 -0.003225 -3.62865 -0.869125 -0.501825 -2.276825 0.3092 -3.144175 1.811475 -0.86735 1.50225 -0.8659 3.126825 0.003225 3.62865 0.869125 0.501825 2.276825 -0.3092 3.144175 -1.811475Zm0.000175 3.2151c0.869075 0.5018 0.870625 2.1264 0.003225 3.62865 -0.86735 1.50225 -2.27505 2.313275 -3.144175 1.81145 -0.869125 -0.5018 -0.870575 -2.126425 -0.003225 -3.62865 0.86735 -1.50225 2.27505 -2.313275 3.144175 -1.81145ZM16.536225 18.157875c0.86915 0.501825 0.8706 2.126425 0.00325 3.628675 -0.86735 1.502125 -2.275075 2.313225 -3.1442 1.81145 -0.869125 -0.50175 -0.870575 -2.126425 -0.003225 -3.62865 0.867375 -1.502275 2.27505 -2.3133 3.144175 -1.811475Zm-0.003325 -6.843775c0.869125 0.5018 0.870575 2.126425 0.003225 3.628675s-2.27505 2.313275 -3.1442 1.811475c-0.869125 -0.501825 -0.870575 -2.126425 -0.003225 -3.628675 0.86735 -1.502275 2.27505 -2.313275 3.1442 -1.811475Z" stroke-width="0.25"></path>
    <path fill="#f7bf2a" d="M15.138225 2.06721c0 1.003615 -1.40625 1.817215 -3.14095 1.817215 -1.7347 0 -3.14095 -0.8136 -3.14095 -1.817215C8.856325 1.06359 10.262575 0.25 11.997275 0.25c1.7347 0 3.14095 0.81359 3.14095 1.81721ZM9.2166 5.482375c0 1.003625 -1.40625 1.8172 -3.14095 1.8172 -1.7347 0 -3.14095 -0.813575 -3.14095 -1.8172s1.40625 -1.817225 3.14095 -1.817225c1.7347 0 3.14095 0.8136 3.14095 1.817225Zm8.71005 1.8172c1.7347 0 3.14095 -0.813575 3.14095 -1.8172s-1.40625 -1.817225 -3.14095 -1.817225c-1.7347 0 -3.14095 0.8136 -3.14095 1.817225s1.40625 1.8172 3.14095 1.8172Zm-2.788425 1.605625c0 1.003625 -1.40625 1.8172 -3.14095 1.8172 -1.7347 0 -3.14095 -0.813575 -3.14095 -1.8172 0 -1.0036 1.40625 -1.8172 3.14095 -1.8172 1.7347 0 3.14095 0.8136 3.14095 1.8172Z" stroke-width="0.25"></path>
    </svg>}
  >
    Declare templates in your Pulumi programs
  </Card>
</CardGroup>

## Configuration

The default `values.yaml` for this template:

```yaml theme={null}
image: postgres:18  # versions before postgres:17 do not support the backup feature

resources:
  minCpu: 200m
  minMemory: 128Mi
  maxCpu: 500m
  maxMemory: 256Mi

config:
  username: username
  password: password
  database: test

volumeset:
  capacity: 10 # initial capacity in GiB (minimum is 10)
  autoscaling:
    enabled: false
    maxCapacity: 100 # Maximum capacity in GiB
    minFreePercentage: 10 # Trigger scaling when free space drops below this percentage
    scalingFactor: 1.2 # Multiply current capacity by this factor when scaling up

internalAccess:
  type: same-gvc # options: none, same-gvc, same-org, workload-list
  workloads: # Note: can only be used if type is same-gvc or workload-list
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

pgbouncer:
  enabled: false
  image: edoburu/pgbouncer:v1.25.1-p0
  poolMode: transaction # options: session, transaction, statement
  defaultPoolSize: 25   # number of real Postgres connections PgBouncer maintains
  maxClientConn: 1000   # maximum number of client connections PgBouncer accepts
  replicas: 1

  resources:
    cpu: 200m
    memory: 128Mi

backup: # compatible with Postgres 17+ only
  enabled: false
  image: ghcr.io/controlplane-com/backup-images/postgres-backup:18.1.0 # tag 18.1.0 = Postgres 18, 17.1.0 = Postgres 17
  schedule: "0 2 * * *" # cron schedule, default is daily at 2am UTC

  resources:
    cpu: 100m
    memory: 128Mi

  provider: aws # Options: aws, gcp, or minio

  aws:
    bucket: my-backup-bucket
    region: us-east-1
    cloudAccountName: my-backup-cloudaccount
    policyName: my-backup-policy
    prefix: postgres/backups

  gcp:
    bucket: my-backup-bucket
    cloudAccountName: my-backup-cloudaccount
    prefix: postgres/backups

  minio: # Backup to a self-hosted MinIO workload (or any S3-compatible endpoint)
    endpoint: http://my-minio-workload:9000 # e.g. http://WORKLOAD_NAME:9000 for an internal MinIO template deployment
    bucket: my-backup-bucket
    accessKey: my-minio-username # matches the MinIO template's admin.username
    secretKey: my-minio-password # matches the MinIO template's admin.password
    prefix: postgres/backups
```

### Credentials

* `config.username` — PostgreSQL username. **Change before deploying to production.**
* `config.password` — PostgreSQL password. **Change before deploying to production.**
* `config.database` — Name of the database created on startup.

<Note>
  These values are only applied on first startup when the data directory is empty. Updating them after the initial deployment will have no effect on the running database. To change credentials or the database name on an existing instance, use PostgreSQL's native commands (e.g. `ALTER USER`, `ALTER DATABASE`).
</Note>

### Resources

* `resources.minCpu` / `resources.minMemory` — Minimum CPU and memory guaranteed to the workload.
* `resources.maxCpu` / `resources.maxMemory` — Maximum CPU and memory the workload can use.

### Storage

* `volumeset.capacity` — Initial volume size in GiB (minimum 10).
* `volumeset.autoscaling.enabled` — Automatically expand the volume as it fills. When enabled:
  * `maxCapacity` — Maximum volume size in GiB.
  * `minFreePercentage` — Trigger a scale-up when free space drops below this percentage.
  * `scalingFactor` — Multiply the current capacity by this factor when scaling up.

### Internal Access

* `internalAccess.type` — Controls which workloads can connect to PostgreSQL on port `5432`:

| Type            | Description                                                     |
| --------------- | --------------------------------------------------------------- |
| `none`          | No internal access allowed                                      |
| `same-gvc`      | Allow access from all workloads in the same GVC                 |
| `same-org`      | Allow access from all workloads in the same organization        |
| `workload-list` | Allow access only from specific workloads listed in `workloads` |

### Connecting to PostgreSQL

Once deployed, connect to the database from within the same GVC using:

```text theme={null}
RELEASE_NAME-postgres.GVC_NAME.cpln.local:5432
```

### PgBouncer Connection Pooling

PgBouncer is an optional connection pooler that sits in front of PostgreSQL and multiplexes application connections into a smaller pool of real database connections. This reduces connection overhead and protects PostgreSQL from exhaustion under high concurrency.

When enabled, PgBouncer is deployed as a separate workload and becomes the primary connection endpoint for your applications:

```
RELEASE_NAME-pgbouncer.GVC_NAME.cpln.local:5432
```

* `pgbouncer.enabled` — Enable or disable PgBouncer.
* `pgbouncer.poolMode` — Controls how connections are reused:

| Mode          | Description                                                                                                                                                                                                             |
| ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `transaction` | Connection held only for the duration of a transaction, then returned to the pool. Best for most web and API workloads. Not compatible with session-level features (`SET` variables, temporary tables, advisory locks). |
| `session`     | Connection held for the entire client session. Compatible with all PostgreSQL features but provides less reuse. Increase `defaultPoolSize` to match your expected concurrent client count.                              |
| `statement`   | Connection returned after every statement. Transactions are not supported. Rarely used.                                                                                                                                 |

* `pgbouncer.defaultPoolSize` — Number of real PostgreSQL connections PgBouncer maintains per pool (default: `25`).
* `pgbouncer.maxClientConn` — Maximum number of client connections PgBouncer accepts (default: `1000`).
* `pgbouncer.replicas` — Number of PgBouncer instances. PgBouncer is stateless and can be scaled horizontally for high-throughput workloads.
* `pgbouncer.resources.cpu` / `pgbouncer.resources.memory` — Resources allocated to each PgBouncer replica.

<Note>
  PgBouncer shares the same credentials and identity as the PostgreSQL workload — no additional secrets or IAM configuration is required. The `userlist.txt` and `pgbouncer.ini` are generated automatically from your `config.username`, `config.password`, and `config.database` values at startup.
</Note>

### Backup

Backup is disabled by default. When enabled, a cron workload runs `pg_dump` on the configured schedule and uploads compressed SQL dumps to AWS S3 or GCS.

<Note>
  Backup requires PostgreSQL 17 or later. Set `backup.image` to match your PostgreSQL version: `ghcr.io/controlplane-com/backup-images/postgres-backup:18.1.0` for Postgres 18, or `ghcr.io/controlplane-com/backup-images/postgres-backup:17.1.0` for Postgres 17.
</Note>

* `backup.enabled` — Enable scheduled backups.
* `backup.schedule` — Cron expression for backup frequency (default: daily at 2am UTC).
* `backup.provider` — `aws`, `gcp`, or `minio`.
* `backup.resources.cpu` / `backup.resources.memory` — Resources for the backup cron container.

## Backup Prerequisites

### AWS S3

Before enabling backup with `provider: aws`, complete the following in your AWS account:

1. Create an S3 bucket. Set `backup.aws.bucket` to its name and `backup.aws.region` to its region.
2. If you do not have a Cloud Account set up, refer to the docs to [Create a Cloud Account](/guides/create-cloud-account). Set `backup.aws.cloudAccountName` to its name.
3. Create an IAM policy with the following JSON, replacing `YOUR_BUCKET_NAME`:

```json theme={null}
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject",
                "s3:ListBucket",
                "s3:GetObjectVersion",
                "s3:DeleteObjectVersion"
            ],
            "Resource": [
                "arn:aws:s3:::YOUR_BUCKET_NAME",
                "arn:aws:s3:::YOUR_BUCKET_NAME/*"
            ]
        }
    ]
}
```

4. Set `backup.aws.policyName` to the name of the policy created in step 3.
5. Set `backup.aws.prefix` to the folder path where backups will be stored.

### GCS

Before enabling backup with `provider: gcp`, complete the following in your GCP account:

1. Create a GCS bucket. Set `backup.gcp.bucket` to its name.
2. If you do not have a Cloud Account set up, refer to the docs to [Create a Cloud Account](/guides/create-cloud-account). Set `backup.gcp.cloudAccountName` to its name.
3. Add the **Storage Admin** role to the GCP service account associated with the Cloud Account.
4. Set `backup.gcp.prefix` to the folder path where backups will be stored.

### MinIO

Before enabling backup with `provider: minio`, ensure your MinIO instance is accessible:

1. Create a bucket in MinIO. Set `backup.minio.bucket` to its name.
2. Set `backup.minio.endpoint` to the MinIO S3 API address including the port. For the `minio` marketplace template deployed in the same GVC, use `http://WORKLOAD_NAME:9000`.
3. Set `backup.minio.accessKey` and `backup.minio.secretKey` to the MinIO root credentials — these match the `admin.username` and `admin.password` values from the MinIO template installation.
4. Set `backup.minio.prefix` to the folder path where backups will be stored.

<Note>
  MinIO backup requires no Control Plane Cloud Account — credentials are passed directly to the backup job.
</Note>

## Restoring a Backup

Run the following from a client with access to the backup bucket:

**AWS S3:**

```sh theme={null}
export PGPASSWORD="PASSWORD"

aws s3 cp "s3://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" - \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-postgres.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=postgres

unset PGPASSWORD
```

**GCS:**

```sh theme={null}
export PGPASSWORD="PASSWORD"

gsutil cp "gs://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" - \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-postgres.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=postgres

unset PGPASSWORD
```

**MinIO:**

```sh theme={null}
export PGPASSWORD="PASSWORD"
export AWS_ACCESS_KEY_ID="MINIO_ACCESS_KEY"
export AWS_SECRET_ACCESS_KEY="MINIO_SECRET_KEY"
aws configure set default.s3.addressing_style path

aws s3 cp "s3://BUCKET_NAME/PREFIX/BACKUP_FILE.sql.gz" - --endpoint-url "http://MINIO_ENDPOINT:9000" \
  | gunzip \
  | psql \
      --host=RELEASE_NAME-postgres.GVC_NAME.cpln.local \
      --port=5432 \
      --username=USERNAME \
      --dbname=postgres

unset PGPASSWORD AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY
```

## External References

<CardGroup cols={2}>
  <Card title="PostgreSQL Documentation" icon="database" href="https://www.postgresql.org/docs/">
    Official PostgreSQL documentation
  </Card>

  <Card title="PgBouncer Documentation" icon="database" href="https://www.pgbouncer.org/config.html">
    PgBouncer configuration reference
  </Card>

  <Card title="Backup Image Source" icon="github" href="https://github.com/controlplane-com/backup-images/tree/main/postgres-backup">
    Source code for the PostgreSQL backup container image
  </Card>

  <Card title="PostgreSQL Template" icon="github" href="https://github.com/controlplane-com/templates/tree/main/postgres">
    View the source files, default values, and chart definition
  </Card>
</CardGroup>