Best Backup and Restore Strategy for Migrating Pg13 + TDB 2.14.0 to Pg16 + TDB 2.14.0

TimescaleDB and PostgreSQL
,
1

Hello,

I am currently running a TimescaleDB database in Docker with PostgreSQL 13 and TimescaleDB 2.14.0. I plan to migrate to PostgreSQL 16 with TimescaleDB 2.14.0 (initially) and would like to know the best strategy for performing an efficient backup and restore.

Context:

  • The database is running in Docker containers with mounted volumes for data persistence.
  • I am planning to upgrade both PostgreSQL and TimescaleDB versions.

Questions:

  1. Complete Backup: What would be the best approach for performing a full backup of the database in this scenario? Should I use pg_dumpall, pg_dump or would pg_basebackup be a better approach due to the migration between PostgreSQL versions?
  2. Restore and Version Migration: What are the recommended steps for restoring the data when migrating from PostgreSQL 13 + TimescaleDB 2.14.0 to PostgreSQL 16 + TimescaleDB 2.14.0? Is there anything specific I should consider when dealing with hypertables and compression?
  3. Preserving TimescaleDB Settings: How can I ensure that all TimescaleDB-specific settings, such as compression and retention policies, are properly restored in the new environment?
  4. Testing: Are there any recommended procedures to test the database integrity after the restore, especially regarding the functioning of hypertables and chunks?

Thank you in advance for any guidance and suggestions. I’m open to any advice on how to safely and efficiently perform this migration.

Thanks!

2

Hello Elias, welcome!

For upgrading, consider the official docs here:

Let’s talk about what both upgrades mean:

  1. Postgresql upgrade - has its own tooling and metadata upgrades for the catalogs
  2. Timescaledb extension - has its own metadata upgrades

The data and config is already there, so, about the number 3, it should remain the same.

  1. Testing - You can query your tables/hypertables and go to timescaledb_information.job_errors
3

hello @jonatasdp

thanks for the feedback!

1 Like