Verificando o tamanho da base de dados no PostgreSQL


Olá, neste post irei mostrar como verificar o tamanho da base de dados no PostgreSQL.

SELECT oid,datname FROM pg_database;

Esta consulta irá retornar o OID e o respectivo nome da base de dados. Com base nestas informações iremos descobrir o tamanho ocupado pela base de dados.

Acesse o diretório onde está instalado o PostgreSQL e entre no subdiretório data/base/ (ex: /var/lib/pgsql/9.3/data/base/ no Linux)

Agora com base no OID do banco de dados obtido anteriormente verificamos o tamanho da base.

Nos sistemas Linux podemos informar o seguinte comando: du -sh <OID>/ em sistemas Windows podemos verificar com o botâo direito > Propriedades para visualizar o tamanho do diretório.

 

Até a próxima

Exibindo informações das colunas, tabelas e banco de dados no PostgreSQL, MySQL e SQL Server


Alternando entre banco de dados corrente:

MySQL/SQL Server:

USE database_name;

PostgreSQL (psql):

\c database_name;

Exibindo as tabelas do banco de dados:

MySQL:

SHOW TABLES;

SQL Server:

SELECT * FROM sys.tables;

PostgreSQL (psql):  

\d;

PostgreSQL: 

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Listagem dos banco de dados:

MySQL:

SHOW DATABASES;

PostgreSQL(psql):

\l;

PostgreSQL:

SELECT datname FROM pg_database;

Exibindo as colunas de uma tabela:

MySQL: 

SHOW COLUMNS;

PostgreSQL(psql):

\d table_name;

PostgreSQL:  

SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';

MySQL: 

DESCRIBE TABLE;

PostgreSQL (psql):

d+ table_name;

PostgreSQL:

SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';

SQL Server;

exec sp_help 'table_name';

Até a próxima.

Queries dinâmicas no PostgreSQL


Em certos casos é necessário criar e executar consultas SQL dinâmicas para mostrar resultados diferentes ou até mesmo melhorar a performance das consultas.

No PostgreSQL a instrução EXECUTE é específica para esta finalidade, executando a string que contém o comando SQL.

Resultados de instruções SELECT são desprezados pelo EXECUTE. Portanto, não há como extrair o resultado de um SELECT criado dinamicamente com uma instrução EXECUTE pura. Porém existem duas formas de obter o resultado do SELECT, com o laço FOR-IN-EXECUTE ou um cursor OPEN-FOR-EXECUTE, que futuramente abordaremos.

Exemplo:

CREATE OR REPLACE FUNCTION instrucaoExecute(table TEXT, col TEXT, newValue TEXT, oldValue TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Equivalente a UPDATE table SET col='newValue' WHERE col='oldValue'
    EXECUTE 'UPDATE '||table||' SET '||col||'='||''''||newValue||''''||' WHERE '||col||'='||''''||oldValue||'''';

    RETURN 'OK';
END;
$$ LANGUAGE 'plpgsql';

-- Chamando a função
SELECT instrucaoExecute('clientes', 'endereco', 'Rua ABC, 123', 'Rua XV de Novembro, 5432');

Até a próxima

Criptografia de senhas no MySQL


Em muitos sites e outros sistemas informatizados existe a opção de cadastro e autenticação de usuários, e é sempre bom manter um nível alto de segurança em tais aplicações, o qual deve ter um cuidado cada vez maior dependendo do ramo que ela atua, como por exemplo sites de Internet Banking e outros.

Neste post irei mostrar alguns dos recursos voltados para a criptografia de dados  existentes no MySQL.

AES_ENCRYPT

Criptografa o dado com algoritmos simétricos (AES) que só pode ser descriptografada se a chave informada para recuperar o dado for igual a chave utilizada para criptografar.

Este comando possui a seguinte sintaxe:

AES_ENCRYPT('texto a ser criptografado', 'chave criptográfica')

Exemplo:

INSERT INTO usuarios(login, senha) VALUES ('meuusuario', AES_ENCRYPT('minha_senha123*', 'ASKASKSDJVS106DVSDJAHDLDSDFSBGXHG');

AES_DECRYPT

Sua função é descriptografar a sequencia que foi criptografada com o comando AES_ENCRYPT.

A sintaxe do comando é esta:

AES_DECRYPT('dado a ser descriptografado', 'chave criptográfica')

Exemplo:

Para recuperar a senha do usuário ‘meuusuario’ podemos utilizar a seguinte consulta:

SELECT AES_DECRYPT(senha, 'ASKASKSDJVS106DVSDJAHDLDSDFSBGXHG') FROM usuarios WHERE login = 'meuusuario'

PASSWORD

Esta função não é bem uma criptografia, ela na verdade embaralha os valores e até o presente momento não é possível recuperar o valor original da string.

Sua sintaxe é:

PASSWORD('string a ser embaralhada')

Exemplo

INSERT INTO usuarios(login, senha) VALUES('mynewuser', PASSWORD('mypassword'));

Para recuperar a mesma é só por comparações (tentativa/erro)

SELECT * FROM usuarios WHERE login = 'mynewuser' AND senha = PASSWORD('mypassord');

Descobrindo as tabelas que possuem uma determinada coluna


 

Olá, neste post  mostrarei como descobrir o nome de todas as tabelas de um banco de dados Microsoft SQL Server com uma simples query (consulta)
Neste caso quero descobrir o nome de todas as tabelas que possuem a coluna ‘idCliente’

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%idCliente%’
ORDER BY schema_name, table_name;

Espero que ajude

 

Até a próxima.

Liberando acessos remotos PostgreSQL


Neste post mostrarei como configurar o SGDB (Sistema Gerenciador de Banco de Dados) PostgreSQL para receber conexões remotas, pois por padrão o PostgreSQL somente recebe conexões locais (localhost ou 127.0.0.1) para acesso às suas bases de dados.

Inicialmente você deverá encontrar os arquivos postgresql.conf  e pg_hba.conf.

Após os encontrar, abra cada um deles em seu editor de textos preferido e localize as sequintes linhas:

No postgresql.conf vamos editar a seguinte linha

#listen_addresses = ‘localhost’ # what IP address(es) to listen on

Como em todo arquivo de configuração em programas linux, o # na frente da linha significa um comentário e com isso desabilita o parâmetro seguinte, tomando seu valor default, que no caso é localhost.  Descomente essa linha e substitua seu valor por *.  Assim seu PostgreSQL estará apto a ouvir conexões de qualquer IP. Então essa linha ficará da seguinte forma:

#listen_addresses = ‘localhost’ # what IP address(es) to listen on
Isso não quer dizer que ele estará aberto para qualquer IP da rede.  É possível criar políticas de acesso através de outro arquivo que falaremos a seguir.

Liberando conexões no arquivo pg_hba.conf
Este é o arquivo do Postgresql responsável pela liberação de usuários, hosts e bancos de dados.  Nele é possível dizer qual usuário de qual IP (ou rede) conectará a qual banco.  É possível liberar qualquer usuário para coenctar a qualquer banco, mas esta é uma regra cautelosa a ser seguida pois pode comprometer a segurança do seu servidor de bases de dados.
Por padrão o arquivo vem com a seguinte configuração:
# “local” is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
Pode haver alguma variação das configurações padrões de acordo com a versão utilizada, mas geralmente é algo que represente essas informações. Basicamente elas querem dizer que todas as conexões locais podem ser realizadas por qualquer usuário do banco a qualquer banco de dados.Para liberar o acesso remoto basta criar uma nova regra seguindo o seguinte padrão:

host all all 0.0.0.0/0 trust

Com essa regra qualquer usuário de qualquer IP poderá se conectar a qualquer database.
Importante: O parâmetro trust quer dizer que não será solicitada qualquer senha para conectar ao banco de dados. Então, qualquer pessoa com acesso à sua rede poderá se conectar ao servidor PostgreSQL sem nenhuma restrição. Se a sua rede for restrita como uma turma de um curso ou um ambiente de desenvolvimento interno de empresa não há riscos, porém, é necessário possuir um ambiente de rede interno isolado.

Este parâmetro é útil quando você ainda não se conectou ao banco para criar uma senha de acesso ao usuário postgres (do banco e não do sistema operacional). Então você se conecta e reseta a senha do usuário. Logo em seguida, altere o parâmetro para md5. Assim, uma senha será solicitada ao tentar conectar-se ao servidor Postgresql.

A liberação fica da seguinte maneira:
host all all 0.0.0.0/0 md5
É possível também liberar o acesso apenas a uma rede específica:

host all all 192.168.0.0/32 md5
Ou também, informar qual usuário poderá conectar:

host all usuariodobanco 192.168.0.0/32 md5
Também informar  o banco de dados a ser conectaro por este usuário:
host bancodedados usuariodobanco 192.168.0.0/32 md5
Na regra acima, o usuário usuariodobanco poderá conectar-se apenas ao banco bancodedados apenas se estiver dentro da rede 192.168.0.0.
Bom, por hoje é só, espero que seja útil.
T+

Efetuando backup e restore no SQL Server por meio de consultas SQL


Olá, neste post mostrarei como fazer o backup e o restore de banco de dados do SQL Server por meio de scripts (consultas SQL), anteriormente eu mostrei como fazer essas operações no modo gráfico, sem ter que digitar muita coisa (neste post):

Vamos para a parte prática:

Abra seu SQL Server e crie uma nova consulta, vamos supor qu você  possua uma base de dados chamada SistemaAcademico, pode ser com qualquer base existente em seu sistema..

Digite nesta consulta as seguintes linhas para fazer o backup com compressão de dados, verificação de erros e espelhamento do backup.
Continue reading “Efetuando backup e restore no SQL Server por meio de consultas SQL”