Categoria: PostgreSQL

Introdução

O pg_catalog é um schema especial no PostgreSQL que guarda todos os segredos do seu banco de dados. Quer saber como suas tabelas são organizadas? Lá podemos encontrar essa e outras respostas.

Neste artigo, vamos dar uma olhada nesse recurso, descobrindo como ele funciona, o que você pode encontrar nele e como pode ser útil para você.

Roteiro:

• O que é o pg_catalog?
• Principais tabelas e views do cátalogo;
• Exemplos de junções de tabelas do catálogo;
• Information schema;
• Objetos físicos e lógicos;
• Objetos globais e locais.

O que é?

Como dito anteriormente, o pg_catalog é um schema interno e padrão do PostgreSQL que guarda o catálogo do sistema, que nada mais é do que um conjunto de tabelas que contém metadados sobre o banco de dados. Essas tabelas registram informações cruciais, como a estrutura das tabelas, índices, tipos de dados, usuários e permissões.

Todos os bancos de dados do PostgreSQL possuem essas tabelas, pois, no momento da criação de um novo banco, elas são copiadas de um template (modelo), que vem dos bancos padrão template0 e template1 (existem dois desses, pois em um é possível fazer modificações, e no outro, não, o que garante uma cópia de segurança do template).

Abaixo seguem visualizações de consultas dos bancos de dados existentes, em que podemos ver o template0 e template1. Primeiro, vamos consultar de uma forma já ensinada no artigo de psql, com o comando \l (as três últimas colunas da saída foram ocultadas para melhor visualização):

postgres@postgres# \l
                                   List of databases
     Name      |    Owner     | Encoding | Locale Provider | Collate |  Ctype  |   
---------------+--------------+----------+-----------------+---------+---------+
 aplicacao     | app_admin    | UTF8     | libc            | C.UTF-8 | C.UTF-8 |             
 pagila        | admin_pagila | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 postgres      | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 template0     | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 template1     | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 | 
(5 rows)

Agora, vamos consultar pela tabela pg_database, a qual compõe o catálogo do sistema:

[local]: postgres@postgres=# SELECT datname FROM pg_database;
  datname
-----------
 postgres
 template1
 template0
 pagila
 aplicacao
(5 rows)

Apesar de, na prática, parecerem comandos diferentes, por baixo dos panos ambos fazem a mesma coisa, que é a consulta da tabela pg_database. Para observar isso, podemos ativar a variável ECHO_HIDDEN, que mostra as consultas realizadas nos comandos do psql. Para ativar, basta rodar:

[local]: postgres@postgres=# \set ECHO_HIDDEN on

E, então, executar o \l novamente e, assim, vermos o que é feito de forma oculta para nos trazer a listagem de banco de dados:

[local]: postgres@postgres=# \l
********* QUERY **********
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  d.daticulocale as "ICU Locale",
  d.daticurules as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
                                                      List of databases
   Name    |    Owner     | Encoding | Locale Provider | Collate |  Ctype  | 
 ---------+--------------+----------+-----------------+---------+----------+
 aplicacao | app_admin    | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 pagila    | admin_pagila | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 postgres  | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 | 
 template0 | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
 template1 | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |
(5 rows)

A variável ECHO_HIDDEN também pode ser usada dessa forma para os diversos outros comandos do psql, possibilitando a visibilidade da importância dos dados presentes nas tabelas do catálogo do sistema. Recomendamos que teste sem moderação!

Principais tabelas e views do catálogo

pg_authid

Essa tabela armazena informações sobre os usuários de autenticação (no PostgreSQL utilizamos o termo “roles” em inglês tanto para usuários como para grupos de usuários). Ela contém detalhes como nome de usuário, tipo de papel (usuário ou grupo), senha criptografada (se aplicável) e outras informações relacionadas à autenticação e a permissões de acesso. Você pode acessar a documentação dessa tabela por aqui. Entre as principais colunas, temos:

• rolname: nome do usuário ou grupo de usuários;
• rolsuper: indica se o usuário tem privilégios de superusuário;
• rolcreaterole: indica se o usuário tem o privilégio para criar outros usuários ou grupos;
• rolcreatedb: indica se o usuário pode criar bancos de dados;
• rolcanlogin: mostra se o usuário pode efetuar login como um usuário ou se apenas se comporta como um grupo de usuários;
• rolreplication: indica se o usuário pode ser usado para replicação de dados.

pg_roles

Essa view do sistema também usa dados da tabela pg_authid para fornecer informações sobre todas as roles (usuários e grupo de usuários) do banco de dados, a diferença é a ocultação completa da senha (na outra vemos ela criptografada). A documentação oficial pode ser acessada aqui.

pg_database

Essa tabela contém uma linha para cada banco de dados no sistema PostgreSQL. Ela armazena várias informações importantes sobre cada banco, incluindo seu nome, dono e configurações específicas. Sua documentação oficial pode ser encontrada aqui. As principais colunas são:

• datname: nome do banco de dados;
• datdba: OID (Object Identifier ou Identificador de Objeto) do dono do banco de dados (esse OID fica na tabela pg_authid, que vimos anteriormente);
• encoding: codificação de caracteres do banco de dados;
• datctype: tipo de caracteres padrão do banco de dados;
• datistemplate: indica se o banco de dados pode ser usado como um template (modelo), conforme os bancos template0 e template1;
• datallowconn: indica se conexões ao banco de dados são permitidas, uma vez que, por padrão, não são permitidas conexões no template0;
• datconnlimit: limite máximo de conexões ao banco de dados;
• dattablespace: OID da tablespace em que o banco está;
• datacl: lista de controle de acesso do banco de dados.

pg_namespace 

Essa tabela armazena informações sobre os schemas no banco de dados. Os schemas são usados para organizar objetos como tabelas, índices, funções etc. no PostgreSQL, fornecendo uma estrutura lógica de organização e controle de permissões como se fosse pastas no banco de dados. A documentação oficial pode ser conferida aqui. As colunas dessa tabela são:

• oid: identificador do schema;
• nspname: nome do schema;
• nspowner: OID do usuário dono do schema (faz ponte com a tabela pg_authid);
• nspacl: lista o controle de acesso do schema.

pg_class

Nessa tabela, temos diversas informações sobre tabelas e outros objetos similares (como views, índices etc.). É possível ver uma descrição de cada coluna dessa tabela aqui na documentação oficial. Algumas das colunas que destacamos são:  

• oid: número identificador do objeto;
• relname: nome do objeto;
• relnamespace: OID (identificador) do schema ao qual esse objeto pertence (referência pg_namespace);
• relowner: OID do dono do objeto (referência pg_authid);
• relfilenode: número identificador do arquivo físico que armazena o objeto em questão (que geralmente é igual ao OID, citado no início da lista. Citaremos adiante um caso em que eles não são iguais);
• reltablespace: OID do tablespace onde o objeto está armazenado. Se for 0, o objeto está no tablespace padrão do banco de dados (referência pg_tablespace);
• relpages: número de páginas de disco usado pelo objeto (cada página tem por padrão 8 kb, e se o objeto for maior do que isso, ocupará múltiplos desse valor em número de páginas. Por exemplo, um objeto de 36 kb ocupará 5 páginas, não 4,5);
• reltuples: número de linhas na tabela;
• relkind: uma letra que indica o tipo do objeto:
  • r = tabela, 
  • i = índice 
  • s = sequência etc.
• relacl: lista de controle de acesso do objeto, ou seja, mostra os usuários e suas permissões de acesso ao objeto;
• reloptions: opções de ajustes para otimização de objetos.

pg_attribute

Armazena informações sobre cada atributo, ou seja, a coluna, de todas as tabelas do banco de dados, incluindo nome das colunas, tipo de dados, restrições etc. A documentação oficial está aqui. Suas principais colunas são:

• attrelid: o OID da tabela ao qual essa coluna pertence (referência pg_class);
• attname: o nome da coluna;
• attypid: o OID do tipo de dado dessa coluna (referência pg_type);
• attnotnull: indica se a coluna foi definida como ‘NOT NULL’;
• atthasdef: indica se a coluna tem valor padrão definido.

pg_type

Nessa tabela, encontramos informações sobre os tipos de dados do banco, que podem ser de tabelas, colunas etc. Sua documentação está nessa página. As principais colunas são:

• typname: nome do tipo de dado;
• typnamespace: OID do schema que contém o tipo de dado em questão (referência pg_namespace);
• typlen: tamanho em bytes do tipo de dado;
• typtype: indica o tipo de dado, ou seja, muda conforme o objeto ao qual está se referenciando, sendo b para tipo base, c para tipo composto (o caso das tabelas), d para domínio etc.

pg_constraints

Essa tabela armazena informações sobre as restrições (constraints) definidas em diferentes objetos do banco de dados, como tabelas. As restrições são regras que impõem integridade de dados e outras condições para garantir que os dados no banco de dados estejam consistentes e de acordo com as regras de negócio. A documentação oficial está aqui. Principais colunas:

• conname: nome da constraint;
• connamespace: OID do esquema ao qual a constraint pertence (referente a pg_namespace);
• contype: tipo da restrição, como PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK etc.;
• conrelid: OID da tabela à qual a restrição está associada (referente a pg_class).

pg_proc

Essa tabela armazena informações sobre as funções e os procedimentos (functions e procedures em inglês) definidos no banco de dados. Sua documentação está aqui. Algumas das principais colunas são:

• proname: nome da função;
• prolang: linguagem na qual a função é escrita (referente a pg_language);
• prorettype: tipo de retorno da função (referente a pg_type);
• proargtypes: tipos dos argumentos da função (referente a pg_type).

pg_sequence

Armazena informações sobre as sequências definidas no banco de dados. Sequências são objetos especiais usados para gerar números únicos e sequenciais, frequentemente utilizados para colunas de chave primária artificiais. A documentação pode ser achada aqui. Suas colunas são:

• seqrelid: OID (identificador) do objeto da sequência em questão (referente a pg_class);
• seqtypid: tipo de dados da sequência, por exemplo, integer, bigint etc. (referente a pg_type);
• seqstart: valor inicial da sequência;
• seqincrement: incremento da sequência;
• seqcache: número de valores da sequência a serem cacheados (colocados em cache);
• seqcycle: indica se a sequência cicla ao atingir o valor máximo/mínimo (t para TRUE/f para FALSE).

pg_stats

Essa view fornece um acesso mais amigável às estatísticas armazenadas na tabela pg_statistic, que conta com estatísticas sobre o conteúdo de cada coluna de cada tabela do banco. Essas estatísticas são usadas pelo otimizador de consultas para gerar planos de execução eficientes. A documentação oficial está nesta página, já a página da pg_statistic você encontra aqui. As principais colunas da pg_stats são:

• schemaname: nome do esquema da tabela;
• tablename: nome da tabela;
• attname: nome da coluna;
• avg_width: largura média dos valores da coluna (em bytes);
• most_common_vals: valores mais comuns na coluna;
• most_common_freqs: frequências dos valores mais comuns.

pg_settings

Essa view nos permite visualizar parâmetros de tempo de execução do servidor, ou seja, nos permite visualizar e modificar parâmetros de configuração em tempo real. A documentação pode ser encontrada aqui. Além disso, temos um artigo antigo sobre configurações do PostgreSQL citando essa tabela, podendo ser interessante para ver exemplos.
Suas principais colunas são:

• name: nome do parâmetro;
• setting: valor atual do parâmetro;
• category: grupo lógico do parâmetro;
• short_desc: descrição resumida do parâmetro.

Exemplos de junção (JOIN) de tabelas do catálogo

Tabelas pg_database e pg_authid

Abaixo temos um exemplo que nos mostra os seguintes dados:

• Tabela pg_database:
  • OID e nome do banco;
  • OID do usuário dono do banco.
• Tabela pg_authid:
  • Nome do usuário em questão.

[local]: postgres@postgres=# SELECT 
d.oid, 
d.datname, 
d.datdba, 
a.rolname 
FROM pg_database d 
JOIN pg_authid a ON d.datdba = a.oid;

  oid  |      datname      | datdba |   rolname
-------+-------------------+--------+--------------
 18206 | readme_to_recover |     10 | postgres
 18203 | banco_tblspc      |     10 | postgres
     4 | template0         |     10 | postgres
     1 | template1         |     10 | postgres
     5 | postgres          |     10 | postgres
 16539 | aplicacao         |  16520 | app_admin
 17660 | pagila            |  17189 | admin_pagila
(7 rows)

Tabelas pg_class, pg_attribute e pg_type

Agora, vamos ver uma junção com os dados de tabelas, colunas da tabela e tipos de dados:

• Tabela pg_class:
  • OID e nome do objeto;
  • OID do tipo de dado na tabela pg_type.
• Tabela pg_attribute:
  • Número da coluna (posição dela em relação à tabela);
  • Nome da coluna.
• Tabela pg_type:
  • Nome do tipo de dado;
  • Tipo de dado do dado, que varia conforme o objeto em questão. Aqui vamos utilizar a cláusula CASE WHEN para nomear os objetos tipo base explicitamente.

SELECT
    c.oid AS oid_tab,
    c.relname AS nome_tab,
    a.attnum AS num_col,
    a.attname AS nome_col,
    t.oid AS oid_tipo,
    t.typname AS nome_tipo,
    CASE
        WHEN t.typtype = 'b' THEN 'tipo base'
        END AS tipo
FROM pg_class c
JOIN pg_attribute a ON c.oid = a.attrelid
JOIN pg_type t ON a.atttypid = t.oid
WHERE 
c.relname = 'pg_class' 
AND a.attnum > 0
ORDER BY 
c.relname, 
a.attnum;

    oid_tab |   nome_tab  | num_col  |   nome_col  | oid_tipo |nome_tipo |   tipo
------------+-------------+------------+---------------------+----------+------------+
       1259 | pg_class    |        1 | oid         |       26 | oid      | tipo base
       1259 | pg_class    |        2 | relname     |       19 | name     | tipo base
       1259 | pg_class    |        3 |relnamespace |       26 | oid      | tipo base
       1259 | pg_class    |        4 | reltype     |       26 | oid      | tipo base
       1259 | pg_class    |        5 | reloftype   |       26 | oid      | tipo base
       1259 | pg_class    |        6 | relowner    |       26 | oid      | tipo base
       1259 | pg_class    |        7 | relam       |       26 | oid      | tipo base
       1259 | pg_class    |        8 | relfilenode |       26 | oid      | tipo base
       1259 | pg_class    |        9 |reltablespace|       26 | oid      | tipo base
       ...

information_schema

O information_schema é um schema padronizado definido pelo ISO que fornece uma maneira portável e independente do fornecedor do banco de dados para se obter informações sobre os objetos do banco de dados, assim como o pg_catalog, rodando, na verdade, em cima dele, utilizando-o como fonte de dados. Esse schema é também suportado por muitos outros sistemas de gerenciamento de banco de dados (SGBDs). A documentação oficial do PostgreSQL pode ser encontrada aqui.

Assim como através das tabelas do catálogo, aqui podemos listar todas as tabelas de um determinado schema utilizando uma tabela do information_schema:

localhost: dba@aplicacao=# SELECT 
table_schema, 
table_name 
FROM information_schema.tables 
ORDER BY table_schema;

    table_schema    |              table_name
--------------------+---------------------------------------
 information_schema | collations
 information_schema | information_schema_catalog_name
 information_schema | applicable_roles
 information_schema | domain_constraints
 information_schema | administrable_role_authorizations
 information_schema | collation_character_set_applicability
 information_schema | attributes
 information_schema | character_sets
 information_schema | column_udt_usage
 information_schema | check_constraint_routine_usage
 information_schema | column_column_usage
...

As principais diferenças entre o information_schema e o pg_catalog são:

• Padrão: conforme citado, o information_schema segue o padrão ISO SQL e é compatível com diversos outros servidores, já o pg_catalog é específico do PostgreSQL;
• Nomenclaturas: como o information_schema segue o padrão ISO SQL, é notável que seus objetos têm nomenclaturas mais simples e explícitas, diferente do pg_catalog, em que há termos mais técnicos e específicos do Postgres;
• Nível de detalhe: o information_schema fornece uma visão mais simplificada dos metadados do banco, enquanto o pg_catalog traz informações mais específicas e de baixo nível sobre os objetos do banco de dados;
• Informações sensíveis: no pg_catalog é possível encontrar informações sensíveis, por isso é um schema mais utilizado pelos DBAs, ao passo que o information_schema foi projetado para acesso público, ocultando informações sensíveis como senhas.

A seguir, está uma maneira de listar todas as tabelas do information_schema (informação que pode ser encontrada na documentação disponibilizada anteriormente) utilizando o pg_catalog:

localhost: dba@aplicacao=# SELECT 
c.relname AS table_name 
FROM pg_class c 
JOIN pg_namespace n ON n.oid = c.relnamespace 
WHERE 
n.nspname = 'information_schema' 
AND c.relkind = 'v' 
ORDER BY c.relname;

              table_name
---------------------------------------
 administrable_role_authorizations
 applicable_roles
 attributes
 character_sets
 check_constraint_routine_usage
 check_constraints
 collation_character_set_applicability
 collations
 column_column_usage
 column_domain_usage
 column_options
 column_privileges
 column_udt_usage
 columns
 constraint_column_usage
 constraint_table_usage
 data_type_privileges
...

Objetos físicos e lógicos

Objetos físicos referem-se aos componentes do banco de dados que têm representação física no sistema de arquivos do PostgreSQL, ou seja, além de estarem presentes no banco, também são representados por arquivos em disco. Alguns dos principais objetos físicos são:

• Tabelas (incluindo, portanto, as do catálogo);
• Ínidices;
• Tablespaces;
• Databases etc.

Podemos encontrar esses objetos nas pastas nomeadas base e global, a depender de serem globais ou locais. Mas não se preocupe, no próximo tópico do artigo, explicaremos mais sobre objetos globais e locais.
Abaixo, seguem exemplos de arquivos de objetos, que são nomeados com números, presentes no diretório global no PGDATA: 

$ ls -lha $PGDATA/global
total 644K
drwx------  2 postgres postgres 4.0K May 23 17:56 .
drwx------ 19 postgres postgres 4.0K Apr  9 18:41 ..
...
-rw-------  1 postgres postgres 8.0K May 16 14:44 1260
-rw-------  1 postgres postgres  24K Mar 27 15:39 1260_fsm
-rw-------  1 postgres postgres 8.0K Mar 27 15:49 1260_vm
-rw-------  1 postgres postgres 8.0K May 13 19:14 1261
-rw-------  1 postgres postgres  24K Mar 27 15:39 1261_fsm
-rw-------  1 postgres postgres 8.0K Apr 22 15:11 1261_vm
-rw-------  1 postgres postgres 8.0K Jun  4 19:45 1262
...
-rw-------  1 postgres postgres 8.0K Jun  4 19:45 pg_control
-rw-------  1 postgres postgres  524 Mar 27 15:39 pg_filenode.map
-rw-------  1 postgres postgres  29K May 23 17:56 pg_internal.init

A seguir, estão os arquivos dos objetos que se encontram no diretório base, também no PGDATA:

# ls -lha $PGDATA/base
total 76K
drwx------ 11 postgres postgres 4.0K Jun  4 19:40 .
drwx------ 19 postgres postgres 4.0K Apr  9 18:41 ..
drwx------  2 postgres postgres 4.0K May 13 18:50 1
drwx------  2 postgres postgres 4.0K Apr  9 19:11 16398
drwx------  2 postgres postgres  12K Apr 15 18:50 16449
drwx------  2 postgres postgres  12K May 23 17:55 16539
drwx------  2 postgres postgres 4.0K May  8 13:46 16645
drwx------  2 postgres postgres  12K May 16 12:37 17660
drwx------  2 postgres postgres 4.0K Mar 27 15:39 4
drwx------  2 postgres postgres  12K May 23 18:05 5
drwx------  2 postgres postgres 4.0K May 23 17:55 pgsql_tmp

Já os objetos lógicos são abstrações que não têm uma representação física direta no sistema de arquivos, mas são gerenciadas pelo PostgreSQL. Alguns desses objetos são schemas, views, funções etc.

Objetos globais e locais

Objetos globais são aqueles que não pertencem a um banco de dados específico e podem ser acessados por qualquer banco de dados de uma instância no PostgreSQL. É possível encontrá-los no diretório global no PGDATA, que vimos antes: 

$ ls -lha $PGDATA/global
total 644K
drwx------  2 postgres postgres 4.0K May 23 17:56 .
drwx------ 19 postgres postgres 4.0K Apr  9 18:41 ..
...
-rw-------  1 postgres postgres 8.0K May 16 14:44 1260
-rw-------  1 postgres postgres  24K Mar 27 15:39 1260_fsm
-rw-------  1 postgres postgres 8.0K Mar 27 15:49 1260_vm
-rw-------  1 postgres postgres 8.0K May 13 19:14 1261
-rw-------  1 postgres postgres  24K Mar 27 15:39 1261_fsm
-rw-------  1 postgres postgres 8.0K Apr 22 15:11 1261_vm
-rw-------  1 postgres postgres 8.0K Jun  4 19:45 1262
...
-rw-------  1 postgres postgres 8.0K Jun  4 19:45 pg_control
-rw-------  1 postgres postgres  524 Mar 27 15:39 pg_filenode.map
-rw-------  1 postgres postgres  29K May 23 17:56 pg_internal.init

É importante notar que os nomes dos arquivos não são números aleatórios: geralmente eles são o mesmo número OID do objeto em questão. Por exemplo, nesta saída temos o arquivo 1262 e, através de uma consulta a uma tabela do catálogo do sistema no psql, a pg_class (nela é possível encontrar informações de alguns objetos do banco de dados, como tabelas, índices, views etc.), e buscando pelo OID 1262, é possível descobrir que esse arquivo representa a tabela pg_database, conforme saída da consulta a seguir:

postgres@postgres=# SELECT 
oid,
relname 
FROM pg_class 
WHERE oid = 1262;

 oid  |   relname
------+-------------
 1262 | pg_database
(1 row)

Nessa consulta, utilizamos as colunas relisshared e relkind como filtro para encontrar as tabelas do catálogo na tabela pg_class. Isso porque a coluna relisshared é de valor booleano, sendo verdadeira para indicar uma tabela global (do catálogo do sistema), e a coluna relkind traz o tipo de objeto, sendo ‘r’ representativo de tabelas. A seguir podemos ver outras tabelas globais do catálogo pela pg_class:

postgres@postgres=# SELECT 
oid,
relname 
FROM pg_class 
WHERE 
relisshared 
AND relkind = 'r';

 oid  |        relname
------+-----------------------
 1260 | pg_authid
 6100 | pg_subscription
 1262 | pg_database
 2964 | pg_db_role_setting
 1213 | pg_tablespace
 1261 | pg_auth_members
 1214 | pg_shdepend
 2396 | pg_shdescription
 6000 | pg_replication_origin
 3592 | pg_shseclabel
 6243 | pg_parameter_acl
(11 rows)

Outro exemplo de objeto global são as tablespaces, que são locais de armazenamento que contêm dados de bancos PostgreSQL fora do diretório base, o PGDATA. Aqui criamos uma chamada novaTablespace e um banco de dados dentro dela, chamado banco_tblspc.

Além do local onde a tablespace foi criada, também podemos encontrá-la via link simbólico no caminho $PGDATA/pg_tblspc/: 

# ls -l $PGDATA/pg_tblspc/
total 0
lrwxrwxrwx 1 postgres postgres 26 Jun 11 17:22 18202 -> /var/lib/postgresql/tblspc

Dentro dela, temos mais um diretório, que abriga o nosso banco recém-criado:

# ls -lha $PGDATA/pg_tblspc/18202
drwx------ 3 postgres postgres 4.0K Jun 11 17:22 .
drwxr-xr-x 6 postgres postgres 4.0K Jun 11 17:34 ..
drwx------ 3 postgres postgres 4.0K Jun 11 17:34 PG_16_202307071

Então, chegamos no diretório oficial do banco, que vai abrigar os objetos criados nele:

# ls -lha $PGDATA/pg_tblspc/18202/PG_16_202307071/
total 12K
drwx------ 3 postgres postgres 4.0K Jun 11 17:34 .
drwx------ 3 postgres postgres 4.0K Jun 11 17:22 ..
drwx------ 2 postgres postgres 4.0K Jun 11 17:34 18203

O número que nomeia o arquivo do banco também é o OID desse objeto, o que facilita encontrá-lo na pg_database, por exemplo:

[local]: postgres@postgres=# SELECT * FROM pg_database WHERE oid = 18203;
-[ RECORD 1 ]--+-------------
oid            | 18203
datname        | banco_tblspc
datdba         | 10
encoding       | 6
datlocprovider | c
datistemplate  | f
datallowconn   | t
datconnlimit   | -1
datfrozenxid   | 722
datminmxid     | 1
dattablespace  | 18202
datcollate     | C.UTF-8
datctype       | C.UTF-8
daticulocale   | (null)
daticurules    | (null)
datcollversion | (null)
datacl         | (null)

E também podemos encontrar nossa tablespace na tabela pg_tablespace:

[local]: postgres@postgres=# select * from pg_tablespace;
  oid  |    spcname     | spcowner | spcacl | spcoptions
-------+----------------+----------+--------+------------
  1663 | pg_default     |       10 | (null) | (null)
  1664 | pg_global      |       10 | (null) | (null)
 18202 | novatablespace |       10 | (null) | (null)
(3 rows)

Já os locais são objetos pertencentes a um banco de dados específico e só podem ser acessados dentro desse banco. Ficam no diretório base, no PGDATA. A seguir, podemos ver a listagem de arquivos desse diretório:

# ls -lha $PGDATA/base
total 72K
drwx------ 10 postgres postgres 4.0K Jun  6 03:44 .
drwx------ 19 postgres postgres 4.0K Apr  9 18:41 ..
drwx------  2 postgres postgres 4.0K May 13 18:50 1
drwx------  2 postgres postgres  12K Apr 15 18:50 16449
drwx------  2 postgres postgres  12K May 23 17:55 16539
drwx------  2 postgres postgres 4.0K May  8 13:46 16645
drwx------  2 postgres postgres  12K May 16 12:37 17660
drwx------  2 postgres postgres 4.0K Mar 27 15:39 4
drwx------  2 postgres postgres  12K May 23 18:05 5
drwx------  2 postgres postgres 4.0K May 23 17:55 pgsql_tmp

Ao entrar nesse diretório, diferente da global em que já vemos os arquivos dos objetos, temos mais um nível de diretórios representando cada banco de dados existente na instância do PostgreSQL, e é dentro de cada um deles que estão armazenados os objetos locais do banco. É possível checar a qual banco de dados cada diretório é referente consultando a tabela pg_database, novamente usando os OIDs de cada objeto para identificá-los:

postgres@postgres=# SELECT oid, datname FROM pg_database;
  oid  |  datname
-------+-----------
     5 | postgres
     1 | template1
     4 | template0
 17660 | pagila
 16539 | aplicacao
(5 rows)

Vamos listar os arquivos do diretório do banco criado há alguns artigos, o aplicacao, diretório 16539:

# ls -lha $PGDATA/base/16539/
total 7.9M
drwx------ 2 postgres postgres  12K May 23 17:55 .
drwx------ 8 postgres postgres 4.0K Jun  6 03:56 ..
-rw------- 1 postgres postgres 8.0K Apr 22 15:41 112
...
-rw------- 1 postgres postgres 8.0K May 10 12:49 16649
-rw------- 1 postgres postgres 8.0K May  8 17:43 16656
-rw------- 1 postgres postgres  16K May  8 17:43 16660
-rw------- 1 postgres postgres 8.0K May 16 12:14 16662
-rw------- 1 postgres postgres 8.0K May 16 12:14 16663
-rw------- 1 postgres postgres  16K May 16 12:14 16667
-rw------- 1 postgres postgres 8.0K May  8 17:43 16690
-rw------- 1 postgres postgres 8.0K May  9 13:44 16691
-rw------- 1 postgres postgres  16K May  8 17:43 16695
-rw------- 1 postgres postgres 8.0K May  8 17:43 16697
-rw------- 1 postgres postgres 8.0K May  8 17:43 16698
-rw------- 1 postgres postgres  16K May  8 17:43 16702
-rw------- 1 postgres postgres 8.0K May  8 17:43 16704
-rw------- 1 postgres postgres 8.0K May  8 17:43 16705
-rw------- 1 postgres postgres  16K May  8 17:43 16709
-rw------- 1 postgres postgres 8.0K Apr 22 15:41 174
-rw------- 1 postgres postgres 8.0K Apr 22 15:41 175
-rw------- 1 postgres postgres 8.0K May 16 12:39 18131
-rw------- 1 postgres postgres 8.0K May 16 12:39 18132
-rw------- 1 postgres postgres  16K May 16 12:39 18136
-rw------- 1 postgres postgres 8.0K May 16 12:44 18140
-rw------- 1 postgres postgres 8.0K May 16 12:44 18141
-rw------- 1 postgres postgres  16K May 16 12:44 18145
-rw------- 1 postgres postgres 8.0K May 16 14:39 18148
-rw------- 1 postgres postgres 8.0K May 16 14:39 18149
-rw------- 1 postgres postgres  16K May 16 14:39 18153
...
-rw------- 1 postgres postgres    3 Apr 22 15:40 PG_VERSION
-rw------- 1 postgres postgres  524 Apr 22 15:40 pg_filenode.map
-rw------- 1 postgres postgres 156K May 23 17:55 pg_internal.init

A consulta realizada para encontrar o objeto correspondente no catálogo do psql (conectando agora no próprio banco de dados aplicacao) foi:

dba@aplicacao=#  SELECT 
oid,
relname 
FROM pg_class 
WHERE NOT relisshared AND relkind = 'r' AND relname LIKE '%tabela%';

  oid  |   relname
-------+--------------
 18149 | tabela6_app1
 16691 | tabela1_app2
 16656 | tabela2_app1
 16663 | tabela3_app1
 16698 | tabela2_app2
 16705 | tabela3_app2
 16649 | tabela1_app1
 18132 | tabela4_app1
 18141 | tabela5_app1
(9 rows)

Aqui, podemos ver os OIDs de todas as tabelas desse banco, que também são identificáveis como arquivos na nossa consulta no diretório 16539 (banco aplicacao).
Entre os demais arquivos nesse diretório, temos outras tabelas do catálogo, índices, schemas etc.

Mas e quando os OIDs de tabelas não são iguais aos números que nomeiam seus arquivos? Um dos casos é quando utilizamos o comando TRUNCATE, que serve para remover todas as linhas de uma tabela e também pode mudar o relfilenode, já que o PostgreSQL pode optar por recriar fisicamente a tabela para otimização da operação.

Confira uma consulta dos dados da tabela na pg_class antes do TRUNCATE:

localhost: dba@aplicacao=# SELECT 
oid, 
relname, 
relfilenode 
FROM pg_class 
WHERE relname = 'tabela1_app1';

  oid  |   relname    | relfilenode
-------+--------------+-------------
 16649 | tabela1_app1 |       16649
(1 row)

Execução do TRUNCATE:

localhost: dba@aplicacao=# TRUNCATE TABLE aplicacao1.tabela1_app1;
TRUNCATE TABLE

Nova consulta dos dados da tabela na pg_class, após o TRUNCATE:

localhost: dba@aplicacao=# SELECT 
oid, 
relname, 
relfilenode 
FROM pg_class 
WHERE relname = 'tabela1_app1';

  oid  |   relname    | relfilenode
-------+--------------+-------------
 16649 | tabela1_app1 |       18204
(1 row)

Isso implica termos um novo arquivo para representar essa tabela no diretório do banco de dados, nomeado com a numeração do novo relfilenode:

$ ls -lha $PGDATA/base/16539/
total 7.9M
drwx------ 2 postgres postgres  12K Jun 11 18:28 .
drwx------ 8 postgres postgres 4.0K Jun  6 03:56 ..
...
-rw------- 1 postgres postgres    0 Jun 11 18:25 18204
...
-rw------- 1 postgres postgres    3 Apr 22 15:40 PG_VERSION
-rw------- 1 postgres postgres  524 Apr 22 15:40 pg_filenode.map
-rw------- 1 postgres postgres 156K Jun 11 18:27 pg_internal.init

Conclusão

Exploramos o pg_catalog, um componente crucial do PostgreSQL que armazena metadados essenciais para a administração do banco de dados. Compreender tabelas e views como pg_authid, pg_database e pg_class permite realizar auditorias, otimizações e controle de acessos com eficácia.

O uso de comandos como \l e a variável ECHO_HIDDEN desmistifica operações internas, proporcionando maior controle e entendimento. O pg_catalog é uma ferramenta poderosa que garante a integridade e a performance do seu sistema de banco de dados, o que é essencial para qualquer DBA ou desenvolvedor.