If a PostgreSQL (using TimescaleDB extension) database that holds time series data is continuously growing in size, what is the recommended backup strategy if we do not want to continuously create backups of old data that is over 1 year old, since this old data will not be updated.

Assume that

  • There are about 5000 tables in the database, each growing at different rates
  • Full backups are taken every 3 months, with incremental backups taken every day
  • The backup files will be saved to the same machine that the database is on. After the backup process is completed, these backup files will then be moved to a remote server (not S3, etc) for storage
  • PostgreSQL 11 is running on Ubuntu 18.04

Possible Backup Strategies

  1. Create multiple databases, one for each year

    For example: databases temperature_2019, temperatures_2020, etc..., each containing 5000 tables

  2. Maintain one database and create a new table every year for each existing series of related tables.

    For example: One database temperature, with tables newyork_2019, newyork_2020, etc...

  3. Logical backup by writing a Python/Nodejs script that selects data within the time range that needs to be backed up and writes these rows to a file. Maintains 1 database, 5000 tables. A restore script will need to be written as well. What file format (CSV, HDF5, Parquet, etc.) & compression is recommended for this?

Method #1 appears to be the simplest to use with existing tools like barman, as we can select the databases that we want to create full & incremental backups.

What will be your recommendation? Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.