Dicas gerais de tablespace

<< Click to Display Table of Contents >>

Navigation:  PostgreSQL > Avançado > Tablespace >

Dicas gerais de tablespace

Previous pageReturn to chapter overviewNext page

Saber quais são tablespace da base, seu tamanho e onde apontam

 

select spcname as tablespace,

       pg_size_pretty(pg_tablespace_size(spcname)) as tamanho,

       spclocation as caminho

from pg_tableSpace;

 

Result

 

   tablespace   | tamanho |    caminho

----------------+---------+----------------

 pg_default     | 13 MB   |

 pg_global      | 359 kB  |

 datapg         | 25 MB   | d:/temp/datapg

 meu_tablespace | 4256 kB | d:/temp/local

(4 rows)

 

Montar um script para transferir tabelas de tablespace

 

SELECT 'ALTER TABLE', n.nspname AS schemaname, '.', c.relname AS tablename, 'SET TABLESPACE banco_data;'

FROM pg_class c

     LEFT JOIN pg_namespace  n ON n.oid = c.relnamespace

     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

WHERE c.relkind = 'r' ::char AND

      nspname NOT IN ('dbateste''information_schema''pg_catalog',

                      'pg_temp_1-''pg_toast''postgres''publico''public')

ORDER BY n.nspname

 

Result

 

  ?column?   | schemaname | ?column? |            tablename            |          ?column?

-------------+------------+----------+---------------------------------+----------------------------

 ALTER TABLE | banco      | .        | tb_banco_tipo_hist              | SET TABLESPACE banco_data;

 ALTER TABLE | banco      | .        | tb_conta_hist_numero            | SET TABLESPACE banco_data;

 ALTER TABLE | banco      | .        | tb_banco_mvto                   | SET TABLESPACE banco_data;

 ALTER TABLE | banco      | .        | tb_banco_tarifa                 | SET TABLESPACE banco_data;

 ALTER TABLE | bb         | .        | tb_vip239_ag_vencida15          | SET TABLESPACE banco_data;

 ALTER TABLE | bb         | .        | tb_vip239_inc_rest05            | SET TABLESPACE banco_data;

 ALTER TABLE | bb         | .        | tb_bb_hist_cartao_solicita      | SET TABLESPACE banco_data;

...

 

Saber cada tabela em qual tablespace está

 

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS Tablespace

FROM pg_class c

     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

WHERE c.relkind = 'r'::char AND

      nspname NOT IN ('dbateste''information_schema',' pg_catalog'

                      'pg_temp_1-','pg_toast',' postgres''publico',' public')

ORDER BY n.nspname, c.relname

 

Result

 

 schemaname |            tablename            | tablespace

------------+---------------------------------+------------

 banco      | tb_agencia                      |

 banco      | tb_banco                        |

 banco      | tb_banco_conta                  |

 banco      | tb_conta_hist_numero            |

 bb         | tb_arquivo_vip238               |

 bb         | tb_bb_lote_etiqueta             |

 bb         | tb_bb_lote_etiqueta_item        |

 bb         | tb_bb_nsu                       |

 

Saber quais tabelas não tem tablespace

 

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS Tablespace

FROM pg_class c

     LEFT JOIN pg_namespace  n ON n.oid = c.relnamespace

     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

WHERE c.relkind = 'r' ::char AND

      nspname NOT IN ('dbateste''information_schema''pg_catalog',

                      'pg_temp_1-''pg_toast''postgres''publico''public') AND

      t.spcname IS NULL

ORDER BY t.spcname DESC

 

Result

 

 schemaname |            tablename            | tablespace

------------+---------------------------------+------------

 sistema    | tb_sys_grupo                    |

 produto    | tb_produto_codbarra             |

 banco      | tb_banco_mvto                   |

 banco      | tb_banco_tarifa                 |

 banco      | tb_conta_hist_numero            |

 produto    | tb_divisao                      |

 bb         | tb_arquivo_vip239               |

 bb         | tb_bb_cdc_nsu                   |

 sistema    | tb_sys_parametro                |

 

Verificar quais são os índices (não primários) e o tamanho (em mb)

 

SELECT n.nspname AS schemaname,

        c.relname AS tablename,

        c.relpages::numeric * 4.096 / 1024::numeric AS espaco_mb

 FROM pg_class c

      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

      LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

      LEFT JOIN pg_index x ON x.indexrelid = c.oid

 WHERE c.relkind = 'i' ::char AND

       x.indisprimary != 't' AND

       x.indisunique != 't' AND

       nspname NOT IN ('dbateste''information_schema''pg_catalog',

                       'pg_temp_1-','pg_toast''postgres''publico''public')

 ORDER BY n.nspname

 

Result

 

 schemaname |                            tablename                            | espaco_mb

------------+-----------------------------------------------------------------+----------

 banco      | idx_tb_banco_mvto_cd_banco_mvto_tipo_hist_nr_doc                |     0.13

 cadastro   | idx_tb_cliente_bloqueio_cliente_tp_bloqueio_dt_bloq             |     45.6

 cadastro   | tb_cliente_dep_idx                                              |      0.2

 cadastro   | idx_tb_base_documento_cd_base_cliente_tipo_doc                  |     1.11

 cadastro   | idx_tb_base_nm_base                                             |     3.28

 cartao     | idx_tb_cartao_cd_base_cliente                                   |     0.53

 historico  | idx_tb_hist_pagar_dt_hist                                       |     0.79

 historico  | idx_tb_hist_receber_dt_hist                                     |     0.02

 movimento  | idx_tb_nota_fiscal_item_cd_codbarra                             |    57.10

 movimento  | idx_tb_nota_fiscal_item_nr_produto                              |    55.19

 movimento  | idx_tb_saida_item_dh_alt                                        |     2.79

 movimento  | idx_tb_saida_item_nr_produto                                    |     2.79

 

Gerar script para mudar índices de tablepace

 

SELECT 'ALTER INDEX', n.nspname AS schemaname, '.', c.relname AS tablename, 'SET TABLESPACE banco_idx;'

 FROM pg_class c

      LEFT JOIN pg_namespace  n ON n.oid = c.relnamespace

      LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

      LEFT JOIN pg_index      x ON x.indexrelid = c.oid

 WHERE c.relkind = 'i' ::char AND

       x.indisprimary != 't' AND

       x.indisunique != 't' AND

       nspname NOT IN ('dbateste''information_schema''pg_catalog',

                       'pg_temp_1-','pg_toast''postgres''publico''public')

 ORDER BY n.nspname

 

Result

 

?column?    schemaname    ?column?_1                                        tablename      ?column?_2

ALTER INDEX banco       . idx_tb_banco_mvto_cd_banco_mvto_tipo_hist_nr_doc  SET TABLESPACE banco_idx;

ALTER INDEX cadastro    . idx_tb_base_contato_cd_base_contato_tp_princ      SET TABLESPACE banco_idx;

ALTER INDEX cartao      . idx_tb_cartao_cd_base_cliente                     SET TABLESPACE banco_idx;

ALTER INDEX historico   . idx_tb_hist_pagar_dt_hist                         SET TABLESPACE banco_idx;

ALTER INDEX movimento   . idx_tb_nota_fiscal_item_cd_codbarra               SET TABLESPACE banco_idx;

 

Exibe o tablespace dos índices

 

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS Tablespace

 FROM pg_class c

      LEFT JOIN pg_namespace  n ON n.oid = c.relnamespace

      LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

      LEFT JOIN pg_index      x ON x.indexrelid = c.oid

 WHERE c.relkind = 'i' ::char AND x.indisprimary != 't' AND

       x.indisunique != 't' AND

       nspname NOT IN ('dbateste''information_schema''pg_catalog',

                       'pg_temp_1-','pg_toast''postgres''publico''public')

 ORDER BY n.nspname

 

Result

 

 schemaname |                            tablename                            | tablespace

------------+-----------------------------------------------------------------+------------

 banco      | idx_tb_banco_mvto_cd_banco_mvto_tipo_hist_nr_doc                |

 cadastro   | idx_tb_base_contato_cd_base_contato_tp_princ                    |

 cadastro   | idx_tb_cliente_bloqueio_cliente_tp_bloqueio_dt_bloq             |

 cadastro   | tb_cliente_dep_idx                                              |

 cadastro   | idx_tb_base_contato_cd_base                                     |

 cadastro   | idx_tb_base_documento_cd_base_cliente_tipo_doc                  |

 cadastro   | idx_tb_base_nm_base                                             |

 cartao     | idx_tb_cartao_cd_base_cliente                                   |

 historico  | idx_tb_hist_pagar_dt_hist                                       |

 historico  | idx_tb_hist_receber_dt_hist                                     |

 movimento  | idx_tb_trans_is_liberado                                        |

 

Exibe índices sem tablespace

 

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS Tablespace

 FROM pg_class c

      LEFT JOIN pg_namespace  n ON n.oid = c.relnamespace

      LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

      LEFT JOIN pg_index      x ON x.indexrelid = c.oid

 WHERE c.relkind = 'i' ::char AND x.indisprimary != 't' AND

       x.indisunique != 't' AND

       nspname NOT IN ('dbateste''information_schema''pg_catalog',

                       'pg_temp_1-''pg_toast''postgres''publico''public') AND

       t.spcname IS NULL

 ORDER BY t.spcname DESC

 

Result

 

 schemaname |                            tablename                            | tablespace

------------+-----------------------------------------------------------------+------------

 movimento  | idx_tb_nota_fiscal_item_nr_produto                              |

 movimento  | idx_tb_nota_fiscal_item_cd_codbarra                             |

 cadastro   | idx_tb_base_contato_cd_base_contato_tp_princ                    |

 cadastro   | idx_tb_cliente_bloqueio_cliente_tp_bloqueio_dt_bloq             |

 movimento  | idx_tb_entrada_item_nr_produto                                  |

 movimento  | idx_tb_n_f_dupl_cd_n_f_dupl                                     |

 receber    | idx_tb_receber_baixa_cd_estorno_baixa                           |

 movimento  | idx_tb_saida_cd_depto                                           |

 movimento  | idx_tb_saida_item_dh_alt                                        |

 movimento  | idx_tb_saida_item_nr_produto                                    |