Attach de um arquivo de Banco de dados apenas com MDF

Blog: https://cristianojsantos.wordpress.com/

Normalmente faríamos este processo utilizando as seguintes sequências de telas:

Figura 1 – Selecionando a Opção de Attach no Banco de Dados

Figura 2 – Selecionando o arquivo MDF do Banco de dados que iremos atachar.

Desta forma, ao selecionarmos nosso banco de dados, conforme veremos na Figura 3 abaixo, o mesmo somente terá o arquivo MDF sendo informado, onde, para o Arquivo de Log, aparece uma mensagem ao lado direito da indicação do caminho do arquivo, informando que o mesmo não fora localizado.

Se tivéssemos o arquivo LOG disponível no computador/servidor, bastaria somente informarmos seu local de origem.

Figura 3 – Apresentando o arquivo MDF sem o Log informado.

Para resolvermos este problema, poderemos agir de duas formas, sendo a primeira:

– Removendo via Tela do SSMS a informação do arquivo de Log, selecionando o mesmo na tela que estamos utilizando para atachar o arquivo, conforme a Figura 4 demostra.

Figura 4 – Removendo o arquivo de Log pelo SSMS

– Executando uma query com a Procedure sp_attach_single_file_db, que espera receber dois parâmetros:

• @dbname nvarchar(128): Nome do Banco de Dados
• @physname nvarchar(260): Caminho de origem do arquivo MDF do Banco de Dados

No final, a query será interpretada da seguinte forma:

EXEC sp_attach_single_file_db @dbname=’AdventureWorksDW2012′,@physname=’D:\Software\SQL Server\MSSQL11.SQLBI01\MSSQL\DATA\AdventureWorksDW2012_Data.mdf’

GO

Figura 5 – Executando a query que irá atachar o banco de Dados AdventureWorksDW

Após, o banco de dados será criado e já estará disponível no SSMS, conforme Figura 6 abaixo.

Figura 6 – Banco de Dados criado e disponível no SQL Server

O servidor SMTP requer uma conexão segura ou o cliente não foi autenticado

Ao configurar o envio de email pelo sqlserver 2012 corretamente aparecia a seguinte mensagem de erro:

Gmail Error :The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required

Message "O servidor SMTP requer uma conexão segura ou o cliente não foi autenticado. A resposta do servidor foi: 5.5.1 Authentication Required. Learn more at" string

Depois de inúmeras tentativas descobri que o problema estava na própria configuração do gmail. Eles possuem uma configuração para aplicativos "menos" seguros conforme a imagem abaixo:

Retirar Zeros a Esquerda com PostgreSQL

Exemplo:

create table funcionarios
(
codigo character varying(10) not null,
nome character varying(100) not null,
salario numeric(15, 2)
);

insert into funcionarios (codigo, nome, salario) values ('000232', 'Zorrilho', 672.40);
insert into funcionarios (codigo, nome, salario) values ('000239', 'Graxaim', 672.40);
insert into funcionarios (codigo, nome, salario) values ('001431', 'Lebre', 1402.46);

select
trim(leading '0' from codigo) as codigo,
nome,
salario
from funcionarios;

Listar BUSCAS/SELECTs que já foram feitos a uma tabela.

SELECT sum(seq_scan+idx_scan) AS total_de_leituras
FROM pg_stat_user_tables
WHERE relname=’nome_da_tabela’;

Obs.: Um select com um JOIN considera duas BUSCAS

Lista as tabelas ligadas por chave estrangeira a uma determinada tabela.

SELECT tab_pk.relname AS “tab PK”, tab_fk.relname AS “tab_FK”
FROM pg_class tab_pk
JOIN pg_constraint     ON tab_pk.oid=pg_constraint.confrelid
JOIN pg_class tab_fk  ON pg_constraint.conrelid=tab_fk.oid
and tab_fk.relname=’cliente’;

work mem

Quando algum processo pesado é executado, por exemplo a geracao de indices do drupal ou consultas que usam muito ORDER BY, um diretorio pgsqltemp será criado caso o uso da memoria setado na variavel WORKMEM seja execedido.

Nestes caso o aconselhável é aumentar - perante monitoramento - o parametro WORK_MEM na sessão em que está executando a query ou rodando os indices.

SET WORK_MEM TO "64MB"

Esse valor é por consulta, se colocar um valor muito alto a máquina vai sentar bonito, procure um valor entre 64 e 128, monitorando o servidor para verificar o impacto.

Listar tabelas de um BD em Postgresql

SELECT * FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY schemaname, tablename

Postgresql - Formatar CPF com REGEXP_REPLACE

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função REGEXP_REPLACE para formatar o CPF.

1º Exemplo

Para formatar o CPF vamos utilizar a tabela "tb_alunos", exibida na imagem a seguir:

 Solução

Observe que na função REGEXP_REPLACE:

• Utilizamos parenteses "( )" para separar cada parte da string, neste caso o CPF;
• Utilizamos colchetes   "[ ]" para indicar quais os caracteres que iremos utilizar em cada parte, neste caso utilizamos caracteres numéricos de "0" até "9", representados pela expressão 0-9;
• Indicamos dentro das chaves "{}" a quantidade de dígitos que vamos utilizar; 
• Utilizamos contra-barra "\" antes de cada parte criada;

Após a execução da sentença, teremos o seguinte resultado:

 2º Exemplo

Também podemos substituir a expressão "0-9" que indica a utilização de caracteres numéricos de 0 até 9, pela expressão [:digit:]. O resultado será o mesmo. 

Solução

Após a execução da sentença, teremos o seguinte resultado:

3º Exemplo

Para facilitar, podemos substituir a expressão "[[:digit:]]" que indica a utilização de caracteres numéricos de 0 até 9, pela expressão abreviada "\d". O resultado será o mesmo.

Solução

Após a execução da sentença, teremos o seguinte resultado:

COMANDOS PSQL

Além de realizar comandos SQL, o psql também inclui alguns comandos próprios. Por exemplo, para sair do psql, você deve usar o comando \q (note que é uma barra invertida mesmo). 

Existem várias opções interessantes, veja alguns exemplos:

• \d: lista as tabelas do banco de dados
• \dv: lista as views do banco de dados
• \di: lista os índices do banco de dados
• \db: lista as tablespaces
• \l: lista os bancos de dados
• \dg: lista as roles existentes (usuários ou grupos)
• \conninfo: apresenta informações sobre a conexão atual
• \h: lista os comandos SQL
• \h comando: apresenta detalhes sobre o comando
• \dn: lista os esquemas do banco de dados
• \c BASE: conecta a base
• \d TABELA: apresenta a estrutura da tabela

Suponha que eu queira fazer uma conexão ao banco de dados BANCO, com o usuário USER, que fica no servidor 10.10.10.10

Para isto, eu deveria usar o comando: 

# psql -h 10.10.10.10 -U USER-d BANCO

OU

# psql -U USER

Se vc esquecer o usuário que está usando, basta usar o comando:

#select current_user; 

É importante lembrar que o PostgreSQL faz subdivisões no banco de dados, os esquemas. Por padrão, o psql tenta se conectar primeiro a um esquema cujo nome seja igual ao do usuário. 

Caso esse esquema não exista, o psql conecta-se ao esquema public. Se você quiser, por exemplo, se conectar ao esquema testes, deverá executar o comando: 

#set search_path to TESTE; (conecta ao esquema TESTE)

Criando Ranks no Postgres

Vamos supor que você tenha a seguinte tabela:

E você tenha que ordená-la de acordo com os seguintes critérios: a maior média por departamento, seguido de maior nota a, maior nota b e finalmente maior nota c. Fica bem simples se usarmos a cláusula order by:

SELECT department, name, grade_a, grade_b, grade_c
FROM candidates
ORDER BY department, ((grade_a + grade_b + grade_c) / 3) DESC,
grade_a DESC, grade_b DESC, grade_c DESC

Resultado:

Mas agora queremos criar uma qualificação indicando a posição do candidato de acordo com os critérios acima e esta posição deve ser relativa ao departamento que ele pertence, ou seja queremos saber os trê melhores qualificados no departamento "Development" e os três melhores no departamento "Marketing". Talvez muitos já estejam pensando em usar sua linguagem de programação favorita para resolver este problema mas no Postgres temos as Window Functions:

SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates

Resultado:

Legal, agora temos um número indicando a posição de cada candidato relativo ao seu departamento. Mas digamos agora que apenas os dois melhores candidatos de cada departamento devem ser retornados. Neste caso a seguinte query deve ser satisfatória:

SELECT * FROM (SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates) AS sub_query
WHERE rank < 3

Resultado:

Número de conexões PostgreSQL

Comando:
select datname, count(*) from pg_stat_activity group by datname;

Com este comando podemos ver o número de conexões ativas no momento de execução agrupando-as pelo nome do banco de dados.
A saída será semelhante a isso:

  datname  | count
-----------+-------
 testdb    |     5
 template1 |     1
(2 rows)

A tabela pg_stat_activity também tem outras colunas com outras informações interessantes:

datid - oid do banco de dados
datname - nome do banco de dados
procpid - id do processo
usesysid - OID do usuário
usename - nome do usuário
current_query - query sendo executada atualmente
waiting - status de espera da query
xact_start - horário em que a transação atual começou a executar
query_start - horário em que a query começou a executar
backend_start - horário em que o processo foi iniciado
client_addr - Endereço do cliente
client_port - Porta do cliente

As colunas que mostram informações sobre a query só podem ser vistas pelo super usuário ou se o usuário que estiver vendo as informações for o mesmo do processo que está sendo listado.

Tipos de backup do PostgreSQL

Diferentes maneiras de fazer backup dos bancos de dados do PostgreSQL.

O PostgreSQL possui varias formar de se fazer backup, podendo ser através de arquivos SQL, tar, ou customizado, além de poder fazer backup de apenas uma base ou todos de uma só vez, é para o caso de backups muito grandes a opção de dividir o arquivo em vários; vou mostrar as formas mais utilizadas de backup no PostgreSQL.

Backup de um único banco em formato SQL

pg_dump -U usuario -d banco > backup.sql

Backup de um único banco em formato tar

pg_dump -Ft -U usuario -d banco > backup.tar

Backup de um único banco em formato especifico do PostgreSQL

pg_dump -Fc -U usuario -d banco > backup

Por padrão o pg_dump não faz backup de objeto grandes dentro do banco, para ativar esta opção é necessário utilizar a opção "-b" como por exemplo.

Backup de um único banco em formato especifico e com objetos grandes

pg_dump -Fc -b -U usuario -d banco > backup

Backup de uma única tabela de único banco

pg_dump -Fc -b -U usuario -d banco -t nomedatabela > backup

Backup de todos os bancos do servidor 

pg_dumpall -Fc -b -U usuario > backup

Backup remoto de um banco

pg_dump -h hostremoto -d nomebanco | psql -h hostlocal -d banco

Backup em multivolumes (exemplo de volumes de 600MB)

pg_dump nomebanco | split -m 600 nomearquivo

OBS: m para 1Mega, k para 1K, b para 512bytes

Pessoalmente eu prefiro utilizar como backup o formato customizado com a opção dos blocos grandes ativados. "-Fc -b", esse tipo de backup é mais rápido tanto na hora de fazer como na hora de voltar além de sair em um tamanho bem reduzido em relação aos demais.

Listando as 10 maiores tabelas no PostgreSQL

Para listar as maiores tabelas do seu banco de dados, utilize a consulta abaixo:

WITH table_stats AS (

  SELECT

    schemaname,

    tablename,

    pg_relation_size(schemaname || '.'|| tablename) as table_size,

    (pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size,

    pg_total_relation_size(schemaname || '.'|| tablename) as total_size

  FROM

    pg_tables

)

SELECT

  table_stats.schemaname,

  table_stats.tablename,

  pg_size_pretty(table_stats.table_size) as table_size,

  pg_size_pretty(table_stats.index_size) as index_size,

  pg_size_pretty(table_stats.total_size) as total_size

FROM

  table_stats

WHERE

  -- ajuste o filtro conforme sua necessidade!

  table_stats.schemaname = 'public'

ORDER BY

  table_stats.total_size desc,

  table_stats.index_size desc,

  table_stats.table_size desc

LIMIT 10;

Tamanho das tabelas no PostgreSQL

Em muitos casos é preciso saber o espaço em que as tabelas do banco de dados estão utilizando em disco, para fins de monitoramento e decisão de estruturação de banco. Para tal tarefa no PostgreSQL o comando abaixo resolve o problema.

SELECT esquema, tabela,
       pg_size_pretty(pg_relation_size(esq_tab)) AS tamanho,
       pg_size_pretty(pg_total_relation_size(esq_tab)) AS tamanho_total
  FROM (SELECT tablename AS tabela,
               schemaname AS esquema,
               schemaname||'.'||tablename AS esq_tab
          FROM pg_catalog.pg_tables
         WHERE schemaname NOT
            IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
 ORDER BY pg_total_relation_size(esq_tab) DESC;

A coluna tamanho mostra o tamanho que os registros (tuplas) da tabela está ocupando no disco, e a coluna tamanho_total inclui também os TOASTs e os índices associados à tabela.

Com essa consulta, é possível inclusive montar scripts (em shellscript, perl, python, groovy, etc..) de monitoramento do banco, por exemplo: caso a tabela tal, ou a soma de todas as tabelas chegar a X Mb, envie um email de alerta. Outra utilização seria de geração de indicadores para análise de tendência da expansão do banco.

Localizando e Excluindo Registros Duplicados

Evitar a ocorrência de registros duplicados garante a integridade de um banco de dados, pra isso, planejar e prever situações no ato da modelagem do banco são imprescindíveis para não ter dor de cabeça.

No entanto, é comum nos deparamos com bases de dados repletas de registros duplicados, o que pode ter ocorrido por conta de um mal planejamento ou, forçado devido a importação de dados externos.

Localizar e eliminar estes registros, será o que veremos neste artigo.

• Simulando Registros Duplicados

A forma mais simples de localizar os duplicados é através dos comandos GROUP BY e HAVING. Vamos popular uma tabela de produtos a fim de duplicar registros:

 Create Table produto (  
  id_produto bigint not null,  
  descricao varchar (100),  
  estoque int not null default 0  
 ) ;

  Insert Into produto    
  Values   
  (1, "Produto A", 10),   
  (2, "Produto B", 10),   
  (3, "Produto C", 10),   
  (4, "Produto B", 12),   
  (5, "Produto C", 8),   
  (6, "Produto D", 10),   
  (7, "Produto E", 10),   
  (8, "Produto E", 10),   
  (9, "Produto F", 10);   

• Identificando os Duplicados

Vejam que os produtos B, C e E estão duplicados, vamos então apresenta-los utilizando um simples select:

 select   
  *   
 from  
   produto   
 group by descricao   
 having count(descricao) > 1 ;  

 id_produto descricao estoque  
 ---------- --------- -------  
      2 Produto B    10  
      3 Produto C    10  
      7 Produto E    10  

Localizar os registros foi possível pois, agrupamos os diferentes produtos em um só (group by), após, adicionamos a condição para que somente os registros que contenham mais de uma ocorrência (having...) fossem apresentados. (Mais de uma ocorrência após agrupado). No exemplo, filtramos somente os registros com mais de uma ocorrência, mas você pode especificar a quantidade desejada.

Alterar Schema de Tabela no PostgreSQL

Em sua configuração original, quando criamos uma tabela (ou qualquer outro objeto) no PostgreSQL sem especificar o schema que ela deve ficar, automaticamente o PG irá cria-lá no schema public.

Caso você necessite alterar o schema de suas tabelas, você pode realizar um alter table, através da seguinte 

instrução SQL:

 ALTER TABLE SCHEMA.SUA_TABELA SET SCHEMA NOME_SCHEMA_DESTINO;  

No exemplo, aponto o schema qual a tabela esta atualmente, desta forma posso estar conectado a qualquer 

schema dentro do mesmo banco de dados que conseguirei realizar a mudança.

Como zerar um campo auto_increment no MySQL

Você pode redefinir o valor do campo auto increment de duas formas, vejamos:

1º - Através de Alter Table.

 ALTER TABLE tabela AUTO_INCREMENT = 1;  

2º - Através de Truncate Table (Cuidado com esta alternativa).

 TRUNCATE TABLE tabela;  

No primeiro caso os dados são mantidos em sua tabela, somente os valores do campo que você configurou como auto incremento serão atualizados, já no segundo caso toda sua tabela é apagada, por isso, somente execute este passo se realmente deseja reiniciar a numeração sem se importar com os dados, e o mais importante, não custa ter um backup da tabela.

Configurando Stream Replication No PostgreSQL 9

Esse tutorial explica como configurar 2 servidores PosgreSQL como master e slave com stream replication, é bem simples.

Cenário:

• Master 192.168.1.200 (Debian 6 – PostgreSQL 9)
• Slave 192.168.1.201 (Debian 6 – PostgreSQL 9)

Configuração do Master: 

No arquivo postgresql.conf, alterar as seguintes opções:

listen_address = ‘*’ # Reponderá por qualquer ip cadastrado no server
wal_level = hot_standby
max_wal_senders = 1 # Número maximo de servidores slave
wal_keep_segments = 32

No arquivo pg_hba.conf, adicione o servidor slave para replicação:

host    replication     postgres         192.168.1.201/32        trust

Configuração do Slave 

Criar o arquivo recovery.conf no mesmo diretório do arquivo postgresql.conf, com o seguinte conteúdo:

standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.200 port=5432 user=postgres’
trigger_file = ‘/tmp/pgsql.trigger’

Parar o PostgreSQL dos 2 servidores:

[postgres@server ~]$ /usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data/

[postgres@client ~]$ /usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data/

Agora você deverá copiar todo o conteúdo do data do master para o data do slaver:

[postgres@server ~]$ rsync -a -v -e ssh /usr/local/pgsql/data/ 192.168.1.201:/usr/local/pgsql/data/ –exclude postmaster.pid

E no arquivo postgresql.conf do slave, alterar as seguintes opções:

hot_standby = on

Agora você pode iniciar os 2 serviços do PostgreSQL:

[postgres@server ~]$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data/

[postgres@client ~]$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data/

Para verificar se a replicação está certa você pode ver assim:

No master:

[postgres@server ~]$ ps ax | grep sender

1573 ?        Ss     0:00 postgres: wal sender process postgres 192.168.1.201(53314) streaming 0/C0000D0

No slave:

[postgres@server ~]$ ps ax | grep receiver

3180 ?        Ss     0:00 postgres: wal receiver process   streaming 0/C0000D0

Testando a replicação:

No master crie uma tabela teste e popule:

postgres=# CREATE TABLE tabelaTeste (valor varchar(30));

postgres=# INSERT INTO tabelaTeste VALUES(‘teste1′);

postgres=# INSERT INTO tabelaTeste VALUES(‘teste2′);

Agora no server slave (stand by) veja se a tabela “tabelaTeste| foi criada:

postgres=# SELECT * FROM tabelaTeste;

Se o resultado for esse abaixo, está correto:

valor
—————
teste1
teste2
(2 rows)