Switching your databases

The Deployment Monitoring Console (DMC) allows you to switch from an internal to an external database or from an external to another external database with the use of a few simple commands. In this topic, we will cover the tools, commands, and the workflow for switching your database, so you can do so easily.

About PostgreSQL tools for backup and migration of DMC DB data

PostgreSQL has command line utilities that allow you to migrate data from one database and restore it to another database using pg_dump and pg_restore commands.

  • The pg_dump command allows you to create a backup file which contains the structure and data of a PostgreSQL database.
  • The pg_restore command allows you to populate a new database from a backup file created with pg_dump.

These commands can be executed from any system and are automatically installed when you install a PostgreSQL database, or when you install the PostgreSQL client. For more information on these commands, view the PostgreSQL documentation that corresponds to your version.

Using pg_dump to create a PostgreSQL dump file

To create a PostgreSQL dump file, use the following command:

pg_dump -U <database_user> -h <database_hostname/ip> -p <database_port> <database_name> > <dump_file_name>
$> pg_dump -U datical -h dmc.mynetwork.net -p 5432 datical > dmc-dump

Using pg_restore to populate a new empty PostgreSQL database

To create a PostgreSQL dump file, use the following command:

code pg_restore --clean --if-exists -h <database_hostname/ip> -U <database_user> -Fc -n public -d <database_name>  <dump_file_name>
$> pg_restore --clean --if-exists -h dmc.mynetwork.net -U datical -Fc -n public -d datical  dmc-dump

Note: pg_restore should only be used to populate an empty database intended for DMC usage.

About the db configure command

You can update the database connection information for your DMC installation by using a sub-command to the datical-control. Before running the command you will need the connection information for your new database.

$> sudo datical-control db configure -h
Change the Application DB configuration
jdbc url example - jdbc:postgresql://HOSTNAME:PORTNUMBER/DATABASE
usage: datical-control db configure [args]
--debug            Prints debug-level messages
-h,--help            Help (Prints this message)
--password <arg>   DMC Database password
--url <arg>        DMC Database jdbc url
--username <arg>   DMC Database username
$> sudo datical-control db configure --url jdbc:postgresql://dmc.mynetwork.net:5432/datical --username datical --password mypassword

How to switch the DMC between databases

Now that you have specific information about the tools and commands, let's go over the workflow. To switch the DMC from one database to another (regardless of it being internal or external):

  1. Backup your existing DMC DB using the pg_backup command in PostgreSQL.
    2. pg_dump -U datical -h dmc.mynetwork.com -p 5432 datical > dmc-dump
  2. Follow the instructions for creating a new PostgreSQL database for the DMC found here.
  3. Populate the new DMC DB with data from the dump file you created in step 1.
    4. pg_restore --clean --if-exists -h newdmcdb.mynetwork.com -U datical -Fc -n public -d datical dmc-dump
  4. On your DMC host, run the following command to configure the DMC to use the new database.
    5. sudo datical-control db configure --url jdbc:postgresql://newdmcdb.mynetwork.com:5432/datical --username datical --password <password>

Related links