Re: pg_restore fails to restore sequence of specific table

Thanks for the clear explanation! Tom you are the best .

On Sun, Feb 17, 2019, 11:02 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:

> Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)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
>