Administração do

SGBD PostgreSQL
Unidade 2
Arquitetura e funcionamento do PostgreSQL
 Diretor Executivo
DAVID LIRA STEPHEN BARROS
Gerente Editorial
CRISTIANE SILVEIRA CESAR DE OLIVEIRA
Projeto Gráfico
TIAGO DA ROCHA
Autoria
CAMILA FREITAS SARMENTO
 AUTORIA
Camila Freitas Sarmento
Sou formada em Telemática com mestrado em Ciência da
Computação e, atualmente, doutoranda na área de Engenharia de Software,
com uma experiência técnico-profissional na área de Soluções Digitais
(Redes e Programação Back-End) de mais de sete anos. Atualmente sou
Analista de Informática – Programadora Web Back-End no Instituto Senai de
Tecnologia em Automação Industrial (IST-Senai). Fui professora substituta
na Universidade Estadual da Paraíba (UEPB) e atuei como professora
substituta e, posteriormente, como tutora no Instituto Federal de Educação,
Ciência e Tecnologia da Paraíba (IFPB). Sou apaixonada pelo que faço e
adoro transmitir minha experiência àqueles que estão iniciando em suas
profissões. Por isso fui convidada pela Editora Telesapiens a integrar seu
elenco de autores independentes. Estou muito feliz em poder ajudar você
nesta fase de muito estudo e trabalho. Conte comigo!
 ICONOGRÁFICOS
Olá. Esses ícones irão aparecer em sua trilha de aprendizagem toda vez
que:

OBJETIVO: DEFINIÇÃO:
para o início do houver necessidade
desenvolvimento de de se apresentar um
uma nova compe- novo conceito;
tência;

NOTA: IMPORTANTE:
quando forem as observações
necessários obser- escritas tiveram que
vações ou comple- ser priorizadas para
mentações para o você;
seu conhecimento;
EXPLICANDO VOCÊ SABIA?
MELHOR: curiosidades e
algo precisa ser indagações lúdicas
melhor explicado ou sobre o tema em
detalhado; estudo, se forem
necessárias;
SAIBA MAIS: REFLITA:
textos, referências se houver a neces-
bibliográficas e links sidade de chamar a
para aprofundamen- atenção sobre algo
to do seu conheci- a ser refletido ou dis-
mento; cutido sobre;
ACESSE: RESUMINDO:
se for preciso aces- quando for preciso
sar um ou mais sites se fazer um resumo
para fazer download, acumulativo das últi-
assistir vídeos, ler mas abordagens;
textos, ouvir podcast;
ATIVIDADES: TESTANDO:
quando alguma quando o desen-
atividade de au- volvimento de uma
toaprendizagem for competência for
aplicada; concluído e questões
forem explicadas;
 SUMÁRIO
Banco de dados e índices......................................................................... 12
O banco de dados........................................................................................................................... 12

Conectando ao usuário de conexão do banco de dados no

Windows.............................................................................................................................. 12

Conectando ao usuário de conexão do banco de dados no

Linux....................................................................................................................................... 16

O pgAdmin......................................................................................................................... 18

Índices...................................................................................................................................................... 22

Schemas – tablespaces e gerenciamento de usuários................ 27

Schemas ................................................................................................................................................27

Tablespaces.........................................................................................................................................35

Gerenciamento de usuários.................................................................................................... 36

Roles.......................................................................................................................................37

Privilégios........................................................................................................................... 38

Inicialização e interrupção do PostgreSQL....................................... 41

Inicialização do PostgreSQL..................................................................................................... 41

Arquivo [Link]..............................................................................................................42

Localização de arquivos..............................................................................................................42

Conexões e autenticação........................................................................................43

SSL...........................................................................................................................................44

Recursos de consumo..............................................................................................45

Atraso de vácuo baseado em custo.............................................................. 46

Relatório e registro de erros................................................................................ 46

Interrupção do PostgreSQL......................................................................................................47
Carga de dados no PostgreSQL.............................................................50
Transações PostgreSQL............................................................................................................. 50

Uso do COPY FROM e ANALYSE......................................................................................... 59

 Administração do SGBD PostgreSQL 9

02
UNIDADE
10 Administração do SGBD PostgreSQL

INTRODUÇÃO
Você sabia que o PostgreSQL é o Sistema Gerenciador de
Banco de Dados de código aberto mais utilizado no mundo? É isso
mesmo! Agora você sabe que as chances de uma empresa contratar
um profissional que domine esse assunto são bem maiores por ser
uma das mais demandadas na indústria. Sabia também que apesar
da linguagem ser, relativamente, antiga (1986) ela está em constante
evolução? Pois é! O grupo PostgreSQL permite que seus utilizadores
contribuam com a correção de erros e melhorias da linguagem e,
posteriormente, que os avaliadores lancem um novo release. Quem
sabe você não será o próximo profissional a sugerir modificações? Mas
para isso, vamos aprender tudo direitinho? A começar com a leitura do
material. Vamos lá? Ao longo desta unidade letiva você vai mergulhar
neste universo!
 Administração do SGBD PostgreSQL 11

OBJETIVOS
Olá. Seja muito bem-vindo à Unidade II. Nosso objetivo é auxiliar
você no desenvolvimento das seguintes competências profissionais até o
término desta etapa de estudos:

1. Entender o conceito e a finalidade dos índices em um banco de

dados PostgreSQL.

2. Gerenciar usuários e seus espaços de dados e esquemas no

PostgreSQL.

3. Inicializar e interromper um banco de dados PostgreSQL.

4. Realizar carga de dados no PostgreSQL, visando processos de

migração e povoamento de dados para testes.

Então, preparado para uma viagem sem volta rumo ao

conhecimento? Vamos conhecer mais desse assunto incrível e mais
utilizado no mundo! Você não vai ficar de fora dessa, não é? Ao trabalho!
12 Administração do SGBD PostgreSQL

Banco de dados e índices

OBJETIVO:
Ao término deste capítulo você será capaz de entender
como funciona o PostgreSQL e compreender a estrutura
de funcionamento por meio da criação de tabelas de banco
de dados e compreender conceitos e implementação de
índices. Isso será fundamental para o exercício de sua
profissão. As pessoas que tentaram até conseguiram
êxito na criação, mas sem a devida instrução e com uma
alta carga de dados, talvez o sistema possa precisar de
constantes ajustes e isso poderá colocar o sistema de
uma empresa inacessível por horas. E isso, caso seja
uma empresa totalmente on-line, cada minuto é crucial
para a sustentabilidade de todos dentro do ecossistema
empresa-funcionário-cliente. Agora a responsabilidade é
toda sua para implementar e manter uma boa estruturação
do banco de dados. E então, motivado(a) para desenvolver
essa competência? Então vamos lá. Avante!

O banco de dados
Antes de aprendermos qualquer manipulação com o banco de
dados é essencial saber como manipular os arquivos de configuração que
controlam as operações básicas de uma instância do servidor PostgreSQL.

Dessa forma, mostraremos como realizar a configuração de

conexão do usuário PostgreSQL ao banco de dados no Windows e Linux.
Nesse caso, você poderá ir para a seção de seu interesse. Posteriormente,
vamos te mostrar como criar as tabelas de dados no banco de dados.

Conectando ao usuário de conexão do banco de

dados no Windows
Se você já executou o processo de instalação no seu computador,
vai ser fácil localizar o SQL Shell (psql). Basta localizar no Windows e abrir
o terminal, veja na figura 1.
 Administração do SGBD PostgreSQL 13

Figura 1 – SQL Shell no Windows

Fonte: Elaborado pela autora (2021).

Agora, vamos te mostrar como realizar a conexão. Ao abrir o terminal

do psql, você vai ter de escolher o servidor, o banco de dados e a senha
configurada, mas não se preocupe. É super fácil, pois você pode iniciar
com a configuração padrão, isto é, algumas partes você pode aceitar a
sugestão e pressionar somente a tecla Enter. Vamos te mostrar:

1. Ao abrir o terminal irá aparecer a mensagem: Server localhost.

Nesse caso, é só apertar a tecla Enter do teclado que você
aceitará a configuração para o servidor local.

2. O próximo passo é a escolha da base de dados. No terminal terá

Database postgres. Você pode aceitar apertando Enter.

Nota: O postgres é o superusuário do PostgreSQL que já vem, por

padrão, da instalação.
14 Administração do SGBD PostgreSQL

3. Essa é a etapa da escolha da porta. Sugerimos deixar a porta

padrão (que é a 5432) e para aceitar, basta pressionar a tecla
Enter.

4. Agora, você precisa escolher o usuário. Nesse caso, você pode

colocar o usuário que você criou durante a instalação pelo
terminal que você praticou na Unidade passada, está lembrado(a)
de como fazer? Caso contrário, pode deixar a forma padrão
mesmo, só pressionando a tecla Enter.

5. Este é um momento importante: a senha. Utilize a mesma que

você configurou quando criou o usuário.

E então, você executou o passo a passo na sua máquina também?

Ficou conforme a figura 2? O passo final é quando você entra na base de
dados. Sabe como verificar? Vai aparecer no terminal (veja na figura 2) o
postgres=#.
Figura 2 – Terminal após a inserção das informações de configuração

Fonte: Elaborado pela autora (2021).

Prontinho! Viu como é super fácil? Se você tiver alguma dúvida,

poderá digitar help diretamente no terminal que aparecerá uma série de
sugestões de ajuda para você. Veja na figura 3 as sugestões de ajuda do
terminal.
 Administração do SGBD PostgreSQL 15

Figura 3 – Sugestões de ajuda do terminal

Fonte: Elaborado pela autora (2021).

Como podemos ver na figura 3, temos algumas opções de ajuda

que você pode digitar:

• \ para os termos de distribuição.

• \h para ajuda com os comandos SQL.

• \? Para ajuda com os comandos do psql.

• \g ou terminar com um ponto e vírgula para executar uma query.

• \q para sair.

Caso você tenha digitado alguns comandos e não conseguir sair

da tela, pode pressionar as teclas ctrl + C para voltar ao postgres=#.
Compreendido isso, já podemos partir para a criação das tabelas no
banco de dados, preparado(a)? Antes disso, vamos ver como funciona a
mesma configuração em um Sistema Linux.
16 Administração do SGBD PostgreSQL

Conectando ao usuário de conexão do banco de

dados no Linux
Como você já sabe, na instalação do PostgreSQL no Linux, o
usuário do PostgreSQL já vem, por padrão, como super usuário postgre
(isto é, com permissões de root do sistema). Sendo assim, vamos começar
e nos conectar ao usuário postgre? Para começar, você precisa digitar
no terminal o comando de superusuário e entrar no usuário do postgres
executando os seguintes passos:

1. Digite no terminal do Linux o comando: sudo su - postgres

e pressione a tecla Enter. Nisso, você entrará no usuário do
postgres para executar o próximo passo.

2. Entre na ferramenta padrão de acesso ao PostreSQL digitando:

psql conforme figura 4.
Figura 4 – Comando para acesso ao PostgreSQL

Fonte: Elaborado pela autora (2021).

Perceba que o nome do usuário é postgres=#. O # significa que

ele é um superusuário e isso é necessário para outras manipulações do
banco de dados que iremos ver. Para um usuário comum, a representação
seria a seguinte: usuário=>.

Prontinho! Não é fácil e super tranquilo? Tem dúvida e deseja ver

qual o usuário que está conectado? Então você poderá digitar select
current_user; e depois pressione a tecla Enter que já poderá ver o usuário
ativo conforme a figura 5.
 Administração do SGBD PostgreSQL 17

Figura 5 – Identificando usuário ativo

Fonte: Elaborado pela autora (2021).

Para verificar se houve a conexão com o PostgreSQL e de forma

correta, você pode digitar select inet_server_port(). Assim, você verificará
qual o retorno obtido. Veja a progressão dos comandos na figura 6.
Figura 6 – Identificando ativação

Fonte: Elaborado pela autora (2021).

Ainda, podemos verificar em qual porta o servidor está executando

(ver figura 7). Para isso, podemos verificar por meio do seguinte comando:
select current_database();.
Figura 7 – Identificando ativação

Fonte: Elaborado pela autora (2021).

18 Administração do SGBD PostgreSQL

Para o caso da observação de versões do PostgreSQL, podemos

coletar informações do sistema operacional, do banco de dados etc.
Vamos ver quais informações o comando select version(); pode nos
retornar? Vamos analisar as Figuras 8 e 9 e responder: o que podemos
compreender das versões com base nesse comando?
Figura 8 – Comando para visualizar as versões

Fonte: Elaborado pela autora (2021).

Figura 9 – Informações de versões

Fonte: Elaborado pela autora (2021).

Você fez a mesma análise que eu? Vejamos: podemos observar que
temos um PostgreSQL na versão 9.5.7 com o kernel Linux x86_64 em um
sistema operacional Ubuntu server na versão 16.04.4. Então, compreendeu
tudo? Vamos agora compreender o que é o pgadmin, para que serve e
qual a sua relação com o PostgreSQL.

O pgAdmin
De acordo com o próprio site do pgAdmin ([Link]), o pgAdmin
é a plataforma de desenvolvimento e administração de código aberto
mais popular e rica em recursos para PostgreSQL, o banco de dados de
código aberto mais avançado do mundo e que pode ser manuseado em
Linux, Unix, MacOS e Windows.

O cliente pgAdmin pode acessar múltiplos servidores PostgreSQL

e um servidor PostgreSQL também pode ser acessado por vários clientes
pgAdmin simultaneamente.
 Administração do SGBD PostgreSQL 19

Ainda, o pgAdmin pode ser instalado por ambiente gráfico,

automaticamente, durante a instalação do PostgreSQL no Windows ou
via terminal e instalando as dependências no Linux. Ficou curioso(a) de
como fazer a instalação no Linux? Vamos ver o passo a passo!

1. Digite no seu terminal Linux o comando: sudo apt-get install

python2.7-dev virtualenv python-pip libpq-dev. Esse comando
irá instalar as dependências para o funcionamento.
Figura 9 – Instalação das dependências no Linux

Fonte: Elaborado pela autora (2021).

2. Agora, vamos criar um diretório do boot com o comando: mkdir

-p ~/apps/pgadmin4/ e em seguida entrar no diretório com o
comando cd apps/pdadmin4/ (ver figura 10) para a instalação
dos demais repositórios e, posteriormente, a inicialização da
interface gráfica.
Figura 10 – Criação do diretório do boot

Fonte: Elaborado pela autora (2021).

Lembrando que o pgadmin4 é a versão atual do pgAdmin,

futuramente, você poderá se deparar com novas versões.
20 Administração do SGBD PostgreSQL

3. Depois de criado o diretório, iremos utilizar o comando virtualenv

venv -p /usr/bin/python2.7 para podermos criar um ambiente
isolado para evitar conflitos, bugs ou incompatibilidade de
sistemas.
Figura 11 – Criação do ambiente isolado

Fonte: Elaborado pela autora (2021).

4. Pronto! Agora é só ativar com o comando: source ./venv/bin/

activate, e para saber que está ativo, basta observar o (venv)
antes da identificação do usuário postgres. Veja a Figura 12 para
melhor compreensão.
Figura 12 – Comando para a ativação

Fonte: Elaborado pela autora (2021).

5. Agora vamos fazer o download do pgAdmin com os seguintes

comandos: wget [Link]
pgadmin4/v1.5/pip/[Link].
E, posteriormente, o comando ls para listar o diretório para
observar a orientação da pasta do próximo comando: pip install
[Link]. Veja o comando melhor
nas figura 13 e 14.
 Administração do SGBD PostgreSQL 21

Figura 13 – Download do pgAdmin

Fonte: Elaborado pela autora (2021).

Figura 14 – Instalação do pgAdmin

Fonte: Elaborado pela autora (2021).

Observe que o comando ls permitiu gerar o endereço (destacado

em amarelo) e inserimos após o comando pip install (destacado em
branco) conforme apresentado na figura 14.

Se você estiver utilizando uma máquina virtual (VirtualBox, por

exemplo) ou realizando um acesso remoto, é necessário editar o
documento [Link] para que o servidor permita o acesso. Contudo,
utilize apenas para testes, em produção não é seguro.

Assim, terminamos a instalação. Para abrirmos o pgAdmin (ver

figura 15), basta digitar o seu IP seguido de : e número da porta 5050. Por
exemplo: digamos que seu IP seja [Link]. Então, o comando será:
[Link]/5050.
22 Administração do SGBD PostgreSQL

Figura 15 – Imagem do pgAdmin no navegador

Fonte: Elaborado pela autora (2021).

No Sistema Operacional Windows, basta ir no menu de inicialização,

procurar pelo diretório do PostgreSQL e dentro estará o pgAdmin.

Agora, para finalizarmos totalmente e partirmos para o próximo

assunto, devemos instalar o phppgadmin (um agendador de tarefas para
PostgreSQL que pode ser gerenciado usando o pgAdmin) com o comando
apt-get install phpphadmin, conforme podemos ver na figura 16.
Figura 16 – Instalação do phppgAdmin

Fonte: Elaborado pela autora (2021).

Você realizou a instalação conosco? Ah! Vale lembrar que caso

o pgAdmin solicite senha, lembre-se: é a mesma que você utilizou no
processo de instalação do PostgreSQL na Unidade anterior. E então?
Depois de tudo instalado na sua máquina, está ansioso(a) para colocar a
mão na massa? Vamos para o próximo assunto!

Índices
A documentação do PostgreSQL, disponível aqui e traduzida para
o português brasileiro, afirma que os índices são um modo comum de
melhorar o banco de dados e permite que o servidor de banco de dados
 Administração do SGBD PostgreSQL 23

possa localizar e retornar linhas selecionadas em um tempo mais hábil.

Contudo, é recomendável a utilização com continência, pois os índices
podem produzir esforço adicional para o sistema de banco de dados.

Sendo assim, para uma melhor compreensão do funcionamento

dos índices, é essencial sabermos como manipular as tabelas do banco
de dados. Você pode ter visto em outros momentos do curso, mas vale
reforçar, pois é de extrema importância você saber manusear as tabelas.
Primeiramente, vamos ver como criar!

Para criar a estrutura de uma tabela, há algumas regras de

nomeação. Nesse caso, não devemos duplicar os nomes dos objetos
do mesmo proprietário. E outra consideração importante é que para os
nomes de tabelas e nomes de colunas sugere-se iniciar com uma letra e
conter os somente os seguintes símbolos: A_Z, a_z, 0_9, _, $, e #.

Como você viu na Unidade anterior, então já sabe que uma tabela
é composta por linhas e colunas nomeadas, cujo número e ordem de
apresentação são fixos. Contudo, o número de linhas pode variar conforme
a expansão do banco de dados, refletindo nessa quantidade armazenada.

Ainda, para os valores que são atribuídos à coluna dos dados, há

um tipo de dado associado. Vejamos a sintaxe:

CREATE TABLE nome_tabela

(
coluna1 tipoDoDado,
coluna2 tipoDoDado,
coluna3 tipoDoDado,
....
);

Ainda, devemos levar em consideração: a chave primária, que

considera como identificador único para as linhas da tabela; a chave
estrangeira, que especifica o valor correspondente em uma linha de
outra tabela, e o valor não nulo, que especifica que uma coluna não
poderá receber o valor nulo.
24 Administração do SGBD PostgreSQL

Vejamos uma demonstração com a chave primária, estrangeira e

não nulo e tente identificar qual delas é a chave estrangeira e por quê:

CREATE TABLE professores

(
professorID integer NOT NULL PRIMARY KEY,
professorNome varchar(255),
professorSobrenome varchar(255),
professorDisciplina varchar(255) NOT NULL,
campus varchar(255)
);

Conseguiu identificar qual é a chave estrangeira? Se você respondeu

ProfessorDisciplina porque está associada a outra tabela, então você
acertou!

Devemos levar em consideração para o banco de dados que:

quando um valor é adicionado ao banco, isto é, quando uma linha é
criada, e nenhum valor é especificado para determinadas colunas, então,
você pode automatizar para adicionar um valor padrão. Vejamos:

CREATE TABLE pessoas

(
pessoaID integer NOT NULL,
pessoaNome varchar(255),
pessoaSobrenome varchar(255) DEFAULT ‘NA’,
pessoaCPF varchar(255),
cidade varchar(255) DEFAULT ‘NA’
);

Além do valor DEFAULT, existe o AUTO_INCREMENT para a chave-

primária, que é utilizado para incrementar automaticamente o valor do
campo ao ser adicionado a uma linha.

Tratando-se de índices, há vários tipos de índices e cada Sistema

Gerenciador de Banco de Dados pode administrar seus próprios tipos de
índices. Para o nosso caso, vamos considerar o índice do PostgreSQL. Mas
você já sabia disso, não é mesmo? Vamos ver um exemplo!
 Administração do SGBD PostgreSQL 25

Vamos supor que temos a seguinte tabela no banco de dados:

CREATE TABLE professores

(
professorID integer NOT NULL PRIMARY KEY,
professorNome varchar(255),
professorSobrenome varchar(255),
professorDisciplina varchar(255) NOT NULL,
campus varchar(255)
);

E foi emitida a seguinte requisição:

SELECT ProfessorNome FROM professores WHERE professorID =

$variavel;

Dessa forma, poderá haver desperdício de processamento, pois

se formos considerar múltiplas requisições para retornar na consulta
uma única – ou nenhuma – linha correspondente para o Id do professor
(professorID), então o SGBD teria de procurar em cada linha da tabela
professores.

Então, o que poderíamos fazer para otimizar esse serviço?

Simplesmente criando o comando para gerar índices. Vejamos:

CREATE INDEX idx_professores_professorID ON professores

(professorID);

Assim, a busca ao invés de varrer toda a tabela, irá procurar somente

nos campos de interesse, cujo nome idx_professores_professorID é
apenas uma recomendação, pois a nomeação é livre. Contudo, sugere-se
que escolha um nome que você possa associar mais tarde para que serve
o índice.

Para o caso de remover o índice criado, basta utilizar o comando

DROP INDEX, assim como para os comandos de atualização UPDATE e
remoção DELETE.
26 Administração do SGBD PostgreSQL

SAIBA MAIS:
Quer se aprofundar neste tema? O PostgreSQL também
possui vários tipos de índices, tais como: B-tree (árvore B),
R-tree (árvore R), hash e GIST. E cada uma delas utiliza seu
próprio algoritmo. Quer saber mais sobre eles? Você pode
acessar o conteúdo aqui.

RESUMINDO:
E então, gostou do que lhe mostramos? Aprendeu mesmo
tudinho? Agora, só para termos certeza de que você
realmente entendeu o tema de estudo deste capítulo,
vamos resumir tudo o que vimos. Você deve ter aprendido
que antes de manipularmos um banco de dados, é
necessário conectar o usuário ao banco de dados. Dessa
forma, apresentamos um passo a passo de como realizar
essa conexão com base em um sistema operacional
Windows e Linux. Também conhecemos o pgAdmin, que
além de poder acessar múltiplos servidores PostgreSQL,
um servidor PostgreSQL, também pode ser acessado por
vários clientes pgAdmin simultaneamente. E te mostramos
como realizar a instalação do pgAdmin em linha de código
com posterior abertura do ambiente gráfico. Também
vimos como criar uma tabela em um banco de dados e a
utilidade das chaves primária e secundária. Por fim, vimos
a importância do uso de índices em bancos de dados,
pois com ele é possível otimizar as consultas e melhorar o
desempenho do banco de dados.
 Administração do SGBD PostgreSQL 27

Schemas – tablespaces e gerenciamento

de usuários
OBJETIVO:
Agora que você já sabe como criar um usuário, conectá-
lo a um banco e ao PostgreSQL, já criou tabelas e viu
como otimizar buscas com índices, vamos para o próximo
passo. Ao término deste capítulo você será capaz de
compreender o funcionamento de schemas e tablespaces
e administrar usuários e banco de dados por interface
gráfica e por linhas de comando. Além disso, irá aprender
como trabalhar com recursos de autenticação e atribuição
de regras e privilégios. Você sabia que esse tipo de trabalho
é extremamente importante para uma empresa? Pois é!
Por meio desses recursos, é possível proteger os dados
da empresa conforme a permissão de cada funcionário. E
isso é uma grande responsabilidade, não é mesmo? Então
vamos lá. Rumo ao avanço!

Schemas
Um schema (ou esquema) é um namespace que organiza o banco
de dados em grupos lógicos e pertence a um usuário do banco de dados.
Dessa forma, os objetos. Contudo, não interfere na forma com que os
dados são armazenados. A documentação do PostgreSQL define schema
como um agrupamento de dados que contém nome e que os usuários e
os grupos de usuários são compartilhados por todo o agrupamento.

Entretanto, isso não significa que os usuários de um agrupamento

possuam todos os bancos de dados do agrupamento, a possibilidade
existe apenas quando configurado no sistema de gerenciamento de
usuários.

Em um schema, para os acessos ao banco de dados, em todas as

conexões dos clientes com o servidor, só são permitidos aqueles que
foram especificados durante o pedido de conexão.
28 Administração do SGBD PostgreSQL

Dessa forma, é possível utilizar o conceito de cross-database,

ou seja, fazer consultas em SQL ao banco realizando cruzamento de
informações entre campos de tabelas que estão em bancos de dados
diferentes.

Em grandes aplicações, o uso de schema também permite

que softwares desenvolvidos por terceiros possam ficar logicamente
separados para não ocorrer colisão com os nomes de outros objetos
existentes no banco de dados.

Vamos ver como criar um schema? Vejamos a sintaxe:

CREATE SCHEMA nome_esquema;

Também podemos criar esquemas exclusivos para determinados

para usuários. Vejamos:

CREATE SCHEMA AUTHORIZATION camila;

Nesse caso, o esquema será autorizado para o usuário camila e,

consequentemente, terá o mesmo nome. Entretanto, podemos criar um
esquema com outro nome e que pertença ao usuário camila.

CREATE SCHEMA IF NOT EXISTS novo_esquema AUTHORIZATION

camila;

Pronto! Criamos um schema chamado novo_esquema para o

usuário camila. O IF NOT EXISTS verificará se não há outro esquema com
o mesmo nome.

Também, para melhor organização dos dados, é possível criar um

schema com uma tabela e visualização da seguinte forma:

CREATE SCHEMA universo

CREATE TABLE marvel (personagem, principal poder,filmes)
CREATE VIEW melhores AS
SELECT personagem FROM marvel WHERE principal poder IS NOT
NULL;
 Administração do SGBD PostgreSQL 29

Observe que os subcomandos não estão com ponto e vírgula.

Ainda, você também poderá se deparar com a seguinte forma de schema
que obtém o mesmo resultado:

CREATE SCHEMA universo;

CREATE TABLE [Link] (personagem, principal poder,filmes);
CREATE VIEW melhores AS
SELECT personagem FROM [Link] WHERE principal poder
IS NOT NULL;

Caso seja necessário remover os schemas do banco de dados, há

duas formas de remover: removendo um schema vazio e removendo um
schema e todos os objetos que possui.

Vejamos o caso em que removerá todos os esquemas considerando

que os objetos já foram removidos:

DROP SCHEMA universo;

O comando para remover o schema e todos os seus objetos não

difere muito. Só acrescentamos a palavra CASCADE. Vejamos:

DROP SCHEMA universo CASCADE;

Você sabia que além de criar um schema por linhas de comando,

também podemos criar em ambiente gráfico? Tem alguma ideia de onde?
Se você respondeu pelo phppgadmin, acertou! Por isso que te falamos do
pgAdmin e do phppgadmin no capítulo anterior. Lembra-se disso?

Então, agora que você já viu os comandos em linhas de comando

vamos te mostrar como criar schemas com base em um ambiente gráfico,
o phppgadmin. Vejamos o passo a passo:

1. No phppgadmin, expanda o banco de dados de seu interesse

que deseja criar um schema. Em Schemas, clique com o botão
direito do mouse e depois em Create > Schema ... (conforme
observado na Figura 17).
30 Administração do SGBD PostgreSQL

Figura 17 – Criando um Schema

Fonte: Elaborado pela autora (2021).

2. Após isso, uma janela para a inserção dos dados do Schema

desejado será aberta. Lembra-se do que te falamos para os
usuários dos schemas em linhas de comando? A regra para o
ambiente gráfico é a mesma. Então, você precisará preencher
com o nome do Schema, o usuário e um comentário.
 Administração do SGBD PostgreSQL 31

Figura 18 – Preenchendo um Schema

Fonte: Elaborado pela autora (2021).

3. Após preenchido, podemos observar que o schema criado

posicionou-se após o schema público.
32 Administração do SGBD PostgreSQL

Figura 19 – Schema criado

Fonte: Elaborado pela autora (2021).

E para criar as tabelas dentro de um Schema em um ambiente

gráfico, o procedimento também é supersimples. Vejamos o passo a
passo:

1. Basta procurar o schema de interesse, procurar no menu

expandido o agrupamento das tabelas e clicar com o botão
direito do mouse indo até o Create e depois Table... (vejamos na
figura 20).
 Administração do SGBD PostgreSQL 33

Figura 20 – Schema criado

Fonte: Elaborado pela autora (2021).

2. Com isso, a janela de preenchimento da tabela é aberta com os

campos para o preenchimento. Nela, é possível criar de forma
gráfica ou por meio do comando SQL para os casos em que a linha
de comando é gerada automaticamente ou pode ser modificada
manualmente. Vejamos na figura 21 com os campos preenchidos
e na Figura 22 a opção de criação gerada automaticamente após
o preenchimento dos campos.
34 Administração do SGBD PostgreSQL

Figura 21 – Criação da tabela

Fonte: Elaborado pela autora (2021).

Figura 22 – Comando SQL para gerar tabela.

Fonte: Elaborado pela autora (2021).

 Administração do SGBD PostgreSQL 35

Viu como é tudo muito prático? Perceba que na Figura 22 foi gerado
o tablespace pg_default que será nosso próximo assunto. Vamos lá!

Tablespaces
Um tablespace (ou espaços da tabela) pode ser considerado um
contêiner para dados, cujos espaços na tabela do PostgreSQL possibilita
aos superusuários definirem o local no sistema de arquivos em que os
arquivos dos objetos de banco de dados podem ser armazenados.

Depois de criado o tablespace, é possível atribuir nomes lógicos aos

locais físicos no disco rígido do servidor e, assim, facilitar a referenciação
dos objetos de banco de dados.

A inicialização de um cluster PostgreSQL envolve automaticamente

duas formas de guardar os dados, cujo tablespace é chamado de
pg_default, para guardar todos os dados do usuário, e o pg_global para
guardar todos os dados do sistema que é visto por todos os bancos de
dados.

Por padrão, os tablespaces ficam localizados no mesmo diretório

do seu cluster, entretanto, você pode armazenar em qualquer disco do
servidor. Também permitem que você possa mover os objetos de banco
de dados existentes para novos.

Para definir um espaço de tabela, você utilizará o comando CREATE

TABLESPACE. Vale ressaltar que o espaço deverá ser um diretório vazio
e que pertença ao usuário do PostgreSQL, e que todos os objetos
criados no tablespace serão armazenados em arquivos sobre o diretório
especificado. Ainda, o local escolhido deverá possibilitar o armazenado
removível, pois o cluster não funcionará caso o tablespace esteja ausente
ou perdido.

Ficou curioso com como executar? Vejamos um exemplo:

CREATE TABLESPACE second LOCATION '/var/lib/postgresql/9.5/';

36 Administração do SGBD PostgreSQL

Lembre-se de que o usuário que você esteja utilizando para criar o

tablespace deverá ter a permissão de salvar. Note que agora o seu banco
de dados será salvo em /var/lib/postgresql/9.5/.

Agora, a partir da versão 9.4, é possível mover um grupo de objetos

de um tablespace para outro. Por exemplo: para os casos em que você já
tenha criado o banco de dados e deseje mover todos os objetos do banco
de dados para o tablespace criado, então, podemos definir da seguinte
forma:

ALTER DATABASE meuDB_anterior SET TABLESPACE second;

Nos casos em que se deseja mover todo o banco de dados: objetos,

schemas, tabelas etc., você utilizará o seguinte comando:

ALTER TABLESPACE pg_default MOVE ALL TO second;

Durante a migração com o MOVE ALL TO, todo o banco de dados

ou tabela ficarão bloqueados temporariamente.

Gerenciamento de usuários
Uma questão bastante importante em banco de dados é o
gerenciamento de usuários, pois, em toda grande aplicação, um banco de
dados pode ser acessado por vários usuários e por motivos de segurança
é necessário implementar controle de acessos.

Está lembrado de como criamos os superusuários e fizemos a

conexão com o servidor no capítulo anterior? Pois são eles quem possuem
controle dos objetos de banco de dados, tais como tabelas e visões. E,
também, podem conceder privilégios de acesso.

Diante do assunto de gerenciamento de usuários, para um bom

gerenciamento do banco de dados, devemos em mente quem são os
usuários, grupos de usuários e qual a função que cada um atua no contexto
da segurança do banco de dados. Sendo assim, vamos primeiramente
conhecer sobre usuários e grupos no PostgreSQL, e depois como
conceder privilégios.
 Administração do SGBD PostgreSQL 37

Roles
Tratando-se de usuários e grupos, um bom gerenciamento é
essencial para o banco de dados. Em versões anteriores do PostgreSQL,
os usuários e grupos de usuários eram entidades de tipos distintos, ou
seja, utilizavam USER e GROUP.

Atualmente (a partir da versão 8.1), são utilizadas as Roles que pode

atuar como um usuário, um grupo ou desempenhar ambos os papéis.
Contudo, não elimina o uso dos comandos CREATE USER e CREATE
GROUP por questões de compatibilidade, mas é interessante que vejamos
os comandos mais atuais. Nesse sentido, veremos como criar e gerenciar
usuários por meio de Roles.

Você já sabe que na instalação do PostgreSQL, por padrão, já é

criado o superusuário postgres. Agora, vamos ver como criar usuários
comuns. Vejamos como criar um usuário com senha que pode ser utilizado
até uma data específica ou por tempo indeterminado:

CREATE ROLE camila LOGIN PASSWORD '0123456' CREATEDB VALID

UNTIL 'infinity';

Olhando para o comando, você parou para pensar o que significa

CREATEDB VALID UNTIL ‘infinity’? Pois bem! Esse termo no comando
significa que a validade daquele usuário é por tempo infinito, isto é, por
tempo indeterminado.

Ainda, é possível criar outro superusuário – além do postgres.

Sendo assim, vejamos um exemplo: você é um administrador que usa o
superusuário postgres e precisa conceder privilégios de superusuário a
um colega com contrato predefinido. Então, para que você não forneça
suas credenciais, você poderá executar um comando exclusivamente
para ele com tempo determinado. Vamos supor que o usuário seja Harry
Potter e a data de expirar seja 1º de janeiro de 2025. Vejamos:

CREATE ROLE potter LOGIN PASSWORD '0123456' SUPERUSER VALID

UNTIL '2025-1-1 00:00';
38 Administração do SGBD PostgreSQL

Caso você deseje remover algum usuário (até mesmo antes da data
de expirar), você pode utilizar o seguinte comando:

DROP ROLE camila;

Uma Role pode possuir e ser contida por outra Role.

Para o caso de grupos de usuários, você também pode considerar

a criação de um grupo que receba determinados privilégios. Vamos ver?

CREATE ROLE grupo1 INHERIT;

O INHERIT significa que qualquer membro do grupo1 terá,

automaticamente, direitos concedidos ao papel do grupo1, exceto para
superusuário.

Então, podemos fazer a seguinte pergunta: como associar o usuário

Hermione que foi recém-criado ao grupo1? Simples! Utilizando o comando
GRANT. Vejamos:

GRANT grupo1 TO hermione;

Gostou do que aconteceu com Hermione sendo adicionada ao

grupo e ganhando privilégios do grupo automaticamente? Bem prático,
não é mesmo? O comando GRANT faz parte do assunto sobre privilégios.
Vamos ver!

Privilégios
No PostgreSQL os privilégios precisam de um pouco mais de
atenção devido ao controle minucioso que ele possui. Alguns dos
privilégios de nível de objeto usam os seguintes comandos: SELECT,
INSERT, UPDATE, ALTER, EXECUTE, TRUNCATE; e um qualificador para
os chamados WITH GRANT. Vejamos a sintaxe:

GRANT nome_privilegio TO alguma_role;

Agora, para que você entenda melhor o uso e os termos, vamos ver
um exemplo prático em um passo a passo com alguns comandos que
você já está familiarizado:
 Administração do SGBD PostgreSQL 39

1. Inicialmente, devemos criar um usuário e senha com o comando:

CREATE ROLE admin LOGIN PASSWORD '0123456';

2. Agora, iremos precisar do banco de dados. Então vamos criar

um banco de dados chamado meubd de propriedade do usuário
admin. Ao afirmar que um usuário é owner de um banco de
dados, significa que ele já recebeu todos os privilégios.

CREATE DATABASE meubd WITH owner = admin;

Ao criar o banco de dados, é necessário entrar no banco de

dados para gerar os comandos GRANT. Nesse caso, você entrará com o
comando \c meubd.

3. Agora, criaremos um schema do banco de dados:

CREATE SCHEMA meu_schema;

4. Então, já podemos utilizar o comando GRANT para conceder

privilégios ao usuário admin:

GRANT ALL ON ALL TABLES IN SCHEMA IN SCHEMA meu_schema TO

admin WITH GRANT OPTION;

Com isso, o usuário admin poderá conceder privilégios de

alterações a outros usuários no shema meu_schema dentro do banco
de dados meubd. O inverso também poderá acontecer com o comando
REVOKE que remove os privilégios.

Para trabalhar com o GRANT existem algumas premissas, que

são: Você precisa ser o titular do privilégio que você está concedendo
e, também, deve ter o poder de conceder privilégios. Para os comandos
DROP e ALTER, os privilégios sempre permanecem com o proprietário de
um objeto e nunca podem ser concedidos.
40 Administração do SGBD PostgreSQL

SAIBA MAIS:
Quer se aprofundar neste tema? Com mecanismos do
PostgreSQL é possível realizar controle de concorrência.
Quer saber mais sobre esse tema? Você pode acessar o
conteúdo aqui.

RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que lhe
mostramos? Agora, vamos recapitular tudo o que vimos
para ver se o conteúdo foi fixado. Nós falamos sobre
o funcionamento de schemas. Então, você viu que os
schemas são agrupamentos lógicos do banco de dados que
o superusuário pode manipular. Agora sabemos que com o
uso de schemas pode melhorar a segurança do nosso banco
de dados, pois, por meio de schemas, podemos separar
aplicações desenvolvidas por terceiros simplesmente
criando um schema e deixando-as logicamente separadas
para não ocorrer colisão com os nomes de outros objetos
existentes no banco de dados. Também vimos o conceito
e como manipular o tablespaces, administrando usuários
e banco de dados por interface gráfica e por linhas de
comando. Por fim, vimos como trabalhar com recursos de
autenticação e atribuição de regras e privilégios.
 Administração do SGBD PostgreSQL 41

Inicialização e interrupção do PostgreSQL

OBJETIVO:
Agora que você já sabe como administrar usuários, pode
otimizar uma busca por meio de shemas e controlar
acessos, vamos para o próximo passo. Ao término deste
capítulo você será capaz de compreender como realizar
a inicialização e interrupção do PostgreSQL e conhecer
os principais parâmetros do arquivo [Link].
Além disso, você irá aprender como parar e reiniciar
o servidor PostgreSQL usando estratégias smart, fast,
immediate e aplicar o comando kill. Você sabia que para
qualquer operação prévia de acesso ao banco de dados é
necessária a inicialização do servidor PostgreSQL? Sem ele
inicializado, poderá impedir a execução das atividades de
acesso ao banco de dados. Pois é! E isso requer uma série
de procedimentos que você precisa executar para realizar
uma boa administração do banco de dados. E então?
Motivado(a) para desenvolver essa competência? Vamos
nessa!

Inicialização do PostgreSQL
Você já sabe que para qualquer operação prévia de acesso ao
banco de dados é preciso a inicialização do servidor. Há várias formas
de inicializar o PostgreSQL, mas o nome padrão para uso por utilitários,
usuários e aplicativos terceiros é denominado postgres. Assim, você
poderá iniciar por meio do seguinte comando:

Postgres -D /bd/data

Tal comando executará o PostgreSQL em primeiro plano enquanto

estiver com conexão ativa ao usuário do PostgreSQL. Para o caso da
execução em background, o seguinte comando poderá ser utilizado:

Postgres &
42 Administração do SGBD PostgreSQL

Para corroborar com a auditoria e facilitar o diagnóstico de

problemas, é fundamental armazenar a saída stdout e stderr do servidor
em um arquivo de log. Para isso, você poderá utilizar o seguinte comando:

Postgres -D /bd/data > [Link] 2 > &1 &

Entretanto, há uma forma muito simples de inicializar o PostgreSQL

em background e enviar o stdout e stderr para um arquivo de log, que é
utilizando o utilitário pg_ctl.

Sendo assim, é necessário realizar a configuração para o

armazenamento no arquivo [Link] e, dessa forma, utilizar apenas
uma linha de comando para a inicialização:

pg_ctl start

De acordo com a documentação do PostgreSQL, o pg_ctl é um

utilitário que pode ser utilizado para mostrar o status de um servidor ativo,
para iniciar, parar ou reiniciar o PostgreSQL. Além de o pg_ctl poder utilizar
a saída padrão para um arquivo de log, é possível fornecer opções para
uma parada controlada.

Arquivo [Link]
Ainda, o superusuário pode configurar algumas variáveis pelo
arquivo [Link] (ou diretamente na linha de comando) para abordar
conceitos de dimensionamento para itens de hardware que estão
operando e controlar a autenticação de clientes etc. Para visualizá-los,
em pg_settings, filtrar postmaster.

Dessa forma, por meio da documentação pública disponibilizada

pelo PostgreSQL (2019) em seu domínio (disponível em: [Link]
[Link]), listamos a seguir as principais utilizadas.

Localização de arquivos
A localização de arquivos é suportada a partir da versão 9.5
do PostgreSQL e sua versão atual é a 12 com sua versão 13 já em
desenvolvimento. Por meio da localização de arquivos é possível que
 Administração do SGBD PostgreSQL 43

os backups dos arquivos de configuração sejam realizados de forma

correta quando mantidos separadamente. Vejamos as configurações
possíveis (disponibilizadas na documentação do PostgreSQL), cujas
parametrizações só podem ser definidas na inicialização do servidor:

• data_directory: especifica o diretório para ser usado para

armazenamento de dados.

• config_file: só pode ser definido por meio da linha de comando. Ele

especifica o arquivo de configuração do servidor principal.

• hba_file: especifica o arquivo de configuração para autenticação

baseada em host, comumente chamado de pg_hba.conf.

• ident_file: comumente chamado de pg_ident.conf. Especifica o

arquivo de configuração para mapeamento de nome de usuário.

• external_pid_file: especifica o nome de um arquivo de ID de

processo (PID) adicional que o servidor deve criar para uso por
programas de administração de servidor.

Conexões e autenticação
As conexões e autenticação envolvem a administração do número
de slots de conexão que são reservados para superusuários, soquete,
grupo do proprietário, porta, número máximo de conexões etc. Vejamos
as principais:

• port: é o número da porta TCP na qual o servidor escuta. Por

padrão, assume a 5432.

• max_connections: define o número máximo de conexões

simultâneas com o PostgreSQL. O padrão de inicialização é de
100 conexões máximas. Contudo, pode ser configurável caso as
configurações do kernel não as suporte.

• listen_addresses: define o(s) endereço(s) TCP/IP nos quais o

servidor deve escutar conexões de aplicativos cliente, cuja entrada
[Link] é para endereços IPv4 e : : para IPv6.
44 Administração do SGBD PostgreSQL

• superuser_reserved_connections: especifica o número de slots de

conexão que são reservados para conexões de superusuários do
PostgreSQL, cujo valor padrão é de três conexões.

• unix_socket_directories: define o diretório do(s) soquete(s) do

domínio Unix nos quais o servidor deve escutar as conexões dos
aplicativos clientes e vários sockets podem ser criados listando
vários diretórios separados por vírgula. Ainda, esse parâmetro é
irrelevante no Windows, pois não possui soquetes de domínio
Unix.

• unix_socket_group: especifica o grupo proprietário do(s) soquete(s)

do domínio Unix. Vale considerar que o usuário proprietário é
sempre o que inicia o servidor.

• unix_socket_permissions: especifica as permissões de acesso do(s)

soquete(s) do domínio Unix, cujas permissões padrões são:

0777: qualquer pessoa pode se conectar.

0700: somente usuário.

0770: somente usuário e grupo.

• Bonjour: permite anunciar a existência do servidor via Bonjour.

• Bonjour_name: especifica o nome do serviço Bonjour. Caso o

servidor não seja compilado com suporte Bonjour, então esse
parâmetro é ignorado.

• password_encryption: determina o algoritmo a ser usado para

criptografar a senha, cujo padrão é md5. Apenas quando a senha
é especificada em CREATE ROLE ou ALTER ROLE.

SSL
O PostgreSQL possui suporte nativo para conexões do tipo SSL
que criptografa comunicações cliente/servidor. Contudo, necessita que
o OpenSSL seja instalado nos sistemas cliente e servidor. Vejamos os
principais parâmetros:

• SSL: ativa as conexões SSL, cujo padrão é OFF.

 Administração do SGBD PostgreSQL 45

• ssl_ca_file: define o nome do arquivo que contém a autoridade

de certificação do servidor SSL, cujos caminhos relativos são de
acordo com o diretório dos dados.

• ssl_cert_file: define o nome do arquivo que contém o certificado do

servidor SSL, cujos caminhos são relativos ao diretório de dados.

• ssl_crl_file: define o nome do arquivo que contém a lista de

revogação de certificado do servidor SSL.

Recursos de consumo
O PostgreSQL também utiliza vários recursos do sistema
operacional, especialmente quando está ativo ou ao iniciar. Com isso,
por meio do PostgreSQL, podemos administrar memória, disco, recursos
do kernel, atraso de vácuo baseado em custo etc. Vejamos os principais
parâmetros:

• shared_buffers: refere-se à quantidade de memória que o servidor

utiliza para buffers de memória compartilhada, cujo valor mínimo
é 128 kilobytes.

• huge_pages: controla se as páginas grandes são solicitadas para a

área de memória principal compartilhada.

• temp_buffers: determina o número máximo de buffers temporários

por cada sessão de banco de dados.

• max_prepared_transactions: Define o número máximo de transações

que podem estar no estado “preparado” simultaneamente, cujo
padrão é 0.

• work_mem: a quantidade de memória a ser usada por operações

internas de classificação e tabelas de hash antes de gravar em
arquivos de disco temporários, cujo valor padrão é 4 MB.

• maintenance_work_mem: determina a quantidade máxima de

memória a ser utilizada pelas operações de manutenção, tais
como VACUUM, CREATE INDEX e ALTER TABLE ADD FOREIGN
KEY, cuja quantidade máxima padrão é de 64 MB.
46 Administração do SGBD PostgreSQL

• autovacuum_work_mem: especifica a quantidade máxima de

memória a ser usada por cada processo de trabalho autovacuum,
cujo padrão é -1.

• temp_file_limit: especifica a quantidade máxima de espaço em

disco que um processo pode usar para arquivos temporários.
Apenas superusuários podem alterar essa configuração.

Atraso de vácuo baseado em custo

No momento da execução dos comandos VACUUM (recupera
o armazenamento ocupado por duplas mortas, isto é, coleta de lixo) e
ANALYZE (coleta estatísticas sobre um banco de dados), o sistema
mantém um contador interno que mantém o controle do custo estimado
das várias operações de I/O (entrada e saída) que são realizadas.

• vacuum_cost_delay: período de tempo, em milissegundos, que o

processo ficará suspenso quando o limite de custo for excedido,
cujo valor padrão é 0.

• vacuum_cost_page_miss: custo estimado para limpar um buffer

que deve ser lido do disco, cujo valor padrão é 10.

• vacuum_cost_page_dirty: custo estimado cobrado quando o

vácuo modifica um bloco que estava limpo anteriormente, cujo
valor padrão é 20.

Relatório e registro de erros

O PostgreSQL suporta vários métodos para gerar relatório e registro
de erros, coletando mensagens de log, por exemplo, e redirecionando
para arquivos especificando o diretório de dados etc. Vejamos os principais:

• log_destination: por padrão, esse parâmetro registra apenas erros

stderr. Contudo, é possível configurar no arquivo [Link]
ou a partir da linha de comando do servidor.

• logging_collector: na própria documentação do PostgreSQL

afirma-se que essa abordagem é mais útil do que registrar no
syslog, pois alguns tipos de mensagens podem ficar ocultas na
 Administração do SGBD PostgreSQL 47

saída do syslog. Este parâmetro ativa o coletor de log, que é um

processo em segundo plano que captura mensagens de log
enviadas para stderr e as redireciona para arquivos de log.

• log_directory: este parâmetro determina o diretório no qual os

arquivos de log serão criados e pode ser especificado com um
caminho absoluto ou relativo dos dados.

• log_filename: define os nomes dos arquivos de log criados. Caso a

saída em formato .csv estiver habilitada em log_destination, então
será anexada ao nome do arquivo de log com carimbo da data e
hora para criar o nome do arquivo no formato de saída .csv.

• log_min_messages: controla quais níveis de mensagens são

gravadas no log do servidor, cujo padrão é WARNING. Quanto
mais avançado for o nível, menos mensagens serão enviadas para
o log.

Interrupção do PostgreSQL
Para interromper o PostgreSQL, é utilizado o comando Kill do
Linux. Entretanto, requer o número do PID (Id do processo em execução)
principal do PostgreSQL. O comando Kill envia três tipos de notificações
(sinais) para o serviço do PostgreSQL:

• TERM (modo smart shutdown): o banco aguardará todas as

conexões existentes finalizarem suas transações, mas também
não aceitará novas.

kill -TERM 1880

• INT (modo fast shutdown): além de não aceitar novas conexões,

envia um sinal TERM para que todas as conexões existentes
recebam um sinal para interromper suas transações e fecharem.
Contudo, este sinal também aguardará as conexões terminarem
para parar o banco.

kill -INT 1880

48 Administração do SGBD PostgreSQL

• QUIT (modo immediate shutdown): envia um sinal QUIT para

todas as conexões e, assim, são encerradas imediatamente e
fechadas subitamente. Caso o banco encerre com o sinal QUIT, ao
inicializar, entrará em recovery para desfazer as ações que ficaram
incompletas.

kill -QUIT 1880

Para obter o PID do processo principal, você pode utilizar o comando

ps. Vejamos as duas alternativas:

Primeira alternativa:

ps -ef f | grep postgres

Uma alternativa é por meio do arquivo [Link] com o

seguinte comando:

Cat /db/data/[Link]

Também, há a opção de parar o servidor sem a necessidade do

número do PID, que é utilizando o pg_ctl com o seguinte comando:

Pg_ctl stop -mf

Caso queira somente reiniciar, então o comando será:

Pg_ctl restart

Já para os casos em que há alteração de parâmetros do postgre.

conf, o ideal é utilizar o comando para recarregar os parâmetros: reload.
Vejamos:

Pg_ctl reload
 Administração do SGBD PostgreSQL 49

SAIBA MAIS:
Ficou curioso(a) para conhecer mais parâmetros do
[Link] e quer se aprofundar neste tema? Por
meio da documentação do PostgreSQL você pode acessar
todos os parâmetros e suas devidas descrições. Você pode
acessar o conteúdo aqui.

RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que lhe
mostramos? Agora, vamos recapitular tudo o que vimos
para ver se o conteúdo foi fixado. Nós falamos sobre a
inicialização do PostgreSQL e como armazenar a saída
stdout e stderr do servidor em um arquivo de log por meio
do utilitário pg_ctl. Mostramos os principais parâmetros
variáveis do arquivo [Link] e falamos como
interromper o servidor de várias formas: com o número
do PID por meio do comando kill e usando estratégias
smart e fast em que o servidor aguarda as conexões serem
encerradas para parar. E vimos, também, a estratégia
immediate em que as conexões são encerradas e fechadas
imediatamente. Ainda, apresentamos a alternativa por meio
do utilitário pg_ctl. Por fim, vimos como reiniciar o servidor
para o caso de atualização de parâmetros utilizando o
reload.
50 Administração do SGBD PostgreSQL

Carga de dados no PostgreSQL

OBJETIVO:
Agora que você já sabe como parar e reiniciar o servidor
PostgreSQL, vamos discutir sobre carga de dados no
PostgreSQL, que é o nosso próximo passo para tornar o
banco de dados mais eficiente e com melhor performance
de carga. Sendo assim, ao término deste capítulo, você
será capaz de compreender como realizar transações
no banco de dados por meio das palavras-chave BEGIN,
COMMIT, ROLLBACK e SAVEOPOINT. Também será capaz
de carregar dados no PostgreSQL usando o comando copy
from, melhorando a performance da carga com a remoção
de índices e aumento das variáveis de configuração, e
analisando o resultado com o comando analyse. Você
sabia que há muitas empresas que possuem sistemas
mais complexos e ambientes críticos em que há vários
servidores funcionando simultaneamente e não podem
parar? Pois é! E realizar um bom manuseio da carga de
dados torna-se fundamental para atingir um cenário de
sucesso. Principalmente em grandes empresas. E então,
motivado(a) para desenvolver essa competência? Vamos
nessa!

Transações PostgreSQL
Os modos de confirmação automática tratam cada comando SQL
como se estivessem agrupados em uma única transação. Por padrão,
os programas com SQL integrados não estão no modo de confirmação
automática e, assim, por meio de palavras-chaves dentro das transações
(BEGIN, COMMIT, ROLLBACK e SAVEOPOINT), é possível definir o
comportamento de confirmação automática da sessão de banco de
dados atual e melhorar o desempenho do servidor.

Uma transação é uma unidade lógica de trabalho que contém

uma ou mais instruções SQL em que apresenta para todas as instruções
somente duas condições: será bem-sucedido para todos ou todas
falharão.
 Administração do SGBD PostgreSQL 51

Caso ocorra alguma falha com uma inserção em algum ponto dentro
de uma transação, é possível desfazer a inserção de todas as inserções
das linhas anteriores até o ponto do início da transação. Dessa forma, há
uma menor preocupação com a carga parcial dos dados e, caso ocorra
alguma falha, não afetará o banco de dados. Vejamos as transações na
prática:

Inicialmente, para melhor compreensão, vamos nos conectar

ao database que criamos anteriormente. Lembra-se da sua senha?
A conexão segue o mesmo processo. O comando no terminal do SQL
shell que instalamos anteriormente para listar os databases existentes no
servidor PostgreSQL é \l. Veja na figura 23.
Figura 23 – Tabelas atuais

Fonte: Elaborado pela autora (2021).

Como já estamos conectados ao banco postgres, não é necessário

entrar, mas se fosse necessário, o comando seria: \c database_name;

Para iniciar um bloco de transações é utilizada a palavra-chave

BEGIN e, dessa forma, todas as instruções SQL posteriores ao BEGIN
serão executadas dentro de uma única unidade de transação que só
considerará o processo da transação como finalizado quando encontrar
as palavras-chaves COMMIT ou ROLLBACK. Vejamos o uso do BEGIN da
transação na Figura 24.
52 Administração do SGBD PostgreSQL

Figura 24 –BEGIN da transação

Fonte: Elaborado pela autora (2021).

Já a palavra-chave COMMIT salva as alterações no banco de dados.

Para você compreender melhor, vamos criar uma tabela e linha no banco
de dados permitindo que outros usuários, autorizados, que se conectarem
ao banco por meio de outra sessão ou terminal, possam acessar. Vejamos
o procedimento na figura 25.
Figura 25 – Uso do COMMIT da transação

Fonte: Elaborado pela autora (2021).

 Administração do SGBD PostgreSQL 53

Notou algo de diferente no comando? Você percebeu que utilizamos

o end transaction? Pois é! Propositalmente, inserimos a palavra-chave end
transaction. Perceba que realizou com sucesso. Tal fato é que COMMIT e
END TRANSACTION desempenham o mesmo papel, isto é, são iguais.

E agora? Você é daquelas pessoas que só acreditam vendo?

Pois bem! Para verificar e validar a criação da tabela, você pode abrir
um terminal SQL shell (psql) em paralelo e verificar. Para isso, basta se
conectar ao postgres novamente e digitar o comando \d. Esse comando
lista as tabelas existentes no banco. Veja na figura 26 a tabela chamada
teste que criamos no exemplo anterior.
Figura 26 – Tabela teste criada no database

Fonte: Elaborado pela autora (2021).

Você já imaginou se algo ocorreu errado e gostaria de voltar no

tempo? Não precisa de poderes especiais. Basta utilizar o ROLLBACK para
isso. Legal, não é mesmo?
54 Administração do SGBD PostgreSQL

O ROLLBACK faz uma operação de reversão no banco de dados

deixando-o no estado conforme a etapa anterior. Sendo assim, podemos
dizer que o ROLLBACK mantém a integridade dos dados, ou seja, pode
restaurar os dados mesmo após execuções incorretas.

Vamos ver um exemplo. Iremos deletar a tabela teste e utilizar o

comando rollback e verificar se a tabela ainda consta no banco de dados.
Vejamos o procedimento do comando para deletar na figura 27.
Figura 27 – Remoção da tabela criada

Fonte: Elaborado pela autora (2021).

Agora, com o comando \d (lembra-se dele e para que serve?)

vamos observar se ainda há a tabela teste no banco. Vejamos na figura 27
a tela de confirmação da tabela removida.
Figura 27 – Tela de confirmação da tabela removida

Fonte: Elaborado pela autora (2021).

 Administração do SGBD PostgreSQL 55

Após confirmada a remoção da tabela, vamos utilizar o comando

ROLLBACK (ver figura 28) e observar que a tabela foi restaurada (ver figura
29).
Figura 28 – Comando ROLLBACK

Fonte: Elaborado pela autora (2021).

Figura 29 – Tela de confirmação da restauração da tabela

Fonte: Elaborado pela autora (2021).

56 Administração do SGBD PostgreSQL

Sendo assim, podemos perceber que a tabela foi retornada

mantendo a integridade dos dados, uma vez que a tabela foi removida e
voltou ao seu estado atual após o uso do comando ROLLBACK.

Contudo, também existe a possibilidade de salvar um ponto de

restauração por meio do comando SAVEPOINT, fornecendo a capacidade
de reverter a transação até um determinado ponto sem reverter a
transação total.

Vamos realizar um passo a passo para melhor compreensão:

1. Criar uma tabela e inserir uma linha. Para isso, teremos de iniciar
o comando BEGIN. Não esqueça! Vejamos na figura 30.
Figura 30 – Criação da tabela e inserção da linha

Fonte: Elaborado pela autora (2021).

2. Criar um ponto de restauração com o SAVEPOINT. Veja na

figura 31 que foi criado o SAVEPOINT, após esse passo, você vai
perceber que qualquer operação que fizermos e chamarmos o
ROLLBACK, as operações serão desfeitas até esse ponto.
 Administração do SGBD PostgreSQL 57

Figura 31 – Criação da tabela e inserção da linha

Fonte: Elaborado pela autora (2021).

3. Inserir mais duas linhas na tabela criada. Perceba (ver figura 32)
que houve mais duas inserções na tabela. E ao listar o conteúdo
da tabela por meio do comando select * from minha_tabela;
percebemos os elementos 1, 3 e 2, inseridos.

Figura 32 – Criação da tabela e inserção da linha

Fonte: Elaborado pela autora (2021).

58 Administração do SGBD PostgreSQL

4. Disparar o comando ROLLBACK até o ponto de restauração

SAVEPOINT (ver figura 33).
Figura 33 – ROLLBACK até o ponto de restauração

Fonte: Elaborado pela autora (2021).

5. Visualizar a tabela após o ROLLBACK (figura 34). Sendo assim, ao

selecionarmos a tabela novamente, podemos notar que a tabela
apresenta somente a linha inserida até o SAVEPOINT.
Figura 34 – ROLLBACK até o ponto de restauração

Fonte: Elaborado pela autora (2021).

 Administração do SGBD PostgreSQL 59

6. Finalizar com o commit. Você deve notar que o bloco de

transações ainda está ativo e que, para finalizar, precisamos
disparar o COMMIT ou um segundo ROLLBACK.
Figura 35 – ROLLBACK até o ponto de restauração

Fonte: Elaborado pela autora (2021).

Uso do COPY FROM e ANALYSE

O comando COPY FROM realiza uma cópia dos dados de um
arquivo para uma tabela. Caso a tabela já contenha dados, os novos dados
copiados serão adicionados aos já existentes.

Ainda, existem alguns parâmetros que devem ser considerados

na especificação do comando COPY FROM, pois, por exemplo, quando
é utilizado o STDIN e STDOUT, os dados são transmitidos por meio da
conexão entre o cliente e o servidor.

Os parâmetros são: nome da tabela, lista de colunas a serem

copiadas, nome do caminho absoluto do arquivo, especificação da
entrada e saída do aplicativo cliente, armazenamento no formato binário,
identificador do objeto (OID), CSV (modo valor separado por vírgula), Force
Quote (força a utilização do demarcador em todos os valores diferentes
de NULL) e Force not null (processa cada coluna especificada como se
estivesse demarcada e, portanto, não sendo um valor NULL).
60 Administração do SGBD PostgreSQL

Vejamos um exemplo do uso do COPY e COPY FROM, em que

iremos realizar uma cópia de uma tabela (estados) para o cliente,
utilizando: no primeiro comando o delimitador de campo da barra vertical
e; no segundo comando, o COPY FROM para copiar os dados do arquivo
dados_estados para a tabela estados.

COPY estados TO STDOUT WITH DELIMITER '|';

COPY estados FROM 'user1/proj/sql/dados_estados';

Dessa forma, sempre que alterar os dados da tabela, isto é, após a

alteração da distribuição dos dados no banco de dados, recomenda-se
executar o comando ANALYSE (ou VACUUM ANALYSE).

Tal comando visa a garantir que o planejador possa ter estatísticas

atualizadas sobre a tabela, pois, com estatísticas obsoletas, é possível
que tenha um desempenho fraco. Vejamos exemplos com os comandos:

1. Comando VACUUM simples: libera espaço para reutilização

VACUUM minha_tabela;

2. Comando VACUUM completo: bloqueia a tabela do banco de

dados e recupera mais espaço do que o VACUUM simples.
Note que há dois tipos de comando, dependendo da versão do
postgres instalada.

/* Para Postgres nas versões anteriores ao 9.0: */

VACUUM FULL

/* Para Postgres nas versões posteriores ao 9.0: */

VACUUM(FULL) minha_tabela;

3. Full VACUUM and ANALYZE: executa um Full VACUUM e reúne

novas estatísticas em caminhos de execução de consulta usando
ANALYZE. Note que há dois tipos de comando, dependendo da
versão do postgres instalada.
 Administração do SGBD PostgreSQL 61

/* Para Postgres nas versões anteriores ao 9.0: */

VACUUM FULL ANALYZE minha_tabela;

/* Para Postgres nas versões posteriores ao 9.0: */

VACUUM (FULL, ANALYZE) minha_tabela;

Ou podemos utilizar somente o comando ANALYSE, em que há

a opção de ter ou não o nome da tabela. Sendo que, sem o nome de
uma tabela especificada, o comando será executado apenas nas tabelas
disponíveis no schema atual ao qual o usuário tem acesso.

/* nome da tabela opcional */

ANALYZE VERBOSE minha_tabela;

Já para os casos de reconstruir um ou mais índices do banco

de dados, substituindo a versão anterior do índice, podemos utilizar o
comando REINDEX. O REINDEX pode ser utilizado em vários cenários. De
acordo com a documentação do PostgreSQL, há outras funcionalidades
do REINDEX, por exemplo: reduzir o consumo de espaço do índice ou
reconstruir uma falha em um índice inválido.

Para construir o índice sem interferir na produção, você deve

descartar o índice e emitir novamente o comando CREATE INDEX
CONCURRENTLY.

Vejamos alguns exemplos. Vale considerar que qualquer um desses

comandos pode ser forçado adicionando a palavra-chave FORCE após o
comando:

1. Recriar um índice específico:

REINDEX INDEX meuindex

2. Recriar todos os índices de uma tabela:

REINDEX TABLE minha_tabela

3. Recriar todos os índices de um schema público:

REINDEX SCHEMA public

62 Administração do SGBD PostgreSQL

4. Recriar todos os índices do banco de dados postgres:

REINDEX DATABASE postgres

5. Recriar todos os índices nos catálogos do sistema no banco de

dados postgres:

REINDEX SYSTEM postgres

SAIBA MAIS:
Ficou curioso(a) e quer se aprofundar neste tema? Leia
o artigo sobre o maintenance_work_mem. Nele você
compreenderá como é controlada a quantidade de
memória que o sistema irá alocar. Você pode acessar o
conteúdo aqui.

RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que lhe
mostramos? Agora, vamos recapitular tudo o que vimos para
ver se o conteúdo foi fixado. Nós falamos sobre como realizar
transações no banco de dados por meio das palavras-
chaves BEGIN, COMMIT, ROLLBACK e SAVEOPOINT. Agora
sabemos que podemos criar um ponto de restauração
e, caso ocorra alguma falta, podemos voltar ao estado
anterior e garantir a integridade dos dados. Também vimos
como utilizar os comandos copy from, como melhorar
a performance da carga com a remoção de índices e o
aumento das variáveis de configuração, reconstruindo um
ou mais índices do banco de dados, substituindo a versão
anterior do índice. Por fim, vimos como analisar o resultado
com o comando analyse ou VACUUM.
 Administração do SGBD PostgreSQL 63

REFERÊNCIAS
CARVALHO, V. PostgreSQL: banco de dados para aplicações web
modernas. São Paulo: Editora Casa do Código, 2017.

FRANCO, G.; VALE, L. (s.d.). A importância e influência do setor de

compras nas organizações. TecHoje. Disponível em: [Link]
Acesso em: 4 jul. 2017.

HASS, R. How Much maintenance_work_mem Do I Need?. Robert

Hass, 2019. Disponível em: [Link] Acesso em: 22 ago. 2020.

PostgreSQL. Disponível em: [Link] Acesso

em: 25 ago. 2020.

[Link]. PostgreSQL: The World’s Most Advanced Open

Source Relational Database. Disponível em: [Link]
Acesso em: 25 ago. 2020.

THE POSTGRESQL GLOBAL DEVELOPMENT GROUP. Documentação

do PostgreSQL. 2005. Disponível em: [Link]
Acesso em: 18 ago. 2020.