Managing Scaleway Elements Database instances with pgAdmin 4

pgAdmin Overview

pgAdmin is an open-source management tool for PostgreSQL databases. It allows the management of your Scaleway Elements Database instances and other PostgreSQL databases through an easy-to-use web-interface within your web browser.

Requirements

Installing pgAdmin

1 . Connect to your Scaleway Elements Virtual Cloud Instance by SSH.

2 . Update the apt sources and the software already installed on the instance:

apt update && apt upgrade -y

3 . Import the PostgreSQL repository key:

sudo apt-get install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

4 . Add the PostgreSQL repository to the APT packet manager, by configuring the file /etc/apt/sources.list.d/pgdg.list:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

5 . Update the apt sources and install pgAdmin 4 via the apt packet-manager. The following command will install the packages pgadmin4 and pgadmin4-apache2 providing an Apache web server configured for pgAdmin:

apt-get update && apt-get install pgadmin4 pgadmin4-apache2 -y

6 . During the installation, you are prompted to enter the email address of the first user for pgAdmin. Enter your email address and press Enter:

7 . The installation tool asks for a password for the pgAdmin user. Enter it and press Enter:

The basic installation of pgAdmin is complete now.

Important: By default, connections to the application are being made using an insecure HTTP connection. It is recommended to configure a HTTPS connection to encrypt the traffic between your instance and your computer. To do so, you should setup an A-Record within your domain name and install Certbot to obtain a free Let’s Encrypt TSL/SSL certificate:

1 . Install Certbot and the Apache web server plugin:

sudo apt-get install certbot python-certbot-apache

2 . Run Certbot to obtain a certificate and configure the Apache web server automatically:

sudo certbot --apache

Configuring pgAdmin

Once installed, proceed to the configuration of pgAdmin with your Scaleway Database instance.

1 . Open your web browser and point it to http://<your_instance_ip>/pgadmin4/, or if installed with HTTPS https://<your_domain_name>/pgadmin4/. The pgAdmin login page displays:

2 . Enter the username and password configured during the installation and click on Login

3 . The pgAdmin dashboard displays, to connect the application with your Scaleway Elements Database instance, click on Add New Server:

4 . Specify the general information of the Database instance:

Name: Enter a name of your choice for the Database instance
Server group: If you are managing multiple Database instances, you can create server groups to make the management easier. Background: You can configure a background color for the instance
Foreground: You can set a foreground color for the instance
Connect now: Tick this box to connect to the instance once the configuration is completed
Comments: You may add additional comments to the instance

5 . Click on the Connection tab of the create server window and enter the connection details of your Database instance:

Host name/address: Enter the IP address of your Database instance, displayed on the database information page of your instance.
Port: Enter the port of your database instance. This information is available from the database information page of your instance.
Maintenance database: Enter the information of your default database here. With default configuration this value shall be rdb.
Username: Enter the username for your database. You can find this information in the users tab of your database instance in the Scaleway console.
Save Password: Tick this box to save your database password in pgAdmin

6 . Click on the SSL panel and upload the SSL certificate available from the database information page of your Scaleway Console. By default pgAdmin will negociate a SSL connection without checking the CA (this is the postgresql behaviour). With this option, we add a layer of security by checking the certificate authority (CA) used to sign the certificate and make sure this is the same as the one on the server.

SSL mode: Set the SSL mode value to Verify-CA to use the certificate issued by Scaleway
Client certificate: Keep this value empty
Client certificate key: Keep this value empty
Root certificate: Click on ... to upload the SSL certificate available from your Scaleway console.

7 . Once all settings are made, click on Save to update the configuration of pgAdmin and to establish a connection to your Database Instance.

8 . The Dashboard displays and your Database Instance is listed in the servers section of pgAdmin:

You can now manage your Database Instance from a visual interface. For more information using pgAdmin, you may follow the official documentation.

Discover a New Cloud Experience

Deploy SSD Cloud Servers in seconds.