Additional checks for orphaned files

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

The backstory: an alter tablespace failed due to space exhaustion in
pg_xlog, and it would appear to have left files in the destination
tablespace despite rolling back during recovery. After increasing disk
space in pg_xlog the moves into the new empty were subsequently
completed. At that point the suspected orphan files were discovered
due to higher than expected disk space usage in the target tablespace.

Mopping up orphan files does not seem to be covered well, so it was
left until that table space was finished with, i.e. everything that
had been altered in had been altered out, just leaving orphaned files.
It was hoped a drop tablespace would safely mop up, but no joy. So it
boils down to "Is checking pg_relation_filepath() a sufficient check
before deleting files from under Postgresql?".

Thanks,

Simon

The gory details (with changed names):

hedatabase=# select pg_class.oid, relname, nspname,
pg_relation_filepath(pg_class.oid) as primary_file_path,
pg_size_pretty(pg_relation_size(pg_class.oid)) as primary_size,
reltoastrelid, pg_relation_filepath(reltoastrelid) as toast_file_path
from pg_class inner join pg_namespace on pg_class.relnamespace =
pg_namespace.oid left join pg_tablespace on pg_class.reltablespace =
pg_tablespace.oid where pg_tablespace.spcname not like 'pg_%' or
relname like 'user_activity%' order by pg_class.relpages desc ;
   oid   |            relname            | nspname  |
primary_file_path                 | primary_size | reltoastrelid |
            toast_file_path
---------+-------------------------------+----------+--------------------------------------------------+--------------+---------------+--------------------------------------------------
   25899 | user_activity                 | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1317075 | 5262 MB      |
   27087 | pg_tblspc/1309110/PG_9.5_201510051/17552/1317078
   27087 | pg_toast_25899                | pg_toast |
pg_tblspc/1309110/PG_9.5_201510051/17552/1317078 | 5092 MB      |
       0 |
   25913 | user_activity_user_id_ts_idx  | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406888 | 365 MB       |
       0 |
 1025096 | user_activity_action_idx      | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406886 | 357 MB       |
       0 |
 1025093 | user_activity_ip_addr_idx     | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406892 | 287 MB       |
       0 |
   25912 | user_activity_ts_idx          | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406869 | 272 MB       |
       0 |
 1025110 | user_activity_api_idx         | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406882 | 268 MB       |
       0 |
 1025104 | user_activity_result_idx      | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406893 | 267 MB       |
       0 |
 1025094 | user_activity_customer_id_idx | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406877 | 255 MB       |
       0 |
   25910 | user_activity_pkey            | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1406890 | 231 MB       |
       0 |
   27089 | pg_toast_25899_index          | pg_toast |
pg_tblspc/1309110/PG_9.5_201510051/17552/1317080 | 107 MB       |
       0 |
   25897 | user_activity_id_seq          | public   | base/17552/25897
                                | 8192 bytes   |             0 |
(12 rows)

thedatabase=# vacuum full user_activity;
VACUUM

thedatabase=# select pg_class.oid, relname, nspname,
pg_relation_filepath(pg_class.oid) as primary_file_path,
pg_size_pretty(pg_relation_size(pg_class.oid)) as primary_size,
reltoastrelid, pg_relation_filepath(reltoastrelid) as toast_file_path
from pg_class inner join pg_namespace on pg_class.relnamespace =
pg_namespace.oid left join pg_tablespace on pg_class.reltablespace =
pg_tablespace.oid where pg_tablespace.spcname not like 'pg_%' or
relname like 'user_activity%' order by pg_class.relpages desc ;
   oid   |            relname            | nspname  |
primary_file_path                 | primary_size | reltoastrelid |
            toast_file_path
---------+-------------------------------+----------+--------------------------------------------------+--------------+---------------+--------------------------------------------------
   25899 | user_activity                 | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828110 | 1559 MB      |
   27087 | pg_tblspc/1309110/PG_9.5_201510051/17552/1828113
 1025096 | user_activity_action_idx      | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828121 | 83 MB        |
       0 |
   25913 | user_activity_user_id_ts_idx  | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828118 | 81 MB        |
       0 |
 1025093 | user_activity_ip_addr_idx     | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828119 | 62 MB        |
       0 |
   25912 | user_activity_ts_idx          | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828117 | 58 MB        |
       0 |
 1025104 | user_activity_result_idx      | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828122 | 58 MB        |
       0 |
 1025110 | user_activity_api_idx         | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828123 | 58 MB        |
       0 |
   25910 | user_activity_pkey            | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828116 | 58 MB        |
       0 |
 1025094 | user_activity_customer_id_idx | public   |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828120 | 58 MB        |
       0 |
   25897 | user_activity_id_seq          | public   | base/17552/25897
                                | 8192 bytes   |             0 |
   27089 | pg_toast_25899_index          | pg_toast |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828115 | 3208 kB      |
       0 |
   27087 | pg_toast_25899                | pg_toast |
pg_tblspc/1309110/PG_9.5_201510051/17552/1828113 | 151 MB       |
       0 |
(12 rows)

thedatabase=#
thedatabase=# ALTER TABLE user_activity SET TABLESPACE pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_action_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_user_id_ts_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_ip_addr_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_ts_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_result_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_api_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_pkey SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=# ALTER TABLE user_activity_customer_id_idx SET TABLESPACE
pg_default;
ALTER TABLE
thedatabase=#
thedatabase=# select pg_class.oid, relname, nspname,
pg_relation_filepath(pg_class.oid) as primary_file_path,
pg_size_pretty(pg_relation_size(pg_class.oid)) as primary_size,
reltoastrelid, pg_relation_filepath(reltoastrelid) as toast_file_path
from pg_class inner join pg_namespace on pg_class.relnamespace =
pg_namespace.oid left join pg_tablespace on pg_class.reltablespace =
pg_tablespace.oid where pg_tablespace.spcname not like 'pg_%' or
relname like 'user_activity%' order by pg_class.relpages desc ;
   oid   |            relname            | nspname | primary_file_path
 | primary_size | reltoastrelid |  toast_file_path
---------+-------------------------------+---------+--------------------+--------------+---------------+--------------------
   25899 | user_activity                 | public  |
base/17552/1828124 | 1559 MB      |         27087 | base/17552/1828125
 1025096 | user_activity_action_idx      | public  |
base/17552/1828128 | 83 MB        |             0 |
   25913 | user_activity_user_id_ts_idx  | public  |
base/17552/1828129 | 81 MB        |             0 |
 1025093 | user_activity_ip_addr_idx     | public  |
base/17552/1828130 | 62 MB        |             0 |
 1025094 | user_activity_customer_id_idx | public  |
base/17552/1828135 | 58 MB        |             0 |
 1025110 | user_activity_api_idx         | public  |
base/17552/1828133 | 58 MB        |             0 |
   25912 | user_activity_ts_idx          | public  |
base/17552/1828131 | 58 MB        |             0 |
   25910 | user_activity_pkey            | public  |
base/17552/1828134 | 58 MB        |             0 |
 1025104 | user_activity_result_idx      | public  |
base/17552/1828132 | 58 MB        |             0 |
   25897 | user_activity_id_seq          | public  | base/17552/25897
 | 8192 bytes   |             0 |
(10 rows)

thedatabase=# select pg_class.oid, relname, nspname,
pg_relation_filepath(pg_class.oid) as primary_file_path,
pg_size_pretty(pg_relation_size(pg_class.oid)) as primary_size,
reltoastrelid, pg_relation_filepath(reltoastrelid) as toast_file_path
from pg_class inner join pg_namespace on pg_class.relnamespace =
pg_namespace.oid left join pg_tablespace on pg_class.reltablespace =
pg_tablespace.oid where pg_tablespace.spcname not like 'pg_%' and
pg_relation_filepath(pg_class.oid) not like 'base%' order by
pg_class.relpages desc ;
 oid | relname | nspname | primary_file_path | primary_size |
reltoastrelid | toast_file_path
-----+---------+---------+-------------------+--------------+---------------+-----------------
(0 rows)

thedatabase=#
thedatabase=# \db
          List of tablespaces
    Name    |  Owner   |   Location
------------+----------+---------------
 pg_default | postgres |
 pg_global  | postgres |
 tmp        | postgres | /pgtblspc_tmp
(3 rows)

thedatabase=# \q
simonm@dbserver-95-01:~$ sudo find /pgtblspc_tmp -ls
        2      4 drwx------   4 postgres postgres     4096 Aug 31
18:16 /pgtblspc_tmp
   262145      4 drwx------   3 postgres postgres     4096 Aug 31
18:17 /pgtblspc_tmp/PG_9.5_201510051
   262146      4 drwx------   2 postgres postgres     4096 Oct  5
10:17 /pgtblspc_tmp/PG_9.5_201510051/17552
   262149 1048580 -rw-------   1 postgres postgres 1073741824 Aug 31
18:18 /pgtblspc_tmp/PG_9.5_201510051/17552/1309116.2
   262155   72464 -rw-------   1 postgres postgres   74203136 Aug 31
18:19 /pgtblspc_tmp/PG_9.5_201510051/17552/1309134.2
   262151     872 -rw-------   1 postgres postgres     892928 Aug 31
18:18 /pgtblspc_tmp/PG_9.5_201510051/17552/1309116_fsm
   262152      48 -rw-------   1 postgres postgres      49152 Aug 31
18:18 /pgtblspc_tmp/PG_9.5_201510051/17552/1309116_vm
   262150  307048 -rw-------   1 postgres postgres  314417152 Aug 31
18:18 /pgtblspc_tmp/PG_9.5_201510051/17552/1309116.3
   262154 1048580 -rw-------   1 postgres postgres 1073741824 Aug 31
18:19 /pgtblspc_tmp/PG_9.5_201510051/17552/1309134.1
   262147 1048580 -rw-------   1 postgres postgres 1073741824 Aug 31
18:18 /pgtblspc_tmp/PG_9.5_201510051/17552/1309116
   262153 1048580 -rw-------   1 postgres postgres 1073741824 Aug 31
18:19 /pgtblspc_tmp/PG_9.5_201510051/17552/1309134
   262148 1048580 -rw-------   1 postgres postgres 1073741824 Aug 31
18:18 /pgtblspc_tmp/PG_9.5_201510051/17552/1309116.1
       11      16 drwx------   2 root     root          16384 Aug 31
17:35 /pgtblspc_tmp/lost+found
simonm@dbserver-95-01:~$ sudo -u postgres psql thedatabase
psql (9.5.9)
Type "help" for help.

thedatabase=# DROP TABLESPACE tmp;
ERROR:  tablespace "tmp" is not empty
thedatabase=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select pg_class.oid, relname, nspname,
pg_relation_filepath(pg_class.oid) as primary_file_path,
pg_size_pretty(pg_relation_size(pg_class.oid)) as primary_size,
reltoastrelid, pg_relation_filepath(reltoastrelid) as toast_file_path
from pg_class inner join pg_namespace on pg_class.relnamespace =
pg_namespace.oid left join pg_tablespace on pg_class.reltablespace =
pg_tablespace.oid where pg_tablespace.spcname not like 'pg_%' and
pg_relation_filepath(pg_class.oid) not like 'base%' order by
pg_class.relpages desc ;
 oid | relname | nspname | primary_file_path | primary_size |
reltoastrelid | toast_file_path
-----+---------+---------+-------------------+--------------+---------------+-----------------
(0 rows)


postgres=# \c thedatabase
You are now connected to database "thedatabase" as user "postgres".
thedatabase=# SELECT relname, pg_relation_filepath(oid), relpages FROM
pg_class WHERE pg_relation_filepath(oid) =
'pg_tblspc/1309110/PG_9.5_201510051/17552/1309134';
 relname | pg_relation_filepath | relpages
---------+----------------------+----------
(0 rows)

thedatabase=# SELECT relname, pg_relation_filepath(oid), relpages FROM
pg_class WHERE pg_relation_filepath(oid) =
'pg_tblspc/1309110/PG_9.5_201510051/17552/1309116';
 relname | pg_relation_filepath | relpages
---------+----------------------+----------
(0 rows)

thedatabase=#
thedatabase=# SELECT oid, spcname FROM pg_tablespace;
   oid   |  spcname
---------+------------
    1663 | pg_default
    1664 | pg_global
 1309110 | tmp
(3 rows)

thedatabase=#
thedatabase=# \q



(originally accidentally posted to pgadmin-support@xxxxxxxxxxxxxx)


-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux