# 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](/postgresql/how-to/connect).

## Default database and user

Zerops creates a default database and user automatically when a PostgreSQL service is [created](/postgresql/how-to/create).

- **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](#extensions)).

:::info
For connection methods, ports, and environment variables, see [Connect to PostgreSQL](/postgresql/how-to/connect).
:::

## Extensions

### List available extensions

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

```sql
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:

   ```sql
   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:

#### Available dictionary files

**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](https://www.postgresql.org/docs/current/textsearch-dictionaries.html) 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](/features/backup). 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:

   ```sh
   pg_restore -d [database_name] schemaName.dump
   ```

   See the [official PostgreSQL backup documentation](https://www.postgresql.org/docs/current/backup-dump.html) for restore options. To use a web UI like [Adminer](/postgresql/how-to/connect#database-management-tools), 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](/postgresql/how-to/connect#connect-via-zerops-vpn) for a local tool or via [Adminer](/postgresql/how-to/connect#database-management-tools) in the browser.

### With Adminer or a desktop tool

Once connected, use the tool's built-in export/import functions. See [Database management tools](/postgresql/how-to/connect#database-management-tools) and [Desktop tools and psql](/postgresql/how-to/connect#desktop-tools-and-psql).

### With the command line

Export data and schema with [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html):

```sh
pg_dump [database_name] > dump.sql
```

Import a plain SQL dump with `psql`:

```sh
psql [database_name] < dump.sql
```

For a custom-format dump (`pg_dump -Fc`, the format Zerops backups use), use [pg_restore](https://www.postgresql.org/docs/current/app-pgrestore.html):

```sh
pg_restore -d [database_name] dump.dump
```

See [Desktop tools and psql](/postgresql/how-to/connect#desktop-tools-and-psql) for how to reach the database and pass the password with `PGPASSWORD`.