[ADMIN] Additional checks for orphaned files

Поиск
Список
Период
Сортировка
От	Simon Major
Тема	[ADMIN] Additional checks for orphaned files
Дата	5 октября 2017 г. 20:53:57
Msg-id	CALsCcGHv45O8cG9=edBaX0UO+VoFx30OeRpaOho_E=Q1aocMrw@mail.gmail.com обсуждение исходный текст
Список	pgsql-admin
Дерево обсуждения
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/18281131025096| 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/18281251025096 | 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_global1309110 | tmp
(3 rows)

thedatabase=#
thedatabase=# \q



(originally accidentally posted to pgadmin-support@postgresql.org)


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
В списке pgsql-admin по дате отправления:
Предыдущее

От: Günce Kaya
Дата: 05 октября 2017 г., 20:19:45
Сообщение: Re: [ADMIN] Postgresql FDW - some difference between remote andtarget table structure
Следующее

От: Don Seiler
Дата: 05 октября 2017 г., 20:56:49
Сообщение: Re: [ADMIN] psql can't connect to old DB after installing new binaries
Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

[ADMIN] Additional checks for orphaned files

Предыдущее

Следующее
