CODEWITHSUNDEEP
postgresql
Chris
Chris

Reputation: 159

postgreSQL doesnt use temp_tablespace

I am currently running into space problems when running some of my queries, postgreSQL runs out of temporary space. the queries generate lists of several GB (and they are already split pieces)

for the time being i'd like to solve it by just moving the temp_tablespace to another folder, before moving the whole DB as next step.

etc/postgresql.conf

temp_tablespaces = '/var/lib/postgresql/9.2/importdb02/tmp_consDB'

/var/lib/postgresql/9.2/importdb02/

drwxr-xr-x  2 postgres postgres 4096 May 22 12:08 tmp_consDB

postgresql:postgresql is owner of the folder, i restarted, yet still postgreSQL is not using this tablespace for queries.

when i run the queries and monitor the available space via DF, it is still using the main tablespace for those temp tables, i can also see that the folder /var/lib/.../base/pgsql_tmp is increasing while the temp_tablespace folder stays empty.

the queries all look like that

COPY(
SELECT  column_a,
        column_b,
        (around 8 group by columns)
        sum(tosumup),
        sum(tosumup)
        (up to 100 sum columns)
FROM    master_table
WHERE   id>=25000 AND id<50000 (running in batches)
GROUP BY
        group by colums
) TO stdout WITH DELIMITER ';' CSV HEADER;

what i am i missing here?

my alternative solution would be just to link /base/pg_tmp to another drive, but that's the hacking version.

I did this after the answer from Clodoaldo Neto

as postgres

CREATE TABLESPACE tempimpdb02 LOCATION '/var/lib/postgresql/9.2/importdb02/tmp_consDB';

/var/etc

temp_tablespaces = 'tempimpdb02'

restarted, checking everything 

postgres=# SELECT * FROM pg_tablespace;
   spcname   | spcowner | spcacl | spcoptions 
-------------+----------+--------+------------
 pg_default  |       10 |        | 
 pg_global   |       10 |        | 
 tempimpdb02 |       10 |        | 
(3 rows)


postgres=# show temp_tablespaces;
 temp_tablespaces 
------------------
 tempimpdb02
(1 row)

there is now a folder in /var/lib/postgresql/9.2/importdb02/tmp_consDB (PG_9.2....).

yet still the queries use /base/psql_temp for the temp data and the folder in 'tempimpdb02' is empty.

Upvotes: 2

Views: 9407

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125464

The value passed to temp_tablespaces is a list of names of tablespaces. A tablespace must be created first as superuser:

CREATE TABLESPACE tempimpdb02 LOCATION '/var/lib/postgresql/9.2/importdb02/tmp_consDB';

Now create a table in that tablespace:

create table t tablespace tempimpdb02 as 
    SELECT  column_a,
            column_b,
            (around 8 group by columns)
            sum(tosumup),
            sum(tosumup)
            (up to 100 sum columns)
    FROM    master_table
    WHERE   id>=25000 AND id<50000 (running in batches)
    GROUP BY group by colums

It is necessary to have the create privilege in that tablespace.

Upvotes: 6

Related Questions