Cleaning pg_log should be enough to be able to start your postgres instance. However, it will let you with almost no space for your database to grow. If you have some tables with information you can delete, that can free up space.
Unless most of your database information is "volatile"(case in which you should implement an automatic cleanup cron), the only remaining option is to get a larger disk for your vps. Regards, Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248 Website: www.ocs.pe ----- Original Message ----- From: "Sachin Srivastava" <ssr.teleat...@gmail.com> To: "Alvaro Aguayo Garcia-Rada" <aagu...@opensysperu.com> Cc: "pgsql-general@postgresql.org >> PG-General Mailing List" <pgsql-general@postgresql.org> Sent: Sunday, 19 February, 2017 1:03:09 AM Subject: Re: [GENERAL] No space left on device With correction: pg_log consumed 897 MB only Regards, Sachin Srivastava Mb.: 9811149139 On Sun, Feb 19, 2017 at 11:32 AM, Sachin Srivastava < ssr.teleat...@gmail.com > wrote: I have checked pg_log only 890 GB space consumed and pg_log folder consumed 1.1 GB only. All the space consumed by the database file only. -bash-4.2$ du -h 0 ./pg_xlog/archive_status 1.1G ./pg_xlog 712K ./global 47M ./pg_clog 0 ./pg_commit_ts 0 ./pg_dynshmem 0 ./pg_notify 0 ./pg_serial 0 ./pg_snapshots 32K ./pg_subtrans 0 ./pg_twophase 296K ./pg_multixact/members 124K ./pg_multixact/offsets 420K ./pg_multixact 7.2M ./base/1 7.2M ./base/13289 7.2M ./base/13294 98G ./base/16393 0 ./base/pgsql_tmp 98G ./base 0 ./pg_replslot 0 ./pg_tblspc 0 ./pg_stat 216K ./pg_stat_tmp 0 ./pg_logical/snapshots 0 ./pg_logical/mappings 0 ./pg_logical 897M ./pg_log 100G . -bash-4.2$ Regards, Sachin Srivastava Mb.: 9811149139 On Sun, Feb 19, 2017 at 11:29 AM, Alvaro Aguayo Garcia-Rada < aagu...@opensysperu.com > wrote: Cleanup some space on that device. Maybe your "pg_log" folder has some space to free. If that doesn't helps, you may need to forcefully add more space. There's not really much alternatives there. Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248 Website: www.ocs.pe ----- Original Message ----- From: "Sachin Srivastava" < ssr.teleat...@gmail.com > To: " pgsql-general@postgresql.org >> PG-General Mailing List" < pgsql-general@postgresql.org > Sent: Sunday, 19 February, 2017 12:51:49 AM Subject: [GENERAL] No space left on device Dear Expert, My PostgreSQL database got down due to no space left on my mount point /dev/vdb1 where my data directory are available, please suggest what's the best solution to resolve this issue. I am also not able to start my database again due to no space available. And right now, we don't have any further space to add in this mount point. -bash-4.2$ df -h Filesystem Size Used Avail Use% Mounted on /dev/vda1 50G 11G 37G 24% / devtmpfs 3.9G 0 3.9G 0% /dev tmpfs 3.9G 0 3.9G 0% /dev/shm tmpfs 3.9G 57M 3.9G 2% /run tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup /dev/vdb1 100G 100G 28K 100% /opt1 tmpfs 799M 0 799M 0% /run/user/1002 2017-02-19 03:46:53.513 UTC [24101]: [36911-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.522 UTC [2240]: [157092-1] host=192.168.60.103,user=agent2,db=pem ERROR: could not extend file "base/16393/112735.5": wrote only 4096 of 8192 bytes at block 722454 2017-02-19 03:46:53.522 UTC [2240]: [157093-1] host=192.168.60.103,user=agent2,db=pem HINT: Check free disk space. 2017-02-19 03:46:53.522 UTC [2240]: [157094-1] host=192.168.60.103,user=agent2,db=pem CONTEXT: SQL statement "INSERT INTO pemhistory.table_frozenxid (recorded_time, server_id, database_name, schema_name, table_name, frozenxid) VALUES (NEW.recorded_time, NEW.server_id, NEW.database_name, NEW.schema_name, NEW.table_name, NEW.frozenxid)" PL/pgSQL function pemdata.copy_table_frozenxid_to_history() line 4 at SQL statement 2017-02-19 03:46:53.522 UTC [2240]: [157095-1] host=192.168.60.103,user=agent2,db=pem STATEMENT: UPDATE pemdata.table_frozenxid SET "frozenxid" = '76483784', recorded_time = now() WHERE "schema_name" = 't_0e2c170dc92d4d05bcf533b6e719cae4' AND "table_name" = 'd20150809_x_c73db8eb0037481fa1f10371179fbf3e' AND "server_id" = '4' AND "database_name" = 'cloud' 2017-02-19 03:46:53.523 UTC [24101]: [36912-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.523 UTC [24101]: [36913-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.524 UTC [24101]: [36914-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.524 UTC [24101]: [36915-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.524 UTC [24101]: [36916-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.525 UTC [24101]: [36917-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.607 UTC [24101]: [36918-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.607 UTC [24101]: [36919-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.713 UTC [24101]: [36920-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.714 UTC [24101]: [36921-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.817 UTC [24101]: [36922-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.818 UTC [24101]: [36923-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.818 UTC [24101]: [36924-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.818 UTC [24101]: [36925-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.818 UTC [24101]: [36926-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.819 UTC [24101]: [36927-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.913 UTC [24101]: [36928-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.913 UTC [24101]: [36929-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.982 UTC [24101]: [36930-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.982 UTC [24101]: [36931-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.985 UTC [24101]: [36932-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:53.985 UTC [24101]: [36933-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device 2017-02-19 03:46:54.045 UTC [24101]: [36934-1] host=,user=,db= LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No space left on device2017-02-19 04:39:33.813 UTC [24094]: [8-1] host=,user=,db= FATAL: could not access status of transaction 0 2017-02-19 04:39:33.813 UTC [24094]: [9-1] host=,user=,db= DETAIL: Could not open file "pg_notify/0000": No space left on device. Regards, SSR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
