Este conteúdo foi traduzido por máquina para sua conveniência e a Huawei Cloud não pode garantir que o conteúdo foi traduzido com precisão. Para exibir o conteúdo original, use o link no canto superior direito para mudar para a página em inglês.
GaussDB(DWS)
GaussDB(DWS)

    Todos os resultados de "" dentro deste produto

    Computação
    Elastic Cloud Server
    Bare Metal Server
    Auto Scaling
    Image Management Service
    Dedicated Host
    FunctionGraph
    Cloud Phone Host
    Huawei Cloud EulerOS
    Gerenciamento e governança
    Cloud Eye
    Identity and Access Management
    Cloud Trace Service
    Resource Formation Service
    Tag Management Service
    Log Tank Service
    Config
    Resource Access Manager
    Simple Message Notification
    Application Performance Management
    Application Operations Management
    Organizations
    Optimization Advisor
    Cloud Operations Center
    Resource Governance Center
    Cloud Ecosystem
    KooGallery
    Partner Center
    Análises
    MapReduce Service
    Data Lake Insight
    CloudTable Service
    Cloud Search Service
    Data Lake Visualization
    Data Ingestion Service
    GaussDB(DWS)
    DataArts Studio
    Outros
    Product Pricing Details
    System Permissions
    Console Quick Start
    Common FAQs
    Instructions for Associating with a HUAWEI CLOUD Partner
    Message Center
    Blockchain
    Blockchain Service
    Armazenamento
    Object Storage Service
    Elastic Volume Service
    Cloud Backup and Recovery
    Cloud Server Backup Service
    Storage Disaster Recovery Service
    Scalable File Service
    Volume Backup Service
    Data Express Service
    Dedicated Distributed Storage Service
    Bancos de dados
    Relational Database Service
    Document Database Service
    Data Admin Service
    Data Replication Service
    GeminiDB
    GaussDB
    Distributed Database Middleware
    Database and Application Migration UGO
    TaurusDB
    Dedicated Cloud
    Dedicated Computing Cluster
    AI
    Face Recognition Service
    Graph Engine Service
    Content Moderation
    Image Recognition
    Data Lake Factory
    Optical Character Recognition
    ModelArts
    ImageSearch
    Conversational Bot Service
    Speech Interaction Service
    Huawei HiLens
    Distribuição de conteúdo e computação de borda
    Content Delivery Network
    Intelligent EdgeFabric
    CloudPond
    Serviços para desenvolvedore
    ServiceStage
    CodeArts
    CodeArts PerfTest
    CodeArts Req
    CodeArts Pipeline
    CodeArts Build
    CodeArts Deploy
    CodeArts Artifact
    CodeArts TestPlan
    CodeArts Check
    Cloud Application Engine
    Redes
    Virtual Private Cloud
    Elastic IP
    Elastic Load Balance
    NAT Gateway
    Direct Connect
    Virtual Private Network
    VPC Endpoint
    Cloud Connect
    Enterprise Router
    Enterprise Switch
    Global Accelerator
    Migração
    Server Migration Service
    Object Storage Migration Service
    Cloud Data Migration
    Migration Center
    User Support
    My Account
    Billing Center
    Cost Center
    Resource Center
    Enterprise Management
    Service Tickets
    HUAWEI CLOUD (International) FAQs
    ICP Filing
    Support Plans
    My Credentials
    Customer Operation Capabilities
    Partner Support Plans
    Professional Services
    IoT
    IoT Device Access
    Segurança e conformidade
    Security Technologies and Applications
    Web Application Firewall
    Host Security Service
    Cloud Firewall
    SecMaster
    Anti-DDoS Service
    Data Encryption Workshop
    Database Security Service
    Cloud Bastion Host
    Data Security Center
    Cloud Certificate Manager
    Situation Awareness
    Managed Threat Detection
    Serviços de mídia
    Media Processing Center
    Video On Demand
    Live
    SparkRTC
    Containers
    Cloud Container Engine
    SoftWare Repository for Container
    Application Service Mesh
    Ubiquitous Cloud Native Service
    Cloud Container Instance
    Middleware
    Distributed Cache Service
    API Gateway
    Distributed Message Service for Kafka
    Distributed Message Service for RabbitMQ
    Distributed Message Service for RocketMQ
    Cloud Service Engine
    EventGrid
    Aplicações de negócios
    ROMA Connect
    Message & SMS
    Domain Name Service
    Edge Data Center Management
    Meeting
    Developer Tools
    SDK Developer Guide
    API Request Signing Guide
    Terraform
    Koo Command Line Interface
    Soluções
    SAP Cloud
    High Performance Computing
    MacroVerse aPaaS
    KooPhone
    KooDrive
    Computação
    Elastic Cloud Server
    Bare Metal Server
    Auto Scaling
    Image Management Service
    Dedicated Host
    FunctionGraph
    Cloud Phone Host
    Huawei Cloud EulerOS
    Redes
    Virtual Private Cloud
    Elastic IP
    Elastic Load Balance
    NAT Gateway
    Direct Connect
    Virtual Private Network
    VPC Endpoint
    Cloud Connect
    Enterprise Router
    Enterprise Switch
    Global Accelerator
    Gerenciamento e governança
    Cloud Eye
    Identity and Access Management
    Cloud Trace Service
    Resource Formation Service
    Tag Management Service
    Log Tank Service
    Config
    Resource Access Manager
    Simple Message Notification
    Application Performance Management
    Application Operations Management
    Organizations
    Optimization Advisor
    Cloud Operations Center
    Resource Governance Center
    Migração
    Server Migration Service
    Object Storage Migration Service
    Cloud Data Migration
    Migration Center
    Cloud Ecosystem
    KooGallery
    Partner Center
    User Support
    My Account
    Billing Center
    Cost Center
    Resource Center
    Enterprise Management
    Service Tickets
    HUAWEI CLOUD (International) FAQs
    ICP Filing
    Support Plans
    My Credentials
    Customer Operation Capabilities
    Partner Support Plans
    Professional Services
    Análises
    MapReduce Service
    Data Lake Insight
    CloudTable Service
    Cloud Search Service
    Data Lake Visualization
    Data Ingestion Service
    GaussDB(DWS)
    DataArts Studio
    IoT
    IoT Device Access
    Outros
    Product Pricing Details
    System Permissions
    Console Quick Start
    Common FAQs
    Instructions for Associating with a HUAWEI CLOUD Partner
    Message Center
    Segurança e conformidade
    Security Technologies and Applications
    Web Application Firewall
    Host Security Service
    Cloud Firewall
    SecMaster
    Anti-DDoS Service
    Data Encryption Workshop
    Database Security Service
    Cloud Bastion Host
    Data Security Center
    Cloud Certificate Manager
    Situation Awareness
    Managed Threat Detection
    Blockchain
    Blockchain Service
    Serviços de mídia
    Media Processing Center
    Video On Demand
    Live
    SparkRTC
    Armazenamento
    Object Storage Service
    Elastic Volume Service
    Cloud Backup and Recovery
    Cloud Server Backup Service
    Storage Disaster Recovery Service
    Scalable File Service
    Volume Backup Service
    Data Express Service
    Dedicated Distributed Storage Service
    Containers
    Cloud Container Engine
    SoftWare Repository for Container
    Application Service Mesh
    Ubiquitous Cloud Native Service
    Cloud Container Instance
    Bancos de dados
    Relational Database Service
    Document Database Service
    Data Admin Service
    Data Replication Service
    GeminiDB
    GaussDB
    Distributed Database Middleware
    Database and Application Migration UGO
    TaurusDB
    Middleware
    Distributed Cache Service
    API Gateway
    Distributed Message Service for Kafka
    Distributed Message Service for RabbitMQ
    Distributed Message Service for RocketMQ
    Cloud Service Engine
    EventGrid
    Dedicated Cloud
    Dedicated Computing Cluster
    Aplicações de negócios
    ROMA Connect
    Message & SMS
    Domain Name Service
    Edge Data Center Management
    Meeting
    AI
    Face Recognition Service
    Graph Engine Service
    Content Moderation
    Image Recognition
    Data Lake Factory
    Optical Character Recognition
    ModelArts
    ImageSearch
    Conversational Bot Service
    Speech Interaction Service
    Huawei HiLens
    Developer Tools
    SDK Developer Guide
    API Request Signing Guide
    Terraform
    Koo Command Line Interface
    Distribuição de conteúdo e computação de borda
    Content Delivery Network
    Intelligent EdgeFabric
    CloudPond
    Soluções
    SAP Cloud
    High Performance Computing
    Serviços para desenvolvedore
    ServiceStage
    CodeArts
    CodeArts PerfTest
    CodeArts Req
    CodeArts Pipeline
    CodeArts Build
    CodeArts Deploy
    CodeArts Artifact
    CodeArts TestPlan
    CodeArts Check
    Cloud Application Engine
    MacroVerse aPaaS
    KooPhone
    KooDrive

      Todos os resultados de "" dentro deste produto

      Nesta página

      Mostrar todos

      Central de ajuda/ GaussDB(DWS)/ Melhores práticas/ Gerenciamento de banco de dados/ Exibição de informações sobre tabela e banco de dados

      Exibição de informações sobre tabela e banco de dados

      Atualizado em 2024-05-09 GMT+08:00
      Ver PDF
      Compartilhar

      Consultar informações da tabela

      • Consultar informações sobre todas as tabelas em um banco de dados usando o catálogo do sistema pg_tables 
        1
        		 
        SELECT * FROM pg_tables;
      • Consultar a estrutura da tabela usando o comando \d+ da ferramenta gsql.
        Exemplo: crie uma tabela customer_t1 e insira dados na tabela. 
        1
2
3
4
5
6
7
8
9
        		 
        CREATE TABLE customer_t1
(
    c_customer_sk             integer,
    c_customer_id             char(5),
    c_first_name              char(6),
    c_last_name               char(8)
)
with (orientation = column,compression=middle)
distribute by hash (c_last_name);

        1
2
3
4
        		 
        INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES
    (6885, 'map', 'Peter'),
    (4321, 'river', 'Lily'),
    (9527, 'world', 'James');

        Consulte a estrutura da tabela. Se nenhum esquema for especificado quando você criar uma tabela, o esquema da tabela assumirá como padrão public.

         1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
        		 
        \d+ customer_t1;
                          Table "public.customer_t1"
    Column     |     Type     | Modifiers | Storage  | Stats target | Description
---------------+--------------+-----------+----------+--------------+-------------
 c_customer_sk | integer      |           | plain    |              |
 c_customer_id | character(5) |           | extended |              |
 c_first_name  | character(6) |           | extended |              |
 c_last_name   | character(8) |           | extended |              |
Has OIDs: no
Distribute By: HASH(c_last_name)
Location Nodes: ALL DATANODES
Options: orientation=column, compression=middle, colversion=2.0, enable_delta=false

        As opções podem variar em versões diferentes, mas a diferença não afeta os serviços. As opções aqui são apenas para referência. As opções reais estão sujeitas à versão.

      • Use pg_get_tabledef para consultar a definição da tabela. 
         1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
        		 
        SELECT * FROM PG_GET_TABLEDEF('customer_t1');
                                  pg_get_tabledef                                  
-----------------------------------------------------------------------------------
 SET search_path = tpchobs;                                                       +
 CREATE  TABLE customer_t1 (                                                      +
         c_customer_sk integer,                                                   +
         c_customer_id character(5),                                              +
         c_first_name character(6),                                               +
         c_last_name character(8)                                                 +
 )                                                                                +
 WITH (orientation=column, compression=middle, colversion=2.0, enable_delta=false)+
 DISTRIBUTE BY HASH(c_last_name)                                                  +
 TO GROUP group_version1;
(1 row)
      • Consultar todos os dados em customer_t1 
        1
2
3
4
5
6
7
        		 
        SELECT * FROM customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
          6885 | map           | Peter        |
          4321 | river         | Lily         |
          9527 | world         | James        |
(3 rows)
      • Consultar todos os dados de uma coluna em customer_t1 usando SELECT 
        1
2
3
4
5
6
7
        		 
        SELECT c_customer_sk FROM customer_t1;
 c_customer_sk
---------------
          6885
          4321
          9527
(3 rows)
      • Verifique se uma tabela foi analisada. A hora em que a tabela foi analisada será devolvida. Se nada for retornado, isso indica que a tabela não foi analisada. 
        1
        		 
        SELECT pg_stat_get_last_analyze_time(oid),relname FROM pg_class where relkind='r';

        Consulte a hora em que a tabela public foi analisada.

         1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
        		 
        SELECT pg_stat_get_last_analyze_time(c.oid),c.relname FROM pg_class c LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind='r' AND n.nspname='public';
 pg_stat_get_last_analyze_time |       relname
-------------------------------+----------------------
 2022-05-17 07:48:26.923782+00 | warehouse_t19
 2022-05-17 07:48:26.964512+00 | emp
 2022-05-17 07:48:27.016709+00 | test_trigger_src_tbl
 2022-05-17 07:48:27.045385+00 | customer
 2022-05-17 07:48:27.062486+00 | warehouse_t1
 2022-05-17 07:48:27.114884+00 | customer_t1
 2022-05-17 07:48:27.172256+00 | product_info_input
 2022-05-17 07:48:27.197014+00 | tt1
 2022-05-17 07:48:27.212906+00 | timezone_test
(9 rows)
      • Consulte rapidamente as informações da coluna de uma tabela. Se uma visão em information_schema tiver um grande número de objetos no banco de dados, levará muito tempo para retornar o resultado. Você pode executar a seguinte instrução SQL para consultar rapidamente as informações da coluna de uma ou mais tabelas: 
        1
        		 
        SELECT /*+ nestloop(a c)*/ c.column_name, c.data_type, c.ordinal_position, pgd.description, pp.partkey, c.is_nullable, c.column_default, c.character_maximum_length, c.numeric_precision,  c.numeric_scale, c.datetime_precision, c.interval_type, c.udt_name from information_schema.columns as c left join pg_namespace sp on sp.nspname = c.table_schema left join pg_class cla on cla.relname = c.table_name and cla.relnamespace = sp.oid left join pg_catalog.pg_partition pp on (pp.parentid = cla.oid and pp.parttype = 'r') left join pg_catalog.pg_description pgd on (pgd.objoid=cla.oid and pgd.objsubid = c.ordinal_position)where c.table_name in ('tablename') and c.table_schema = 'public';

        Por exemplo, para consultar rapidamente as informações da coluna da tabela customer_t1, execute o seguinte comando:

         1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
        		 
        SELECT /*+ nestloop(a c)*/ c.column_name, c.data_type, c.ordinal_position, pgd.description, pp.partkey, c.is_nullable, c.column_default, c.character_maximum_length, c.numeric_precision, c.numeric_scale, c.datetime_precision, c.interval_type, c.udt_name from information_schema.columns as c left join pg_namespace sp on sp.nspname = c.table_schema left join pg_class cla on cla.relname = c.table_name and cla.relnamespace = sp.oid left join pg_catalog.pg_partition pp on (pp.parentid = cla.oid and pp.parttype = 'r') left join pg_catalog.pg_description pgd on (pgd.objoid=cla.oid and pgd.objsubid = c.ordinal_position) where c.table_name in ('customer_t1') and c.table_schema = 'public';
  column_name  | data_type | ordinal_position | description | partkey | is_nullable | column_default | character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_type | udt_name
---------------+-----------+------------------+-------------+---------+-------------+----------------+--------------------------+-------------------+-------------
--+--------------------+---------------+----------
 c_last_name   | character |                4 |             |         | YES         |                |                        8 |                   |
  |                    |               | bpchar
 c_first_name  | character |                3 |             |         | YES         |                |                        6 |                   |
  |                    |               | bpchar
 c_customer_id | character |                2 |             |         | YES         |                |                        5 |                   |
  |                    |               | bpchar
 c_customer_sk | integer   |                1 |             |         | YES         |                |                          |                32 |
0 |                    |               | int4
(4 rows)
      • Obtenha a definição da tabela consultando logs de auditoria.

        Use a função pgxc_query_audit para consultar logs de auditoria de todos os CNs. A sintaxe é a seguinte:

        1
        		 
        pgxc_query_audit(timestamptz startime,timestamptz endtime)

        Consulte os registros de auditoria de vários objetos.

        1
2
        		 
        SET audit_object_name_format TO 'all';
SELECT object_name,result,operation_type,command_text FROM pgxc_query_audit('2022-08-26 8:00:00','2022-08-26 22:55:00') where command_text like '%student%';

      Consultar o tamanho da tabela

      • Consultar o tamanho total de uma tabela (índices e dados incluídos) 
        1
        		 
        SELECT pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));

        Exemplo:

        Primeiro, crie um índice em customer_t1.

        1
        		 
        CREATE INDEX index1 ON customer_t1 USING btree(c_customer_sk);

        Em seguida, consulte o tamanho da tabela customer_t1 de public.

        1
2
3
4
5
        		 
        SELECT pg_size_pretty(pg_total_relation_size('public.customer_t1'));
 pg_size_pretty
----------------
 264 kB
(1 row)
      • Consultar o tamanho de uma tabela (índices excluídos) 
        1
        		 
        SELECT pg_size_pretty(pg_relation_size('<schemaname>.<tablename>'));
        Exemplo: consulte o tamanho da tabela customer_t1 de public. 
        1
2
3
4
5
        		 
        SELECT pg_size_pretty(pg_relation_size('public.customer_t1'));
 pg_size_pretty
----------------
 208 kB
(1 row)
      • Consulte todas as tabelas, classificadas por seu espaço ocupado. 
        1
2
3
        		 
        SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit xx;
        Exemplo 1: consulte as 15 tabelas que ocupam mais espaço. 
         1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
        		 
        SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 15;
      table_full_name      |  size
---------------------------+---------
 pg_catalog.pg_attribute   | 2048 KB
 pg_catalog.pg_rewrite     | 1888 KB
 pg_catalog.pg_depend      | 1464 KB
 pg_catalog.pg_proc        | 1464 KB
 pg_catalog.pg_class       | 512 KB
 pg_catalog.pg_description | 504 KB
 pg_catalog.pg_collation   | 360 KB
 pg_catalog.pg_statistic   | 352 KB
 pg_catalog.pg_type        | 344 KB
 pg_catalog.pg_operator    | 224 KB
 pg_catalog.pg_amop        | 208 KB
 public.tt1                | 160 KB
 pg_catalog.pg_amproc      | 120 KB
 pg_catalog.pg_index       | 120 KB
 pg_catalog.pg_constraint  | 112 KB
(15 rows)
        Exemplo 2: consulte as 20 principais tabelas com o maior uso de espaço no esquema public. 
         1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
        		 
        SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_schema='public'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20;
       table_full_name       |  size
-----------------------------+---------
 public.tt1                  | 160 KB
 public.product_info_input   | 112 KB
 public.customer_t1          | 96 KB
 public.warehouse_t19        | 48 KB
 public.emp                  | 32 KB
 public.customer             | 0 bytes
 public.test_trigger_src_tbl | 0 bytes
 public.warehouse_t1         | 0 bytes
(8 rows)

      Consultar rapidamente o espaço ocupado por todas as tabelas no banco de dados

      Em um cluster grande com uma grande quantidade de dados (mais de 1000 tabelas), é aconselhável usar o modo de exibição pgxc_wlm_table_distribution_skewness para consultar todas as tabelas no banco de dados. Essa exibição pode ser usada para consultar o uso do tablespace e a distribuição de desvio de dados no banco de dados. A unidade de total_size e avg_size é byte.

      1
2
3
4
5
6
7
      		 
      SELECT *, pg_size_pretty(total_size) as tableSize FROM pgxc_wlm_table_distribution_skewness ORDER BY total_size desc;
    schema_name     |                    table_name                     | total_size | avg_size  | max_percent | min_percent | skew_percent | tablesize 
--------------------+---------------------------------------------------+------------+-----------+-------------+-------------+--------------+-----------
 public             | history_tbs_test_row_1                            |  804347904 | 134057984 |       18.02 |       15.63 |         7.53 | 767 MB
 public             | history_tbs_test_row_3                            |  402096128 |  67016021 |       18.30 |       15.60 |         8.90 | 383 MB
 public             | history_tbs_test_row_2                            |  401743872 |  66957312 |       18.01 |       15.01 |         7.47 | 383 MB
 public             | i_history_tbs_test_1                              |  325263360 |  54210560 |       17.90 |       15.50 |         6.90 | 310 MB

      O resultado da consulta mostra que a tabela history_tbs_test_row_1 ocupa o maior espaço e ocorre uma distorção de dados.

      1. A exibição pgxc_wlm_table_distribution_skewness só pode ser consultada quando os parâmetros de GUC use_workload_manager e enable_perm_space estão ativados. Em versões anteriores, você é aconselhado a usar a função table_distribution() para consultar o banco de dados inteiro. Se apenas o tamanho de uma tabela for consultado, a função table_distribution(schemaname text, tablename text) é recomendada.
      2. Em versões de cluster 8.2.1 e posteriores, o GaussDB(DWS) suporta a visualização pgxc_wlm_table_distribution_skewness, que pode ser consultada diretamente.
      3. Na versão de cluster 8.1.3, você pode usar a seguinte definição para criar um modo de exibição e, em seguida, consultar o modo de exibição:
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
      		 
      CREATE OR REPLACE VIEW
pgxc_wlm_table_distribution_skewness AS
WITH skew AS
(
SELECT
schemaname,
tablename,
pg_catalog.sum(dnsize)
AS totalsize,
pg_catalog.avg(dnsize)
AS avgsize,
pg_catalog.max(dnsize)
AS maxsize,
pg_catalog.min(dnsize)
AS minsize,
(maxsize
- avgsize) * 100 AS skewsize
FROM
pg_catalog.gs_table_distribution()
GROUP
BY schemaname, tablename
)
SELECT
    schemaname AS schema_name,
    tablename AS table_name,
    totalsize AS total_size,
    avgsize::numeric(1000) AS avg_size,
    (
        CASE
            WHEN totalsize = 0 THEN 0.00
            ELSE (maxsize * 100 /
totalsize)::numeric(5, 2)
        END
    ) AS max_percent,
    (
        CASE
            WHEN totalsize = 0 THEN 0.00
            ELSE (minsize * 100 /
totalsize)::numeric(5, 2)
        END
    ) AS min_percent,
    (
        CASE
            WHEN totalsize = 0 THEN 0.00
            ELSE (skewsize /
maxsize)::numeric(5, 2)
        END
    ) AS skew_percent
FROM skew;

      Consultar informações do banco de dados

      • Consultar a lista de banco de dados usando o meta-comando \l da ferramenta gsql. 
         1
 2
 3
 4
 5
 6
 7
 8
 9
10
        		 
        \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
 gaussdb   | Ruby  | SQL_ASCII | C       | C     |
 template0 | Ruby  | SQL_ASCII | C       | C     | =c/Ruby           +
           |       |           |         |       | Ruby=CTc/Ruby
 template1 | Ruby  | SQL_ASCII | C       | C     | =c/Ruby           +
           |       |           |         |       | Ruby=CTc/Ruby
(3 rows)
        • Se os parâmetros LC_COLLATE e LC_CTYPE não forem especificados durante a instalação do banco de dados, os valores padrão deles serão C.
        • Se LC_COLLATE e LC_CTYPE não forem especificados durante a criação do banco de dados, a ordem de classificação e a classificação de caracteres do banco de dados de modelo serão usadas por padrão.

          Para obter detalhes, consulte CREATE DATABASE.

      • Consultar a lista do banco de dados usando o catálogo do sistema PG_DATABASE 
        1
2
3
4
5
6
7
        		 
        SELECT datname FROM pg_database;
  datname
-----------
 template1
 template0
 gaussdb
(3 rows)

      Consultar o tamanho do banco de dados

      Consultar o tamanho dos bancos de dados 
      1
      		 
      select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

      Exemplo:

      1
2
3
4
5
6
7
      		 
      select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
  datname  | pg_size_pretty
-----------+----------------
 template1 | 61 MB
 template0 | 61 MB
 postgres  | 320 MB
(3 rows)

      Consultar o tamanho de uma tabela e o tamanho do índice correspondente em um esquema especificado

      SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;
      Tópico principal: Gerenciamento de banco de dados

      Usamos cookies para aprimorar nosso site e sua experiência. Ao continuar a navegar em nosso site, você aceita nossa política de cookies. Saiba mais

      Feedback

      Feedback

      Feedback

      0/500

      Conteúdo selecionado

      Envie o conteúdo selecionado com o feedback