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
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.
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
