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

[Andreas Joseph Krogh]

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: [phone] ***@visena.com
<mailto:***@visena.com> www.visena.com <https://www.visena.com>
<https://www.visena.com>

[Guillaume Lelarge]

Hi,

Post by Andreas Joseph Krogh
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

Post by Andreas Joseph Krogh
# create tablespace apeland location

'/home/andreak/programs/postgresql/9.3.5/tablespaces/apeland';

Post by Andreas Joseph Krogh
CREATE TABLESPACE
$ du -hs data/ tablespaces/
59M data/
27M tablespaces/
Move the database to new tablespace
# alter database apeland set tablespace apeland;
ALTER DATABASE
$ du -hs data/ tablespaces/
52M data/
34M tablespaces/
# create table files(data oid);
CREATE TABLE
$ 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'));

Post by Andreas Joseph Krogh
INSERT 0 1
$ 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.

[Andreas Joseph Krogh]

PÃ¥ torsdag 09. oktober 2014 kl. 07:29:30, skrev Guillaume Lelarge <

Post by Andreas Joseph Krogh
Hi all.
 
I'm having a database, called "apeland", which at first (when created) was
 
# create tablespace apeland location

'/home/andreak/programs/postgresql/9.3.5/tablespaces/apeland';

Post by Andreas Joseph Krogh
CREATE TABLESPACE
 
$ du -hs data/ tablespaces/
59M     data/
27M     tablespaces/
 
Move the database to new tablespace
# alter database apeland set tablespace apeland;
ALTER DATABASE
 
$ du -hs data/ tablespaces/
52M     data/
34M     tablespaces/
 
 
# create table files(data oid);
CREATE TABLE
 
$ 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'));

Post by Andreas Joseph Krogh
INSERT 0 1
 
$ 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?

Post by Andreas Joseph Krogh
 

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:
[phone] ***@visena.com <mailto:***@visena.com> www.visena.com
<https://www.visena.com> <https://www.visena.com>