Re: [GENERAL] No space left on device - Mailing list pgsql-general

Thanks Alvaro for your suggestion !!

Regards,

Sachin Srivastava

Mb.: 9811149139

On Sun, Feb 19, 2017 at 11:40 AM, Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.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 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "Sachin Srivastava" <ssr.teleatlas@gmail.com>

To: "Alvaro Aguayo Garcia-Rada" <aaguayo@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.teleatlas@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 < aaguayo@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.teleatlas@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