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@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 <mailto:guillaume@lelarge.info>>: Hi,

Le 9 oct. 2014 01:31, "Andreas Joseph Krogh" <andreas@visena.com
<mailto: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 andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com&gt; <https://www.visena.com&gt;