1

Today I fired up my linux mint via my virtual computer (host is windows 8), left for 30 minutes, and when I was back, I got an error saying I was out of disk space. Yesterday I had about 20 free GB (70 GB total). Looking at the disk, Postgresql is using all the space. I have several databases on my local (linux mint) machine.

Last week I had inserted a bunch of geospatial data via postgis, ran some queries, deleted the data from the geospatial tables, and VACUUM FULL the tables.

Now I can't even log onto postgresql. ie:

12:47:32 $ psql -d sf_grace
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Here is the contents of /var/log/postgresql

12:55:56 $ ls -l --block-size=G
total 47G
-rw-r----- 1 postgres adm 24G Jul 14 12:27 postgresql-9.1-main.log
-rw-r----- 1 postgres adm 24G Jul 14 11:16 postgresql-9.1-main.log.1
-rw-r----- 1 postgres adm  1G Apr 27 14:37 postgresql-9.1-main.log.10.gz
-rw-r----- 1 postgres adm  1G Apr 20 12:40 postgresql-9.1-main.log.11.gz
-rw-r----- 1 postgres adm  1G Jul  5 10:51 postgresql-9.1-main.log.2.gz
-rw-r----- 1 postgres adm  1G Jun 28 11:12 postgresql-9.1-main.log.3.gz
-rw-r----- 1 postgres adm  1G Jun 23 14:34 postgresql-9.1-main.log.4.gz
-rw-r----- 1 postgres adm  1G Jun  2 13:39 postgresql-9.1-main.log.5.gz
-rw-r----- 1 postgres adm  1G May 26 13:48 postgresql-9.1-main.log.6.gz
-rw-r----- 1 postgres adm  1G May 19 08:54 postgresql-9.1-main.log.7.gz
-rw-r----- 1 postgres adm  1G May 11 20:52 postgresql-9.1-main.log.8.gz
-rw-r----- 1 postgres adm  1G May  4 12:08 postgresql-9.1-main.log.9.gz

I am not a database expert, and have had no luck googling for a solution. Any help would be REALLY APPRECIATED!

Here is the ouput of head -n 100 postgresql-9.1-main.log

2015-07-05 12:02:38 PDT ERROR:  function st_bandnodtavalue(raster, integer) does not exist at character 8
2015-07-05 12:02:38 PDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2015-07-05 12:02:38 PDT STATEMENT:  SELECT ST_BandNoDtaValue(rast,1) FROM snodas_swe_full LIMIT 1;
2015-07-05 12:03:01 PDT ERROR:  function st_bandnodtavalue(raster) does not exist at character 8
2015-07-05 12:03:01 PDT HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2015-07-05 12:03:01 PDT STATEMENT:  SELECT ST_BandNoDtaValue(rast) FROM snodas_swe_full LIMIT 1;
2015-07-05 12:03:58 PDT ERROR:  column "format" does not exist at character 29
2015-07-05 12:03:58 PDT STATEMENT:  SELECT ST_GeoReference(rast,format=GDAL) FROM snodas_swe_full LIMIT 1;
2015-07-05 12:18:29 PDT ERROR:  date/time field value out of range: "0030-93-00"
2015-07-05 12:18:29 PDT HINT:  Perhaps you need a different "datestyle" setting.
2015-07-05 12:18:29 PDT STATEMENT:  UPDATE snodas_swe_full SET m_date = CAST(SUBSTRING(filename FROM 29 FOR 4)||'-'||SUBSTRING(filename FROM 33 FOR 2)||'-'||SUBSTRING(filename FROM 35 FOR 2) AS date);
2015-07-05 12:25:07 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:07 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:11 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:11 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:14 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:14 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:18 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:18 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:21 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:21 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:24 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:24 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:28 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:28 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:31 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:31 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:35 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:35 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:38 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:38 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:41 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:41 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:45 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:45 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:49 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:49 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:53 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:25:53 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:56 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:56 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:25:59 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:25:59 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:03 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:03 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:07 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:07 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:11 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:11 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:14 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:26:14 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:18 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:18 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:21 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:26:21 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:25 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:25 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:28 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:26:28 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:32 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:32 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:35 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:26:35 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:39 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:39 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:42 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:26:42 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:26:46 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:26:46 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:08 PDT LOG:  checkpoints are occurring too frequently (22 seconds apart)
2015-07-05 12:27:08 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:11 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:27:11 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:15 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:15 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:19 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:19 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:22 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:27:22 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:26 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:26 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:29 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:27:29 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:32 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:27:32 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:36 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:36 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:39 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:27:39 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:43 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:43 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:47 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:47 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:51 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:51 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:55 PDT LOG:  checkpoints are occurring too frequently (4 seconds apart)
2015-07-05 12:27:55 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:27:58 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-07-05 12:27:58 PDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2015-07-05 12:28:01 PDT LOG:  checkpoints are occurring too frequently (3 seconds apart)

What should I do? Delete this file and Drop (rather than just delete the data) from the postgis tables I had last week?

  • In normal conditions, these files contain only errors and warnings and should be near empty. Look at 100 lines with head -n 100 postgresql-9.1-main.log to see what kind of contents fills them. – Daniel Vérité Jul 14 '15 at 20:19
  • And in the meantime you may delete postgresql-9.1-main.log.*.gz, the database does not need them, it's just for you to consult. – Daniel Vérité Jul 14 '15 at 20:20
  • Daniel. Thanks a bunch! I deleted the .gz files. I also edited my post with the output of head -n 100 postgresql-9.1-main.log. It looks like I am having errors with the raster tables I was using last week. Should I just delete postgresql-9.1-main.log and postgresql-9.1-main.log1 and then drop the geospatial tables altogether? – Rowan_Gaffney Jul 14 '15 at 20:39
  • These entries date from 2015-07-05 (surprisingly), days ago. The warnings on checkpoints are related to intense database activity such as caused by a large import. Simply having tables in a database, geospatial or otherwise, should not generate continuous errors. – Daniel Vérité Jul 15 '15 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.