index numbers in pg_restore list
Dear all,
I was hoping for some quick guidance on the structure of the pg_restore
list.
We have a database which we are restoring each day for a data warehouse.
For efficiency, we are not restoring a number of tables and functions, just
the tables, indexes and data.
(the database is being dumped on a postgresql 10.13 database, and restored
on a 12.4)
To do this we had been using a template list file, with the items we wanted
(around 1900 of them) However, after a week or so it started failing with a
message the ID 9601 was missing. on checking this was:
9601; 0 19580 TABLE DATA backoffice animalid angus
when we ran the list option on the dump, the above row was present, but the
index number is now 9602 (this was previously something else).
The text in the manual pages notes:
the numbers at the start of lines refer to the internal archive ID
assigned to each item.
we were under the impression that this number was like an oid? I guess then
my question is:
" Is the index number ( the archive ID) assigned at the time of creation
of the archive and not otherwise related to the item in the
original database?"
If this is mutable, I presume we need to write a script to grep the lines
we need out of the list of the current dump, not re-use the same list file?
cheers
Ben
--
[image: Ausvet Logo] <https://www.ausvet.com.au/>
Dr Ben Madin
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
ben@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia
On 8/14/20 9:16 AM, Ben Madin wrote:
Dear all,
I was hoping for some quick guidance on the structure of the pg_restore
list.We have a database which we are restoring each day for a data warehouse.
For efficiency, we are not restoring a number of tables and functions,
just the tables, indexes and data.(the database is being dumped on a postgresql 10.13 database, and
restored on a 12.4)To do this we had been using a template list file, with the items we
wanted (around 1900 of them) However, after a week or so it started
failing with a message the ID 9601 was missing. on checking this was:9601; 0 19580 TABLE DATA backoffice animalid angus
when we ran the list option on the dump, the above row was present, but
the index number is now 9602 (this was previously something else).The text in the manual pages notes:
the numbers at the start of lines refer to the internal archive ID
assigned to each item.we were under the impression that this number was like an oid? I guess
then my question is:" Is the index number ( the archive ID) assigned at the time of
creation of the archive and not otherwise related to the item in the
original database?"
From pg_dump.c:
/*
* Create archive TOC entries for all the objects to be dumped,
in a safe
* order.
*/
/* First the special ENCODING, STDSTRINGS, and SEARCHPATH
entries. */
dumpEncoding(fout);
dumpStdStrings(fout);
dumpSearchPath(fout);
/* The database items are always next, unless we don't want
them at all */
if (dopt.outputCreateDB)
dumpDatabase(fout);
/* Now the rearrangeable objects. */
for (i = 0; i < numObjs; i++)
dumpDumpableObject(fout, dobjs[i]);
If this is mutable, I presume we need to write a script to grep the
lines we need out of the list of the current dump, not re-use the same
list file?cheers
Ben
--
Ausvet Logo <https://www.ausvet.com.au/>
Dr Ben Madin
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <tel:+61448887220>
E-mail:
ben@ausvet.com.au <mailto:ben@ausvet.com.au>
Website:
www.ausvet.com.au <https://www.ausvet.com.au/>
Skype:benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia
--
Adrian Klaver
adrian.klaver@aklaver.com
Ben Madin <ben@ausvet.com.au> writes:
" Is the index number ( the archive ID) assigned at the time of creation
of the archive and not otherwise related to the item in the
original database?"
Yes, it's just assigned internally during pg_dump. I think the numbers
would hold still as long as you make no DDL changes in the database, but
as soon as you do, all bets are off.
regards, tom lane
Thanks Tom and Adrian,
The clarity is helpful - We'll run up a solution to specifically choose the
elements.
cheers
Ben
On Sat, 15 Aug 2020 at 00:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ben Madin <ben@ausvet.com.au> writes:
" Is the index number ( the archive ID) assigned at the time of creation
of the archive and not otherwise related to the item in the
original database?"Yes, it's just assigned internally during pg_dump. I think the numbers
would hold still as long as you make no DDL changes in the database, but
as soon as you do, all bets are off.regards, tom lane
--
[image: Ausvet Logo] <https://www.ausvet.com.au/>
Dr Ben Madin
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
ben@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia