Re: Additional checks for orphaned files

Hi Simon,

You may want to send it to pgsql-general(at)postgresql(dot)org(dot)
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(dot)major+github(at)simonmajor(dot)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(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
>
>