Thread

1. no space left on device

   Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-09T20:31:11Z

   I'm running Postgres 7.1.3, and just started having a problem where my
   dynamic site is going down (read-only DB, with no writes happening to the
   DB) regularly (every other day).  I have no idea whay this is happening,
   and my search of the FAQ's and mail list don't bring up anything.  i've
   attached the error from the log file, at the end of this message.
   
   Here's an output of the disk usage from within the DB dir
   
   [postgres - DB]$ du -k .
   1716    ./base/1
   1716    ./base/16555
   5192    ./base/56048
   8628    ./base
   116 ./global
   32812   ./pg_xlog
   11380   ./pg_clog
   53192   .
   
   Note that the pg_xlog dir is huge!  Here's its contents:
   
   [postgres - DB/pg_xlog]$ ls -al
   total 32816
   drwx------    2 postgres admin        4096 Mar 29  2003 .
   drwx------    6 postgres admin        4096 Jan  9 15:04 ..
   -rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   -rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   
   What are these files, and what can I do to resolve this issue?
   
   Thx,
   
   Zeb
   
   
   --
   DEBUG:  statistics collector process (pid 2523) exited with exit code 1
   PGSTAT: Error closing temp stats file
   PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
   device
   PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
   code
   1
   FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   device
   DEBUG:  server process (pid 3741) exited with exit code 2
   DEBUG:  terminating any other active server processes
   NOTICE:  Message from PostgreSQL backend:
           The Postmaster has informed me that some other backend
           died abnormally and possibly corrupted shared memory.
           I have rolled back the current transaction and am
           going to terminate your database system connection and exit.
           Please reconnect to the database system and repeat your query.
   NOTICE:  Message from PostgreSQL backend:
           The Postmaster has informed me that some other backend
           died abnormally and possibly corrupted shared memory.
           I have rolled back the current transaction and am
           going to terminate your database system connection and exit.
           Please reconnect to the database system and repeat your query.
   NOTICE:  Message from PostgreSQL backend:
           The Postmaster has informed me that some other backend
           died abnormally and possibly corrupted shared memory.
           I have rolled back the current transaction and am
           going to terminate your database system connection and exit.
           Please reconnect to the database system and repeat your query.
   DEBUG:  all server processes terminated; reinitializing shared memory and
   semaph
   ores
   DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
   DEBUG:  checkpoint record is at 0/138CFD4
   DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
   DEBUG:  next transaction id: 45811837; next oid: 65205
   DEBUG:  database system was not properly shut down; automatic recovery in
   progre
   ss
   DEBUG:  redo starts at 0/138D014
   FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   device
   DEBUG:  startup process (pid 3785) exited with exit code 2
   DEBUG:  aborting startup due to startup process failure
   
   

2. Re: no space left on device

   scott.marlowe <scott.marlowe@ihs.com> — 2004-01-09T21:03:40Z

   On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
   
   > I'm running Postgres 7.1.3, and just started having a problem where my
   > dynamic site is going down (read-only DB, with no writes happening to the
   > DB) regularly (every other day).  I have no idea whay this is happening,
   > and my search of the FAQ's and mail list don't bring up anything.  i've
   > attached the error from the log file, at the end of this message.
   > 
   > Here's an output of the disk usage from within the DB dir
   > 
   > [postgres - DB]$ du -k .
   > 1716    ./base/1
   > 1716    ./base/16555
   > 5192    ./base/56048
   > 8628    ./base
   > 116 ./global
   > 32812   ./pg_xlog
   > 11380   ./pg_clog
   > 53192   .
   > 
   > Note that the pg_xlog dir is huge!  Here's its contents:
   
   That's normal.  32 meg isn't really that big.  How big of a partition do 
   you have this database on?  Your best bet is to put it on a bigger 
   partition.  the pg_xlog directory is gonna be at least 16 megs for most 
   installations.
   
   Do you have any transactions sitting at idle keeping postgresql from 
   recycling the xlogs?
   
   Normally when you run out of space it's a lack of vacuuming, but here it 
   just sounds like either the partition is too small or the postgres user is 
   living under a quota on that partition.
   
   
   

3. Re: no space left on device

   Joshua D. Drake <jd@commandprompt.com> — 2004-01-09T21:05:49Z

   >
   >Note that the pg_xlog dir is huge!  Here's its contents:
   >
   >[postgres - DB/pg_xlog]$ ls -al
   >total 32816
   >drwx------    2 postgres admin        4096 Mar 29  2003 .
   >drwx------    6 postgres admin        4096 Jan  9 15:04 ..
   >-rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   >-rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   >
   >What are these files, and what can I do to resolve this issue?
   >  
   >
   The are check_point files. You need them. Have you ran a vacuum recently?
   
   Sincerely,
   
   Joshua D. Drake
   
   
   
   >Thx,
   >
   >Zeb
   >
   >
   >--
   >DEBUG:  statistics collector process (pid 2523) exited with exit code 1
   >PGSTAT: Error closing temp stats file
   >PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
   >device
   >PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
   >code
   >1
   >FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   >device
   >DEBUG:  server process (pid 3741) exited with exit code 2
   >DEBUG:  terminating any other active server processes
   >NOTICE:  Message from PostgreSQL backend:
   >        The Postmaster has informed me that some other backend
   >        died abnormally and possibly corrupted shared memory.
   >        I have rolled back the current transaction and am
   >        going to terminate your database system connection and exit.
   >        Please reconnect to the database system and repeat your query.
   >NOTICE:  Message from PostgreSQL backend:
   >        The Postmaster has informed me that some other backend
   >        died abnormally and possibly corrupted shared memory.
   >        I have rolled back the current transaction and am
   >        going to terminate your database system connection and exit.
   >        Please reconnect to the database system and repeat your query.
   >NOTICE:  Message from PostgreSQL backend:
   >        The Postmaster has informed me that some other backend
   >        died abnormally and possibly corrupted shared memory.
   >        I have rolled back the current transaction and am
   >        going to terminate your database system connection and exit.
   >        Please reconnect to the database system and repeat your query.
   >DEBUG:  all server processes terminated; reinitializing shared memory and
   >semaph
   >ores
   >DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
   >DEBUG:  checkpoint record is at 0/138CFD4
   >DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
   >DEBUG:  next transaction id: 45811837; next oid: 65205
   >DEBUG:  database system was not properly shut down; automatic recovery in
   >progre
   >ss
   >DEBUG:  redo starts at 0/138D014
   >FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   >device
   >DEBUG:  startup process (pid 3785) exited with exit code 2
   >DEBUG:  aborting startup due to startup process failure
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 6: Have you searched our list archives?
   >
   >               http://archives.postgresql.org
   >  
   >
   
   
   -- 
   Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
   Postgresql support, programming shared hosting and dedicated hosting.
   +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
   PostgreSQL Replicator -- production quality replication for PostgreSQL
   
   
   

4. Re: no space left on device

   Richard Huxton <dev@archonet.com> — 2004-01-09T21:10:49Z

   On Friday 09 January 2004 20:31, Aurangzeb M. Agha wrote:
   > I'm running Postgres 7.1.3, and just started having a problem where my
   > dynamic site is going down (read-only DB, with no writes happening to the
   > DB) regularly (every other day).  I have no idea whay this is happening,
   > and my search of the FAQ's and mail list don't bring up anything.  i've
   > attached the error from the log file, at the end of this message.
   >
   > Here's an output of the disk usage from within the DB dir
   >
   > [postgres - DB]$ du -k .
   > 1716    ./base/1
   > 1716    ./base/16555
   > 5192    ./base/56048
   > 8628    ./base
   > 116 ./global
   > 32812   ./pg_xlog
   > 11380   ./pg_clog
   > 53192   .
   
   OK, and what does "df -m" show? That will display disk sizes and free space 
   remaining. Your error is that you have run out of disk space.
   
   > Note that the pg_xlog dir is huge!  Here's its contents:
   
   Well - it's 32MB (2 x 16MB as you show below).
   
   > -rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   > -rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   >
   > What are these files, and what can I do to resolve this issue?
   
   They're transaction logs (see the section on WAL). You can probably reduce 
   them from their default size of 16MB, I'm guessing by changing some constant 
   in the source and re-compiling.
   
   -- 
     Richard Huxton
     Archonet Ltd
   
   

5. Re: no space left on device

   Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-09T21:11:51Z

   I've not run a vacuum in quite some time, and that's because I've only
   been doing reads from this DB.  I was under the impression that I should
   run vacuum when tables are heavily modified:
   
   http://www.postgresql.org/docs/aw_pgsql_book/node110.html
   
   I guess I must have been mistaken?
   
   I'm looking through the docs now, but am having trouble finding this: how
   can I vacuum the entire DB at once?
   
   Thx,
   
   Zeb
   
   On Fri, 9 Jan 2004, Joshua D. Drake wrote:
   
   :
   :>
   :>Note that the pg_xlog dir is huge!  Here's its contents:
   :>
   :>[postgres - DB/pg_xlog]$ ls -al
   :>total 32816
   :>drwx------    2 postgres admin        4096 Mar 29  2003 .
   :>drwx------    6 postgres admin        4096 Jan  9 15:04 ..
   :>-rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   :>-rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   :>
   :>What are these files, and what can I do to resolve this issue?
   :>
   :>
   :The are check_point files. You need them. Have you ran a vacuum recently?
   :
   :Sincerely,
   :
   :Joshua D. Drake
   :
   :
   :
   :>Thx,
   :>
   :>Zeb
   :>
   :>
   :>--
   :>DEBUG:  statistics collector process (pid 2523) exited with exit code 1
   :>PGSTAT: Error closing temp stats file
   :>PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
   :>device
   :>PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
   :>code
   :>1
   :>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   :>device
   :>DEBUG:  server process (pid 3741) exited with exit code 2
   :>DEBUG:  terminating any other active server processes
   :>NOTICE:  Message from PostgreSQL backend:
   :>        The Postmaster has informed me that some other backend
   :>        died abnormally and possibly corrupted shared memory.
   :>        I have rolled back the current transaction and am
   :>        going to terminate your database system connection and exit.
   :>        Please reconnect to the database system and repeat your query.
   :>NOTICE:  Message from PostgreSQL backend:
   :>        The Postmaster has informed me that some other backend
   :>        died abnormally and possibly corrupted shared memory.
   :>        I have rolled back the current transaction and am
   :>        going to terminate your database system connection and exit.
   :>        Please reconnect to the database system and repeat your query.
   :>NOTICE:  Message from PostgreSQL backend:
   :>        The Postmaster has informed me that some other backend
   :>        died abnormally and possibly corrupted shared memory.
   :>        I have rolled back the current transaction and am
   :>        going to terminate your database system connection and exit.
   :>        Please reconnect to the database system and repeat your query.
   :>DEBUG:  all server processes terminated; reinitializing shared memory and
   :>semaph
   :>ores
   :>DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
   :>DEBUG:  checkpoint record is at 0/138CFD4
   :>DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
   :>DEBUG:  next transaction id: 45811837; next oid: 65205
   :>DEBUG:  database system was not properly shut down; automatic recovery in
   :>progre
   :>ss
   :>DEBUG:  redo starts at 0/138D014
   :>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   :>device
   :>DEBUG:  startup process (pid 3785) exited with exit code 2
   :>DEBUG:  aborting startup due to startup process failure
   :>
   :>---------------------------(end of broadcast)---------------------------
   :>TIP 6: Have you searched our list archives?
   :>
   :>               http://archives.postgresql.org
   :>
   :>
   :
   :
   :
   
   

6. Re: no space left on device

   Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-09T21:13:26Z

   Here's the output of "df -m":
   
   [postgres - DB]$ df -m .
   Filesystem           1M-blocks      Used Available Use% Mounted on
   -                        63328     55308      4803  93% /
   
   Thx for the info.
   
   
   Rgs,
   
   Aurangzeb
   
   
   On Fri, 9 Jan 2004, Richard Huxton wrote:
   
   :On Friday 09 January 2004 20:31, Aurangzeb M. Agha wrote:
   :> I'm running Postgres 7.1.3, and just started having a problem where my
   :> dynamic site is going down (read-only DB, with no writes happening to the
   :> DB) regularly (every other day).  I have no idea whay this is happening,
   :> and my search of the FAQ's and mail list don't bring up anything.  i've
   :> attached the error from the log file, at the end of this message.
   :>
   :> Here's an output of the disk usage from within the DB dir
   :>
   :> [postgres - DB]$ du -k .
   :> 1716    ./base/1
   :> 1716    ./base/16555
   :> 5192    ./base/56048
   :> 8628    ./base
   :> 116 ./global
   :> 32812   ./pg_xlog
   :> 11380   ./pg_clog
   :> 53192   .
   :
   :OK, and what does "df -m" show? That will display disk sizes and free space
   :remaining. Your error is that you have run out of disk space.
   :
   :> Note that the pg_xlog dir is huge!  Here's its contents:
   :
   :Well - it's 32MB (2 x 16MB as you show below).
   :
   :> -rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   :> -rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   :>
   :> What are these files, and what can I do to resolve this issue?
   :
   :They're transaction logs (see the section on WAL). You can probably reduce
   :them from their default size of 16MB, I'm guessing by changing some constant
   :in the source and re-compiling.
   :
   :
   
   -- 
   Aurangzeb M. Agha     | Email : ama@mltp.com
                         | Home  : +1 413 586.4863
                         | Pager : +1 413 785.7568
                         |       : 4137857568@myairmail.com
   73 Bridge St. #15     | Mobile: <coming soon>
   Northampton, MA 01060 | e-Fax : +1 978 246.0770
   USA                   | PGP id: <coming soon>
   
   
   

7. Re: no space left on device

   Joshua D. Drake <jd@commandprompt.com> — 2004-01-09T21:13:59Z

   Aurangzeb M. Agha wrote:
   
   >I've not run a vacuum in quite some time, and that's because I've only
   >been doing reads from this DB.  I was under the impression that I should
   >run vacuum when tables are heavily modified:
   >  
   >
   
   That would be accurate. Did you recently add a second database?
   
   Sincerely,
   
   Joshua D. Drake
   
   
   >http://www.postgresql.org/docs/aw_pgsql_book/node110.html
   >
   >I guess I must have been mistaken?
   >
   >I'm looking through the docs now, but am having trouble finding this: how
   >can I vacuum the entire DB at once?
   >
   >Thx,
   >
   >Zeb
   >
   >On Fri, 9 Jan 2004, Joshua D. Drake wrote:
   >
   >:
   >:>
   >:>Note that the pg_xlog dir is huge!  Here's its contents:
   >:>
   >:>[postgres - DB/pg_xlog]$ ls -al
   >:>total 32816
   >:>drwx------    2 postgres admin        4096 Mar 29  2003 .
   >:>drwx------    6 postgres admin        4096 Jan  9 15:04 ..
   >:>-rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   >:>-rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   >:>
   >:>What are these files, and what can I do to resolve this issue?
   >:>
   >:>
   >:The are check_point files. You need them. Have you ran a vacuum recently?
   >:
   >:Sincerely,
   >:
   >:Joshua D. Drake
   >:
   >:
   >:
   >:>Thx,
   >:>
   >:>Zeb
   >:>
   >:>
   >:>--
   >:>DEBUG:  statistics collector process (pid 2523) exited with exit code 1
   >:>PGSTAT: Error closing temp stats file
   >:>PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
   >:>device
   >:>PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
   >:>code
   >:>1
   >:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   >:>device
   >:>DEBUG:  server process (pid 3741) exited with exit code 2
   >:>DEBUG:  terminating any other active server processes
   >:>NOTICE:  Message from PostgreSQL backend:
   >:>        The Postmaster has informed me that some other backend
   >:>        died abnormally and possibly corrupted shared memory.
   >:>        I have rolled back the current transaction and am
   >:>        going to terminate your database system connection and exit.
   >:>        Please reconnect to the database system and repeat your query.
   >:>NOTICE:  Message from PostgreSQL backend:
   >:>        The Postmaster has informed me that some other backend
   >:>        died abnormally and possibly corrupted shared memory.
   >:>        I have rolled back the current transaction and am
   >:>        going to terminate your database system connection and exit.
   >:>        Please reconnect to the database system and repeat your query.
   >:>NOTICE:  Message from PostgreSQL backend:
   >:>        The Postmaster has informed me that some other backend
   >:>        died abnormally and possibly corrupted shared memory.
   >:>        I have rolled back the current transaction and am
   >:>        going to terminate your database system connection and exit.
   >:>        Please reconnect to the database system and repeat your query.
   >:>DEBUG:  all server processes terminated; reinitializing shared memory and
   >:>semaph
   >:>ores
   >:>DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
   >:>DEBUG:  checkpoint record is at 0/138CFD4
   >:>DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
   >:>DEBUG:  next transaction id: 45811837; next oid: 65205
   >:>DEBUG:  database system was not properly shut down; automatic recovery in
   >:>progre
   >:>ss
   >:>DEBUG:  redo starts at 0/138D014
   >:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   >:>device
   >:>DEBUG:  startup process (pid 3785) exited with exit code 2
   >:>DEBUG:  aborting startup due to startup process failure
   >:>
   >:>---------------------------(end of broadcast)---------------------------
   >:>TIP 6: Have you searched our list archives?
   >:>
   >:>               http://archives.postgresql.org
   >:>
   >:>
   >:
   >:
   >:
   >  
   >
   
   
   -- 
   Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
   Postgresql support, programming shared hosting and dedicated hosting.
   +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
   PostgreSQL Replicator -- production quality replication for PostgreSQL
   
   
   

8. Re: no space left on device

   Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-09T21:15:42Z

   No, I've not added any new DB's.  In fact, what's puzzling is that this DB
   has been running without issue (except for one server restart) for the
   last nine months.  Now, all of a sudden, with no DB changes, additions,
   etc... I'm getting this problem.
   
   Do you suggest that I still run a vacuumdb?
   
   Rgs,
   
   Zeb
   
   
   
   On Fri, 9 Jan 2004, Joshua D. Drake wrote:
   
   :Aurangzeb M. Agha wrote:
   :
   :>I've not run a vacuum in quite some time, and that's because I've only
   :>been doing reads from this DB.  I was under the impression that I should
   :>run vacuum when tables are heavily modified:
   :>
   :>
   :
   :That would be accurate. Did you recently add a second database?
   :
   :Sincerely,
   :
   :Joshua D. Drake
   :
   :
   :>http://www.postgresql.org/docs/aw_pgsql_book/node110.html
   :>
   :>I guess I must have been mistaken?
   :>
   :>I'm looking through the docs now, but am having trouble finding this: how
   :>can I vacuum the entire DB at once?
   :>
   :>Thx,
   :>
   :>Zeb
   :>
   :>On Fri, 9 Jan 2004, Joshua D. Drake wrote:
   :>
   :>:
   :>:>
   :>:>Note that the pg_xlog dir is huge!  Here's its contents:
   :>:>
   :>:>[postgres - DB/pg_xlog]$ ls -al
   :>:>total 32816
   :>:>drwx------    2 postgres admin        4096 Mar 29  2003 .
   :>:>drwx------    6 postgres admin        4096 Jan  9 15:04 ..
   :>:>-rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   :>:>-rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   :>:>
   :>:>What are these files, and what can I do to resolve this issue?
   :>:>
   :>:>
   :>:The are check_point files. You need them. Have you ran a vacuum recently?
   :>:
   :>:Sincerely,
   :>:
   :>:Joshua D. Drake
   :>:
   :>:
   :>:
   :>:>Thx,
   :>:>
   :>:>Zeb
   :>:>
   :>:>
   :>:>--
   :>:>DEBUG:  statistics collector process (pid 2523) exited with exit code 1
   :>:>PGSTAT: Error closing temp stats file
   :>:>PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
   :>:>device
   :>:>PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
   :>:>code
   :>:>1
   :>:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   :>:>device
   :>:>DEBUG:  server process (pid 3741) exited with exit code 2
   :>:>DEBUG:  terminating any other active server processes
   :>:>NOTICE:  Message from PostgreSQL backend:
   :>:>        The Postmaster has informed me that some other backend
   :>:>        died abnormally and possibly corrupted shared memory.
   :>:>        I have rolled back the current transaction and am
   :>:>        going to terminate your database system connection and exit.
   :>:>        Please reconnect to the database system and repeat your query.
   :>:>NOTICE:  Message from PostgreSQL backend:
   :>:>        The Postmaster has informed me that some other backend
   :>:>        died abnormally and possibly corrupted shared memory.
   :>:>        I have rolled back the current transaction and am
   :>:>        going to terminate your database system connection and exit.
   :>:>        Please reconnect to the database system and repeat your query.
   :>:>NOTICE:  Message from PostgreSQL backend:
   :>:>        The Postmaster has informed me that some other backend
   :>:>        died abnormally and possibly corrupted shared memory.
   :>:>        I have rolled back the current transaction and am
   :>:>        going to terminate your database system connection and exit.
   :>:>        Please reconnect to the database system and repeat your query.
   :>:>DEBUG:  all server processes terminated; reinitializing shared memory and
   :>:>semaph
   :>:>ores
   :>:>DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
   :>:>DEBUG:  checkpoint record is at 0/138CFD4
   :>:>DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
   :>:>DEBUG:  next transaction id: 45811837; next oid: 65205
   :>:>DEBUG:  database system was not properly shut down; automatic recovery in
   :>:>progre
   :>:>ss
   :>:>DEBUG:  redo starts at 0/138D014
   :>:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   :>:>device
   :>:>DEBUG:  startup process (pid 3785) exited with exit code 2
   :>:>DEBUG:  aborting startup due to startup process failure
   :>:>
   :>:>---------------------------(end of broadcast)---------------------------
   :>:>TIP 6: Have you searched our list archives?
   :>:>
   :>:>               http://archives.postgresql.org
   :>:>
   :>:>
   :>:
   :>:
   :>:
   :>
   :>
   :
   :
   :
   
   -- 
   Aurangzeb M. Agha     | Email : ama@mltp.com
                         | Home  : +1 413 586.4863
                         | Pager : +1 413 785.7568
                         |       : 4137857568@myairmail.com
   73 Bridge St. #15     | Mobile: <coming soon>
   Northampton, MA 01060 | e-Fax : +1 978 246.0770
   USA                   | PGP id: <coming soon>
   
   
   

9. Re: no space left on device

   Joshua D. Drake <jd@commandprompt.com> — 2004-01-09T21:24:36Z

   Hello,
   
    Wait... from the df you provided you have space left on the device:
   
   postgres - DB]$ df -m .
   Filesystem           1M-blocks      Used Available Use% Mounted on
   -                        63328     55308      4803  93% /
   
   Perhaps you are out of inodes?
   
   Sincerely,
   
   Josuha D. Drake
   
   
   
   
   Aurangzeb M. Agha wrote:
   
   >No, I've not added any new DB's.  In fact, what's puzzling is that this DB
   >has been running without issue (except for one server restart) for the
   >last nine months.  Now, all of a sudden, with no DB changes, additions,
   >etc... I'm getting this problem.
   >
   >Do you suggest that I still run a vacuumdb?
   >
   >Rgs,
   >
   >Zeb
   >
   >
   >
   >On Fri, 9 Jan 2004, Joshua D. Drake wrote:
   >
   >:Aurangzeb M. Agha wrote:
   >:
   >:>I've not run a vacuum in quite some time, and that's because I've only
   >:>been doing reads from this DB.  I was under the impression that I should
   >:>run vacuum when tables are heavily modified:
   >:>
   >:>
   >:
   >:That would be accurate. Did you recently add a second database?
   >:
   >:Sincerely,
   >:
   >:Joshua D. Drake
   >:
   >:
   >:>http://www.postgresql.org/docs/aw_pgsql_book/node110.html
   >:>
   >:>I guess I must have been mistaken?
   >:>
   >:>I'm looking through the docs now, but am having trouble finding this: how
   >:>can I vacuum the entire DB at once?
   >:>
   >:>Thx,
   >:>
   >:>Zeb
   >:>
   >:>On Fri, 9 Jan 2004, Joshua D. Drake wrote:
   >:>
   >:>:
   >:>:>
   >:>:>Note that the pg_xlog dir is huge!  Here's its contents:
   >:>:>
   >:>:>[postgres - DB/pg_xlog]$ ls -al
   >:>:>total 32816
   >:>:>drwx------    2 postgres admin        4096 Mar 29  2003 .
   >:>:>drwx------    6 postgres admin        4096 Jan  9 15:04 ..
   >:>:>-rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
   >:>:>-rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
   >:>:>
   >:>:>What are these files, and what can I do to resolve this issue?
   >:>:>
   >:>:>
   >:>:The are check_point files. You need them. Have you ran a vacuum recently?
   >:>:
   >:>:Sincerely,
   >:>:
   >:>:Joshua D. Drake
   >:>:
   >:>:
   >:>:
   >:>:>Thx,
   >:>:>
   >:>:>Zeb
   >:>:>
   >:>:>
   >:>:>--
   >:>:>DEBUG:  statistics collector process (pid 2523) exited with exit code 1
   >:>:>PGSTAT: Error closing temp stats file
   >:>:>PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
   >:>:>device
   >:>:>PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
   >:>:>code
   >:>:>1
   >:>:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   >:>:>device
   >:>:>DEBUG:  server process (pid 3741) exited with exit code 2
   >:>:>DEBUG:  terminating any other active server processes
   >:>:>NOTICE:  Message from PostgreSQL backend:
   >:>:>        The Postmaster has informed me that some other backend
   >:>:>        died abnormally and possibly corrupted shared memory.
   >:>:>        I have rolled back the current transaction and am
   >:>:>        going to terminate your database system connection and exit.
   >:>:>        Please reconnect to the database system and repeat your query.
   >:>:>NOTICE:  Message from PostgreSQL backend:
   >:>:>        The Postmaster has informed me that some other backend
   >:>:>        died abnormally and possibly corrupted shared memory.
   >:>:>        I have rolled back the current transaction and am
   >:>:>        going to terminate your database system connection and exit.
   >:>:>        Please reconnect to the database system and repeat your query.
   >:>:>NOTICE:  Message from PostgreSQL backend:
   >:>:>        The Postmaster has informed me that some other backend
   >:>:>        died abnormally and possibly corrupted shared memory.
   >:>:>        I have rolled back the current transaction and am
   >:>:>        going to terminate your database system connection and exit.
   >:>:>        Please reconnect to the database system and repeat your query.
   >:>:>DEBUG:  all server processes terminated; reinitializing shared memory and
   >:>:>semaph
   >:>:>ores
   >:>:>DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
   >:>:>DEBUG:  checkpoint record is at 0/138CFD4
   >:>:>DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
   >:>:>DEBUG:  next transaction id: 45811837; next oid: 65205
   >:>:>DEBUG:  database system was not properly shut down; automatic recovery in
   >:>:>progre
   >:>:>ss
   >:>:>DEBUG:  redo starts at 0/138D014
   >:>:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
   >:>:>device
   >:>:>DEBUG:  startup process (pid 3785) exited with exit code 2
   >:>:>DEBUG:  aborting startup due to startup process failure
   >:>:>
   >:>:>---------------------------(end of broadcast)---------------------------
   >:>:>TIP 6: Have you searched our list archives?
   >:>:>
   >:>:>               http://archives.postgresql.org
   >:>:>
   >:>:>
   >:>:
   >:>:
   >:>:
   >:>
   >:>
   >:
   >:
   >:
   >
   >  
   >
   
   
   -- 
   Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
   Postgresql support, programming shared hosting and dedicated hosting.
   +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
   PostgreSQL Replicator -- production quality replication for PostgreSQL
   
   
   

10. Re: no space left on device

    Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-09T21:29:30Z

    :That's normal.  32 meg isn't really that big.  How big of a partition do
    :you have this database on?  Your best bet is to put it on a bigger
    :partition.  the pg_xlog directory is gonna be at least 16 megs for most
    :installations.
    :
    :Do you have any transactions sitting at idle keeping postgresql from
    :recycling the xlogs?
    :
    :Normally when you run out of space it's a lack of vacuuming, but here it
    :just sounds like either the partition is too small or the postgres user is
    :living under a quota on that partition.
    
    Scott -- I'm at 93% disk usage:
    
    [postgres - DB]$ df -m .
    Filesystem           1M-blocks      Used Available Use% Mounted on
    -                        63328     55308      4803  93% /
    
    I don't know about transactions sitting idle--like I mentioned this DB is
    read-only, and there's no writes taking place.  Would I still need to
    worry about transactions?  How can I check to see if there are any?
    
    Re vacuuming, I haven't run vacuum for the same reason as above.  This is
    only a read-only DB, and I didn't think a vacuum was necessary if there's
    no writes happening to the DB.
    
    My concern is why this problem is happening now (on a read-only DB).  The
    DB has had nothing written to it, no new DB's have been created, and the
    disk usage has stayed constant.  I'm stumped as to why this problem has
    started all of a sudden.
    
    Rgs,
    
    Zeb
    
    

11. Re: no space left on device

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-01-09T21:46:47Z

    On Fri, Jan 09, 2004 at 01:11:51PM -0800, Aurangzeb M. Agha wrote:
    > I've not run a vacuum in quite some time, and that's because I've only
    > been doing reads from this DB.  I was under the impression that I should
    > run vacuum when tables are heavily modified:
    > 
    > http://www.postgresql.org/docs/aw_pgsql_book/node110.html
    
    Another reason to vacuum is that the pg_clog files are deleted if they
    are no longer needed.  So if you had vacuumed, there would be less files
    there (I'm not sure if this was the case on 7.1 though).
    
    Maybe in the meantime you could move one of the pg_xlog files to another
    filesystem and make a symlink to the correct position.  That should given
    you some breathing room.  Vacuum right after that.
    
    Also keep in mind that deleted files that are kept open by running
    processes do not release the occupied space ... see if you have some
    process with an open file on that filesystem which is no longer present
    (some clever usage of fuser and ps should give you that info)
    
    -- 
    Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
    "We are who we choose to be", sang the goldfinch
    when the sun is high (Sandman)
    
    

12. Re: no space left on device

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2004-01-09T21:55:55Z

    > Wait... from the df you provided you have space left on the device:
    > 
    > postgres - DB]$ df -m .
    > Filesystem           1M-blocks      Used Available Use% Mounted on
    > -                        63328     55308      4803  93% /
    > 
    > Perhaps you are out of inodes?
    Remember that df shows the *total* space left on the device,
    especially when run as root. Some percent are reserved for
    root, however, AFAIR pretty much exactly 7% in my experience,
    eg. user postgres can't use them but rather sees a device
    that's out of space. That may be the case here.
    
    Karsten
    -- 
    GPG key ID E4071346 @ wwwkeys.pgp.net
    E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
    
    

13. Re: no space left on device

    scott.marlowe <scott.marlowe@ihs.com> — 2004-01-09T22:32:40Z

    On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    
    > :That's normal.  32 meg isn't really that big.  How big of a partition do
    > :you have this database on?  Your best bet is to put it on a bigger
    > :partition.  the pg_xlog directory is gonna be at least 16 megs for most
    > :installations.
    > :
    > :Do you have any transactions sitting at idle keeping postgresql from
    > :recycling the xlogs?
    > :
    > :Normally when you run out of space it's a lack of vacuuming, but here it
    > :just sounds like either the partition is too small or the postgres user is
    > :living under a quota on that partition.
    > 
    > Scott -- I'm at 93% disk usage:
    > 
    > [postgres - DB]$ df -m .
    > Filesystem           1M-blocks      Used Available Use% Mounted on
    > -                        63328     55308      4803  93% /
    
    Do you have root access to it?  if so, set the reserved space for root to 
    be 0%, and then try vacuuming.  Vacuuming requires some free space, and 
    since you're pretty much out, it isn't gonna be able to complete.
    
    > I don't know about transactions sitting idle--like I mentioned this DB is
    > read-only, and there's no writes taking place.  Would I still need to
    > worry about transactions?  How can I check to see if there are any?
    
    If you stop and restart it all transactions that are holding will be 
    disconnected, so that would clear that up.
    
    But it looks to me like you just have it on too small of a partition.  On 
    a modern multi-gigabyte hard drive, Postgresql's usage of tens of megs for 
    transactions logs is no big deal, but on a smaller partition like yours it 
    can cause problems.
    
    > Re vacuuming, I haven't run vacuum for the same reason as above.  This is
    > only a read-only DB, and I didn't think a vacuum was necessary if there's
    > no writes happening to the DB.
    
    Well, if the database has been emptied and refilled it would use the 
    space, so it might be something like that.  Or that it was right on the 
    edge of being out of space and some single alter user kinda thing drove 
    it over the edge.  Hard to say.  It looks like your individual databases 
    are pretty small, so I doubt there's lots of lost space in them.
    
    Can you get a larger partition to move the data directory to on that box?  
    I'd recommend having about twice the max size of your database as a 
    minimum, which would be 120 to 150 megs for you.
    
    
    

14. Re: no space left on device

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-01-09T22:56:08Z

    "Joshua D. Drake" <jd@commandprompt.com> writes:
    > Perhaps you are out of inodes?
    
    Either that or he's hitting a per-user quota limit, which is perhaps
    more likely.
    
    			regards, tom lane
    
    

15. Re: no space left on device

    Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-01-09T23:01:00Z

    On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    
    > Here's the output of "df -m":
    > 
    > [postgres - DB]$ df -m .
    > Filesystem           1M-blocks      Used Available Use% Mounted on
    > -                        63328     55308      4803  93% /
    > 
    
    But your du, below, of the postgres data directory shows 53MB in use. That's an
    order of magnitude smaller than the 55GB the above appears to be saying is used
    in the db.
    
    Start again with du -sk /* and follow the biggest numbers.
    
    Ideas:
    
     - have you logfiles that processes, such as postmaster, are writing to and
    have got huge?
    
     - did you have the above and you deleted such files without restarting the
    process that was writing to the deleted files?
    
     - /var/(mail|tmp|whatever) is huge due to huge amounts of email recieved and
    not deleted
    
     - /home/whatever is huge due to logfiles, downloads (inc. application caches),
    datafiles, software builds, ...
    
    
    --
    Nigel Andrews
    
    
    
    > Thx for the info.
    > 
    > 
    > Rgs,
    > 
    > Aurangzeb
    > 
    > 
    > On Fri, 9 Jan 2004, Richard Huxton wrote:
    > 
    > :On Friday 09 January 2004 20:31, Aurangzeb M. Agha wrote:
    > :> I'm running Postgres 7.1.3, and just started having a problem where my
    > :> dynamic site is going down (read-only DB, with no writes happening to the
    > :> DB) regularly (every other day).  I have no idea whay this is happening,
    > :> and my search of the FAQ's and mail list don't bring up anything.  i've
    > :> attached the error from the log file, at the end of this message.
    > :>
    > :> Here's an output of the disk usage from within the DB dir
    > :>
    > :> [postgres - DB]$ du -k .
    > :> 1716    ./base/1
    > :> 1716    ./base/16555
    > :> 5192    ./base/56048
    > :> 8628    ./base
    > :> 116 ./global
    > :> 32812   ./pg_xlog
    > :> 11380   ./pg_clog
    > :> 53192   .
    > :
    > :OK, and what does "df -m" show? That will display disk sizes and free space
    > :remaining. Your error is that you have run out of disk space.
    > :
    > :> Note that the pg_xlog dir is huge!  Here's its contents:
    > :
    > :Well - it's 32MB (2 x 16MB as you show below).
    > :
    > :> -rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
    > :> -rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
    > :>
    > :> What are these files, and what can I do to resolve this issue?
    > :
    > :They're transaction logs (see the section on WAL). You can probably reduce
    > :them from their default size of 16MB, I'm guessing by changing some constant
    > :in the source and re-compiling.
    > :
    > :
    > 
    > 
    
    
    

16. Re: no space left on device

    scott.marlowe <scott.marlowe@ihs.com> — 2004-01-09T23:16:33Z

    On Fri, 9 Jan 2004, Tom Lane wrote:
    
    > "scott.marlowe" <scott.marlowe@ihs.com> writes:
    > >> [postgres - DB]$ df -m .
    > >> Filesystem           1M-blocks      Used Available Use% Mounted on
    > >> -                        63328     55308      4803  93% /
    > 
    > > Do you have root access to it?  if so, set the reserved space for root to 
    > > be 0%, and then try vacuuming.  Vacuuming requires some free space, and 
    > > since you're pretty much out, it isn't gonna be able to complete.
    > 
    > Look again --- it's showing free space in MB not KB.  He's got 4.8GB
    > free.  (Although that might be free-from-root's-point-of-view, rather
    > than what an unprivileged user can use ...)
    
    Good catch.  I'm so used to using raw df output...
    
    Yeah, it looks like root's reserved space is getting him to me, but since 
    it's the root partition, it's possible it's out of inodes as well.
    
    Aurangzeb, try running df -i to see how many inodes you have left...
    
    
    

17. Re: no space left on device

    Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-01-09T23:16:47Z

    On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    > 
    > Scott -- I'm at 93% disk usage:
    > 
    > [postgres - DB]$ df -m .
    > Filesystem           1M-blocks      Used Available Use% Mounted on
    > -                        63328     55308      4803  93% /
    > 
    
    
    BTW, don't do -m with df it confuses us old folk, as you probably noticed from
    the responses.
    
    :)
    
    
    --
    Nigel
    
    
    

18. Re: no space left on device

    Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-09T23:22:17Z

    Right!  Thus my quandry.
    
    Re inodes, how can I check this?  But why would this be?  Is Postgres
    sucking up inodes just sitting there as a read-only DB?
    
    	AMA
    
    On Fri, 9 Jan 2004, Joshua D. Drake wrote:
    
    :Hello,
    :
    : Wait... from the df you provided you have space left on the device:
    :
    :postgres - DB]$ df -m .
    :Filesystem           1M-blocks      Used Available Use% Mounted on
    :-                        63328     55308      4803  93% /
    :
    :Perhaps you are out of inodes?
    :
    :Sincerely,
    :
    :Josuha D. Drake
    :
    :
    :
    :
    :Aurangzeb M. Agha wrote:
    :
    :>No, I've not added any new DB's.  In fact, what's puzzling is that this DB
    :>has been running without issue (except for one server restart) for the
    :>last nine months.  Now, all of a sudden, with no DB changes, additions,
    :>etc... I'm getting this problem.
    :>
    :>Do you suggest that I still run a vacuumdb?
    :>
    :>Rgs,
    :>
    :>Zeb
    :>
    :>
    :>
    :>On Fri, 9 Jan 2004, Joshua D. Drake wrote:
    :>
    :>:Aurangzeb M. Agha wrote:
    :>:
    :>:>I've not run a vacuum in quite some time, and that's because I've only
    :>:>been doing reads from this DB.  I was under the impression that I should
    :>:>run vacuum when tables are heavily modified:
    :>:>
    :>:>
    :>:
    :>:That would be accurate. Did you recently add a second database?
    :>:
    :>:Sincerely,
    :>:
    :>:Joshua D. Drake
    :>:
    :>:
    :>:>http://www.postgresql.org/docs/aw_pgsql_book/node110.html
    :>:>
    :>:>I guess I must have been mistaken?
    :>:>
    :>:>I'm looking through the docs now, but am having trouble finding this: how
    :>:>can I vacuum the entire DB at once?
    :>:>
    :>:>Thx,
    :>:>
    :>:>Zeb
    :>:>
    :>:>On Fri, 9 Jan 2004, Joshua D. Drake wrote:
    :>:>
    :>:>:
    :>:>:>
    :>:>:>Note that the pg_xlog dir is huge!  Here's its contents:
    :>:>:>
    :>:>:>[postgres - DB/pg_xlog]$ ls -al
    :>:>:>total 32816
    :>:>:>drwx------    2 postgres admin        4096 Mar 29  2003 .
    :>:>:>drwx------    6 postgres admin        4096 Jan  9 15:04 ..
    :>:>:>-rwx------    1 postgres admin    16777216 Jan  9 15:09 0000000000000001
    :>:>:>-rwx------    1 postgres admin    16777216 Mar 29  2003 0000000000000002
    :>:>:>
    :>:>:>What are these files, and what can I do to resolve this issue?
    :>:>:>
    :>:>:>
    :>:>:The are check_point files. You need them. Have you ran a vacuum recently?
    :>:>:
    :>:>:Sincerely,
    :>:>:
    :>:>:Joshua D. Drake
    :>:>:
    :>:>:
    :>:>:
    :>:>:>Thx,
    :>:>:>
    :>:>:>Zeb
    :>:>:>
    :>:>:>
    :>:>:>--
    :>:>:>DEBUG:  statistics collector process (pid 2523) exited with exit code 1
    :>:>:>PGSTAT: Error closing temp stats file
    :>:>:>PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
    :>:>:>device
    :>:>:>PGSTAT: AbDEBUG:  statistics collector process (pid 2979) exited with exit
    :>:>:>code
    :>:>:>1
    :>:>:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
    :>:>:>device
    :>:>:>DEBUG:  server process (pid 3741) exited with exit code 2
    :>:>:>DEBUG:  terminating any other active server processes
    :>:>:>NOTICE:  Message from PostgreSQL backend:
    :>:>:>        The Postmaster has informed me that some other backend
    :>:>:>        died abnormally and possibly corrupted shared memory.
    :>:>:>        I have rolled back the current transaction and am
    :>:>:>        going to terminate your database system connection and exit.
    :>:>:>        Please reconnect to the database system and repeat your query.
    :>:>:>NOTICE:  Message from PostgreSQL backend:
    :>:>:>        The Postmaster has informed me that some other backend
    :>:>:>        died abnormally and possibly corrupted shared memory.
    :>:>:>        I have rolled back the current transaction and am
    :>:>:>        going to terminate your database system connection and exit.
    :>:>:>        Please reconnect to the database system and repeat your query.
    :>:>:>NOTICE:  Message from PostgreSQL backend:
    :>:>:>        The Postmaster has informed me that some other backend
    :>:>:>        died abnormally and possibly corrupted shared memory.
    :>:>:>        I have rolled back the current transaction and am
    :>:>:>        going to terminate your database system connection and exit.
    :>:>:>        Please reconnect to the database system and repeat your query.
    :>:>:>DEBUG:  all server processes terminated; reinitializing shared memory and
    :>:>:>semaph
    :>:>:>ores
    :>:>:>DEBUG:  database system was interrupted at 2004-01-09 05:22:52 EST
    :>:>:>DEBUG:  checkpoint record is at 0/138CFD4
    :>:>:>DEBUG:  redo record is at 0/138CFD4; undo record is at 0/0; shutdown FALSE
    :>:>:>DEBUG:  next transaction id: 45811837; next oid: 65205
    :>:>:>DEBUG:  database system was not properly shut down; automatic recovery in
    :>:>:>progre
    :>:>:>ss
    :>:>:>DEBUG:  redo starts at 0/138D014
    :>:>:>FATAL 2:  write of clog file 43, offset 188416 failed: No space left on
    :>:>:>device
    :>:>:>DEBUG:  startup process (pid 3785) exited with exit code 2
    :>:>:>DEBUG:  aborting startup due to startup process failure
    :>:>:>
    :>:>:>---------------------------(end of broadcast)---------------------------
    :>:>:>TIP 6: Have you searched our list archives?
    :>:>:>
    :>:>:>               http://archives.postgresql.org
    :>:>:>
    :>:>:>
    :>:>:
    :>:>:
    :>:>:
    :>:>
    :>:>
    :>:
    :>:
    :>:
    :>
    :>
    :>
    :
    :
    :
    
    -- 
    Aurangzeb M. Agha     | Email : ama@mltp.com
                          | Home  : +1 413 586.4863
                          | Pager : +1 413 785.7568
                          |       : 4137857568@myairmail.com
    73 Bridge St. #15     | Mobile: <coming soon>
    Northampton, MA 01060 | e-Fax : +1 978 246.0770
    USA                   | PGP id: <coming soon>
    
    
    

19. Re: no space left on device

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-01-09T23:25:12Z

    "scott.marlowe" <scott.marlowe@ihs.com> writes:
    >> [postgres - DB]$ df -m .
    >> Filesystem           1M-blocks      Used Available Use% Mounted on
    >> -                        63328     55308      4803  93% /
    
    > Do you have root access to it?  if so, set the reserved space for root to 
    > be 0%, and then try vacuuming.  Vacuuming requires some free space, and 
    > since you're pretty much out, it isn't gonna be able to complete.
    
    Look again --- it's showing free space in MB not KB.  He's got 4.8GB
    free.  (Although that might be free-from-root's-point-of-view, rather
    than what an unprivileged user can use ...)
    
    			regards, tom lane
    
    

20. Re: no space left on device

    scott.marlowe <scott.marlowe@ihs.com> — 2004-01-09T23:25:28Z

    On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    
    > Right!  Thus my quandry.
    > 
    > Re inodes, how can I check this?  But why would this be?  Is Postgres
    > sucking up inodes just sitting there as a read-only DB?
    
    If you are out of inodes, I seriously doubt it is Postgresql's fault, as 
    you seem to be running everything on the root partition here, it could be 
    any other process more likely than postgresql is using all the inodes.  
    Basically, when you make a lot of small files you can run out of inodes.  
    Since postgresql tends to make a few rather large files, it's usually not 
    a concern.
    
    df -i shows inode usage.
    
    On linux, you can change the % reserved for root to 1% with tune2fs:
    
    tune2fs -m 1
    
    
    
    

21. Re: no space left on device

    Dennis Bjorklund <db@zigo.dhs.org> — 2004-01-09T23:30:51Z

    On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    
    > DEBUG:  statistics collector process (pid 2523) exited with exit code 1
    > PGSTAT: Error closing temp stats file
    > PGSTAT: /usr/local/G101/App/DB/./global/pgstat.tmp.7823: No space left on
    > device
    
    To me it does not sound strange that the database is growing when the stat 
    collector updates the tables with statistics. And since there are updates 
    it would have been good to have vacuumed avery once in a while.
    
    I don't know the internals of pg as well as some of the other people who 
    have answered, but as far as I know the stat collector is not special in 
    any way but is updating the stat tables.
    
    -- 
    /Dennis Björklund
    
    
    

22. Re: no space left on device

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-01-09T23:35:29Z

    "Aurangzeb M. Agha" <ama-list@mltp.com> writes:
    > Re inodes, how can I check this?
    
    "df -i" should help.
    
    > But why would this be?  Is Postgres
    > sucking up inodes just sitting there as a read-only DB?
    
    I think you have missed the point here.  Postgres is using 0.1 percent
    of your disk; whatever is eating disk space or inodes is somewhere in
    the 92.9% of the disk that you have not told us about.  You are focusing
    on killing the messenger instead of finding the true source of the
    problem.
    
    You should also check into the per-user-quota possibility.
    
    			regards, tom lane
    
    

23. Re: no space left on device

    Mark Kirkwood <markir@paradise.net.nz> — 2004-01-09T23:46:11Z

    I would suspect some *other* service is using the 4G for transient 
    storage every now and again, and it just so happens that Pg is getting 
    tripped up.
    
    What else does this machine run ?
    
    regards
    
    Mark
    
    
    
    Nigel J. Andrews wrote:
    
    >On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    >
    >  
    >
    >>Here's the output of "df -m":
    >>
    >>[postgres - DB]$ df -m .
    >>Filesystem           1M-blocks      Used Available Use% Mounted on
    >>-                        63328     55308      4803  93% /
    >>
    >>    
    >>
    >
    >But your du, below, of the postgres data directory shows 53MB in use. That's an
    >order of magnitude smaller than the 55GB the above appears to be saying is used
    >in the db.
    >
    >
    >  
    >
    
    
    

24. Re: no space left on device

    Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-01-10T00:32:35Z

    On Fri, 9 Jan 2004, scott.marlowe wrote:
    
    > On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:
    > 
    > > Right!  Thus my quandry.
    > > 
    > > Re inodes, how can I check this?  But why would this be?  Is Postgres
    > > sucking up inodes just sitting there as a read-only DB?
    > 
    > If you are out of inodes, I seriously doubt it is Postgresql's fault, as 
    > you seem to be running everything on the root partition here, it could be 
    > any other process more likely than postgresql is using all the inodes.  
    > Basically, when you make a lot of small files you can run out of inodes.  
    
    And a common culprit is whatever is being used for usenet caching/serving...or
    ordinary mail which is just accumulating in /var/mail (or whereever).
    
    
    > Since postgresql tends to make a few rather large files, it's usually not 
    > a concern.
    > 
    > df -i shows inode usage.
    > 
    > On linux, you can change the % reserved for root to 1% with tune2fs:
    > 
    > tune2fs -m 1
    
    -- 
    Nigel J. Andrews
    
    
    

25. Re: no space left on device

    Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-01-10T00:38:43Z

    On Sat, 10 Jan 2004, Nigel J. Andrews wrote:
    > And a common culprit is whatever is being used for usenet caching/serving...or
    > ordinary mail which is just accumulating in /var/mail (or whereever).
    
    Sheesh. Did I really put ordinary mailbox mail in the uses up inodes category?
    I should taken out and whi....errrr...on the other hand better not might be too
    exciting for some and spark off a whole new xxx web site.
    
    
    Nigel Andrews
    
    
    

26. Re: no space left on device

    Bruno Wolff III <bruno@wolff.to> — 2004-01-10T18:19:36Z

    On Sat, Jan 10, 2004 at 00:38:43 +0000,
      "Nigel J. Andrews" <nandrews@investsystems.co.uk> wrote:
    > 
    > On Sat, 10 Jan 2004, Nigel J. Andrews wrote:
    > > And a common culprit is whatever is being used for usenet caching/serving...or
    > > ordinary mail which is just accumulating in /var/mail (or whereever).
    > 
    > Sheesh. Did I really put ordinary mailbox mail in the uses up inodes category?
    > I should taken out and whi....errrr...on the other hand better not might be too
    > exciting for some and spark off a whole new xxx web site.
    
    While mbox mailboxes only take one inode per mailbox, maildir mailboxes take
    one inode per message. So if you are using maildir you could potentially
    use a significant number of inodes for email.
    
    

27. Re: no space left on device

    Christopher Browne <cbbrowne@libertyrms.info> — 2004-01-12T15:44:06Z

    nandrews@investsystems.co.uk ("Nigel J. Andrews") writes:
    > On Sat, 10 Jan 2004, Nigel J. Andrews wrote:
    >> And a common culprit is whatever is being used for usenet caching/serving...or
    >> ordinary mail which is just accumulating in /var/mail (or whereever).
    >
    > Sheesh. Did I really put ordinary mailbox mail in the uses up inodes category?
    > I should taken out and whi....errrr...on the other hand better not might be too
    > exciting for some and spark off a whole new xxx web site.
    
    Mail accumulating in "mbox" spools shouldn't chew up inodes too badly,
    but if you're using "Maildir" to spool mail, whether incoming or
    outgoing, it sure can...
    -- 
    let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
    <http://dev6.int.libertyrms.com/>
    Christopher Browne
    (416) 646 3304 x124 (land)
    
    

28. Re: no space left on device

    Aurangzeb M. Agha <ama-list@mltp.com> — 2004-01-20T04:48:49Z

    On Fri, 9 Jan 2004, Tom Lane wrote:
    
    :"scott.marlowe" <scott.marlowe@ihs.com> writes:
    :>> [postgres - DB]$ df -m .
    :>> Filesystem           1M-blocks      Used Available Use% Mounted on
    :>> -                        63328     55308      4803  93% /
    :
    :> Do you have root access to it?  if so, set the reserved space for root to
    :> be 0%, and then try vacuuming.  Vacuuming requires some free space, and
    :> since you're pretty much out, it isn't gonna be able to complete.
    :
    :Look again --- it's showing free space in MB not KB.  He's got 4.8GB
    :free.  (Although that might be free-from-root's-point-of-view, rather
    :than what an unprivileged user can use ...)
    
    Tom -- You're right here.  This account is running on a virtual server, so
    the 4.8GB free is not for this user.
    
    Re i-nodes:
    
    [admin - temp]$ df -i .
    Filesystem            Inodes   IUsed   IFree IUse% Mounted on
    -                    8241152 1819166 6421986   23% /
    
    However, I did just get word from the ISP that they had some sort of error
    log rotation error which was keeping logs from being deleted off the
    machine, taking up a lot of space (for this user account).  So the 93% is
    aparently not a good representation of the disk usage, as its not for this
    specific user account.
    
    Rgs,
    
    Zeb