Re: index numbers in pg_restore list

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(at)ausvet(dot)com(dot)au <mailto:ben(at)ausvet(dot)com(dot)au>
> Website:
> www.ausvet.com.au <https://www.ausvet.com.au/>
> Skype:benmadin
> Address:
> 5 Shuffrey Street
> Fremantle, WA 6160
> Australia

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com