Re: pg_dump system catalog

On 28/11/2016 19:21, Melvin Davidson wrote:
> On Mon, Nov 28, 2016 at 10:50 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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 <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/creating-cluster.html>
> https://www.postgresql.org/docs/9.6/static/app-initdb.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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
> *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