TimescaleDB Backup

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

Your company’s information is one of its most valuable assets, so it’s important to have a recovery plan to prevent data loss in the event of a hardware failure or disaster. Backing up your database is a key part of disaster recovery and an essential task for database administrators. In this article, we’ll explain a few different ways you can back up your database in TimescaleDB, providing step-by-step instructions for the entire TimescaleDB backup process.

Prerequisites

In order to follow along with the steps outlined in this article, you’ll need to install and configure the following:

You should also have some basic knowledge of PostgreSQL to get the most out of this tutorial.

TimescaleDB Backup and Restore Overview

In TimescaleDB, the backup process makes use of some of PostgreSQL’s reliable functionality. There are a few different ways to back up hypertables in TimescaleDB using PostgreSQL commands:

  • Physical backups using pg_basebackup
  • Physical backups using Write-Ahead Log (WAL) archiving
  • Logical backups using pg_dump and pg_restore.

How to Perform Physical Backups

To perform a physical backup of TimescaledDB, we’ll use the command pg_basebackup. The pg_basebackup command creates a binary copy of a Postgres database, and it can be done without affecting other users of the database.

The basic syntax for the pg_basebackup command is as follows:

1
pg_basebackup <options>

Here’s an example of how the command can be used:

1
pg_basebackup -h localhost -p 5432 -U PostgreSQL -D C:/temp/mydb -Fp -P

We can see that several options can be used with this command. Let’s take a closer look at each one and see how they work:

  1. -h is the name of the host; in this case, it’s localhost.
  2. -p will be the port number where the server is listening.
  3. -U requires us to provide the user name; in this case, it’s PostgreSQL.
  4. -D requires us to indicate the directory that will store the backup files.
  5. Fp indicates that the format output file will be plain.
  6. -P is used for progress reporting purposes.

The output should look something like the following:

1
2
3
C:\>pg_basebackup -h localhost -p 5432 -U PostgreSQL -D C:/temp/mydb -Fp -P
Password:
68844/68844 kB (100%), 1/1 tablespace

The above image shows the generated files during the backup process.

Perform Entire Database Backup

We can perform an entire database backup using the pg_dump command. This utility allows us to perform backups concurrently because pg_dump does not halt other users that are accessing the database; therefore, you can safely perform a backup without disrupting server performance in a production environment.

Shown below is the basic syntax for pg_dump.

1
pg_dump <option<

The following shows an example of this command and its options:

1
pg_dump -h localhost -U PostgreSQL -Fc -f musicdb > musicdb.bak

Let’s look at each of the options used with this command:

a. -Fc tells us that we will be using a custom format. b. -f sends the output to the specified file. c. -h indicates the name of the host; in this case, it’s the localhost. d. -U indicates the username of the user.

After the -f option, we specify the name of the backup file; in this case, it’s musicdb.bak.

We then indicate the name of the database that we want to back up.

NOTE Make sure that the terminal was granted administrator rights before attempting this backup, to avoid permission issues.

Conclusion

Backing up your TimescaleDB database is an important part of good database administration. In the event of a disaster, hardware failure or malicious activity, a regularly scheduled backup system will prevent data loss. In this article, we showed you a few different ways that you can perform a TimescaleDB backup, and we explained the various options available with each backup command. With our examples and instructions, you’ll be able to do backups on your own TimescaleDB database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.