pg_dump system catalog

Started by Julianoover 9 years ago8 messagesgeneral

Jump to latest

Juliano

jplinux@protonmail.com

over 9 years ago

Hi everyone,

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

Regards,
Juliano

Achilleas Mantzios

achill@matrix.gatewaynet.com

over 9 years ago

In reply to: Juliano (#1)

Re: pg_dump system catalog

On 25/11/2016 13:11, Juliano wrote:

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

Juliano

jplinux@protonmail.com

over 9 years ago

In reply to: Achilleas Mantzios (#2)

Re: pg_dump system catalog

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?)

Regards,
Juliano

Import Notes

Resolved by subject fallback

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Juliano (#3)

Re: pg_dump system catalog

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson

melvin6925@gmail.com

over 9 years ago

In reply to: Adrian Klaver (#4)

Re: pg_dump system catalog

On Mon, Nov 28, 2016 at 9:33 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

*To clarify, you cannot dump the pg_catalog schema. It is the main control
of how all other objects are stored in the cluster. There is no point in
dumping it and all it's tables and views are already
clearlydocumented.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.klaver@aklaver.com

over 9 years ago

In reply to: Melvin Davidson (#5)

Re: pg_dump system catalog

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson

melvin6925@gmail.com

over 9 years ago

In reply to: Adrian Klaver (#6)

Re: pg_dump system catalog

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.*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Achilleas Mantzios

achill@matrix.gatewaynet.com

over 9 years ago

In reply to: Melvin Davidson (#7)

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