postgres postgresql database db

Como visto antes, uma instância do PostgreSQL é a unidade sobre a qual operamos para fins de administração de sistemas, como monitoramento, replicação, autenticação e backup. Contudo, essa não é a granularidade adequada para os fins da aplicação, que tem um interesse mais voltado à manipulação (armazenamento e consulta) dos dados.

Um banco de dados é uma segmentação tanto física quanto lógica da instância, destinada a um propósito único, como atender uma aplicação específica. Isso significa que os dados são armazenados fisicamente em outros diretórios (segmentação física) e que conexões são estabelecidas com exatamente um banco de dados e não têm acesso aos dados de outros bancos de dados (segmentação lógica).

Podem ser criados objetos locais dentro de um banco de dados, como tabelas, visões, índices e sequências. Contudo, objetos globais, como usuários, tablespaces, slots de replicação e outros bancos de dados, apesar de poderem ser criados através de uma conexão com um banco de dados, não são criados dentro daquele banco de dados, mas sim na instância.

                     ┌───────────────────┐
╔════════════════════╡ instância/cluster ╞═════════════════════╗
║                    └───────────────────┘                     ║
╠═╡global/╞═══════╗  ╔═════════════════════════════════╡base/╞═╣
║                 ║  ║                                         ║
║     O     O     ║  ║ ┏┥banco de dados┝┓ ┏━┥banco de dados┝━┓ ║
║    ─┼─   ─┼─    ║  ║ ┃└┤postgres├────┘┃ ┃ ┌─┤schema├─────┐ ┃ ║
║     │     │     ║  ║ ┗━━━━━━━━━━━━━━━━┛ ┃ │ ☑ tabelas    │ ┃ ║
║    ╱ ╲   ╱ ╲    ║  ║ ┏┥banco de dados┝┓ ┃ │ ☑ índices    │ ┃ ║
║ ☑ usuários      ║  ║ ┃└┤template1├───┘┃ ┃ │ ☑ visões     │ ┃ ║
║ ☑ grupos        ║  ║ ┗━━━━━━━━━━━━━━━━┛ ┃ │ ☑ sequências │ ┃ ║
║ ☑ slots de      ║  ║ ┏┥banco de dados┝┓ ┃ │ ☑ …          │ ┃ ║
║ ☑ replicação    ║  ║ ┃└┤template0├───┘┃ ┃ └──────────────┘ ┃ ║
║ ☑ pg_control    ║  ║ ┗━━━━━━━━━━━━━━━━┛ ┗━━━━━━━━━━━━━━━━━━┛ ║
║                 ║  ║                                         ║
╠═════════════════╝  ╚═════════════════════════════════════════╣
║                                                              ║
╠═╡pg_wal/╞══════════════════════════╗     ╔══════╡pg_tblspc/╞═╣
║ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐    ║     ║                   ║
║ │…++++│→│…++++│→│…++++│→│…++++│→…  ║     ║                   ║
║ └┤/…1├┘ └┤/…2├┘ └┤/…3├┘ └┤/…4├┘    ║     ║        │          ║
╚════════════════════════════════════╩═════╩════════╪══════════╝
                                                    │
                                                    ↓
                                                 ╔╡/…╞╗
                                                 ║    ║
                                                 ╚════╝

É necessário que exista um banco de dados e um usuário para que a autenticação permita a nova conexão. É necessária uma conexão ativa para criar um banco de dados e um usuário. Para quebrar esse impasse de galinha-e-ovo, toda nova instância é criada automaticamente com um banco de dados chamado postgres e um usuário com permissão SUPERUSER (este com nome igual ao usuário que executou o initdb, normalmente também postgres).

O propósito do usuário postgres e do banco de dados postgres é, portanto, permitir que outros usuários e bancos de dados sejam criados. É recomendado que eles não sejam usados para outros fins, mas sim que outros usuários (com permissões reduzidas) e bancos de dados sejam usados para atender as diversas aplicações, assim como atividades de backup, monitoramento e replicação.

Ao criar um novo banco de dados, o PostgreSQL sempre copia um modelo pré-existente. Quando o modelo não é informado, o template1 é usado. Isso não só traz agilidade na criação de bancos de dados, como também permite que o usuário customize os modelos, adicionando objetos e dados que são importados em todo novo banco de dados e economizando tempo de criá-los em todo novo banco de dados.

O terceiro banco de dados criado com a inicialização da instância é o template0, que atende dois propósitos. Por um lado, ele pode ser usado para recriar o template1 se as customizações deste precisarem ser descartadas. Por outro, o template0 é o único banco de dados com a codificação de caracteres indefinida e, por essa razão, quando é copiado, uma codificação pode ser escolhida para o novo banco de dados.

IMPORTANTE: Note que a codificação recomendada para todos os propósitos no servidor é UTF8 (outras são mantidas para compatibilidade com o passado); e a aplicação deve configurar client_encoding para cada conexão, assim conversões entre codificações de cliente e servidor são feitas de forma transparente e confiável.

Listagem dos bancos de dados existentes, com seus respectivos donos, codificações, privilégios, tamanhos e outras propriedades:

[[local]:5432] postgres@postgres=# \l+
                                                                 List of databases
╔═══════════╤═══════════╤══════════╤═════════╤═════════╤═══════════════════════╤═════════╤════════════╤════════════════════════════════════════════╗
║   Name    │   Owner   │ Encoding │ Collate │  Ctype  │   Access privileges   │  Size   │ Tablespace │                Description                 ║
╠═══════════╪═══════════╪══════════╪═════════╪═════════╪═══════════════════════╪═════════╪════════════╪════════════════════════════════════════════╣
║ postgres  │ postgres  │ UTF8     │ C.UTF-8 │ C.UTF-8 │                       │ 8097 kB │ pg_default │ default administrative connection database ║
║ template0 │ postgres  │ UTF8     │ C.UTF-8 │ C.UTF-8 │ =c/postgres          ↵│ 7841 kB │ pg_default │ unmodifiable empty database                ║
║           │           │          │         │         │ postgres=CTc/postgres │         │            │                                            ║
║ template1 │ postgres  │ UTF8     │ C.UTF-8 │ C.UTF-8 │ postgres=CTc/postgres↵│ 7953 kB │ pg_default │ default template for new databases         ║
║           │           │          │         │         │ =c/postgres           │         │            │                                            ║
╚═══════════╧═══════════╧══════════╧═════════╧═════════╧═══════════════════════╧═════════╧════════════╧════════════════════════════════════════════╝

É possível impedir conexões novas a um banco de dados alterando a propriedade ALLOW_CONNNECTIONS, mas conexões já estabelecidas permanecem, podendo ser fechadas com pg_terminate_backend():

[[local]:5432] postgres@postgres=# ALTER DATABASE app ALLOW_CONNECTIONS FALSE;

Também é possível limitar quantas conexões um banco de dados terá estabelecidas a todo momento:

[[local]:5432] postgres@postgres=# ALTER DATABASE app CONNECTION LIMIT 5;

Por padrão, apenas usuários com a permissão de SUPERUSER podem usar todos os bancos de dados como modelos na criação de novos bancos de dados. Mas podemos marcar bancos de dados específicos com a propriedade IS_TEMPLATE para que todo usuário com permissão CREATEDB possa usar aquele banco de dados como modelo:

[[local]:5432] postgres@postgres=# ALTER DATABASE modelo IS_TEMPLATE TRUE;

[[local]:5432] postgres@postgres=# CREATE DATABASE app;

[[local]:5432] postgres@postgres=# CREATE DATABASE modelo_app;
[[local]:5432] postgres@postgres=# \c modelo_app
[[local]:5432] postgres@modelo_app=# CREATE TABLE tabela ();
[[local]:5432] postgres@modelo_app=# \d
         List of relations
╔════════╤════════╤═══════╤══════════╗
║ Schema │  Name  │ Type  │  Owner   ║
╠════════╪════════╪═══════╪══════════╣
║ public │ tabela │ table │ postgres ║
╚════════╧════════╧═══════╧══════════╝

[[local]:5432] postgres@postgres=# CREATE DATABASE app TEMPLATE modelo_app;
[[local]:5432] postgres@postgres=# \c app
[[local]:5432] postgres@app=# \d
         List of relations
╔════════╤════════╤═══════╤══════════╗
║ Schema │  Name  │ Type  │  Owner   ║
╠════════╪════════╪═══════╪══════════╣
║ public │ tabela │ table │ postgres ║
╚════════╧════════╧═══════╧══════════╝

[[local]:5432] postgres@postgres=# DROP DATABASE modelo_app;
[[local]:5432] postgres@postgres=# DROP DATABASE app;