Michel Metran

Michel Metran

A equação me propõe,
…computador me resolve!

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
  1. É possível acessar esse post em formato html, que possibilita ter uma visualização rápida do código;
  2. Diretamente por meio do repositório, onde está disponível este arquivo .ipynb, que permite fazer edições no código;
  3. 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

Deixe um comentário

Talvez você também goste

PowerBI: Cidades Antirracistas

menos que 1 minuto(s) de leitura

O formato padrão do PowerBI é esse abaixo, que não renderiza bem em celular. <iframe title="Projeto Cidades Antirracistas" width="600" height="373....

PowerToys

menos que 1 minuto(s) de leitura

PowerToys é um conjunto de utilitários para usuários avançados personalizarem o Windows e aumentar a produtividade. Ele inclui uma variedade de ferramentas, ...

Encontrar senha wi-fi que usam WPS

1 minuto(s) de leitura

Seguindo o vídeo Hack wifi with wps crack using reaver in kali linux 2018.1 vi a possibilidade de descobrir senhas wi-fi de roteadores que usam WPS, sem que ...