3

Using PostgreSQL 9.3. I had a long-running query updating around 8M rows. Something obviously went wrong. 2 days later I got a low disk space alert. I stopped the query and this is what I saw.

root@server:/var/lib/postgresql/9.3/main# du -BM * | sort -n
1M  global
1M  pg_notify
1M  pg_serial
1M  pg_snapshots
1M  pg_stat
1M  pg_stat_tmp
1M  pg_tblspc
1M  pg_twophase
1M  PG_VERSION
1M  pg_xlog/archive_status
1M  mydb.opts
1M  mydb.pid
3M  pg_subtrans
7M  base/1
7M  base/12030
7M  base/12035
11M base/22029472
21M pg_clog
72M pg_multixact/offsets
315M    pg_log
444M    pg_multixact/members
516M    pg_multixact
625M    pg_xlog
3493M   base/pgsql_tmp
61851M  base/22053373
65372M  base

Note this: 61851M base/22053373

Since my actual data is in tablespaces stored in different volumes, I presume this is some temporary transaction stuff that has piled up.

There are some online posts about similar problems, but no canonical solution that I found. In general, the advice is "run VACUUM FULL" and sometimes the accumulated cruft goes away. That's what I'm doing now, but it's taking its time and I'm afraid I might fill up the remaining disk space (3GB) and bring everything crashing down.

Anyone had experience with this? What is stored here? Is there a safe way to quickly free up this space? Or at least move it elsewhere (I have plenty of space on my tablespace disks).

1 Answer 1

3

I figured it out. It's my mistake. This is a new database a colleague had created in the default tablespace instead of one of our custom ones.

For those experiencing similar issues, here are a few things I've learned while investigating. base/22053373 in my case is oid of a database. You can see which db it is like this:

SELECT oid, * FROM pg_database

Each file inside is named after oid of a pg type (table or otherwise). The largest files are probably from your largest tables. Connect to the database and select from pg_class collection to find out which.

Right now I'm moving db to a custom tablespace using

ALTER DATABASE mydb SET TABLESPACE mytablespace;

I'm pretty sure this will solve my issue.

2
  • Did it solve your problem? If so, why changing tablespace should solve the problem?
    – EsseTi
    Feb 11, 2016 at 10:37
  • Yes it did. Changing the tablespace effectively copied the data to a different hdd where there was enough space.
    – panta82
    Feb 11, 2016 at 23:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .