pg_restore fails to restore sequence of specific table

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pg_restore fails to restore sequence of specific table
Date: 2019-02-17 12:03:26
Message-ID: CA+t6e1kJjaLL=o6XfVVZRdKg9GPU=ntXHug6dCgEAQa5r7vgYw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hey,
I have a backup of an entire db(9.2.5) that was taken with the next command
:
pg_dump -U postgres -Fc my_db > mydb.dump

Now, I'm trying to restore a specific table (with its sequence) but I'm
getting the following error error:
pg_restore -d mydb -U postgres -v -t specific_table mydb.dump

output :

pg_restore: connecting to database for restore
Password:
pg_restore: creating TABLE specific_table
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 241; 1259 4236973
TABLE specific_table postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"specific_table_id_seq" does not exist
Command was: CREATE TABLE specific_table(
id integer DEFAULT nextval('specific_id_seq'::regclass) NOT NULL,
effectiveid integer DEFAULT ...
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.specific_table" does not exist
Command was: ALTER TABLE public.specific_table OWNER TO postgres;

It doesn't even try to restore the sequence and thats why the table`s
creation fails. I checked if the sequence is included in the dump and
indeed it is included :
[root(at)mymachines]# pg_restore --list mydb.dump | grep specific_table
*240; 1259 4236971 SEQUENCE public specific_table _id_seq postgres*
*3524; 0 0 ACL public specific_table_id_seq postgres*
241; 1259 4236973 TABLE public specific_table postgres
3525; 0 0 ACL public specific_table postgres
3436; 0 4236973 TABLE DATA public specific_table postgres
3565; 0 0 SEQUENCE SET public specific_table_id_seq postgres
3231; 2606 4237762 CONSTRAINT public specific_table_pkey postgres

I tried to generate a plain text from the dump :
pg_restore -U postgres -t specific mydb.dump > output
*However, the output file doesnt contain the creation command of the
sequence.*

Someone can explain the weird behavior ?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2019-02-17 19:31:14 Re: pg_restore fails to restore sequence of specific table
Previous Message soumik.bhattacharjee 2019-02-15 16:22:25 RE: Table Drop Error