Import PostgreSQL database data from an external non-MedStack VM

This example shows one way to import a database from an external, non-MedStack PostgreSQL database.

  • A non-MedStack VM that is running your app, which can connect to your current database.

Note: For compliance reasons, we recommend not storing database dumps on individual workstations. These instructions require that the dump be created and stored on servers for that reason.

If you don’t have the above requirements, then contact us for more guidance.

1. On the non-MedStack VM, perform a database dump

If you are running postgres, you can use this command:

sudo pg_dump -U DB_USER -W -F t DB_NAME > database_dump.tar

Save the database_dump.tar file in a place where it is accessible via SSH so that you can access it below.

2. On MedStack Control, set up a temporary import container

Within your MedStack Control cluster create a new volume, called `dbdumps`.

Next, start a new temporary service with the following arguments:

  • image: debian:buster
  • Click show advanced options:
    • command: sleep
    • arguments: infinity

mounts and volumes: map your new `dbdumps` volume to `/dbdumps`

3. Add your VM’s SSH key to the temporary import container

Install OpenSSH client and set up SSH:

apt-get update && apt-get install -y openssh-client
mkdir /root/.ssh && cd /root/.ssh

With OpenSSH

If use OpenSSH (on Mac or Linux) and you already have an OpenSSH key, then copy and paste the private key for your VM into the file `id_rsa`:

cat > id_rsa
<Paste in the key contents>
<Ctrl-D to end>

With Windows / PuTTY

If you use Windows and PuTTY, then convert the key into OpenSSH format and install it on the container. With your Putty SSH keypair on hand, exec into your container and run the following:

apt-get install -y putty-tools vim
vi keyfile.ppk 
<paste in key contents>
<save + quit (escape + :wq)>
puttygen keyfile.ppk -O private-openssh -o id_rsa

4. Copy the database dump from your VM to the temporary container

You can now securely copy the database dump over using SSH’s Secure Copy Protocol command:

mkdir /dbdumps && cd /dbdumps
scp username@<vm-hostname>:database_dump.tar .

Note the dot at the end of the second line.

5. Mount the database dump into your DB container and restore it

Now, in MedStack Control, update your DB service and add a mount to mount the database dump file that you’ve just copied in:

  • mounts and volumes: `dbdumps` volume to `/dbdumps`

Note: make sure you are loading the data into the correct database!

Exec into your DB service container and run the pg_restore command to load the database dump into your MedStack Control database:

pg_restore -d db_name /dbdumps/database_dump.tar -c -U db_user

For complete instructions on using pg_restore, see the official pg_restore documentation. For example you might want to use the `--clean` option if you want to clear existing data from your database.

6. Test & Clean up

Test your database: Use the command line tools or your app to check that the database has been successfully imported. If you have any problems, please contact us.

In order to avoid leaving behind temporary files, you can now delete them:

  • Delete the temporary database dump file from your existing VM
  • Delete the temporary container
  • Delete the `dbdumps` volume
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Submit a request for support Submit a request for support