Skip to content

postgresql_database

A database in the PostgreSQL server on the host, managed through psql as the postgres user. Settings left unset are left as they are.

Use postgresql_database to make sure the PostgreSQL server on a host has a database, and who owns it. Conn3x runs psql on the host as the postgres user, so nothing on your machine needs a database driver or a route to port 5432.

A database for the app, owned by its role. The role has to exist first:

step "app_role" {
postgresql_role {
name = "app"
password_wo = "use an ephemeral variable here"
}
}
step "app_db" {
postgresql_database {
name = "app"
owner = "app"
encoding = "UTF8"
}
}
Name Type Required Description
encoding string No Character encoding for a new database, such as UTF8. PostgreSQL can’t change it later, so a database with another one fails the apply.
name string Yes Database name, up to 63 bytes. Renaming makes a new database; the old one stays unless on_remove is “delete”.
on_remove string No What happens on the host when this resource is removed: “forget” (default) leaves the database, “delete” drops it and everything in it.
owner string No Role that owns it. The role has to exist, so make it in an earlier postgresql_role step.

Conn3x sets these; reference them as conn3x_postgresql_database.<circuit>__<step>["<host>"].<name>.

Name Type Description
in_sync bool Whether the database matched at the last refresh.
revision string Changes each time Conn3x changes this on the host. Handlers watch it.

A step lowers to the conn3x_postgresql_database resource, one instance per host, and connects with its circuit’s ssh block.

What the host needs. PostgreSQL has to be installed and running, and psql has to be on the host. Conn3x runs it with runuser -u postgres, which needs root, so set become = true in the circuit’s ssh block unless you log in as root. Every statement goes to psql on stdin, never on a command line.

Reading. On every plan, Conn3x reads the database’s owner and encoding from pg_database. in_sync is true when the database exists and has what you set. Anything you leave unset isn’t compared.

Encoding. A new database with encoding is made from template0, which takes any encoding. PostgreSQL can’t change a database’s encoding afterwards, so a database that already has another one fails the apply, saying so, instead of planning a change on every run. Dump it, make it again with the new encoding, and restore.

Changes. Changing owner hands the database to that role. Changing name doesn’t rename anything: Conn3x makes a new, empty database, and on_remove decides what happens to the old one.

When you delete a postgresql_database step, on_remove decides what happens on the host. The default, "forget", leaves the database. "delete" drops it and everything in it, which can’t be undone. PostgreSQL refuses while anyone is connected to it. Conn3x never drops postgres, template0 or template1.