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

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?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Hi,

Le 9 oct. 2014 01:31, "Andreas Joseph Krogh" <andreas@visena.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.

På torsdag 09. oktober 2014 kl. 07:29:30, skrev Guillaume Lelarge <guillaume@lelarge.info>:

Hi,

Le 9 oct. 2014 01:31, "Andreas Joseph Krogh" <andreas@visena.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.


Thanks. After inserting lots of more LOs I see that $PGDATA doesn't grow at that rate anymore.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment