Manage & data

This guide covers the default database setup, installing extensions, backups, and moving data in and out. For connecting and management tools, see Connect.

Default database and user

Zerops creates a default database and user automatically when a PostgreSQL service is created.

• Database name: db
• User: db
• Password: randomly generated
• Encoding: UTF8 (locale C.UTF-8)

Both names are fixed; they don't follow the service hostname. Besides the db user, Zerops also creates a postgres superuser for you (see Extensions).

Info

For connection methods, ports, and environment variables, see Connect to PostgreSQL.

Extensions

List available extensions

Any user can list the extensions available to install (superuser not required):

SELECT * FROM pg_available_extensions ORDER BY name;

Install an extension (requires superuser)

1. Connect as the superuser. Use the superUser (user postgres) and superUserPassword environment variables from your service.

2. Switch to your service database. As the superuser you start in the postgres database, not your service database.

3. Create the extensions you need:

   CREATE EXTENSION pg_stat_statements;
   CREATE EXTENSION vector;
   CREATE EXTENSION postgis;

Warning

You can only install extensions already listed in pg_available_extensions; new extensions cannot be added.

When configuring text search dictionaries, reference the correct stop, dict, and affix files. Zerops PostgreSQL ships the following:

Stop word files, used to remove common words that don't add significant meaning:

czech.stop
danish.stop
dutch.stop
english.stop
finnish.stop
french.stop
german.stop
hungarian.stop
italian.stop
nepali.stop
norwegian.stop
polish.stop
portuguese.stop
russian.stop
slovak.stop
spanish.stop
swedish.stop
turkish.stop

Dictionary and affix files, used for stemming and word normalization:

cs_CZ.affix
cs_CZ.dict
en_US.affix
en_US.dict
pl_PL.affix
pl_PL.dict
sk_SK.affix
sk_SK.dict

Special rules file:

unaccent.rules

See the PostgreSQL text search documentation for more.

Backups

Zerops automatically backs up PostgreSQL services with full encryption. For scheduling, retention, tagging, storage quotas, and manual backups (and the CLI tools), see Zerops Backups. This section covers the PostgreSQL specifics.

Backup format

• Format: .zip containing per-schema .dump files
• Tooling: pg_dump, custom format (-Fc), files named schemaName.dump
• Storage: encrypted, in isolated object storage

Restore a backup

1. Download the backup .zip from the GUI

2. Extract it to get the per-schema .dump files

3. Prepare the target (clean existing data, or use a fresh instance)

4. Restore each schema with pg_restore. The dumps are custom format (-Fc), so plain psql can't read them:

   pg_restore -d [database_name] schemaName.dump

   See the official PostgreSQL backup documentation for restore options. To use a web UI like Adminer, first convert a dump to plain SQL with pg_restore -f schema.sql schemaName.dump and import that SQL file.

High availability

For HA services:

• Backups run on a randomly selected healthy node
• Other nodes stay operational during the backup
• Manual backups typically run on the primary node

Best practices

• Take a manual backup with a protected tag before migrations or major schema changes
• Test your restore process periodically in a non-production environment
• Monitor backup storage usage in the Project Overview
• Use descriptive tags like pre-migration-v2
• Mind schema dependency order when restoring

Export & import data

To move data in and out, first connect to your database, either over the VPN for a local tool or via Adminer in the browser.

With Adminer or a desktop tool

Once connected, use the tool's built-in export/import functions. See Database management tools and Desktop tools and psql.

With the command line

Export data and schema with pg_dump:

pg_dump [database_name] > dump.sql

Import a plain SQL dump with psql:

psql [database_name] < dump.sql

For a custom-format dump (pg_dump -Fc, the format Zerops backups use), use pg_restore:

pg_restore -d [database_name] dump.dump

See Desktop tools and psql for how to reach the database and pass the password with PGPASSWORD.