Re: No space left on device

On Sun, Feb 19, 2017 at 1:19 AM, Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com>
wrote:

> Thanks Alvaro for your suggestion !!
>
> Regards,
> Sachin Srivastava
> Mb.: 9811149139
>
>
>
> On Sun, Feb 19, 2017 at 11:40 AM, Alvaro Aguayo Garcia-Rada <
> aaguayo(at)opensysperu(dot)com> wrote:
>
>> 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 <+51%201%203377813> | RPM: #034252 / (+51)
>> 995540103 <+51%20995%20540%20103> | RPC: (+51) 954183248
>> <+51%20954%20183%20248>
>> Website: www.ocs.pe
>>
>> ----- Original Message -----
>> From: "Sachin Srivastava" <ssr(dot)teleatlas(at)gmail(dot)com>
>> To: "Alvaro Aguayo Garcia-Rada" <aaguayo(at)opensysperu(dot)com>
>> Cc: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <
>> pgsql-general(at)postgresql(dot)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(dot)teleatlas(at)gmail(dot)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 <
>> aaguayo(at)opensysperu(dot)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 <+51%201%203377813> | RPM: #034252 / (+51)
>> 995540103 <+51%20995%20540%20103> | RPC: (+51) 954183248
>> <+51%20954%20183%20248>
>> Website: www.ocs.pe
>>
>>
>>
>> ----- Original Message -----
>> From: "Sachin Srivastava" < ssr(dot)teleatlas(at)gmail(dot)com >
>> To: " pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <
>> pgsql-general(at)postgresql(dot)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
>>
>>
>>
>>
>>
>>
>>
>>
> You did not specify what O/S you are using or your Postgres Version.
However, as per advised by Alvaro,
I strongly suggest you take the following additional steps.

1. Add an additional large hard drive and make a tablespace on it.
2. Move a few of your larger tables to the new tablespace.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.