Re: Importing large object (with lo_import) to table in separate tablespaces takes up lots of space in $PGDATA

Hi,

Le 9 oct. 2014 01:31, "Andreas Joseph Krogh" <andreas(at)visena(dot)com> a écrit :
>
> Hi all.
>
> I'm having a database, called "apeland", which at first (when created)
was in the default-tablespace (in $PGDATA), then I moved it with the
commands:
>
> # create tablespace apeland location
'/home/andreak/programs/postgresql/9.3.5/tablespaces/apeland';
> CREATE TABLESPACE
>
> Check space-usage before moving:
> $ du -hs data/ tablespaces/
> 59M data/
> 27M tablespaces/
>
> Move the database to new tablespace
> # alter database apeland set tablespace apeland;
> ALTER DATABASE
>
> Check space-usage after moving:
> $ du -hs data/ tablespaces/
> 52M data/
> 34M tablespaces/
>
>
> Then I created this table:
> # create table files(data oid);
> CREATE TABLE
>
> Insert this file:
> $ du -hs origo-war-01-14-01.20.war
> 130M origo-war-01-14-01.20.war
>
> # insert into files(data)
values(lo_import('/home/andreak/data/origo-war-01-14-01.20.war'));
> INSERT 0 1
>
> Check space-usage:
> $ du -hs data/ tablespaces/
> 164M data/
> 208M tablespaces/
>
> Now - why is so much extra space used in $PGDATA? Is there a way to
reclame it? Was the "apeland"-db moved completely or is there lots
dangeling left in PGDATA?
>

Everything is moved if you used ALTER DATABASE.

I'd guess what you have in the data folder are mostly WAL files. You should
use du on data/base to get size from relations' files, and not everything
else including configuration files.