Additional checks for orphaned files

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(at)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(at)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