PostGreSQL

6 minuto(s) de leitura

O PostgreSQL é o banco de dados tenho mais interesse em aprender devido a extensão PostGIS (Spatial and Geographic Objects for PostgreSQL) empregada em análises com dados espaciais.

Aprendi comandos básicos quando estudei Django, e aprendi com mais profundidade durante um curso realizado em junho/2020 com Daniel Robert Costa, quando então aprofeitei para tomar nota de suas explicações nesse documento.

OBSERVAÇÃO
Esse post tem a finalidade de mostrar os comandos básicos e me deixar com uma "cola" rápida para meu uso cotidiano.
Todas os códigos são exemplificativos e podem/devem ser alterados, indicando o nome dos arquivos e diretórios corretamente.

INFORMAÇÃO

É possível acessar esse post em formato html, que possibilita ter uma visualização rápida do código;
Diretamente por meio do repositório, onde está disponível este arquivo .ipynb, que permite fazer edições no código;
Ou ainda, de maneira interativa, usando o MyBinder, que possibilita rodar e editar o código sem a necessidade de instalar nada.

Comandos Administrativos

Comandos para PostgreSQL instalado via repositórios do Ubuntu

pg_lsclusters lista todos os clusters PostgreSQL
pg_createcluster {version} {clustername} lista todos os clusters PostgreSQL
pg_dropcluster {version} {clustername} apaga um cluster PostgreSQL
pg_ctlcluster {version} {clustername} {action} Start, Stop, Status, Restart, Reload do cluster PostgreSQL

Start / Stop

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
sudo service postgresql status

Inicializar

sudo -u postgres psql
psql -U postgres
psql -h 127.0.0.1 -p 5432
psql -h 127.0.0.1 -p 5432 -U postgres
psql -h 127.0.0.1 -p 5432 -U {username} {db name}

Comandos para gerenciar database

Database

Cria

CREATE DATABASE {db name};
CREATE DATABASE django_jobs;
CREATE DATABASE db_escola OWNER univesp;
CREATE DATABASE opentesouro OWNER django;
CREATE DATABASE db_sabesp OWNER postgres;

Deleta

DROP DATABASE {db name};
DROP DATABASE django_jobs;
DROP DATABASE tesourodireto;
DROP DATABASE db_escola;

Listar

\l

Conectar

\c databasename;
\c sabesp;

Schema

Cria

CREATE SCHEMA IF NOT EXISTS {schema name}

Deleta

DROP SCHEMA IF EXISTS {schema name}

Usuários ou Roles

Após a versão 8.1, users e roles são sinônimos e tem as mesmas propriedades.

Cria

CREATE USER {username};
CREATE USER django;
CREATE USER michelmetran;
CREATE USER michelmetran WITH PASSWORD '12345';
CREATE USER django WITH PASSWORD '12345';
CREATE ROLE univesp NOCREATEDB NOCREATEROLE INHERIT NOLOGIN NOBYPASSRLS CONNECTION LIMIT 10;
CREATE ROLE univesp SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN BYPASSRLS CONNECTION LIMIT 10;
CREATE ROLE daniel LOGIN PASSWORD '12345' IN ROLE professores;

Deleta

DROP USER {username};
DROP USER django_user;
DROP ROLE univesp;

Altera

ALTER USER django_user
ALTER USER univesp WITH PASSWORD null;
ALTER USER django WITH PASSWORD '12345';
ALTER USER postgres SUPERUSER;
ALTER ROLE michelmetran PASSWORD '12345';

Listar Usuários

\du

Grant e Revoke

GRANT ALL ON DATABASE {db name};
GRANT ALL ON TABLE teste TO professores;

REVOKE ALL ON ALL TABLES IN SCHEMAS {schema name} FROM {username};
REVOKE ALL ON DATABASE {db name} FROM {username};
REVOKE ALL ON SCHEMA {schema name} FROM {username};

Tabelas

Criar

CREATE TABLE teste (nome varchar);

Listar

\dt

Sair

\q

Tipos de Dados

Key

smallkey
bigkey

Caracter

varchar(n)
char(n)

Date

timestamp (com e sem timezone)
timestamp (com e sem timezone)
date
interval

Bolleano

booleano

DML e DDL

Data Manipulation Language (DML)

INSERT, UPDATE, DELETE, SELECT

DLL (Data Definition Language)

CREATE TABLE
DROP TABLE
ALTER TABLE

CREATE TABLE IF NOT EXIST {db name} (
codigo INTEGER PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
data_criacao TIMESTAMP NOT NULL DEFAULT NOW(),
telefone INTEGER,
PRIMARY KEY (column name),
)

ALTER TABLE {db name} ADD COLUMN tem_poupanca BOOLEAN;

INSERT INTO {db} (codigo, nome, data_criacao)
VALUES (100, 'Itaú', now());

INSERT INTO {db} (codigo, nome, data_criacao)
SELECT 100, 'Itaú', now();

UPDATE {table name} SET
codigo =  1,
nome = 'Itaú',
WHERE data_criacao IS NULL;

CREATE TABLE IF NOT EXISTS banco ( numero INTEGER NOT NULL, nome VARCHAR(50) NOT NULL, ativo BOOLEAN NOT NULL DEFAULT TRUE, data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(numero) )

Aula

CREATE TABLE IF NOT EXISTS banco (
	numero INTEGER NOT NULL,
	nome VARCHAR(50) NOT NULL,
	ativo BOOLEAN NOT NULL DEFAULT TRUE,
	data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(numero)
);

CREATE TABLE IF NOT EXISTS agencia (
	banco_numero INTEGER NOT NULL,
	numero INTEGER NOT NULL,
	nome VARCHAR(80) NOT NULL,
	ativo BOOLEAN NOT NULL DEFAULT TRUE,
	data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(banco_numero, numero),
	FOREIGN KEY(banco_numero) REFERENCES banco(numero)
);

CREATE TABLE IF NOT EXISTS cliente (
	numero BIGSERIAL PRIMARY KEY,
	nome VARCHAR(120) NOT NULL,
	email VARCHAR(220) NOT NULL,
	ativo BOOLEAN NOT NULL DEFAULT TRUE,
	data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS contacorrente (
	banco_numero INTEGER NOT NULL,
	agencia_numero INTEGER NOT NULL,
	numero BIGINT NOT NULL,
	digito SMALLINT NOT NULL,
	cliente_numero BIGINT NOT NULL,
	ativo BOOLEAN NOT NULL DEFAULT TRUE,
	data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(banco_numero, agencia_numero,numero, digito, cliente_numero),
	FOREIGN KEY(banco_numero, agencia_numero) REFERENCES agencia(banco_numero, numero),
	FOREIGN KEY(cliente_numero) REFERENCES cliente(numero)
);

CREATE TABLE IF NOT EXISTS tipo_transacao (
	id SMALLSERIAL PRIMARY KEY,
	nome VARCHAR(120) NOT NULL,
	ativo BOOLEAN NOT NULL DEFAULT TRUE,
	data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cliente_transacoes (
	id BIGSERIAL PRIMARY KEY,
	banco_numero INTEGER NOT NULL,
	agencia_numero INTEGER NOT NULL,
	conta_corrente_numero INTEGER NOT NULL,
	conta_corrente_digito INTEGER NOT NULL,
	cliente_numero BIGINT NOT NULL,
	tipo_transacao_id SMALLINT NOT NULL,
	valor NUMERIC(15,2) NOT NULL,
	data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY(banco_numero, agencia_numero, conta_corrente_numero, conta_corrente_digito, cliente_numero) REFERENCES contacorrente(banco_numero, agencia_numero,numero, digito, cliente_numero)
);

SELECT *
FROM information_schema.columns
WHERE table_name = 'banco'

Transactions

BEGIN;

COMMIT;

SAVE POINT

ROLLBACK;

Funções

Arquivos de Configurações

postgresql.conf

Arquivo que armazena todas as informações das configurações do banco de dados. A view pgsettings, acessada dentro do banco de dados, guarda todas as configurações atuais, sendo necessário, por vezes, reinicializar o bando de dados para que a _view seja atualizada.

Por padrão, o arquivo encontra-se dentro do diretório PGDATA definido no momento da inicialização do cluster do banco de dados. No Ubuntu, quando instalado pelo repositório oficial, o local do arquivo será diferente do diretório de dados:

/etc/postgresql/{versão}/{nome do cluster}/postgresql.conf
/etc/postgresql/12/main/postgresql.conf

As configurações do arquivo podem ser acessadas também por querys, tais como:

SHOW listen_addresses;                     # Endereços TCP/IP das interfaces que o servidor vai escutar
SHOW port;                                 # Porta TCP que o servidor vai ouvir (Padrão é 5432)
SHOW max_connections;                      # Nº máximo de conexões
SHOW superuser_reserved_connections;       # Nº máximo de conexões de superusuários
SHOW data_directory;                       # Diretório onde os dados são armazenados
SHOW authentication_timeout;               # Tempo máximo para o cliente conseguir conexão
SHOW password_encryption;                  # Algoritmo de criptografia das senhas dos novos usuários
SHOW ssl;                                  # Habilita conexção criptografada
SHOW shared_buffers;                       # Tamanho da memória cache/buffer de tabelas e índices
SHOW work_mem;                             # Tamanho da memória para operações de agrupamento e ordenação
SHOW maintenance_work_mem;                 # Tamanho da memória para operações como vaccum, index

pg_hba.conf

Arquivo responsável pelo controle de autenticação dos usuários no servidor PostGreSQL

Exemplo

pg_ident.conf

Arquivo responsável por mapear os usuários do sistema operacional com os usuários do bando de dados. Localizado no diretório de dados PGDATA de sua instalação. A opção ident deve ser utilizada no arquivo pg_hba.conf.

Interface Gráfica

O pgAdminIV é a inferface gráfica do bando de dados. Uma vez instalado corretamente, estará disponível no localhost

Erros

Encerrar o que estiver na porta 5432

sudo fuser -k 5432/tcp

Referências

Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails, no StackOverflow.
Como Instalar e Utilizar o PostgreSQL no Ubuntu 18.04, no DigitalOcean
Como instalar o pgAdmin4 no Ubuntu e derivados, no Edivaldo Brito.
Install pgAdmin 4 on Ubuntu 20.04/18.04/16.04, no Computing for Geeks.
Skipping acquire of configured file ‘main/binary-i386/Packages’, no StackOverflow.
How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04, usado em outubro de 2020.

Compartilhe

Twitter Facebook LinkedIn

Michel Metran

Comandos Administrativos

Start / Stop

Inicializar

Comandos para gerenciar database

Database

Cria

Deleta

Listar

Conectar

Schema

Cria

Deleta

Usuários ou Roles

Cria

Deleta

Altera

Listar Usuários

Grant e Revoke

Tabelas

Criar

Listar

Sair

Tipos de Dados

Key

Caracter

Date

Bolleano

DML e DDL

Data Manipulation Language (DML)

DLL (Data Definition Language)

Aula

Transactions

Funções

Arquivos de Configurações

postgresql.conf

pg_hba.conf

pg_ident.conf

Interface Gráfica

Erros

Referências

Compartilhe

Deixe um comentário

Talvez você também goste

PowerBI: Cidades Antirracistas

Divisão Administrativa do TJSP

PowerToys

Encontrar senha wi-fi que usam WPS