PostgreSQL
This provisions a new PostgreSQL database in an existing PostgreSQL instance. The instance must be reachable from Humanitec IPs.
Property | Description |
---|---|
Resource type | postgres |
Account type | None |
Inputs
Values
Name | Type | Description |
---|---|---|
host |
string | The IP Address or hostname that the instance is available on. |
port |
integer | The port the instance is listening on. |
name |
string | [Optional] The name of the maintenance database to connect to. Defaults to postgres . |
append_host_to_user |
boolean | [Optional] Azure Databases for Postgres and MySQL require usernames to have @servername appended to them. Set this to true for the Driver to append this automatically. (See: Azure Database connection strings) |
template_name |
string | [Optional] If provided, the Driver will use this database name as a template. See CREATE DATABASE. |
Secrets
Name | Type | Description |
---|---|---|
dbcredentials |
object | An object holding username and password properties for the PostgreSQL superuser. |
agent_url |
string | [Optional] Can be used to specify the Humanitec Agent URL to use to intermediate access to the PostgreSQL instance. This is always done by supplying a placeholder. For example: ${resources["agent.default#agent-01"].outputs.url} . |
Notes
Automatic population of database
PostgreSQL supports creating databases based on templates. (See CREATE DATABASE) This functionality can be used to effectively pre-populate a database as it is provisioned.
It is important to be familiar with the constraints of template databases in PostgreSQL. The documentation states:
Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes.
For more information on managing templates DBs, see Template Databases.
Example
Set the following environment variables for the CLI and API commands:
Variable | Example | Description |
---|---|---|
HUMANITEC_TOKEN |
my-token |
The authentication token for accessing the Humanitec API. |
HUMANITEC_ORG |
my-org-id |
The unique identifier for the organization in Humanitec. |
Use the command below for the interface of your choice to create a fresh PostgreSQL database in an instance available at dev-postgres.example.com
:
- Create a file defining the Resource Definition you want to create:
cat << EOF > dev-postgres.yaml
kind: Definition
apiVersion: entity.humanitec.io/v1b1
metadata:
id: dev-postgres
entity:
name: Dev PostgreSQL
type: postgres
driver_type: humanitec/postgres
driver_inputs:
secrets:
dbcredentials:
username: postgres
password: 53cr3t-P455w0rd
values:
host: dev-postgres.example.com
name: postgres
port: 5432
criteria:
- env_type: development
EOF
- Use the
humctl create
command to create the Resource Definition in the Organization defined by your configured context:
humctl create -f dev-postgres.yaml
rm dev-postgres.yaml
curl https://api.humanitec.io/orgs/${HUMANITEC_ORG}/resources/defs \
-X POST \
-H "Authorization: Bearer ${HUMANITEC_TOKEN}" \
-H "Content-Type: application/json" \
-d '
{
"id": "dev-postgres",
"name": "Dev PostgreSQL",
"type": "postgres",
"criteria": [
{
"env_type": "development"
}
],
"driver_type": "humanitec/postgres",
"driver_inputs": {
"values": {
"host": "dev-postgres.example.com",
"port": 5432,
"name": "postgres"
},
"secrets": {
"dbcredentials": {
"username": "postgres",
"password": "53cr3t-P455w0rd"
}
}
}
}'
This will create a fresh database called postgres
on the instance at dev-postgres.example.com
using the superuser credentials provided.
Most PostgresSQL implementations use a single shared Driver. For Google CloudSQL implementations, see the Google CloudSQL Driver.
Prerequisites
- You must have a database instance/server running.
- You must have a user defined on the instance for Workloads to use when connecting to the database.
Add a Resource Definition
- From the Resource Management screen, click Add resource definition.
- In the modal dialog click Postgres.
- Next, select the postgres Driver.
- Finally, provide the following information, then click Add Postgres.
- In the ID field provide a unique ID for the Resource.
- Provide the user or role of the database.
- Provide the password for the database.
- Choose whether to have
@servername
appended to the username. - Enter the database hostname.
- Provide a database name (defaults to
postgres
). - Provide the database port.
Resource Matching
Now that the resource is defined you will need to add matching criteria.
- Click on the relevant row in the Resource Definition table.
- Then switch to the Matching Criteria tab.
- Click + Add new Criteria.
- Configure the matching rules as needed:
- Environment Type:
development
.
- Environment Type:
- Click Save.