Обсуждение: pg_restore fails to restore sequence of specific table

Mariel Cherkassky wrote:
> 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;

You have to restore the sequence as well:

pg_restore -t specific_table -t specific_table_id_seq ...

> 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.

Well, it isn't called "specific", is it?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> I tried to restore the sequence also by specifying it in the -t flag but
> got the same error. Can you explain why it isn't appearing in the plain
> text backup but it does appear in the output of pg_restore -l ?

Hmm, worked for me, for small values of "work":

pg_restore -t specific_table -t specific_table_id_seq ...

This creates the table and the sequence, but fails to establish any
connection between them.  The trouble here is that pg_restore's "-t"
is a very low-level, literal-minded switch.  Supposing you have

create table specific_table(id serial primary key, effectiveid int);

If you do a pg_dump -Fc and then use "pg_restore -l" to see what
entries are in the dump archive, you will get something like

197; 1259 102953 TABLE public specific_table postgres
196; 1259 102951 SEQUENCE public specific_table_id_seq postgres
3023; 0 0 SEQUENCE OWNED BY public specific_table_id_seq postgres
2891; 2604 102956 DEFAULT public specific_table id postgres
3016; 0 102953 TABLE DATA public specific_table postgres
3024; 0 0 SEQUENCE SET public specific_table_id_seq postgres
2893; 2606 102958 CONSTRAINT public specific_table specific_table_pkey postgres

You need to select *all* of those items if you want to restore the
table fully, but "-t specific_table" is stupid and selects only
the "TABLE specific_table" one (and the TABLE DATA, I think).

The most reliable way to do what you want is to do
"pg_restore -l mydb.dump >mydb.list", edit mydb.list down to
just the items you want, and then use "pg_restore -L mydb.list ..."
to select those items.

Not too user-friendly, I'm afraid, especially since pg_dump's "-t"
switch is quite a bit smarter about what to dump.  But nobody's put
any work into making pg_restore's switch better.  In the meantime, maybe
you could use "pg_dump -t" if you know you just want the one table.

            regards, tom lane