Thread: 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@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