Thread: PostgreSQL 9.6 Temporary files

PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).
I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

Re: PostgreSQL 9.6 Temporary files

From
Andreas Kretschmer
Date:
On 19 March 2018 17:31:20 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>Dear Friends,
>
>I am newbie to postgresql.
>I have 162 GB on my database but when I check size of all tables, I
>approximately obtain 80 GB.


Indexes?


>I also see that I have 68GB of temporary files however I only found

Where can you see that?


>2.4MB
>at postgres/data/base/pgsql_tmp.
>
>Could you tell me what are those temporary files and where are they at?
>Can
>I delete some of them?


No, never delete files in datadir!

>
>All values come from pgAdmin 4 and checked by my own SQL
>queries(postgresql-9.6).
>I already run vacuum full and there is few dead tuples.

A few dead tuples arn't a real problem.


>
>Best regards,
>Jimmy AUGUSTINE


--
2ndQuadrant - The PostgreSQL Support Company


Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
> Dear Friends,
> 
> I am newbie to postgresql.
> I have 162 GB on my database but when I check size of all tables, I 
> approximately obtain 80 GB.
> I also see that I have 68GB of temporary files however I only found 
> 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?

> 
> Could you tell me what are those temporary files and where are they at? 
> Can I delete some of them?
> 
> All values come from pgAdmin 4 and checked by my own SQL 
> queries(postgresql-9.6).

Can you show actual queries used?

> I already run vacuum full and there is few dead tuples.
> 
> Best regards,
> Jimmy AUGUSTINE


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Melvin Davidson
Date:


On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?


Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE


--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.
Note that total_size is the size of the table and all it's indexes.


SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )                   
        END as tablespace
FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:
Hi Andreas thanks for your response,

2018-03-19 17:44 GMT+01:00 Andreas Kretschmer <andreas@a-kretschmer.de>:
On 19 March 2018 17:31:20 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>Dear Friends,
>
>I am newbie to postgresql.
>I have 162 GB on my database but when I check size of all tables, I
>approximately obtain 80 GB.


Indexes?

Indexes are included into 80 GB that I mentioned.

>I also see that I have 68GB of temporary files however I only found

Where can you see that?

I used pgAdmin 4 and I see statistics on my global database.

>2.4MB
>at postgres/data/base/pgsql_tmp.
>
>Could you tell me what are those temporary files and where are they at?
>Can
>I delete some of them?


No, never delete files in datadir!

>
>All values come from pgAdmin 4 and checked by my own SQL
>queries(postgresql-9.6).
>I already run vacuum full and there is few dead tuples.

A few dead tuples arn't a real problem.


>
>Best regards,
>Jimmy AUGUSTINE


--
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:


2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?
 
I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));



Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?


I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/19/2018 10:04 AM, Jimmy Augustine wrote:
> 
> 
> 2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
> 
>         Dear Friends,
> 
>         I am newbie to postgresql.
>         I have 162 GB on my database but when I check size of all
>         tables, I approximately obtain 80 GB.
>         I also see that I have 68GB of temporary files however I only
>         found 2.4MB at postgres/data/base/pgsql_tmp.
> 
> 
>     Exactly how did you determine this?
> 
> I used this command and sum result for all database :
> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
> 
> And this for complete database :
> SELECT pg_size_pretty(pg_database_size('Database Name'));
> 

So where did the 68GB number for temporary files come from?



-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:


2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:04 AM, Jimmy Augustine wrote:


2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

        Dear Friends,

        I am newbie to postgresql.
        I have 162 GB on my database but when I check size of all
        tables, I approximately obtain 80 GB.
        I also see that I have 68GB of temporary files however I only
        found 2.4MB at postgres/data/base/pgsql_tmp.


    Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));


So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/19/2018 10:12 AM, Jimmy Augustine wrote:

> 
>              On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
> 
>                  Dear Friends,
> 
>                  I am newbie to postgresql.
>                  I have 162 GB on my database but when I check size of all
>                  tables, I approximately obtain 80 GB.
>                  I also see that I have 68GB of temporary files however
>         I only
>                  found 2.4MB at postgres/data/base/pgsql_tmp.
> 
> 
>              Exactly how did you determine this?
> 
>         I used this command and sum result for all database :
>         SELECT pg_size_pretty(pg_total_relation_size('table_name'));
> 
>         And this for complete database :
>         SELECT pg_size_pretty(pg_database_size('Database Name'));
> 
> 
>     So where did the 68GB number for temporary files come from?
> 
> I don't measure this value by my own. I was disappointed by the gap 
> between the two queries, so I checked pgAdmin 4 and I saw this value.

In what section of pgAdmin4?

Or do you know what query it used?

> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Melvin Davidson
Date:


On Mon, Mar 19, 2018 at 1:12 PM, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:


2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:04 AM, Jimmy Augustine wrote:


2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

        Dear Friends,

        I am newbie to postgresql.
        I have 162 GB on my database but when I check size of all
        tables, I approximately obtain 80 GB.
        I also see that I have 68GB of temporary files however I only
        found 2.4MB at postgres/data/base/pgsql_tmp.


    Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));


So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com


>I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

I think your problem is that SELECT pg_size_pretty(pg_total_relation_size('table_name')); only looks at the current database
but SELECT pg_size_pretty(pg_database_size('Database Name'));  looks at ALL databases.

Try this query instead to show individual database sizes.

SELECT oid,
       datname,
       pg_size_pretty(pg_database_size(datname))as size_pretty,
       pg_database_size(datname) as size,
       (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint) 
          FROM pg_database)  AS total,
       ((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname)) 
                                       FROM pg_database) ) * 100)::numeric(6,3) AS pct
  FROM pg_database
  ORDER BY datname;

--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:
Hi,

I used this command and I found the same value in total_size column.

2018-03-19 18:01 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:


On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?


Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE


--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.
Note that total_size is the size of the table and all it's indexes.


SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )                   
        END as tablespace
FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From
Melvin Davidson
Date:


On Mon, Mar 19, 2018 at 1:17 PM, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
Hi,

I used this command and I found the same value in total_size column.

2018-03-19 18:01 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:


On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
Dear Friends,

I am newbie to postgresql.
I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

Exactly how did you determine this?


Could you tell me what are those temporary files and where are they at? Can I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL queries(postgresql-9.6).

Can you show actual queries used?

I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE


--
Adrian Klaver
adrian.klaver@aklaver.com

> I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB.
>I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp.

I am not sure what your query was that deteremined table and index sizes, but try using the query instead.
Note that total_size is the size of the table and all it's indexes.


SELECT n.nspname as schema,
       c.relname as table,
       a.rolname as owner,
       c.relfilenode as filename,
       c.reltuples::bigint,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as size,
       pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )) as total_size,
       pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as size_bytes,
       pg_total_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) ) as total_size_bytes,
       CASE WHEN c.reltablespace = 0
            THEN 'pg_default'
            ELSE (SELECT t.spcname
                    FROM pg_tablespace t WHERE (t.oid = c.reltablespace) )                   
        END as tablespace
FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_authid a ON ( a.oid = c.relowner )
  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'pg_%'
    AND quote_ident(relname) NOT LIKE 'information%'
    AND quote_ident(relname) NOT LIKE 'sql_%'
    AND quote_ident(relkind) IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC
Employment by invitation only!


>I used this command and I found the same value in total_size column.

Please be specific. Exactly WHAT is the SQL query?


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:


2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:12 AM, Jimmy Augustine wrote:


             On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

                 Dear Friends,

                 I am newbie to postgresql.
                 I have 162 GB on my database but when I check size of all
                 tables, I approximately obtain 80 GB.
                 I also see that I have 68GB of temporary files however
        I only
                 found 2.4MB at postgres/data/base/pgsql_tmp.


             Exactly how did you determine this?

        I used this command and sum result for all database :
        SELECT pg_size_pretty(pg_total_relation_size('table_name'));

        And this for complete database :
        SELECT pg_size_pretty(pg_database_size('Database Name'));


    So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

In what section of pgAdmin4?
In section "Statistics" when I click on my database.

Or do you know what query it used?
I have found this but not sure
SELECT temp_files AS "Temporary files"    , temp_bytes AS "Size of temporary files"
FROM   pg_stat_database db;


    --     Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>




--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From
Andreas Kretschmer
Date:
On 19 March 2018 18:21:42 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> On 03/19/2018 10:12 AM, Jimmy Augustine wrote:
>>
>>
>>>              On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
>>>
>>>                  Dear Friends,
>>>
>>>                  I am newbie to postgresql.
>>>                  I have 162 GB on my database but when I check size
>of all
>>>                  tables, I approximately obtain 80 GB.
>>>                  I also see that I have 68GB of temporary files
>however
>>>         I only
>>>                  found 2.4MB at postgres/data/base/pgsql_tmp.
>>>
>>>
>>>              Exactly how did you determine this?
>>>
>>>         I used this command and sum result for all database :
>>>         SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>>         And this for complete database :
>>>         SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>>>     So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>>> between the two queries, so I checked pgAdmin 4 and I saw this
>value.
>>>
>>
>> In what section of pgAdmin4?
>>
>In section "Statistics" when I click on my database.
>
>Or do you know what query it used?
>>
>I have found this but not sure
>
>SELECT temp_files AS "Temporary files"
>   , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;


That's aggregated. Not current values.

Andreas


--
2ndQuadrant - The PostgreSQL Support Company


Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/19/2018 10:17 AM, Melvin Davidson wrote:
> 
> 

> *
> *I think your problem is that SELECT 
> pg_size_pretty(pg_total_relation_size('table_name')); only looks at the 
> current database


> *
> *but SELECT pg_size_pretty(pg_database_size('Database Name')); looks at 
> ALL databases.

Not according to here:

https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT
> 
> *
> *Try this query instead to show individual database sizes.
> 
> SELECT oid,
>         datname,
>         pg_size_pretty(pg_database_size(datname))as size_pretty,
>         pg_database_size(datname) as size,
>         (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
>            FROM pg_database)  AS total,
>         ((pg_database_size(datname) / (SELECT SUM( 
> pg_database_size(datname))
>                                         FROM pg_database) ) * 
> 100)::numeric(6,3) AS pct
>    FROM pg_database
>    ORDER BY datname;
> *
> 
> -- 
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:
I tried this query and my database size is equal to 162GB.

2018-03-19 18:17 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:


On Mon, Mar 19, 2018 at 1:12 PM, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:


2018-03-19 18:09 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:04 AM, Jimmy Augustine wrote:


2018-03-19 17:45 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 09:31 AM, Jimmy Augustine wrote:

        Dear Friends,

        I am newbie to postgresql.
        I have 162 GB on my database but when I check size of all
        tables, I approximately obtain 80 GB.
        I also see that I have 68GB of temporary files however I only
        found 2.4MB at postgres/data/base/pgsql_tmp.


    Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));


So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

--
Adrian Klaver
adrian.klaver@aklaver.com


>I don't measure this value by my own. I was disappointed by the gap between the two queries, so I checked pgAdmin 4 and I saw this value.

I think your problem is that SELECT pg_size_pretty(pg_total_relation_size('table_name')); only looks at the current database
but SELECT pg_size_pretty(pg_database_size('Database Name'));  looks at ALL databases.

Try this query instead to show individual database sizes.

SELECT oid,
       datname,
       pg_size_pretty(pg_database_size(datname))as size_pretty,
       pg_database_size(datname) as size,
       (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint) 
          FROM pg_database)  AS total,
       ((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname)) 
                                       FROM pg_database) ) * 100)::numeric(6,3) AS pct
  FROM pg_database
  ORDER BY datname;

--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:


2018-03-19 18:25 GMT+01:00 Andreas Kretschmer <andreas@a-kretschmer.de>:
On 19 March 2018 18:21:42 CET, Jimmy Augustine <jimmy.augustine@enyx.fr> wrote:
>2018-03-19 18:15 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> On 03/19/2018 10:12 AM, Jimmy Augustine wrote:
>>
>>
>>>              On 03/19/2018 09:31 AM, Jimmy Augustine wrote:
>>>
>>>                  Dear Friends,
>>>
>>>                  I am newbie to postgresql.
>>>                  I have 162 GB on my database but when I check size
>of all
>>>                  tables, I approximately obtain 80 GB.
>>>                  I also see that I have 68GB of temporary files
>however
>>>         I only
>>>                  found 2.4MB at postgres/data/base/pgsql_tmp.
>>>
>>>
>>>              Exactly how did you determine this?
>>>
>>>         I used this command and sum result for all database :
>>>         SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>>         And this for complete database :
>>>         SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>>>     So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>>> between the two queries, so I checked pgAdmin 4 and I saw this
>value.
>>>
>>
>> In what section of pgAdmin4?
>>
>In section "Statistics" when I click on my database.
>
>Or do you know what query it used?
>>
>I have found this but not sure
>
>SELECT temp_files AS "Temporary files"
>   , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;


That's aggregated. Not current values.
 
Ah did you know some documentation about that ?
Andreas


--
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/19/2018 10:29 AM, Jimmy Augustine wrote:
> 
> 

> 
> 
>     That's aggregated. Not current values.
> 
> Ah did you know some documentation about that ?

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

> 
>     Andreas
> 
> 
>     --
>     2ndQuadrant - The PostgreSQL Support Company
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
> I tried this query and my database size is equal to 162GB.
> 

Well you can always look in $DATA directly. The database will be under 
$DATA/base/<db oid>.

You can find the <db oid> like this:

select oid, datname from  pg_database where datname='<db name>';


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:
Thanks all for your response,

$du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:
SELECT stats.relname          AS table,      pg_size_pretty(pg_relation_size(statsio.relid))          AS table_size,      pg_size_pretty(pg_total_relation_size(statsio.relid)           - pg_relation_size(statsio.relid))          AS related_objects_size,      pg_size_pretty(pg_total_relation_size(statsio.relid))          AS total_table_size,      stats.n_live_tup          AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS statsUSING (relname)WHERE stats.schemaname = current_schemaUNION ALL
SELECT 'TOTAL'          AS table,      pg_size_pretty(sum(pg_relation_size(statsio.relid)))          AS table_size,      pg_size_pretty(sum(pg_total_relation_size(statsio.relid)           - pg_relation_size(statsio.relid)))          AS related_objects_size,      pg_size_pretty(sum(pg_total_relation_size(statsio.relid)))          AS total_table_size,      sum(stats.n_live_tup)          AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS statsUSING (relname)WHERE stats.schemaname = current_schemaORDER BY live_rows ASC;

I obtain 80GB in total_table_size (half of my database), where are missing data at? 

2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
I tried this query and my database size is equal to 162GB.


Well you can always look in $DATA directly. The database will be under $DATA/base/<db oid>.

You can find the <db oid> like this:

select oid, datname from  pg_database where datname='<db name>';


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: PostgreSQL 9.6 Temporary files

From
Adrian Klaver
Date:
On 03/20/2018 03:16 AM, Jimmy Augustine wrote:
> Thanks all for your response,
> 
> $du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:
> 
> |SELECT stats.relname AS table, 
> pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, 
> pg_size_pretty(pg_total_relation_size(statsio.relid) - 
> pg_relation_size(statsio.relid)) AS related_objects_size, 
> pg_size_pretty(pg_total_relation_size(statsio.relid)) AS 
> total_table_size, stats.n_live_tup AS live_rows FROM 
> pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS 
> stats USING (relname) WHERE stats.schemaname = current_schemaUNION ALL 
> SELECT 'TOTAL' AS table, 
> pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, 
> pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - 
> pg_relation_size(statsio.relid))) AS related_objects_size, 
> pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS 
> total_table_size, sum(stats.n_live_tup) AS live_rows FROM 
> pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS 
> stats USING (relname) WHERE stats.schemaname = current_schemaORDER BY 
> live_rows ASC;
> 
> |
> 
> |I obtain 80GB in total_table_size (half of my database), where are 
> missing data at?

First of all you are using pg_statio_user_tables which does not count 
system tables.

Second pretty sure the use of current_schema is limiting the results to 
only one schema in the database.

> |
> 
> 
> 2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
> 
>         I tried this query and my database size is equal to 162GB.
> 
> 
>     Well you can always look in $DATA directly. The database will be
>     under $DATA/base/<db oid>.
> 
>     You can find the <db oid> like this:
> 
>     select oid, datname from  pg_database where datname='<db name>';
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


Re: PostgreSQL 9.6 Temporary files

From
Jimmy Augustine
Date:


2018-03-20 15:00 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/20/2018 03:16 AM, Jimmy Augustine wrote:
Thanks all for your response,

$du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:

|SELECT stats.relname AS table, pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, pg_size_pretty(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid)) AS related_objects_size, pg_size_pretty(pg_total_relation_size(statsio.relid)) AS total_table_size, stats.n_live_tup AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schemaUNION ALL SELECT 'TOTAL' AS table, pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid))) AS related_objects_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS total_table_size, sum(stats.n_live_tup) AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schemaORDER BY live_rows ASC;

|

|I obtain 80GB in total_table_size (half of my database), where are missing data at?

First of all you are using pg_statio_user_tables which does not count system tables.

Second pretty sure the use of current_schema is limiting the results to only one schema in the database.

AHHH Thanks you I found missing data they are stored into pg_largeobject.

|


2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 03/19/2018 10:27 AM, Jimmy Augustine wrote:

        I tried this query and my database size is equal to 162GB.


    Well you can always look in $DATA directly. The database will be
    under $DATA/base/<db oid>.

    You can find the <db oid> like this:

    select oid, datname from  pg_database where datname='<db name>';


    --     Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>




--
Adrian Klaver
adrian.klaver@aklaver.com