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
For connection methods and environment variables, see the Connect to PostgreSQL in Zerops page.
- When changing passwords, update both the database user password and the environment variable separately - they don't automatically synchronize.
- While both
postgresql://
andpostgres://
URI formats are valid, Zerops uses thepostgresql://
format. If your software requirespostgres://
, 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:
- In Zerops GUI, open your project and select Import services from the left menu
- Copy and paste one of the following YAML configurations:
Accessing Management Toolsβ
After installation, you can access these tools via VPN:
- Start the Zerops VPN
- Type
http://adminerevo
orhttp://phpmyadmin
in your browser
Try http://adminerevo.zerops
or http://phpmyadmin.zerops
if you encounter any connection issues.
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:
- Establish a secure tunnel using the Zerops VPN to create an encrypted connection to your Zerops project
- Obtain the connection details from Zerops GUI
- Environment variables are not available through VPN connections
- 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:
Try {hostname}.zerops
instead of just {hostname}
if you encounter any connection issues.
How to install and manage PostgreSQL pluginsβ
You can list all available PostgreSQL plugins by running the following query (superuser privileges not required):
To install plugins, you must connect as a superuser (postgres
) and run the appropriate CREATE EXTENSION command. For example:
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:
Dictionary and affix files - used for stemming and word normalization:
Special rules file:
For more information on text search dictionaries, refer to the PostgreSQL documentation.