Re: pg_dump system catalog

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Tue, 29 Nov 2016 09:48:40 +0200



    On 28/11/2016 19:21, Melvin Davidson
      wrote:

    
          On Mon, Nov 28, 2016 at 10:50 AM,
            Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
            wrote:

            On 11/28/2016 07:44 AM,
              Melvin Davidson wrote:

              
                *To clarify, you cannot dump the pg_catalog schema. It
                is the main

                  control of how all other objects are

                
              Actually you can. I would not of thought so, but tried
              Achilleas's suggestion and it worked. Example:

              
              --

              -- Name: pg_aggregate; Type: TABLE; Schema: pg_catalog;
              Owner: postgres

              --

              
              CREATE TABLE pg_aggregate (

                  aggfnoid regproc NOT NULL,

                  aggkind "char" NOT NULL,

                  aggnumdirectargs smallint NOT NULL,

                  aggtransfn regproc NOT NULL,

                  aggfinalfn regproc NOT NULL,

                  aggmtransfn regproc NOT NULL,

                  aggminvtransfn regproc NOT NULL,

                  aggmfinalfn regproc NOT NULL,

                  aggfinalextra boolean NOT NULL,

                  aggmfinalextra boolean NOT NULL,

                  aggsortop oid NOT NULL,

                  aggtranstype oid NOT NULL,

                  aggtransspace integer NOT NULL,

                  aggmtranstype oid NOT NULL,

                  aggmtransspace integer NOT NULL,

                  agginitval text,

                  aggminitval text

              );

              
              COPY pg_aggregate (aggfnoid ....

              
              and you do get errors:

              
              pg_dump: WARNING: typtype of data type "any" appears to be
              invalid

              pg_dump: WARNING: typtype of data type "anyarray" appears
              to be invalid

              pg_dump: WARNING: typtype of data type "anyelement"
              appears to be invalid

              pg_dump: WARNING: typtype of data type "anyenum" appears
              to be invalid

              pg_dump: WARNING: typtype of data type "anynonarray"
              appears to be invalid

              pg_dump: WARNING: typtype of data type "anyrange" appears
              to be invalid

              pg_dump: WARNING: typtype of data type "cstring" appears
              to be invalid

              
              ....

              
              Still not sure why you would want to, but you can.

              
                  stored in the cluster. There is no point in dumping it
                  and all it's

                  tables and views are already clearly

                  documented.

                  https://www.postgresql.org/docs/9.6/static/catalogs.html

                  
                  pg_catalog itself is generated with the initdb command
                  when a new

                  postgresql cluster is generated.

                  https://www.postgresql.org/docs/9.6/static/creating-cluster.html

                https://www.postgresql.org/docs/9.6/static/app-initdb.html*

                
                --

                *Melvin Davidson*

                  I reserve the right to fantasize.  Whether or not you

                  wish to share my fantasy is entirely up to you.

                
                  -- 

                  Adrian Klaver

                  adrian.klaver@xxxxxxxxxxx

                
          Hmmm, well you learn something new every day. Albeit,
              although you can view the dump file, I'm pretty sure you
              cannot restore it to a database because,

            
          by definition, those tables would already exist in the
            database.

        
    That's right I was kind of suspicious about the OP's intentions. I
    agree, no user should attempt to restore anything from this dump,
    unless (s)he is a pgsql hacker and knows exactly what he's doing.

    
          -- 

          
            Melvin
                    Davidson

              I reserve the right to
                  fantasize.  Whether or not you 

                wish to share my
                  fantasy is entirely up to you. 

            
    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt