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.
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
- Physical backups using Write-Ahead Log (WAL) archiving
- Logical backups using
How to Perform Physical Backups
To perform a physical backup of TimescaledDB, we’ll use the command
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:
Here’s an example of how the command can be used:
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:
-his the name of the host; in this case, it’s localhost.
-pwill be the port number where the server is listening.
-Urequires us to provide the user name; in this case, it’s PostgreSQL.
-Drequires us to indicate the directory that will store the backup files.
Fpindicates that the format output file will be plain.
-Pis used for progress reporting purposes.
The output should look something like the following:
C:\>pg_basebackup -h localhost -p 5432 -U PostgreSQL -D C:/temp/mydb -Fp -P
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
The following shows an example of this command and its options:
pg_dump -h localhost -U PostgreSQL -Fc -f musicdb > musicdb.bak
Let’s look at each of the options used with this command:
-Fc tells us that we will be using a custom format.
-f sends the output to the specified file.
-h indicates the name of the host; in this case, it’s the localhost.
-U indicates the username of the user.
-f option, we specify the name of the backup file; in this case, it’s
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.
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