pg_dump/pg_restore does not work without more manual steps

[dhyun-obsec]

Relevant system information:

• OS: [e.g. Ubuntu 16.04, Windows 10 x64, etc]
  Ubuntu 18.04

• PostgreSQL version (output of postgres --version): [e.g. 10.5, 9.6.10]
  10.10

• TimescaleDB version (output of \dx in psql): [e.g. 1.0.0]
  1.4.1

• Installation method: [e.g., "using Docker", "apt install", "source"]
  Source

Describe the bug
In following the steps outlined in this document (https://docs.timescale.com/latest/using-timescaledb/backup), the pg_restore throws the following error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3025; 0 17963 TABLE DATA metadata postgres
pg_restore: [archiver (db)] COPY failed for table "metadata": ERROR:  duplicate key value violates unique constraint "metadata_pkey"
DETAIL:  Key (key)=(exported_uuid) already exists.

To Reproduce
Steps to reproduce the behavior:

1. Create a test database, a schema, a single hypertable with one row using the following queries:

CREATE DATABASE test;
\c test
CREATE EXTENSION timescaledb;
CREATE SCHEMA org_test;
CREATE TABLE org_test.test_timescale_table (
    id SERIAL,
    timestamp timestamptz,
    notes varchar,
    PRIMARY KEY (id, timestamp)
);
CREATE INDEX test_timescale_table_timestamp_idx ON org_test.test_timescale_table(timestamp);
SET SEARCH_PATH = org_test, public;
SELECT create_hypertable('test_timescale_table', 'timestamp');
INSERT INTO org_test.test_timescale_table(timestamp, notes) VALUES (now(), 'one');

2. Run pg_dump to create a dump file using the following command:

pg_dump -Fc -f test.bak test

3. Drop the test database and run pg_restore using the following command:

CREATE DATABASE test;
\c test
CREATE EXTENSION timescaledb;
SELECT timescaledb_pre_restore();
\! pg_restore -Fc -d test test.bak
SELECT timescaledb_post_restore();
4. See error

**Expected behavior**
The `test` database should have been restored without warnings or errors.

**Actual behavior**
The error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3025; 0 17963 TABLE DATA metadata postgres
pg_restore: [archiver (db)] COPY failed for table "metadata": ERROR: duplicate key value violates unique constraint "metadata_pkey"
DETAIL: Key (key)=(exported_uuid) already exists.

Actually, you can get one of a few errors depending on how you run the `pg_restore`.

**Additional context**
One caveat - if you create the schema `org_test` during the manual pre-restore step, and run `pg_restore` using the `-n` option to only load the schema, then it works fine.  I also ran the test without using the `org_test` schema, and then there is no way to get a restore to work.

[mfreed]

Hi @dhyun-obsec If you could please fill out the github issue as requested, so we can provide help? Not enough information to actually understand your problem.

Also, please see these instructions if you haven't already:

https://docs.timescale.com/latest/using-timescaledb/backup

[dhyun-obsec]

Sorry, I had used the template but for some reason I must have been on a different browser tab when I submitted. I updated the original post with all of the information.

[dhyun-obsec]

One other thing to note: although the restore works with specifying the -n parameter to specify the schema, the single row of the hypertable is not restored so I can't seem to figure out a way to backup and restore with pg_dump and pg_restore.

[ereyes01]

I'm having the same problem trying to run pg_dump/pg_restore. I googled for the first error I saw from pg_restore and this was the only hit on google :-)

I also asked about this on the TimescaleDB Slack, and there's an ongoing thread there now.

Here's my system details:

• OS: Ubuntu 18.04
• PostgreSQL version
  • Source DB (pg_dump target): 9.6.13
  • Destination DB (pg_restore target) 11.5
• TimescaleDB version (output of \dx in psql): 1.4.1 (on both source/destination)
• Installation method: apt install

So what I'm trying to do is move my data from one timescaledb instance running postgres 9.6.13 to another running postgres 11.5 (both using timescaledb extension 1.4.1). I got the error shown in the original post, followed by a bunch related to check constraints in my data. None of my tables now any of their rows were restored, save for a single (non-hyper) table.

I'm not sure if the different postgres versions matter here, since the original post doesn't have this complication, yet still encounters the problem.

Let me know if there's any further information you need from me, or if there's anything else you need me to try. Thanks...

[dhyun-obsec]

@mfreed This is still listed as need-more-info - not sure why. Do you know if this is the behavior that we should be seeing? Thanks.

[miohtama]

I encountered this error now when trying to restore a pg_dump from PostgreSQL with TimescaleDb extension.

Looks like it could be somehow related to TimescaleDb. It was working fine before, but after adding some more continuous aggregate views and changing column structure the error started appearing.

pg_restore: from TOC entry 3514; 0 17146 TABLE DATA metadata postgres
pg_restore: error: COPY failed for table "metadata": ERROR:  duplicate key value violates unique constraint "metadata_pkey"

Versions

Dump host: TimescaleDB docker, PostgreSQL 12.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit, Timescaledb v2.3.1
Restore host: TimescaleDB docker, PostgreSQL 12.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit, TimescaleDb v2.3.1

Commands

My dumping command:

pg_dump -U postgres --format custom --verbose --create dex_ohlcv > dump.bin

My restore command:

pg_restore --verbose -U postgres -d $DBNAME --exit-on-error --format=custom dump.bin

I will investigate further, but probably going to take some time.

[nwittwer]

Also getting the same error as @miohtama, using Docker.

Same error with both timescale/timescaledb:latest-pg12 and timescale/timescaledb:latest-pg13 images. Primarily have been using the latest-pg12 image with: PostgreSQL 12.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit

Errors when running pg_dump:

pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   chunk
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

After using pg_restore

pg_restore: processing data for table "_timescaledb_catalog.hypertable_data_node"
pg_restore: processing data for table "_timescaledb_catalog.metadata"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3175; 0 16599 TABLE DATA metadata postgres
pg_restore: error: COPY failed for table "metadata": ERROR:  duplicate key value violates unique constraint "metadata_pkey"
DETAIL:  Key (key)=(exported_uuid) already exists.
CONTEXT:  COPY metadata, line 1
pg_restore: processing data for table "_timescaledb_catalog.remote_txn"

2021-07-21 17:55:00.499 UTC [607] ERROR: duplicate key value violates unique constraint "metadata_pkey"
2021-07-21 17:55:00.499 UTC [607] DETAIL: Key (key)=(exported_uuid) already exists.
2021-07-21 17:55:00.499 UTC [607] CONTEXT: COPY metadata, line 1
2021-07-21 17:55:00.499 UTC [607] STATEMENT: COPY _timescaledb_catalog.metadata (key, value, include_in_telemetry) FROM stdin;

[miohtama]

@nwittwer after making sure that the pre- and post pg_restore steps were run, the restore started working. I guess it worked just by luck before that or the steps have become mandatory recently.

See timescale_pre_restore() here in documentation: https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/#backup-entiredb

For the reference, here is my shell script to restore any dump in TimescaleDB docker:

#!/bin/sh

set -e
set -x

DBNAME="my_little_timescaledb"
CONTAINER_NAME=timescaledb
DUMP_SOURCE=dumps/source_dump.bin
DUMP_AT_DOCKER=`basename $DUMP_SOURCE`

docker exec $CONTAINER_NAME dropdb --if-exists -U postgres $DBNAME
docker exec $CONTAINER_NAME createdb -U postgres $DBNAME
docker cp $DUMP_SOURCE $CONTAINER_NAME:./$DUMP_AT_DOCKER

# Needed on Github CI as pg_check does not tell the real database up to date state
# pg_restore: error: connection to database "..." failed: FATAL:  the database system is starting up
sleep 4

docker exec $CONTAINER_NAME psql -U postgres -d $DBNAME -c "SELECT timescaledb_pre_restore();"
docker exec $CONTAINER_NAME pg_restore --verbose -U postgres -d $DBNAME --exit-on-error --format=custom $DUMP_AT_DOCKER
docker exec $CONTAINER_NAME psql -U postgres -d $DBNAME -c "SELECT timescaledb_post_restore();"