Manage PostgreSQL Users, Databases and Plugins in Zerops

This guide covers how to manage your PostgreSQL databases in Zerops, including default setup, database management tools, plugins, and best practices.

Default Database and User

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

Database

• Name: Identical to the service hostname
• Encoding: utf8mb4

DB User

• Username: Identical to the service hostname
• Password: Generated randomly

Info

For connection methods and environment variables, see the Connect to PostgreSQL in Zerops page.

Important notes

• When changing passwords, update both the database user password and the environment variable separately - they don't automatically synchronize.
• While both postgresql:// and postgres:// URI formats are valid, Zerops uses the postgresql:// format. If your software requires postgres://, create a custom environment variable with this format.
• Do not use SSL/TLS protocols for internal connections. Security is assured by the project's private network.

Database Management Tools

You can use any PostgreSQL management tool of your choice to administer your databases in Zerops. For convenience, Zerops provides ready-to-use recipes for two popular web-based database management tools:

• AdminerEvo - developed by the AdminerEvo community and is a continuation of the Adminer project by Jakub Vrána
• phpMyAdmin - a popular free database administration tool that works with both MySQL and PostgreSQL databases

Installing Management Tools

You can install these tools with a simple one-click import in Zerops:

1. In Zerops GUI, open your project and select Import services from the left menu
2. Copy and paste one of the following YAML configurations:

services:
  - hostname: adminerevo
    type: php-apache@8.3
    enableSubdomainAccess: true
    buildFromGit: https://github.com/zeropsio/recipe-adminerevo

Accessing Management Tools

After installation, you can access these tools via VPN:

1. Start the Zerops VPN
2. Type http://adminerevo or http://phpmyadmin in your browser

Tip

Try http://adminerevo.zerops or http://phpmyadmin.zerops if you encounter any connection issues.

Caution

Do not use https when connecting to management tools via VPN.

Database Tools on Your Workstation

You can use various database management tools from your local workstation to connect to your PostgreSQL database in Zerops:

1. Establish a secure tunnel using the Zerops VPN to create an encrypted connection to your Zerops project
2. Obtain the connection details from Zerops GUI
   • Environment variables are not available through VPN connections
3. Connect with your preferred database tool
   • Do not use SSL/TLS (security is provided by the VPN)
   • Desktop Database Tools - popular GUI tools like pgAdmin, DBeaver, DataGrip, or any other PostgreSQL-compatible client will work with Zerops
   • Command Line with psql - connect using the standard PostgreSQL command-line client with the credential obtained above:

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

Tip

Try {hostname}.zerops instead of just {hostname} if you encounter any connection issues.

How to install and manage PostgreSQL plugins

Viewing available plugins

You can list all available PostgreSQL plugins by running the following query (superuser privileges not required):

SELECT * FROM pg_available_extensions ORDER BY name;

Installing plugins (requires superuser)

1. Connect with superuser credentials:

   • Use the superUser (user postgres) and superUserPassword environment variables from your PostgreSQL service

2. Switch to your service database: When logging in as the superuser, you're initially in the postgres database, not your service database.

3. Install required extensions:

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

Warning

Currently, it is not possible to add new plugins that are not already listed in pg_available_extensions.

When working with text search functionality, you'll need to reference the correct stop, dict, and affix files when creating dictionaries in your database. These files are essential for proper text search configuration.

Zerops PostgreSQL includes the following 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

For more information on text search dictionaries, refer to the PostgreSQL documentation.