pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore fails to restore sequence of specific table
5+ messages / 3 participants
[nested] [flat]
* pg_restore fails to restore sequence of specific table
@ 2019-02-17 12:03 Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 19:31 ` Re: pg_restore fails to restore sequence of specific table Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Mariel Cherkassky @ 2019-02-17 12:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@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 ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore fails to restore sequence of specific table
2019-02-17 12:03 pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
@ 2019-02-17 19:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-02-17 20:29 ` Re: pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2019-02-17 19:31 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore fails to restore sequence of specific table
2019-02-17 12:03 pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 19:31 ` Re: pg_restore fails to restore sequence of specific table Laurenz Albe <laurenz.albe@cybertec.at>
@ 2019-02-17 20:29 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 21:02 ` Re: pg_restore fails to restore sequence of specific table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mariel Cherkassky @ 2019-02-17 20:29 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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 ?
On Sun, Feb 17, 2019, 9:31 PM Laurenz Albe <laurenz.albe@cybertec.at wrote:
> 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore fails to restore sequence of specific table
2019-02-17 12:03 pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 19:31 ` Re: pg_restore fails to restore sequence of specific table Laurenz Albe <laurenz.albe@cybertec.at>
2019-02-17 20:29 ` Re: pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
@ 2019-02-17 21:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-02-17 21:58 ` Re: pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2019-02-17 21:02 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pg_restore fails to restore sequence of specific table
2019-02-17 12:03 pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 19:31 ` Re: pg_restore fails to restore sequence of specific table Laurenz Albe <laurenz.albe@cybertec.at>
2019-02-17 20:29 ` Re: pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 21:02 ` Re: pg_restore fails to restore sequence of specific table Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-02-17 21:58 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Mariel Cherkassky @ 2019-02-17 21:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Thanks for the clear explanation! Tom you are the best .
On Sun, Feb 17, 2019, 11:02 PM Tom Lane <tgl@sss.pgh.pa.us wrote:
> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-02-17 21:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-17 12:03 pg_restore fails to restore sequence of specific table Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 19:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-02-17 20:29 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-17 21:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-02-17 21:58 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
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