pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: pg_restore fails to restore sequence of specific table
Date: Sun, 17 Feb 2019 16:02:04 -0500
Message-ID: <3771.1550437324@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+t6e1mo880LOpFTh_YtF8U__sO6fA-1SLq8fXZuEaspW0o7DQ@mail.gmail.com>
References: <CA+t6e1kJjaLL=o6XfVVZRdKg9GPU=ntXHug6dCgEAQa5r7vgYw@mail.gmail.com>
<41dbc1307b3ebe4c9251194f5f380ca44cc8dc66.camel@cybertec.at>
<CA+t6e1mo880LOpFTh_YtF8U__sO6fA-1SLq8fXZuEaspW0o7DQ@mail.gmail.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
view thread (5+ messages) latest in thread
Message-ID: <3771.1550437324@sss.pgh.pa.us> Permalink: ../3771.1550437324@sss.pgh.pa.us/ Also on: postgresql.org/message-id/3771.1550437324@sss.pgh.pa.us ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-admin@postgresql.org Cc: tgl@sss.pgh.pa.us, mariel.cherkassky@gmail.com, laurenz.albe@cybertec.at, pgsql-admin@lists.postgresql.org Subject: Re: pg_restore fails to restore sequence of specific table In-Reply-To: <3771.1550437324@sss.pgh.pa.us> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox