Hi Simon, You may want to send it to pgsql-gene...@postgresql.org. This is a pgAdmin support mailing list.
-- Thanks & Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise PostgreSQL Company <http://www.enterprisedb.com> *http://www.linkedin.com/in/asheshvashi* <http://www.linkedin.com/in/asheshvashi> On Thu, Oct 5, 2017 at 5:53 PM, Simon Major < simon.major+git...@simonmajor.me> wrote: > 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 > >
