Administração do SGBD PostgreSQL - Unidade 2 - Arquitetura e funcionamento do PostgreSQL

Prévia do material em texto

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:
para o início do 
desenvolvimento de 
uma nova compe-
tência;
DEFINIÇÃO:
houver necessidade 
de se apresentar um 
novo conceito;
NOTA:
quando forem 
necessários obser-
vações ou comple-
mentações para o 
seu conhecimento;
IMPORTANTE:
as observações 
escritas tiveram que 
ser priorizadas para 
você;
EXPLICANDO 
MELHOR: 
algo precisa ser 
melhor explicado ou 
detalhado;
VOCÊ SABIA?
curiosidades e 
indagações lúdicas 
sobre o tema em 
estudo, se forem 
necessárias;
SAIBA MAIS: 
textos, referências 
bibliográficas e links 
para aprofundamen-
to do seu conheci-
mento;
REFLITA:
se houver a neces-
sidade de chamar a 
atenção sobre algo 
a ser refletido ou dis-
cutido sobre;
ACESSE: 
se for preciso aces-
sar um ou mais sites 
para fazer download, 
assistir vídeos, ler 
textos, ouvir podcast;
RESUMINDO:
quando for preciso 
se fazer um resumo 
acumulativo das últi-
mas abordagens;
ATIVIDADES: 
quando alguma 
atividade de au-
toaprendizagem for 
aplicada;
TESTANDO:
quando o desen-
volvimento de uma 
competência for 
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 postgresql.conf .............................................................................................................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
9
UNIDADE
02
Administração do SGBD PostgreSQL
10
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! 
Administração do SGBD PostgreSQL
12
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 sustentabilidadede 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.
Administração do SGBD PostgreSQL
14
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.
Administração do SGBD PostgreSQL
16
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).
Administração do SGBD PostgreSQL
18
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 (pgadmin.org), 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.
Administração do SGBD PostgreSQL20
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 https://ftp.postgresql.org/pub/pgadmin/
pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl. 
E, posteriormente, o comando ls para listar o diretório para 
observar a orientação da pasta do próximo comando: pip install 
pgadmin4-1.5-py2.py3-none-any.whl. 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 config.inf 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 192.198.1.108. Então, o comando será: 
192.198.1.108/5050.
Administração do SGBD PostgreSQL
22
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
http://pgdocptbr.sourceforge.net/)
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.
Administração do SGBD PostgreSQL
24
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.
Administração do SGBD PostgreSQL
26
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 
acessaro 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
http://pgdocptbr.sourceforge.net/pg80/indexes-types.html
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. 
Administração do SGBD PostgreSQL
28
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 universo.marvel (personagem, principal poder,filmes);
CREATE VIEW melhores AS
SELECT personagem FROM universo.marvel 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).
Administração do SGBD PostgreSQL
30
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.
Administração do SGBD PostgreSQL
32
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.
Administração do SGBD PostgreSQL
34
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/';
Administração do SGBD PostgreSQL
36
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';
Administração do SGBD PostgreSQL
38
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.
Administração do SGBD PostgreSQL
40
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 
usode 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
https://tinyurl.com/yy2vj43k
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 postgresql.conf. 
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 &
Administração do SGBD PostgreSQL
42
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 > postgresql.log 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 postgresql.conf 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 postgresql.conf
Ainda, o superusuário pode configurar algumas variáveis pelo 
arquivo postgre.conf (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: https://www.
postgresql.org), 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 
0.0.0.0 é para endereços IPv4 e : : para IPv6.
Administração do SGBD PostgreSQL
44
 • 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óriaque 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.
Administração do SGBD PostgreSQL
46
 • 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 postgresql.conf 
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
Administração do SGBD PostgreSQL
48
 • 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 postmaster.pid com o 
seguinte comando:
Cat /db/data/postmaster.pid
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 
postgresql.conf 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 postgre.conf 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.
Administração do SGBD PostgreSQL
https://www.postgresql.org/docs/12/runtime.html
50
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émuma 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.
Administração do SGBD PostgreSQL
52
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?
Administração do SGBD PostgreSQL
54
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).
Administração do SGBD PostgreSQL
56
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).
Administração do SGBD PostgreSQL
58
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).
Administração do SGBD PostgreSQL
60
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 exemploscom 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
Administração do SGBD PostgreSQL
62
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
http://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html
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: http://bit.ly/35gA60Q. 
Acesso em: 4 jul. 2017.
HASS, R. How Much maintenance_work_mem Do I Need?. Robert 
Hass, 2019. Disponível em: https://bit.ly/3GhiVid. Acesso em: 22 ago. 2020.
PostgreSQL. Disponível em: https://tinyurl.com/yy2vj43k. Acesso 
em: 25 ago. 2020. 
POSTGRESQL.ORG. PostgreSQL: The World’s Most Advanced Open 
Source Relational Database. Disponível em: https://www.postgresql.org. 
Acesso em: 25 ago. 2020.
THE POSTGRESQL GLOBAL DEVELOPMENT GROUP. Documentação 
do PostgreSQL. 2005. Disponível em: http://pgdocptbr.sourceforge.net/. 
Acesso em: 18 ago. 2020.
Administração do SGBD PostgreSQL
	Banco de dados e índices
	O banco de dados
	Conectando ao usuário de conexão do banco de dados no Windows
	Conectando ao usuário de conexão do banco de dados no Linux
	O pgAdmin
	Índices
	Schemas – tablespaces e gerenciamento de usuários
	Schemas 
	Tablespaces
	Gerenciamento de usuários
	Roles
	Privilégios
	Inicialização e interrupção do PostgreSQL
	Inicialização do PostgreSQL
	Arquivo postgresql.conf
	Localização de arquivos
	Conexões e autenticação
	SSL
	Recursos de consumo
	Atraso de vácuo baseado em custo
	Relatório e registro de erros
	Interrupção do PostgreSQL
	Carga de dados no PostgreSQL
	Transações PostgreSQL
	Uso do COPY FROM e ANALYSE