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.
- 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
Create multiple databases, one for each year
For example: databases
temperatures_2020, etc..., each containing 5000 tables
Maintain one database and create a new table every year for each existing series of related tables.
For example: One database
temperature, with tables
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!