ADMINISTRAÇÃO DO SGBD POSTGRESQL 2

Prévia do material em texto

Administração 
do SGBD 
PostgreSQL
Camila Freitas Sarmento
Diretor Executivo 
DAVID LIRA STEPHEN BARROS
Gerente Editorial 
CRISTIANE SILVEIRA CESAR DE OLIVEIRA
Projeto Gráfico 
TIAGO DA ROCHA
Autora 
CAMILA FREITAS SARMENTO
A AUTORA
Camila Freitas Sarmento
Olá. Meu nome é 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:
INTRODUÇÃO:
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 ........................................................................10
O banco de dados .......................................................................................................................... 10
Conectando ao usuário de conexão do banco de dados no 
Windows ............................................................................................................................. 10
Conectando ao usuário de conexão do banco de dados no 
Linux ...................................................................................................................................... 14
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 .......................................................................................................................... 39
Inicialização e interrupção do PostgreSQL ..................................... 42
Inicialização do PostgreSQL ....................................................................................................42
Arquivo postgresql.conf .............................................................................................................43
Localização de arquivos .........................................................................................44
Conexões e autenticação .......................................................................................44
SSL ......................................................................................................................................... 46
Recursos de consumo ............................................................................................ 46
Atraso de vácuo baseado em custo ..............................................................47
Relatório e registro de erros ............................................................................... 48
Interrupção do PostgreSQL .................................................................................................... 49
Carga de dados no PostgreSQL .............................................................52
Transações PostgreSQL .............................................................................................................52
Uso do COPY FROM e ANALYSE ........................................................................................ 64
 7
ARQUITETURA E FUNCIONAMENTO DO POSTGRESQL
UNIDADE
02
 8
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 de a 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, os avaliadores lançam uma 
nova versão. Quem sabe você não será o próximo profissional a sugerir 
modificações! Para isso, vamos aprender tudo direitinho, começando 
pela leitura do material. Vamos lá? Ao longo desta unidade letiva você vai 
mergulhar neste universo!
 9
OBJETIVOS
Olá. Seja muito bem-vindo à Unidade 2. Nosso objetivo é auxiliar 
você no desenvolvimento das seguintes competências profissionais até o 
término desta etapa de estudos:
1. 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!
 10
Banco de dados e índices
INTRODUÇÃO:
Ao término deste capítulo você será capaz de entender 
como funciona o PostgreSQL, de compreender a estrutura 
de funcionamento por meio da criação de tabelas de 
banco de dados e de entender 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, o sistema pode precisar de constantes 
ajustes, o que pode colocar o sistema de uma empresa 
inacessível por horas. Caso seja uma empresa totalmente 
online, 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 para desenvolver esta competência? Vamos lá!
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 no 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 encontrá-lo no Windows e 
abrir o terminal, como na Figura 1.
 11
Figura 1 – SQL Shell no Windows
Fonte: Elaborado pela autora.
Passo a passo: Agora, vamos te mostrar como realizar a conexão. 
Ao abrir o terminal do psql, você vai ter que escolher o servidor, o banco 
de dados e a senha configurada. Você pode iniciar com a configuração 
padrão, isto é, você só precisa aceitar as sugestões e pressionar a tecla 
Enter. Vamos te mostrar:
1. Ao abrir o terminal, irá aparecer a mensagem: Server 
[localhost]. Nesse caso, é só apertar a tecla Enter para 
aceitar a configuração para o servidor local.
2. O próximo passo é a escolha da base de dados. No 
terminal terá Database [postgres], que você pode 
aceitar apertando Enter.
 12
NOTA:
O postgres é o superusuário do PostgreSQL que já vem, por 
padrão, na instalação.
3. Essa é a etapa da escolha da porta. Sugerimos deixar a 
porta padrão (5432) e, para aceitar, basta pressionar a tecla 
Enter.
4. Agora, você precisa escolher o usuário. Nesse caso, pode 
colocar o usuário que você criou durante a instalação pelo 
terminal em que praticou na Unidade passada. Caso não 
se lembre de como fazer, 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, executou o passo a passo na sua máquina? 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.
Prontinho! Viu como é fácil? Se você tiver alguma dúvida, poderá 
digitar help diretamente no terminal, então aparecerá uma série de 
sugestões de ajuda. Veja na Figura 3 as sugestões de ajuda do terminal.
Figura 3 – Sugestões de ajuda do terminal
Fonte: Elaborado pela autora.
Como podemos ver na Figura 3, algumas opções de ajuda são: 
 • \ para os termos de distribuição.
 13
 • \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 consegue sair 
da tela, pode pressionar as teclas ctrl + C para voltar ao postgres=#. 
Agora já podemos partir para a criação das tabelas no banco de dados, 
preparado? Antes disso, vamos ver como funciona a mesma configuração 
em um sistema Linux.
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 superusuário postgre (isto é, 
com permissões de root do sistema). Sendo assim, vamos começar e nos 
conectar ao usuário postgre?
Passo a passo: Para começar, você precisa digitar no terminal o 
comando de superusuário e entrar no usuário do postgres executando os 
seguintes passos:
 • Digite no terminal do Linux o comando sudo su - 
postgres e pressione a tecla Enter. Assim, você entrará 
no usuário do postgres para executar o próximo passo.
 • Entre na ferramenta padrão de acesso ao PostreSQL 
digitando psql, conforme a Figura 4.
Figura 4 – Comando para acesso ao PostgreSQL
Fonte: Elaborado pela autora.
 14
NOTA:
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? Tem dúvida e deseja ver qual usuário está 
conectado? Basta digitar select current_user; e depois pressionar a 
tecla Enter para ver o usuário ativo, conforme a Figura 5.
Figura 5 – Identificando usuário ativo
Fonte: Elaborado pela autora.
Para verificar se houve a conexão com o PostgreSQL de forma 
correta, você pode digitar select inet_server_port(), assim, verá o 
retorno obtido. Veja a progressão dos comandos na Figura 6.
Figura 6 – Identificando ativação
Fonte: Elaborado pela autora.
Ainda, podemos verificar qual porta o servidor está executando 
(ver Figura 7), por meio do seguinte comando: select current_
database();.
Figura 7 – Identificando ativação
Fonte: Elaborado pela autora.
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 
 15
nos retornar? Analise as Figuras 8 e 9 e responda: o que podemos 
compreender das versões a partir desse comando?
Figura 8 – Comando para visualizar as versões
Fonte: Elaborado pela autora.
Figura 9 – Informações de versões
Fonte: Elaborado pela autora.
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, entendeu 
tudo? Vamos agora compreender o que é o pgadmin, para que serve e 
qual é a sua relação com o PostgreSQL.
O pgAdmin
DEFINIÇÃO:
De acordo com o próprio site (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 que pode ser manuseado em Linux, Unix, MacOS 
e Windows.
 16
O cliente pgAdmin pode acessar múltiplos servidores PostgreSQL, 
e um servidor PostgreSQL também pode ser acessado por vários clientes 
pgAdmin simultaneamente.
Passo a passo: Ainda, o pgAdmin pode ser instalado por ambiente 
gráfico, automaticamente, durante a instalação do PostgreSQL no Windows, 
ou via terminal, instalando as dependências no Linux. Ficou curioso sobre 
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, que vai instalar as dependências para o 
funcionamento.
Figura 9 – Instalação das dependências no Linux
Fonte: Elaborado pela autora.
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.
NOTA:
Lembrando que o pgadmin4 é a versão atual do pgAdmin. 
Futuramente, você poderá se deparar com novas versões.
3. Depois de criado o diretório, iremos utilizar o comando 
virtualenv venv -p /usr/bin/python2.7 para 
criar um ambiente isolado para evitar conflitos, bugs ou 
incompatibilidade de sistemas.
Figura 11 – Criação do ambiente isolado
 17
Fonte: Elaborado pela autora.
4. Pronto! Agora é só ativar com o comando source ./venv/
bin/activate. Para saber se 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.
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, com 
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 melhor 
nas Figuras 13 e 14.
Figura 13 – Download do pgAdmin
Fonte: Elaborado pela autora.
Figura 14 – Instalação do pgAdmin
Fonte: Elaborado pela autora.
Observe que o comando ls permitiu gerar o endereço (destacado 
em amarelo), e que inserirmos após o comando pip install (destacado 
em branco), conforme apresentado na Figura 14.
 18
NOTA:
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.
Com isso, terminamos a instalação. Para abrir o pgAdmin (ver Figura 
15), basta digitar o seu IP seguido de : e o 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.
Figura 15 – Imagem do pgAdmin no navegador
Fonte: Elaborado pela autora.
NOTA:
No sistema operacional Windows, basta ir ao menu de 
inicialização e procurar pelo diretório do PostgreSQL, no 
qual estará o pgAdmin.
Agora, para finalizarmos totalmente e partimos 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.
Você realizou a instalação? Vale lembrar que, caso o pgAdmin 
solicite senha, é só inserir a mesma que você utilizou no processo de 
instalação do PostgreSQL na Unidade anterior. Depois de tudo instalado 
na sua máquina, está ansioso para colocar a mão na massa? Vamos para 
o próximo assunto!
Índices
 19
DEFINIÇÃO:
A documentação do PostgreSQL, disponível online (http://
pgdocptbr.sourceforge.net/) e traduzida para o português 
brasileiro, afirma que os índices são um modo comum de 
melhorar o banco de dados e permitem que o servidor de 
banco de dados localize e retorne linhas selecionadas em 
um tempo mais hábil. Contudo, é recomendável a utilização 
com moderação, 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 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. Outra consideração importante é que, para os 
nomes de tabelas e de colunas, sugere-se iniciar com uma letra e conter 
somente os seguintes símbolos: A_Z, a_z, 0_9, _, $, e #.
Como você viu na Unidade anterior, uma tabela é composta por 
linhas e por 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,
 ....
 20
IMPORTANTE:
Devemos também 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.
Vejamos uma demonstração com a chave primária, estrangeira e 
não nulo. Tente identificar qual delas é a chave estrangeira e o porquê:
CREATE TABLE professores 
(
 professorID integer NOT NULL PRIMARY KEY,
 professorNome varchar(255),
 professorSobrenome varchar(255),
 professorDisciplina varchar(255) NOT NULL, 
Conseguiu identificar a chave estrangeira? Se você respondeu 
ProfessorDisciplina, por estar associada à outra tabela, 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, 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), 
 21
DEFINIÇÃO:
Além do valor DEFAULT, existe o AUTO_INCREMENT 
para a chave primária, que é utilizado para incrementar 
automaticamente o valor do campo quando uma linha é 
adicionada.
Tratando-se de índices, há vários tipos, 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!
Exemplo: 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, 
E foi emitida a seguinte requisição:
SELECT ProfessorNome FROM professores WHERE professor-
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 que procurar em cada linha da tabela 
professores.
Então, o que poderíamos fazer para otimizar esse serviço? 
Simplesmente criar o comando para gerar índices. Vejamos:
CREATE INDEX idx_professores_professorID ON profes-
 22
Assim, a busca, em vez 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 torne possível associar com o objetivo do 
índice posteriormente.
Para o caso de remover o índice criado, basta utilizar o comando 
DROP INDEX, assim como para os comandos de atualização UPDATE e 
de remoção DELETE.
SAIBA MAIS:
Quer se aprofundar neste tema? O PostgreSQL também 
tem vários tipos de índices, como: B-tree (árvore B), R-tree 
(árvore R), hash e GIST, e cada um deles utiliza seu próprio 
algoritmo. Quer saber mais sobre eles? Acesse o conteúdo 
pelo link: http://pgdocptbr.sourceforge.net/pg80/indexes-
types.html (Acesso em 10 ago. 2020).
RESUMINDO:
E então, gostou do que 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 manipular 
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 a partir dos sistemas operacionais 
Windows e Linux. Também conhecemos o pgAdmin, que 
pode acessar múltiplos servidores PostgreSQL, e um servidor 
PostgreSQL também pode ser acessado por vários clientes 
pgAdmin simultaneamente. Além disso, 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, estudamos 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.
 23
Schemas, tablespaces e gerenciamento de 
usuários
INTRODUÇÃO:
Agora que você já sabe como criar um usuário e como 
conectá-lo a um banco e ao PostgreSQL, já crioutabelas 
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 a trabalhar com recursos de autenticação e de 
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, o que é uma grande responsabilidade. 
Então, vamos lá!
Schemas 
DEFINIÇÃO:
Um Schema (ou esquema) é um namespace que organiza 
o banco de dados em grupos lógicos e que pertence a um 
usuário do banco de dados, isto é, 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 em 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 
possam acessar todos os bancos de dados do agrupamento, a possibilidade 
existe apenas quando configurado no sistema de gerenciamento de 
usuários.
 24
Em um schema, os acessos ao banco de dados, em todas as 
conexões dos clientes com o servidor, só são permitidos para aqueles 
que foram especificados durante o pedido de conexão. 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 banco de dados diferentes. 
Em grandes aplicações, o uso de schema também permite que 
softwares desenvolvidos por terceiros fiquem 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? 
CREATE SCHEMA nome_esquema;
Exemplo: 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 
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.
Para melhor organização dos dados, é possível criar um schema 
com uma tabela e uma visualização da seguinte forma: 
CREATE SCHEMA universo
CREATE TABLE marvel (personagem, principal 
poder,filmes[])
CREATE VIEW melhores AS 
 SELECT personagem FROM marvel WHERE prin-
 25
Observe que os subcomandos não estão com ponto e vírgula. 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 for necessário remover os schemas do banco de dados, há 
duas formas: removendo um schema vazio e removendo um schema e 
todos os seus objetos.
Exemplo: Vejamos o caso em que todos os esquemas serão 
removidos, 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 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 mostrar como criar schemas a partir de um ambiente gráfico, o 
phppgadmin. Veja o passo a passo:
Passo a passo: 
 26
1. No phppgadmin, expanda o banco de dados em 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).
Figura 17 – Criando um schema
Fonte: Elaborado pela autora.
2. Após isso, será aberta uma janela para a inserção dos 
dados do s chema desejado. Lembra-se do que 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.
Figura 18 – Preenchendo um schema
Fonte: Elaborado pela autora.
 27
3. Depois de preenchido, podemos observar que o schema 
criado posicionou-se após o schema público.
Figura 19 – Schema criado
Fonte: Elaborado pela autora.
Para criar as tabelas dentro de um schema em um ambiente gráfico, 
o procedimento também é simples. Veja o passo a passo:
Passo a passo: 
1. Basta procurar o schema de interesse, buscar o 
agrupamento das tabelas no menu expandido e clicar 
com o botão direito do mouse indo até Create e depois 
até Table... (vejamos na Figura 20).
Figura 20 – Schema criado
Fonte: Elaborado pela autora.
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 é possível modificá-la manualmente. 
Veja a Figura 21 com os campos preenchidos, e na Figura 
22 a opção de criação gerada automaticamente após o 
preenchimento dos campos.
Figura 21 – Criação da tabela
Fonte: Elaborado pela autora.
Figura 22 – Comando SQL para gerar tabela
 28
Fonte: Elaborado pela autora.
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
DEFINIÇÃO:
Um tablespace (ou espaços da tabela) pode ser considerado 
um contêiner para dados, cujos espaços na tabela do 
PostgreSQL possibilitam 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 de 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, mas você pode armazenar em qualquer disco do servidor, 
além de permitirem que você mova 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 
pertencente ao usuário do PostgreSQL e que todos os objetos criados no 
tablespace serão armazenados em arquivos sobre o diretório especificado. 
 29
Ainda, o local escolhido deverá possibilitar o armazenado removível, pois 
o cluster não funcionará caso o tablespace esteja ausente ou perdido. 
Exemplo: Ficou curioso? Vejamos um exemplo de execução:
CREATE TABLESPACE second LOCATION ‘/var/lib/post-
Lembre-se de que o usuário que você utilizar 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 deseja 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;
NOTA:
Durante a migração com o MOVE ALL TO, todo o banco de 
dados ficará bloqueado 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 
 30
dados pode ser acessado por vários usuários e, por motivos de segurança, 
é necessário implementar controle de acessos.
Lembra-se de como criamos os superusuários e fizemos a conexão 
com o servidor no capítulo anterior? Eles que obtêm controle dos objetos 
de banco de dados, como tabelas e visões, além de poderem conceder 
privilégios de acesso.
Diante do assunto de gerenciamento de usuários, para um bom 
gerenciamento do banco de dados, devemos ter em mente quem são 
os usuários e os grupos de usuários e qual a função de cada um atua 
no contexto da segurança do banco de dados. Sendo assim, vamos 
primeiramente conhecer usuários e grupos no PostgreSQL, e depois, a 
conceder privilégios.
Roles
Tratando-se de usuários e de grupos, um bom gerenciamento é 
essencial para o banco de dados. Em versões anteriores do PostgreSQL, 
os usuários e os grupos de usuários eram entidades de tipos distintos, ou 
seja, utilizavam USER e GROUP.
IMPORTANTE:
Atualmente (a partir da versão 8.1), são utilizadas as roles, 
que podem atuar como um usuário e/ou um grupo. 
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’;
 31
Olhando para o comando, sabe o que significa CREATEDB VALID 
UNTIL ‘infinity’? Esse termo quer dizer que a validade daquele 
usuário é por tempo infinito, isto é, indeterminado.
Exemplo: Ainda, é possível criar outro superusuário além do 
postgres. 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, poderá executar um comando exclusivamente para ele 
com tempo determinado. Vamos supor que o usuário seja Harry Potter e a 
data de expiração seja 1º de janeiro de 2025.
CREATE ROLE potter LOGIN PASSWORD ‘0123456’ SUPERUSER 
VALID UNTIL ‘2025-1-1 00:00’;
Caso você deseje remover algum usuário (até mesmo antes da data 
de expirar), pode utilizar o seguinte comando:
DROP ROLE camila;
NOTA:
Uma role pode ter 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. Veja:
CREATE ROLE grupo1 INHERIT;
O INHERIT significa que qualquer membro do grupo1 terá, 
automaticamente, direitos concedidos ao papel do grupo1, exceto para 
superusuário. 
 32
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.
Privilégios
No PostgreSQL os privilégios precisam de um pouco mais de 
atenção devido ao controle minucioso que ele tem. 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;
Exemplo: Agora, para entender melhor o uso e os termos, vamos 
ver um exemplo prático em um passo a passo com alguns comandos 
com os quais você já está familiarizado:
1. Inicialmente, devemos criar um usuário e uma senha com 
o comando:
CREATE ROLE admin LOGIN PASSWORD ‘0123456’;
2. Agora, precisamos do banco de dados, então, vamos criar 
um 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;
 33
NOTA:
Ao criar o banco de dados, é necessário entrar nele para 
gerar os comandos GRANT. Nesse caso, utilize o comando 
\c meubd.
Agora, criaremos um schema do banco de dados:
CREATE SCHEMA meu_schema;
3. 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 schema 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: você 
precisa ser o titular do privilégio que 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.
 34
SAIBA MAIS:
Quer se aprofundar neste tema? Você pode acessar o 
conteúdo acessível pelo link https://tinyurl.com/yy2vj43k 
(Acesso em: 15 ago. 2020).
RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que 
mostramos? Agora, vamos recapitular tudo. Nós falamos 
sobre o funcionamento de schemas, que são agrupamentos 
lógicos do banco de dados que o superusuário pode 
manipular. Sabemos que, com o uso de schemas, é possível 
melhorar a segurança do nosso banco de dados, pois, por 
meio deles, 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 de tablespaces 
e como manipulá-lo, 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 
de atribuição de regras e de privilégios.
http://https://tinyurl.com/yy2vj43k
 35
Inicialização e interrupção do PostgreSQL
INTRODUÇÃO:
Agora que você já sabe como administrar usuários, como 
otimizar uma busca por meio de shemas e como controlar 
acessos, vamos para o próximo passo. Ao término deste 
capítulo você será capaz de compreender e de realizar 
a inicialização e a interrupção do PostgreSQL, além de 
conhecer os principais parâmetros do arquivo postgresql.
conf. Você também irá aprender a parar e a reiniciar o servidor 
PostgreSQL usando estratégias smart, fast e immediate e a 
aplicar o comando kill. Você sabia que qualquer operação 
prévia de acesso ao banco de dados exige a inicialização 
do servidor PostgreSQL? Sem isso, poderá impedir a 
execução das atividades de acesso ao banco de dados. 
Pois é! 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 para desenvolver essa 
competência? Vamos lá!
Inicialização do PostgreSQL
Você já sabe que qualquer operação prévia de acesso ao banco de 
dados demanda 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 é postgres. Assim, você poderá fazer isso por meio do 
seguinte comando:
Postgres -D /bd/data
Ele 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 &
 36
Para corroborar com a auditoria e facilitaro 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 é 
usando 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 
e 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 de autenticação do cliente, para controlar a autenticação de 
clientes etc. Para visualizá-los, em pg_settings, filtre por postmaster. 
Dessa forma, a partir da documentação pública disponibilizada 
pelo PostgreSQL (2019) em seu domínio (disponível em https://www.
postgresql.org), listamos as principais utilizadas:
Localização de arquivos
http://https://www.postgresql.org
http://https://www.postgresql.org
 37
A localização de arquivos é suportada a partir da versão 9.5 do 
PostgreSQL, e sua versão atual é a 12, com a 13 já em desenvolvimento. Por 
meio da localização de arquivos é possível que 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 a ser usado para 
armazenamento de dados.
 • config_file: só pode ser definido por meio da linha de 
comando e 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 a 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, é 5432.
 • max_connections: define o número máximo de conexões 
simultâneas com o PostgreSQL. O padrão de inicialização é 
 38
de 100 conexões máximas. Contudo, pode ser configurável 
caso as configurações do kernel não suportem.
 • listen_addresses: define o(s) endereço(s) TCP / IP no(s) 
qual(is) o servidor deve escutar conexões de aplicativos 
cliente, cuja entrada 0.0.0.0 são para endereços IPv4 e : : 
para IPv6.
 • 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 3 
conexões.
 • unix_socket_directories: define o diretório do(s) soquete(s) 
do domínio Unix no(s) qual(is) 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 
tem 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, 
esse parâmetro é ignorado.
 39
 • 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 tem 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.
 • 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 é de 128 kilobytes.
 40
 • 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, como  VACUUM,  CREATE INDEX e  ALTER 
TABLE ADD FOREIGN KEY, cuja quantidade máxima 
padrão é de 64 MB.
 • 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 tem o controle do custo estimado das 
várias operações de I/O (entrada e saída) que são realizadas.
 41
 • 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. Vejamosos 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 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á anexado ao nome do arquivo 
de log com carimbo de data e hora para criar o nome do 
arquivo no formato de saída .csv.
 42
 • log_min_messages: controla quais níveis de mensagens 
são gravados 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, utiliza-se 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 
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 interrompam suas transações 
e fechem. Contudo, este sinal também aguardará as 
conexões terminarem para parar o banco.
kill -INT 1880
 • QUIT (modo immediate shutdown), envia um sinal QUIT 
para todas as conexões e, assim, são encerradas e 
fechadas imediatamente. 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:
 43
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, 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
SAIBA MAIS:
Ficou curioso 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, por meio do link 
https://www.postgresql.org/docs/12/runtime.html (Acesso 
em 20 ago. 2020).
http://https://www.postgresql.org/docs/12/runtime.html
 44
RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que 
mostramos? Agora, vamos recapitular tudo. Nós falamos 
sobre a inicialização do PostgreSQL e como armazenar 
as saídas 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 aprendemos 
a 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. 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.
 45
Carga de dados no PostgreSQL
INTRODUÇÃO:
Agora que ocê 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ê 
saberá 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 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 muitas empresas 
têm sistemas mais complexos e ambientes críticos em que 
há vários servidores funcionando simultaneamente, sem 
poderem parar? Pois é! 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 para desenvolver essa competência? Vamos lá!.
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-chave 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.
 46
DEFINIÇÃO:
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 todas, ou todas falharão.
EXPLICANDO MELHOR:
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, se ocorrer 
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 
banco de dados 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 bancos de dados existentes 
no servidor PostgreSQL é \l. Veja na Figura 23.
Figura 23
Figura 23 – Tabelas atuais
 47
Fonte: Elaborado pela autora.
NOTA:
Como já estamos conectados ao banco postgres, não 
é necessário entrar, mas se fosse, 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-chave COMMIT ou ROLLBACK. Vejamos o uso do BEGIN da 
transação na Figura 24.
Figura 24 – BEGIN da transação
Fonte: Elaborado pela autora.
Já a palavra-chave COMMIT salva as alterações no banco de dados. 
Para compreender melhor, vamos criar uma tabela e uma linha no banco 
de dados, permitindo acesso para outros usuários autorizados que se 
 48
conectarem ao banco por meio de outra sessão ou terminal. Vejamos o 
procedimento na Figura 25.
Figura 25 – Uso do COMMIT da transação
Fonte: Elaborado pela autora.
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, pois 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, pode abrir um terminal 
SQL Shell (psql) em paralelo. Para isso, basta se conectar ao postgres 
novamente e digitar o comando \d, que lista as tabelas existentes no 
banco. Veja na Figura26 a tabela chamada teste que criamos no exemplo 
anterior.
Figura 26 – Tabela teste criada no banco de dados
Fonte: Elaborado pela autora.
E se algo ocorreu errado? Basta utilizar o ROLLBACK para isso. Legal, 
não é mesmo? 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 esse comando mantém a integridade dos dados, ou 
seja, pode restaurar os dados mesmo após execuções incorretas. 
Exemplo: Vamos ver um exemplo: deletaremos a tabela teste e 
utilizaremos o comando ROLLBACK para 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.
 49
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 
28 a tela de confirmação da tabela removida.
Figura 28 – Tela de confirmação da tabela removida
Fonte: Elaborado pela autora.
Após confirmada a remoção da tabela, vamos utilizar o comando 
ROLLBACK (ver Figura 29) e observar que a tabela foi restaurada (ver 
Figura 30). 
Figura 29 – Comando ROLLBACK
Fonte: Elaborado pela autora.
Figura 30 – Tela de confirmação da restauração da tabela
Fonte: Elaborado pela autora.
Sendo assim, podemos perceber que a tabela foi retornada, 
mantendo a integridade dos dados, uma vez que tenha sido removida e 
voltado 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. 
Passo a passo: Vamos realizar um passo a passo para melhor 
compreensão:
1. Criar uma tabela e inserir uma linha. Para isso, teremos 
que iniciar o comando BEGIN. Não esqueça! Vejamos na 
Figura 31.
Figura 31 – Criação da tabela e inserção da linha
Fonte: Elaborado pela autora.
 50
2. Criar um ponto de restauração com o SAVEPOINT. Veja na 
Figura 32 que foi criado o SAVEPOINT. Após esse passo, 
você vai perceber que qualquer operação que fizermos e 
para a qual chamarmos o ROLLBACK, as operações serão 
desfeitas até este ponto.
Figura 32 – Criação da tabela e inserção da linha
Fonte: Elaborado pela autora.
3. Inserir mais duas linhas na tabela criada. Perceba (ver 
Figura 33) que houve mais duas inserções na tabela. Ao 
listar o conteúdo da tabela por meio do comando select 
* from minha_tabela;, observamos os elementos 1, 
3 e 2 inseridos. 
Figura 33 – Criação da tabela e inserção da linha
 51
Fonte: Elaborado pela autora.
4. Disparar o comando ROLLBACK até o ponto de restauração 
SAVEPOINT (ver Figura 34).
Figura 34 – ROLLBACK até o ponto de restauração
Fonte: Elaborado pela autora.
5. Visualizar a tabela após o ROLLBACK (Figura 35). Sendo 
assim, ao selecionarmos a tabela novamente, podemos 
notar que ela apresenta somente a linha inserida até o 
SAVEPOINT.
Figura 35 – ROLLBACK até o ponto de restauração
Fonte: Elaborado pela autora.
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 36 – ROLLBACK até o ponto de restauração
Fonte: Elaborado pela autora.
Uso do COPY FROM e ANALYSE
O comando COPY FROM realiza uma cópia dos dados de um 
arquivo para uma tabela. Caso ela 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 são 
utilizados o STDIN e o STDOUT, os dados são transmitidos por meio da 
conexão entre o cliente e o servidor. 
 52
Os parâmetros são estes: nome da tabela, lista de colunas a serem 
copiadas, nome do caminho absoluto do arquivo, especificação da entrada 
e da 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).
Exemplo: Vejamos um exemplo do uso do COPY e do COPY FROM, 
em que iremos realizar uma cópia de uma tabela (estados) para o cliente, 
utilizando: no primeiro comando o delimitador de campo é a barra vertical, 
e no segundo, 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 garantir que o planejador tenha 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 posteriores ao 9.0: */
VACUUM(FULL) minha_tabela;
 53
/* Para Postgres nas versões anteriores ao 9.0: */
VACUUM FULL
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.
/* Para Postgres nas versões posteriores ao 9.0: */
VACUUM (FULL, ANALYZE) minha_tabela;
/* Para Postgres nas versões anteriores ao 9.0: */
VACUUM FULL ANALYZE minha_tabela;
Podemos utilizar somente o comando ANALYSE, em que há a opção 
de ter ou não o nome da tabela. Sem o nome de uma tabela especificada, 
o comando será executado apenas nas 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, que 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. 
NOTA:
Para construir o índice sem interferir na produção, você 
deve descartar o índice e emitir novamente o comando 
CREATE INDEX CONCURRENTLY.
 54
Vejamos alguns exemplos. Vale ressaltar que qualquer um desses 
comandos podem ser forçados 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
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 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. Acesse o link https://bit.
ly/34cbmcK (Acesso em 22 ago. 2020).
http://https://bit.ly/34cbmcK
http://https://bit.ly/34cbmcK
 55
RESUMINDO:
Chegamos ao fim do nosso capítulo. Gostou do que 
mostramos? Agora, vamos recapitular tudo. Nós falamos 
sobre como realizar transações no banco de dados por 
meio das palavras-chave BEGIN, COMMIT, ROLLBACK 
e SAVEOPOINT. Agora sabemos que é possível criar um 
ponto de restauração e que, caso ocorra alguma falha, 
podemos voltar ao estado anterior e garantira 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 com o aumento das variáveis de 
configuração, reconstruindo um ou mais índices do banco 
de dados, e substituindo a versão anterior do índice. Por fim, 
vimos como analisar o resultado com o comando ANALYSE 
ou VACUUM.
REFERÊNCIAS
CARVALHO, V. PostgreSQL: Banco de dados para aplicações web 
modernas. Editora Casa do Código, 2017.
HASS, R. How Much maintenance_work_mem Do I Need? Robert Hass, 
2019. Disponível em: http://rhaas.blogspot.com/2019/01/how-much-
maintenanceworkmem-do-i-need.html. Acesso em: 22 ago. 2020.
PostgreSQL. PostgreSQL: The World’s Most Advanced Open Source 
Relational Database. Disponível em: https://www.postgresql.org. Acesso 
em: 25 ago. 2020.
 56
THE PostgreSQL Global Development Group . Documentação do 
PostgreSQL. 2005. Disponível em: http://pgdocptbr.sourceforge.net/. 
Acesso em: 18 ago. 2020.
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
Administração 
do SGBD 
PostgreSQL
Camila Freitas Sarmento
	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