Re: [GENERAL] pg_dump system catalog

[Achilleas Mantzios]

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

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

Re: [GENERAL] pg_dump system catalog

[Melvin Davidson]

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

Re: [GENERAL] pg_dump system catalog

[Adrian Klaver]

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

Re: [GENERAL] pg_dump system catalog

[Melvin Davidson]

On Mon, Nov 28, 2016 at 9:33 AM, Adrian Klaver 
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.kla...@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
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.

Re: [GENERAL] pg_dump system catalog

[Adrian Klaver]

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

Re: [GENERAL] pg_dump system catalog

[Juliano]

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

Re: [GENERAL] pg_dump system catalog

[Achilleas Mantzios]

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