Thread: pg_dump system catalog

Hi everyone,

How can I use pg_dump to do a backup of "system catalogs" only?

pg_dump --schema=pg_catalog

Regards,

Juliano

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

On 11/28/2016 03:40 AM, Juliano wrote:
> I'm not knew that system catalogs could be saved as schema, thanks
> Achilleas.
>
> So, I would like to get the data and the schema of the system catalogs.
> How can I include the data in a dump file with system schemas? (The
> parameter -a makes sense together the parameter --schema?)

No.

Use Achilleas's command and see what happens.



>
> Regards,
> Juliano


--
Adrian Klaver
adrian.klaver@aklaver.com

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@aklaver.com

On Mon, Nov 28, 2016 at 10:50 AM, Adrian Klaver <adrian.klaver@aklaver.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

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@aklaver.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