> ## Documentation Index > Fetch the complete documentation index at: https://docs.slipway.sh/llms.txt > Use this file to discover all available pages before exploring further. # Postgres > Run managed Postgres with a persistent disk, generated credentials and scheduled backups. Postgres runs as an ordinary service inside your project. Siblings reach it at its service name over the private network, and it has no public endpoint unless you enable external access. Every Postgres client, driver and ORM works unchanged. The wire protocol, the `postgresql://` URL and the SQL surface are stock Postgres. ## Creating an instance **Console** Select **New → Database**, then **Postgres**. The form asks for: * **Name**, the service name siblings connect to. * **Version**, one of 18, 17, 16 or 15. * **Database**, the database created on first boot. It defaults to a randomised name derived from the service rather than a guessable `postgres`. * **Compute**, the CPU and memory. A database reserves its full CPU allocation and does not burst, so what you pick is what it gets. * **Disk**, mounted at `/var/lib/postgresql`. **slipway.yaml** ```yaml slipway.yaml theme={"system"} version: 1 databases: - name: db engine: postgres version: "18" diskSizeGB: 20 cpu: "1" memory: 2GB services: - name: api runtime: docker env: DATABASE_URL: ${service.db.url} ports: - port: 3000 public: true ``` `diskSizeGB` is required. `version` defaults to the newest available. See [databases](/slipway-yaml/databases) for every field. | Engine | `engine:` | Versions | Default | Port | Data path | Memory floor | | - | - | - | - | - | - | - | | Postgres | `postgres` | 18, 17, 16, 15 | 18 | 5432 | `/var/lib/postgresql` | 512 MiB | | Redis | `redis` | 8 | 8 | 6379 | `/data` | 512 MiB | | MySQL | `mysql` | 8.4, 8.0 | 8.4 | 3306 | `/var/lib/mysql` | 1 GiB | | ClickHouse | `clickhouse` | 25.3, 24.8 | 25.3 | 8123 HTTP, 9000 native | `/var/lib/clickhouse` | 2 GiB | `latest` is not a valid version. A managed database has to be reproducible, so the tag is always pinned. The memory floor is the smallest allocation the engine runs on. If you configure less, the deploy fails rather than silently raising it. > [!NOTE] > > The disk mounts at `/var/lib/postgresql`, the whole data root, not > `/var/lib/postgresql/data`. Postgres 18 and later keep data in a major-version > subdirectory and refuse to start if they find a cluster at the old mount point. ## pgvector `pgvector/pgvector` is the same engine with the extension already in the image. It uses the same port, the same environment variables and the same credential handling, so selecting it changes nothing except that `CREATE EXTENSION vector;` will work. ## PostGIS PostGIS adds geometry and geography types, spatial functions and spatial indexes. Pick **PostGIS** under **Image** when you create the database. It is offered for Postgres 17, 16 and 15; PostGIS has no Postgres 18 build yet. The extension is enabled for you: `postgis` is created in the database when it is first initialised, so `SELECT postgis_full_version();` works straight away. Other extensions that ship in the image, such as `postgis_topology` or `fuzzystrmatch`, can be added with `CREATE EXTENSION`. Unlike pgvector, a PostGIS database runs the same managed runtime as every other Postgres here — it is CloudNativePG's own Postgres image with PostGIS added — so it keeps high availability, connection pooling, snapshots and restore. In `slipway.yaml`, set the image to `ghcr.io/cloudnative-pg/postgis:`: ```yaml theme={"system"} version: 1 services: - name: geo image: ghcr.io/cloudnative-pg/postgis:17 env: POSTGRES_PASSWORD: ${secret.GEO_PASSWORD} disk: mountPath: /var/lib/postgresql sizeGB: 10 ``` An existing Postgres database cannot be switched to PostGIS from the console. Create a PostGIS database and move the data into it. ## Authentication Each instance is provisioned with a generated password for the `postgres` role, stored as the project secret `_PASSWORD`. Additional roles are managed on the service's **Credentials** tab. Each is created live with a generated password and granted privileges on the instance's database, including default privileges on future tables, so an application role does not need re-granting after a migration. The password is stored as a revealable project secret. Roles created this way appear in the environment editor's value picker, so a sibling can wire `${secret.}` without anyone seeing the plaintext. They live in the cluster on disk and survive restarts. Every role — the primary included — can be **rotated** from the Credentials tab (the primary also from the Connection card). Rotation generates a fresh password, applies it to the live database immediately, and rewrites the backing project secret so `${service.X.password}` and `${secret.}` references resolve to the new value. Running services keep the old password until their next deploy, so redeploy them after rotating. Database credential secrets are managed by slipway: the generic secret editor refuses to overwrite or delete them — rotation is the one way to change them. `postgres` cannot be dropped from that tab. It is the account slipway manages the instance with. Dropping a role runs `DROP OWNED BY` before `DROP ROLE`, because Postgres refuses to drop a role that still owns objects. ## Connecting Siblings reference the instance with a [service reference](/slipway-yaml/secrets-and-variables): ```yaml slipway.yaml theme={"system"} services: - name: api env: DATABASE_URL: ${service.db.url} ``` `${service.db.url}` resolves to `postgresql://postgres:@db:5432/`. Available fields: `url`, `user`, `password`, `host`, `port`, `database`, `external_url` when external access is on, and `pooled_url` when [connection pooling](#connection-pooling) is on. The service page's **Connection** card shows the same values with the password masked. Admins can reveal it. ## External access Off by default. An admin can expose the instance on the public internet from the service's Connection card. ``` host: --pg.db. port: 5432 user: postgres sslmode: require ``` Postgres clients send the server name in the TLS handshake, so the hostname is enough to route the connection. Your username stays the plain role name. ```bash theme={"system"} psql "postgresql://postgres:@orders-1a2b3c4d-pg.db.:5432/?sslmode=require" ``` `${service.db.external_url}` already carries `?sslmode=require`, so a sibling wiring it gets a string that works as-is. > [!WARNING] > > TLS is required. The gateway will not proxy a plaintext connection, because the password > would cross the internet in the clear. Set `sslmode=require` or your driver's equivalent. ## High availability By default a Postgres instance is one pod with one disk. If the machine it runs on fails, the disk has to be reattached elsewhere and Postgres has to recover before anything answers again, which takes a few minutes. High availability runs three copies instead: a primary and two standbys, each on its own disk and, where the cluster allows it, on a different machine. The standbys continuously stream the primary's changes. If the primary stops answering, one of them is promoted automatically, usually within about half a minute. Nothing about how you connect changes. The service keeps its name, its port and its password, so `${service.db.url}`, your sibling services and external access all keep working across a failover without reconnecting to a different host. **Console** Open the database and go to its **High Availability** tab, then turn the switch on. You can also turn it on in the **Availability** panel when you create the database. Either way it is the same setting, and you can change it whenever you like. **slipway.yaml** ```yaml slipway.yaml theme={"system"} version: 1 databases: - name: db engine: postgres version: "18" diskSizeGB: 20 cpu: "1" memory: 4GB highAvailability: true ``` ### Turning it on and off High availability is a setting, not a decision you are stuck with. Turning it on adds standbys, each cloned from the running primary; turning it off removes them and their disks. The primary serves throughout, so there is no downtime and nothing to migrate in either direction. The **High Availability** tab lists the members while this happens: which one is the primary, whether each is ready, and which machine it landed on. A new standby takes a few minutes to appear — it has to copy the primary's data before it can start streaming. ### How many copies Three by default, and you can set any number from three upwards on the **High Availability** tab or with `haMembers` in `slipway.yaml`. Each copy is a full one: another member to fail over to, another read replica, and another set of compute and disk on the bill. Three is the floor. With two, losing either one leaves a cluster that cannot lose another, so the first failure spends the whole redundancy budget. There is no requirement for an odd number. Postgres members do not vote — the operator decides failover by watching each member directly — and replication here is asynchronous, so there is no quorum for an even count to split. Four copies work exactly as well as five. The ceiling is your plan's pod allowance, which is checked when you change the count. ### What it costs `cpu`, `memory` and `diskSizeGB` describe **one member**, so the compute and storage lines are multiplied by however many copies you run. The estimate on the create page shows the multiplied figure. Each member gets its own disk — they cannot share one, since each replays the primary's changes into its own copy. The **Disks** tab shows a single row because that row is the declaration ("this much, per member"); the chip next to it says how many claims it became. High availability is available on the Pro plan and above. ### What you should know * **Replication is asynchronous.** A standby is normally a fraction of a second behind the primary, but it is behind. An unplanned failover can lose the last few committed transactions. This is the usual tradeoff: synchronous replication would remove that window at the cost of putting a network round trip inside every commit. * **It is not a backup.** Every member holds the same data, so a dropped table is dropped on all three. Keep backups on. * **pgvector has no high-availability form.** The images that run a replicated cluster do not ship the extension, so the toggle is not offered for it. * **Databases created before August 2026 cannot be switched.** They run an older Postgres image whose data directory the replication operator cannot open, so changing their availability means moving the data into a new database rather than flipping the setting. Their High Availability tab says so. Everything created since can be switched freely. ## Connection pooling Postgres connections are expensive — each one is a server process — so the instance's connection limit is what caps how many clients can talk to it at once. Connection pooling puts a PgBouncer pooler in front of the database: clients connect to the pool, the pool keeps a small number of real Postgres connections, and each is handed to whichever client is running a transaction right now. Thousands of clients fit in a connection limit built for a hundred. Pooling runs in **transaction mode** with settings derived from the database — there is nothing to tune. It costs nothing extra, applies live with no restart, and enabling it changes nothing about direct access. **Console** Open the database and go to its **Connection Pooling** tab, then turn the switch on. The pooled connection URL appears on the service's **Connection** card next to the direct one. **slipway.yaml** ```yaml slipway.yaml theme={"system"} version: 1 databases: - name: db engine: postgres version: "18" diskSizeGB: 20 connectionPooling: true ``` ### Connecting through the pool The pool is a second address for the same database: `-pool:5432`, with exactly the same credentials and database name. Every role that can sign in directly — including ones you add later on the Credentials tab — works through the pool. ```yaml slipway.yaml theme={"system"} services: - name: api env: DATABASE_URL: ${service.db.pooled_url} # postgresql://…@db-pool:5432/… ``` If [external access](#external-access) is on, the pool is reachable from outside too, at `--pgpool.db.:5432` — same gateway, same port, the hostname routes to the pool instead of the primary. Both URLs are on the Connection card. ### When to connect directly Transaction pooling reassigns the server connection at every transaction boundary, so anything that relies on **session state** must bypass the pool and connect to `` directly: * `LISTEN` / `NOTIFY` * temporary tables * session-level advisory locks * session variables (`SET`, as opposed to `SET LOCAL`) * prepared statements managed outside your driver's protocol-level support Direct access always keeps a reserve of connections — the pool is sized below the database's limit — so the psql prompt on the Shell tab and your migrations never queue behind application traffic. Connection pooling is available on every paid plan. It works with high availability (the pool follows the primary across a failover) and is Postgres-only: no other engine here has a pooler, and pgvector databases run an image the pooler cannot ride. ## Backups The disk is snapshotted on a schedule and can be restored in place from the service's **Disks** tab. Retention is measured in days and depends on your plan. See [Disks](/data/disks). A snapshot is a copy of the data directory, so restoring one returns the whole instance to that point. For a logical export, run `pg_dump` against the instance from a sibling service or over external access. Backups work the same whether high availability is on or off. Every copy holds the same data, so one member is snapshotted rather than all of them, and you are billed for one backup however many copies run. Restoring a replicated database rebuilds it from the snapshot and re-clones the standbys, so it takes longer than restoring a single copy and the database is unavailable while it happens. ## Metrics The service overview shows CPU, memory and active connections, sampled every 30 seconds. The service's [Metrics tab](/observability/metrics) charts those over a window you choose, plus disk usage against the volume's size. ## Pricing | What | Rate | | - | - | | CPU | \$8.76 per vCPU per month | | Memory | \$4.38 per GiB per month | | Disk | \$0.25 per GiB per month | | Volume backups | \$0.05 per GiB per month | | Object storage | \$0.05 per GiB per month | | Egress beyond the plan allowance | \$0.01 per GB | Compute is metered by the second against the allocation a service is configured with, so a service that sleeps costs nothing while it is asleep. Disk, backups and object storage are metered on what is provisioned or held, whether or not the service is running. Postgres is priced on the allocation you configure, at the same rates as any other service, with CPU reserved rather than burstable. The disk is billed on its provisioned size. ## FAQ **Can I install extensions?** **Yes**, any extension the image ships. Connect and run `CREATE EXTENSION`. For `vector`, select the `pgvector/pgvector` image when you create the instance. **Can I upgrade the major version in place?** **No.** Change the version and slipway starts a new instance on the new image. Postgres does not read a data directory written by a newer major version, so move the data with `pg_dump` and `pg_restore`. **Is `latest` allowed as a version?** **No.** A managed database has to be reproducible, so the tag is always pinned. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.