Skip to content

DBOD Self-Training: A Hands-On Guide

Welcome to the Database on Demand (DBOD) service. This module is designed to help you master the service through practical exercises. By following these steps, you will learn the full lifecycle of a database instance: from the initial request to advanced recovery, upgrades, and secure connection methods.

1. Requesting Your First Instance

To begin, you must familiarize yourself with the instance creation workflow on the portal.

Exercise: Request a new PostgreSQL instance

  1. Navigate to the DBOD Portal.

  2. Request a new instance using the following parameters:

    • Instance Name: my_pgtest_<YourInitials> (e.g., my_pgtest_JD).
    • Category: Select TEST.
    • Admin Group: Use a specific e-group (e.g., my-admin-users).

    Warning

    Users in this e-group will have full admin access. Avoid using large, generic e-groups.

    • Database Type: Select Postgres 15.x.

  3. Wait for the confirmation email indicating your instance is ready.

2. Portal Management and Configuration

The DBOD portal is your central control hub. While your new instance is created, you can practice navigating the interface.

Exercise: Gather information and modify parameters

  1. Ownership & Description:
    • Identify the current owner and try assigning ownership to a colleague or yourself.
    • Update the Description field with your name or project details and press Enter to save.
  2. Lifecycle: Set the Expiry Date to a future date (e.g., one month from today) to practice lifecycle management.
  3. Monitoring:
    • Check the Jobs tab to review the history of submitted operations.
    • Check the Logs tab to view the database instance logs.
  4. Configuration Parameters (MySQL Example):
    • Locate the my.cnf file in the portal, which hosts the configuration.
    • Find the parameter for the maximum number of concurrent connections.
    • Action: Change the innodb_buffer_pool_size to 256M and submit the changes.
    • Action: Note down the hostname and port number for the next step.

3. Connecting to Your Database

DBOD instances are hosted on the internal CERN network and are not publicly available from the internet.

Exercise: Connect via Command Line

  1. Log in to an lxplus node.

  2. Use the mysql console client (for MySQL) or psql (for PostgreSQL) to connect.

    Note

    Connection details are sent via email when you request the instance but you can also get this information from the instance view in the DBOD web interface.

  3. Verify Configuration: If you modified the MySQL buffer pool in Part 2, run the following query to check the value: 

    select @@innodb_buffer_pool_size/1024/1024;
    Check if the result matches your expectation.

4. Applying Changes (Restart or Reload)

You may notice that changing a parameter in the portal (Exercise 2) did not immediately update the value in the database (Exercise 3). This is because the portal updates the static configuration file, but the running process needs a restart.

Exercise: Apply configuration changes

  1. Go to the On/Off menu in the DBOD portal.

  2. Issue a Restart command.

    Warning

    A restart operation causes downtime for your database.

  3. Monitor the Jobs tab (click "refresh jobs") to verify when the restart is complete.

  4. Reconnect to your database and run the verification query from Exercise 3 again to confirm the value has changed.

    Note

    For some PostgreSQL parameter changes, the configuration can be reloaded without causing downtime.

5. Backup and Point-in-Time Recovery

DBOD performs automated daily backups, but you can also trigger them manually before risky operations.

Exercise: Simulate data loss and recovery

  1. Prepare Data: Connect to your database and create a test table: 
    create database hr;
use hr;
create table employees (name varchar(20), salary integer);
insert into employees values ('Emanuel Macron', 5000);
insert into employees values ('Donald Trump', 100000);
  2. Create Backup: Go to the Backup and Restore tab in the portal and click the button to create a backup.
  3. Simulate Error: Run a specific SQL command to verify the current time (select now();), then accidentally delete all data by omitting the WHERE clause: 
    delete from employees;
  4. Restore:
    • Return to the portal's Backup and Restore tab.
    • Select a point in time before the delete command was issued.
    • Wait for the recovery job to finish (monitor via the Jobs tab).
  5. Verify: Reconnect and confirm the data has returned: 
    use hr;
select * from employees;

6. Creating a Clone (Safe Testing)

A Clone is a temporary copy of your production database. It is the safest way to test changes (like upgrades or schema modifications) without risking your actual data.

Exercise: Create a clone from your test instance

  1. Navigate to your instance’s page on the DBOD Portal.
  2. Click on the Clones tab.

  3. Click the Create new clone button.

    • Option A (from Backup): Select a specific date (marked with a blue dot) to clone from a nightly backup.

      Note

      If you don't see any blue dot because your instance has less than 24h, you can always trigger a manual backup first, as explained in Step 2, Exercise 4.

    • Option B (Point in Time): Use the second tab to select a precise date and time.

  4. Proceed with the creation.

  5. Important: Once the clone appears in the list, refresh your browser session (logout/login) if you cannot immediately see the management options for the new instance.
  6. Connect to the clone using the new hostname provided in the portal and verify your data is present.

7. Upgrading an Instance

DBOD provides a "One-Click Upgrade" feature. Never upgrade a production instance without testing on a clone first.

Exercise: Trigger an upgrade check and upgrade

  1. Identify Upgrade Availability: Look at your instance list. If a new version is available, you will see a blue arrow icon next to your instance. If you created a Postgres 15, you should see an upgrade available to 16.
  2. Run the Checker:
    • If the upgrade arrow is greyed out, click the Upgrade Checker button (icon near the upgrade arrow).
    • This triggers a background job that checks for incompatibilities.
  3. Review Report:
    • Access the report in your specific CERNBox folder (linked in the DBPD web portal).
    • Errors block the upgrade; Warnings should be reviewed but do not block the process.
  4. Execute Upgrade:
    • Once the checker passes, click the blue arrow icon to start the upgrade.
    • The system automatically takes a snapshot before proceeding to ensure rollback capability.

8. MySQL Character Set Conversion

For MySQL 8 instances, the old utf8 (alias for utf8mb3) character set is deprecated. Use the automated tool to convert to utf8mb4.

Exercise: Perform a Dry Run conversion

  1. On your MySQL 8 instance page, click the Character Set Conversion icon.
  2. Configure Scope: Choose to upgrade specific tables or all character sets.
  3. Dry Run:
    • Select the Dry Run option. This simulates the process without changing data.
    • Start the job.
  4. Review Output:
    • Go to the Logs tab.
    • Review alter_statements-<timestamp>.sql to see the proposed SQL commands.
    • Review dump-diff-<timestamp>.log to see schema differences.
  5. If the Dry Run is successful, you would repeat the process without the "Dry Run" option to apply changes.

9. Advanced Access (GUI Clients)

This is an optional exercise if you want to continue working on exercises with the DBoD.

Try installing a GUI database client on your computer or configuring your favorite IDE to connect to a database hosted in the DBoD service.

Direct network access to DBOD from outside CERN is blocked. To use a GUI client (like DBeaver or MySQL Workbench) from your laptop, you must use SSH tunneling.

Exercise: Configure a tunnel

  1. Open a terminal on your local machine.
  2. Run the following SSH command to create a tunnel: 
    ssh -L 5555:dbod-<DB_NAME>.cern.ch:<PORT> <CERN_LOGIN>@lxtunnel.cern.ch
    • Replace <DB_NAME>, <PORT>, and <CERN_LOGIN> with your specific details.
  3. Configure your GUI client to connect to:
    • Hostname: localhost
    • Port: 5555

The forwarding will work for as long as your SSH session remains running.