PostgreSQL Management

PostgreSQL is an open-source object-relational database management system. It is known for its reliability, scalability, and extensive functionality, including JSON support, full-text search, stored procedures, extensions, and more.

With Hostman managed PostgreSQL databases, you get a fully managed PostgreSQL cluster without manual setup: everything is handled automatically.

When creating a cluster, you can select the PostgreSQL version: 14, 15, 16, 17, 18.

Creating Databases

Copy link

When you create a new cluster, a database named default_db is created automatically. 

You can create additional databases:

1. Go to the Databases section of your Hostman control panel.

2. Click on the cluster.

3. In the Databases tab, click Add.

4. Provide a name for your database: 3–64 characters. Letters, numbers, and special characters are allowed.

5. Add a description, if necessary.

6. Click Create.

Database Import

Copy link

To import a database:

1. Go to the Databases section of your Hostman control panel.
2. Click on the cluster.
3. In the Databases tab, click Import.

4. Enter the connection details for the database you want to import.
5. Click Start import. 

Requirements and Limitations

Copy link

• Network Access: The source database must be accessible for connection from the internet from any IP address. Without this, the import will not start.
• Unique Name: The target cluster must not contain a database with the same (identical) name.
• Version Compatibility: Import supports transferring data between different versions of PostgreSQL (for example, from 17 to 18). However, some extensions and parameters may be incompatible or differ between versions.

What will be transferred:

• Table structures
• Indexes
• Data in tables

What will not be transferred:

• Users and roles
• Extensions

You will need to create users and install the required extensions manually in the new database.

Users and Privileges

Copy link

When a new cluster is provisioned, it automatically includes a database user named gen_user. You can add more users in the Databases section.

Creating Users

Copy link

1. Go to the Databases section and click on the cluster.

2. Open the Users tab and click Add.

3. Set the username and password:

• Username: 3–64 characters. Letters, numbers, and special characters are allowed.

• Password: 8–30 characters. Letters, numbers, and special characters are allowed.

4. (Optional) Configure the user’s privileges. You can also leave the default parameters and change them later at any time.

5. Click Create.

Modifying Privileges

Copy link

To update the privileges of an existing user:

1. Go to the Databases section and click on the cluster.

2. Open the Users tab.

3. Click the three dots next to the user.

4. Select Privileges.

5. Choose the required privileges. You can grant access to a specific database or grant the same privileges for all databases by enabling Identical for all databases in the top right.

Available Privileges

Copy link

Changing User Password

Copy link

You can change the user password:

1. Go to the Database section and click on the cluster.
2. Go to the Users tab.
3. Click the three dots next to the user.
4. Select Change password.
5. Enter a new value and save changes.

Extensions

Copy link

PostgreSQL extensions allow you to expand the functionality of PostgreSQL databases by adding additional capabilities to solve certain tasks. You can read more about extensions in the PostgreSQL documentation.

Enabling Extensions

Copy link

To enable or disable extensions:

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify next to Extensions.

4. Select the database and the required extensions. To apply the same extensions to all databases in the cluster, enable the Use the same extensions for all databases option.

Extensions List

Copy link

The following extensions are available:

Database Parameters

Copy link

You can change database settings to optimize its performance. 

By default, the database is created with the parameters that are suitable for most tasks and will ensure the performance of the selected configuration. However, if your project requires specific settings, you can change them as you wish. 

Be careful and change parameters only if you are sure of their purpose. Incorrect configuration can negatively affect the operation of the database and its performance.

Changing Parameters

Copy link

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify parameters next to Database parameters.

4. Adjust the parameters. To read more about each parameter, click Open description next to it.

5. Click Apply at the bottom of the page.

Parameters List

Copy link

Resetting Parameters

Copy link

You can easily restore the default parameters:

1. Go to the Databases section and click on the cluster.

2. Go to the Configuration tab.

3. Click Modify parameters next to Database parameters.

4. Scroll down to the bottom of the page and click Restore default settings.

Connecting to the Database

Copy link

You can connect to your databases using Adminer or via terminal. Connecting via Adminer is possible only if the cluster has a public IP address.

Adminer

Copy link

You can connect to the database via the built-in web interface Adminer. 

1. Go to the Databases section and click on the cluster.

2. Click the Web Interface button and select Adminer.

Terminal

Copy link

Install the PostgreSQL client:

• Ubuntu/Debian

sudo apt install postgresql-client

• Arch

sudo pacman -S postgresql-libs

• Fedora

sudo dnf install postgresql

There are two ways to connect to a PostgreSQL cluster:

• via public IP;
• via private IP.

You can copy the connection command from:

• the Dashboard tab:

• the Connection tab.

Role Management

Copy link

A user with the CREATE ROLE privilege can:

• create new users (CREATE USER);
• create roles (CREATE ROLE);
• manage role memberships (GRANT ... TO ...).

Creating a User

Copy link

A user is a role that is allowed to log in to the database. Such a role can connect with a username and password.

To create a user with a password, run:

CREATE USER someuser WITH PASSWORD 'password';

Here, someuser is the username and password is the password.

The new user will appear in the Users tab shortly after creation.

Creating a Role and Assigning It to a User

Copy link

Instead of granting permissions directly to each user, it is better to create roles with the required privileges and then assign users to these roles.

Let’s create two roles: one with read-only access, and another with read and write access.

Read-only role:

CREATE ROLE app_reader;

Read/write role:

CREATE ROLE app_writer;

Assign a role to a user:

GRANT app_writer TO someuser;

Schema Configuration

Copy link

By default, all PostgreSQL tables are created in the public schema. To grant permissions, create a new schema:

CREATE SCHEMA app_data;

Create tables in the new schema:

CREATE TABLE app_data.users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE app_data.orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES app_data.users(id),
  order_date TIMESTAMP DEFAULT now()
);

Grant access to the schema for the previously created roles:

GRANT USAGE ON SCHEMA app_data TO app_reader;
GRANT USAGE ON SCHEMA app_data TO app_writer;

Grant the right to create objects in the schema:

GRANT CREATE ON SCHEMA app_data TO app_writer;

Table Permissions

Copy link

Grant read access to all existing tables in the schema:

GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_reader;

Grant read/write access:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO app_writer;

Set default permissions for new tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT ON TABLES TO app_reader;

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

And default permissions for new sequences:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

Creating a Dump

Copy link

To create a dump, run:

pg_dump -x 'postgresql://<user>:<password>@<host>:5432/<db>' | gzip > default_db.sql.gz

The -x (--no-privileges) flag excludes GRANT and REVOKE commands from the dump, since permission management is handled via the control panel.

Without this flag, errors may occur when loading the dump, though they won’t affect data restoration.

Loading a Dump

Copy link

To load a dump, run:

zcat default_db.sql.gz | psql 'postgresql://<user>:<password>@<host>:5432/<db>'

This command decompresses the dump and passes it to the PostgreSQL client for execution.

Basic Plan Limitations

Copy link

The basic database plan (1 CPU / 1 GB RAM / 20 GB NVMe) has the following technical limitations:

• Additional databases cannot be added
• Additional users cannot be added
• The following parameters cannot be configured:
  • shared_buffers
  • effective_cache_size
  • maintenance_work_mem
  • max_connections