Connect to PostgreSQL

This guide covers everything about reaching your PostgreSQL database in Zerops: connection details, ports and TLS, connection pooling, connecting from other services or from your workstation, and the database tools you can use.

Connection options at a glance

• Internal: between services in the same project, over the private network.
• Remote: from outside Zerops, either through the VPN (your machine joins the private network) or through public IP access (TLS through pgBouncer).

Connection details

Find your connection details in the service detail page under Peek access details (hostname, port, user, password, connection string). The full list of connection-related environment variables lives under Environment variables in the same service detail.

Connection parameters

Parameter	Internal	Public IP (TLS)	Env Variable
Hostname	Service hostname	Public IP address	hostname
Port (primary)	5432	6432 (via pgBouncer)	port / portTls
Port (replicas, HA only)	5433	N/A	portReplicas
User	db (default)	Same as internal	user
Password	Generated at creation	Same as internal	password
Connection string (primary)	postgresql://${user}:${password}@${hostname}:5432/${dbName}	Same format with TLS port	connectionString / connectionTlsString
Connection string (replicas, HA only)	postgresql://${user}:${password}@${hostname}:5433/${dbName}	N/A	connectionStringReplicas
Database name	db (default)	Same as internal	dbName

Warning

Zerops creates a system user named zps with full privileges for maintenance. Do not delete it, change its password, or remove its privileges, as doing so disrupts Zerops' ability to maintain the database cluster.

Connection ports and TLS

PostgreSQL on Zerops exposes three ports, each for a different purpose:

Port	Purpose	TLS
5432	Primary node (read and write)	Not supported (plaintext)
5433	Read-only replicas, distributing reads (HA mode only)	Not supported (plaintext)
6432	pgBouncer connection pooler	Required

• Ports 5432 and 5433 do not support TLS. Connect with sslmode=disable. Inside a project these ports are reachable only on the private network, and over the VPN the tunnel already encrypts the traffic, so requesting TLS on them fails the handshake.
• Port 6432 (pgBouncer) requires TLS. Connect with at least sslmode=require; the connection is always encrypted. pgBouncer presents a certificate signed by the Zerops CA but does not force the client to validate it, so sslmode=require is enough. If you want to verify the server's identity, use sslmode=verify-full together with the Zerops CA. This holds whether you reach 6432 from inside the project or over a public IP.

Connection pooling with pgBouncer

Port 6432 puts pgBouncer in front of PostgreSQL and multiplexes many client connections onto a small pool of server connections. This is valuable for applications that open many short-lived connections (serverless functions, PHP-FPM, or anything with high connection churn), and we recommend connecting through 6432 rather than 5432 for these workloads, including for internal service-to-service connections.

A few things to know:

• Transaction pooling. A server connection is returned to the pool after each transaction, not when the client disconnects. Prepared statements are supported (cached per server connection), but session-scoped features that span transactions (session-level SET, advisory locks held across statements, LISTEN/NOTIFY) won't behave as expected. Use a direct 5432 connection for those.
• TLS is required on 6432 (see above), even for internal connections.
• HA mode. pgBouncer pools connections to the primary (writes). Read routing across replicas on port 5433 is separate and is not pooled.

Connection limits

max_connections follows the service's RAM and workload type: 50 per GiB (between 20 and 500) for OLTP and WriteHeavy, 25 per GiB (between 10 and 200) for OLAP. RAM counts in memory steps, so a service with 7 GB of RAM still gets the 4 GiB limits. pgBouncer accepts 20 × (max_connections − 4) clients, at least 100.

Memory step	max_connections (5432, 5433)		pgBouncer clients (6432)
	OLTP / WriteHeavy	OLAP	OLTP / WriteHeavy	OLAP
256 MiB	20	10	320	120
512 MiB	25	12	420	160
1 GiB	50	25	920	420
2 GiB	100	50	1920	920
4 GiB	200	100	3920	1920
8 GiB	400	200	7920	3920
16 GiB and up	500	200	9920	3920

• Not all of max_connections is yours. 3 connections are reserved for superusers, and Zerops itself uses up to 4 (monitoring, health checks, backups).
• pgBouncer's server-side pool is much smaller than its client limit. Each user/database pair gets up to 2 × vCPU + 1 server connections (the database's CPU cores), and each database at most a third of max_connections. Transactions beyond that wait in a queue instead of failing.
• The limits cannot be overridden. If you need more connections, connect through 6432, or raise the minimum RAM to the next memory step.
• HA mode. The limits apply per node. Port 5433 balances across both replicas, so reads get twice the max_connections. Each of the two proxies runs its own pgBouncer with the full client limit, but both pool into the same primary: with several busy databases they can still exhaust its max_connections.
• Idle connections in HA mode. The proxies close connections on 5432 and 5433 that stay idle for 60 minutes.

Connect from services in the same project

All services in a project share a private network, so other services reach PostgreSQL directly by its hostname. There are two ways to wire it up.

Direct connection parameters

Use the parameters from Peek access details:

host = db          # your PostgreSQL service hostname
port = 5432        # 5433 for read-only replicas in HA mode
user = db          # default user
password = **********

Environment variables (recommended)

Zerops generates connection environment variables for every PostgreSQL service. To use one service's variables from another, prefix the variable name with the service hostname and an underscore. For example, to read the connectionString of service db, reference db_connectionString.

For read-only connections (HA mode only), use connectionStringReplicas instead.

See the Environment Variables documentation for details and for adding your own variables.

Important notes

• When changing a password, update both the database user and the environment variable, since they don't synchronize automatically.
• Zerops uses the postgresql:// URI scheme. If your software requires the shorter postgres://, create a custom environment variable with that format.
• Internal connections on 5432/5433 don't use SSL/TLS. Security comes from the private network. For TLS-encrypted connection pooling, connect to pgBouncer on 6432.

Connect remotely

There are two ways to reach PostgreSQL from outside Zerops: the VPN (recommended for development) and public IP access (for external applications).

Connect via Zerops VPN

The VPN puts your workstation on the project's private network, so you connect exactly as an internal service would.

1. Install & set up zCLI
2. Start the Zerops VPN
3. Use the connection details from Peek access details in the service detail
4. When finished, stop the Zerops VPN

Important notes

• Connect to 5432/5433 without SSL/TLS over the VPN, since the tunnel already encrypts the traffic.
• Environment variables are not available over the VPN; copy the access details from the GUI.
• If a connection doesn't work, try the .zerops suffix on the hostname (e.g. db.zerops). See the VPN troubleshooting page.

Connect via public IP

Public access always goes through pgBouncer on port 6432 over TLS (see Connection ports and TLS), which also pools your connections. The read-only replica port (5433) is not exposed publicly. Route reads through your application logic instead.

Trusting the TLS certificate

The certificate on 6432 is signed by the Zerops Certificate Authority. To verify it from outside Zerops, download and trust the Zerops CA, for example psql "... sslmode=verify-full sslrootcert=./zerops-ca.pem".

To enable public access:

1. Open your PostgreSQL service in the GUI and go to Public Access through IP Addresses
2. Choose IPv6 (available by default) or IPv4 (requires the unique IPv4 add-on)
3. Open one or more ports pointing to your service (routed through pgBouncer):
   • Any port from 10–65435 (except 80 and 443)
   • Each public port can map to any internal port; multiple public ports can share one internal port
   • IPv4 and IPv6 can be configured independently
4. Optionally enable firewall protection
5. Click Publish X IP access change(s) to apply

Database management tools

You can use any PostgreSQL-compatible tool to administer your database. For a zero-setup option, Zerops provides a ready-to-use recipe for Adminer, a lightweight, full-featured web-based tool that supports PostgreSQL.

Install Adminer

In the GUI, open your project, select Import services, and paste:

zerops-import.yaml

services:
  - hostname: adminer
    type: php-apache@8.5
    maxContainers: 1
    enableSubdomainAccess: true
    buildFromGit: https://github.com/zeropsio/recipe-adminer

Then start the VPN and open http://adminer in your browser (try http://adminer.zerops if it doesn't resolve).

Caution

Do not use https when reaching management tools over the VPN.

Desktop tools and psql

Popular desktop clients (pgAdmin, DBeaver, DataGrip, or any PostgreSQL-compatible tool) work with Zerops over the VPN:

1. Start the Zerops VPN to open an encrypted tunnel to your project
2. Copy the connection details from the GUI (environment variables aren't available over the VPN)
3. Connect without SSL/TLS on 5432/5433, since the tunnel already encrypts the connection

For the psql command-line client:

psql -h [hostname] -U [user] -d [database_name]

psql has no password flag, so it prompts you. To pass the password non-interactively, use PGPASSWORD:

PGPASSWORD=[password] psql -h [hostname] -U [user] -d [database_name]

Tip

Try [hostname].zerops instead of [hostname] if you hit connection issues over the VPN.

To move data in and out with these tools, see Export & import data.