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

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

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

andreas@xxxxxxxxxx

www.visena.com