Same as this previous issue. Backup failing with ts-dump. Sometimes the backup succeeds, but most often it fails, especially when it's scheduled to run early in the morning.
I was previously using PostgreSQL v11 and TimescaleDB v1.6.0 and it was failing there as well, so I upgraded to the latest versions, and it's still failing.
I have about 4-6 continuously aggregated materialized views, and various scheduled database operations running every 15 minutes or so with crontab.
My database backup file size is 2.4 GB when compressed as a tar.gz file, and the backup process with ts-dump usually takes around 25 minutes, whether it succeeds or fails.
Here's the main error message:
pg_dump: error: could not obtain lock on relation "_timescaledb_catalog.continuous_aggs_invalidation_threshold"
This usually means that someone requested an ACCESS EXCLUSIVE lock on the table after the pg_dump parent process had gotten the initial ACCESS SHARE lock on the table.
pg_dump: error: a worker process died unexpectedly
pg_dump run failed with: cmd.Run() failed with 'exit status 1'
Here's the full error message showing the usual "circular foreign-key constraints" warnings (which are apparently harmless):
2021/06/21 07:00:01 Jobs: have stopped, continuing
pg_dump version: pg_dump (PostgreSQL) 13.3
2021/06/21 07:00:14 pg_dump: warning: there are circular foreign-key constraints on this table:
2021/06/21 07:00:14 pg_dump: hypertable
2021/06/21 07:00:14 pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
2021/06/21 07:00:14 pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
2021/06/21 07:00:14 pg_dump: warning: there are circular foreign-key constraints on this table:
2021/06/21 07:00:14 pg_dump: chunk
2021/06/21 07:00:14 pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
2021/06/21 07:00:14 pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
2021/06/21 07:22:26 pg_dump: error: could not obtain lock on relation "_timescaledb_catalog.continuous_aggs_invalidation_threshold"
2021/06/21 07:22:26 This usually means that someone requested an ACCESS EXCLUSIVE lock on the table after the pg_dump parent process had gotten the initial ACCESS SHARE lock on the table.
2021/06/21 07:22:26 pg_dump: error: a worker process died unexpectedly
2021/06/21 07:22:26 pg_dump run failed with: cmd.Run() failed with 'exit status 1'
I'm now running the latest versions of both TimescaleDB and PostgreSQL:
SELECT version();
PostgreSQL 13.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
SELECT default_version, installed_version
FROM pg_available_extensions
WHERE name = 'timescaledb';
2.3.0, 2.3.0
Here's how I installed the latest ts-dump binaries with Docker:
ARG VERSION
# pg_dump and pg_restore come from this base image,
# based on the TimescaleDB version one wants
FROM postgres:${VERSION}-alpine
# Copy the Golang binaries from this official image,
# rather than installing manually
COPY --from=golang:rc-alpine /usr/local/go/ /usr/local/go/
# Configure Go
ENV GOROOT /usr/local/go
ENV GOPATH /go
ENV PATH /usr/local/go/bin:$PATH
RUN mkdir -p ${GOPATH}/src ${GOPATH}/bin
# Download the Linux binaries manually
RUN cd /usr/local/go/bin && \
wget https://github.com/timescale/timescaledb-backup/releases/download/0.1.1/ts-dump_0.1.1_Linux_x86_64 && \
wget https://github.com/timescale/timescaledb-backup/releases/download/0.1.1/ts-restore_0.1.1_Linux_x86_64 && \
# Check the checksums for the downloaded binaries
wget https://github.com/timescale/timescaledb-backup/releases/download/0.1.1/checksums.txt && \
cat checksums.txt && \
sha256sum ts-dump_0.1.1_Linux_x86_64 && \
sha256sum ts-restore_0.1.1_Linux_x86_64 && \
# Rename the downloaded binaries to be the default binaries with generic names
mv ts-dump_0.1.1_Linux_x86_64 ts-dump && \
mv ts-restore_0.1.1_Linux_x86_64 ts-restore && \
# Make the downloaded binaries executable
chmod +x ts-dump ts-restore
Same as this previous issue. Backup failing with
ts-dump
. Sometimes the backup succeeds, but most often it fails, especially when it's scheduled to run early in the morning.I was previously using PostgreSQL v11 and TimescaleDB v1.6.0 and it was failing there as well, so I upgraded to the latest versions, and it's still failing.
I have about 4-6 continuously aggregated materialized views, and various scheduled database operations running every 15 minutes or so with crontab.
My database backup file size is 2.4 GB when compressed as a tar.gz file, and the backup process with
ts-dump
usually takes around 25 minutes, whether it succeeds or fails.Here's the main error message:
Here's the full error message showing the usual "circular foreign-key constraints" warnings (which are apparently harmless):
I'm now running the latest versions of both TimescaleDB and PostgreSQL:
Here's how I installed the latest
ts-dump
binaries with Docker: