PostgreSQL is a free object-relational database management system.

Beget VPS with pre-installed PostgreSQL is a ready-to-use and fully configured database management system on your virtual server. No manual configuration of PostgreSQL database management system is required.

Installation package information

Ubuntu 24.04
PostgreSQL 18
Nginx
Adminer Web UI

Installing PostgreSQL

You will be able to create a server with preinstalled parameters. You will also be prompted to specify:

Password for the administrative user.
Domain that will be used to access the Adminer web-interface.
If you do not have a domain in our system yet, we will give you a free technical domain .beget.app and an automatically installed SSL certificate will be issued for it.
Admin contact email for SSL certificate installation.

Once PostgreSQL is installed and deployment is complete, you can get started.

Working with the Adminer web interface

To go to Adminer, use the access button of the installed software in the panel or open the address: https://your-domain/psql.php

Please use this data for admin authentication:

Server: localhost
Username: postgres (default user) or another user you created earlier
Password: the one you set when you created the server (this is also duplicated to your email) or another password you set manually later
Database: the database you require, you can leave it blank

After authentication, you can create new and modify old databases, tables in them, import dumps and perform all other operations through the web interface.

Working with PostgreSQL from your application

Working with the remote PostgreSQL database from your application is the same as working with a locally deployed database. Use the following credentials to connect:

Server: Public or private IP address of your server OR the domain name you specified during installation
Port: 5432
Username: The default postgres user or another user you create later
Password: the one you set when you created your server (this is also duplicated to your email) or another password you set manually later
Database: postgres default user or one you create later

Example code to connect to PostgreSQL in PHP

<?php
$host     = 'my.ip.address'; // change to your server IP address or IP
$db       = 'postgres';      // database name
$user     = 'postgres';      // username
$password = 'my.password';   // change to your password

try {
    $dsn = "pgsql:host=$host;port=5432;dbname=$db;";

    // connect to PostgreSQL
    $pdo = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

    if ($pdo) {
        echo "Successful connection to the database '{$db}'!", PHP_EOL;
    }
} catch (PDOException $e) {
    die($e->getMessage());
} finally {
    if ($pdo) {
        $pdo = NULL;
    }
}

We wish you good luck with PostgreSQL!

FAQ

How can I create a new user in PostgreSQL?

Method 1. By connecting to the server via SSH:

Connect to the server via SSH: ssh root@server_ip
Authenticate as a postgres system user: su postgres
Use the createuser utility to create a user:
createuser username -s -P

Pay attention!

The user is created with superuser rights. You can omit the -s option to create an ordinary user.

Method 2: Through the Adminer web interface:

Go to Adminer via a link in the control panel or at:
https://your-domain/psql.php
Click on the link on the left side of the menu:
SQL query
Use the following query to create a user with superuser rights:
CREATE USER test with ENCRYPTED PASSWORD 'mypassword' SUPERUSER

How can I add external access to PostgreSQL (for example, to make the database accessible from shared hosting)?

Pay attention!

In our distribution, external access is allowed by default for the database we are installing, it is not necessary to add it separately.

You need to add the following contents to the end of /etc/postgresql/14/main/pg_hba.conf:

host    all    all    0.0.0.0/0    md5

You can replace 0.0.0.0/0 with a specific address.

After all, you need to reload the postgresql service:
systemctl reload postgresql@14-main.service

How can I restart the PostgreSQL service or Adminer?

PostgreSQL:

Run the command: systemctl restart command postgresql@14-main.service

Adminer:

Run the command: systemctl restart php8.1-fpm.service
Run the command: systemctl restart nginx.service

How can I back up my PostgreSQL database?

The easiest way to perform a backup is to use the pg_dump utility:

Connect to the server via SSH: ssh root@server_ip
Go to /var/tmp directory
Authenticate as a postgres system user: su postgres
Run the following command to create a dump: pg_dump postgres > backup.sql
(postgres is the name of the SQL database to be backed up, backup.sql is the desired name of the dump file)

Where are the PostgreSQL configuration files?

Configuration files are located in the directory:
/etc/postgresql/14/main

Where do the PostgreSQL data files sit?

The data files are located in the directory:
/var/lib/postgresql/14/main

PostgreSQL