## BANCO DE DADOS ##

Attach de um arquivo de Banco de dados apenas com MDF

2017-11-21T02:44:00.000-08:00

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’

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

2017-11-17T18:03:00.001-08:00

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

2016-12-30T04:31:00.002-08:00

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.

2016-12-09T04:07:00.003-08:00

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.

2016-12-09T04:04:00.003-08:00

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

2016-12-09T03:55:00.001-08:00

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

2016-12-08T16:59:00.000-08:00

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

2016-05-16T05:46:00.000-07:00

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

2016-05-13T09:46:00.002-07:00

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

# 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

2016-03-26T21:31:00.003-07:00

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

2016-03-26T21:28:00.002-07:00

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

2015-05-28T09:56:00.002-07:00

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

2015-05-12T07:48:00.002-07:00

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

2015-05-12T07:46:00.005-07:00

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

2015-05-11T05:59:00.004-07:00

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

2015-05-11T05:50:00.001-07:00

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

2015-05-11T05:48:00.000-07:00

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

2015-05-07T12:14:00.003-07:00

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)

COMO RECUPERAR BANCO DO POSTGRES PELO DIRETÓRIO

2015-05-05T06:32:00.000-07:00

Pode acontecer de você precisar recuperar um banco de dados Postgres, mas tendo a seu dispor apenas uma cópia de um HD como backup, e não um arquivo de backup gerado com o comando pg_dump.

Neste caso, após algumas tentativas, consegui restaurar o banco e compartilho aqui o procedimento que funcionou comigo. Ou seja, recuperação do banco através dos diretórios.

Antes de tudo, instale no servidor a versão do Postgres idêntica à versão instalada no backup.

Depois, pare o serviço Postgres. No caso do Debian, onde a dica foi testada, executei:

# sh /etc/init.d/postgresql stop

Depois, copie do backup os seguintes diretórios, substituindo os mesmos diretórios no servidor atual. Por exemplo, usando o Postgres 9.3:

/var/lib/postgresql/9.3
/usr/share/postgresql/9.3
/etc/postgresql/9.3

Obs.: recomendo renomear os diretórios originais para "nome-backup", ao invés de simplesmente substituí-los.

É necessário dar permissão para um diretório, após a cópia para o servidor:

# chmod 700 /var/lib/postgresql/9.3 -R

Inicialmente usei "777", mas o próprio sistema informou que era para mudar para 700, quando tentei iniciar o serviço.

Depois inicie o serviço:

# sh /etc/init.d/postgresql start

Pronto! Se tiver o phpPgAdmin instalado, poderá acessá-lo para visualizar o banco, acessando:

http://localhost/phppgadmin/

Talvez exista uma forma mais fácil, mas essa foi a que eu encontrei.

Foi na base do comando find que encontrei os referidos diretórios e fui copiando para o servidor.

Espero ajudar alguém na mesma situação.
Abraço!

CRIAÇÃO DE 1° SUPER USUÁRIO NO POSTGRESQL

2015-04-09T06:02:00.000-07:00

Depois de instalado seu SGBD postgres, como criar seu 1° usuário? Qual a senha de root? Qual a senha padrão?

Esta dica é para iniciantes.

Após a instalação dos seu PostgreSQL no Linux, como criar seu 1° usuário para administração do banco?

Simples, na máquina com o banco instalado siga os seguintes passos.

Logue-se como root, tecle "su root" ou "sudo -i" no Ubuntu (no terminal a mensagem padrão deve terminar com #).

Entre como usuário postgres comando:

# su postgres

Conecte no banco comando:

$ psql

Agora deve aparecer a frase de boas vindas (Bem vindo ao psql...) e você está no terminal do posgres.

Entre com o comando:

CREATE USER nomedousuario SUPERUSER INHERIT CREATEDB CREATEROLE;

e tecle enter.

Depois entre com o comando:

ALTER USER nomedousuario PASSWORD 'senha';

e tecle enter.

Pronto! Usuário criado.

Agora para este usuário acessar o banco de outras máquinas da rede devemos liberar se acesso no arquivo pg_hba.conf com o usuário root, siga os passos.

A localização deste arquivo varia segundo a distribuição Linux, no SUSE está em ~postgres/data/pg_hba.conf, no Ubuntu está em/etc/postgres/8.4/pg_hba.conf. Adicione a linha:

host all nomedousuario 0/0 password

Para que seu usuário tenha acesso de qualquer máquina a todos os bancos de dados neste servidor.

Feito isso reinicie o postgres ou recarregue como o comando:

# /etc/init.d/posgresql restart
ou
# /etc/init.d/posgresql reload

INSTALANDO O POSTGRES 9.4 NO RHEL 7

2015-04-06T12:43:00.002-07:00

Recentemente precisei instalar um Postgres DB em uma VM RHEL 7 para testes. Aproveitei para compartilhar os passos seguidos aqui no Blog. Esse passo a passo deve funcionar em qualquer Distro RHEL-like: Fedora, Centos, Scientific Linux, OEL, etc.

A instalação é a mais básica possível, pois o propósito desse DB é apenas para testes e laboratórios. Nada de tuning ou personalização.
Instale os seguintes pacotes do repositório Postgresql.org oficial.

[rsoares@rhel7-server-1 ~]$ sudo yum install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-1.noarch.rpm

[rsoares@rhel7-server-1 ~]$ sudo yum groupinstall "PostgreSQL Database Server 9.4 PGDG"

Inicialize o Postgres DB.

sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb

Habilite e teste o serviço do Postgres.

[rsoares@rhel7-server-1 ~]$ sudo systemctl enable postgresql-9.4.service

[rsoares@rhel7-server-1 ~]$ sudo systemctl start postgresql-9.4.service

[rsoares@rhel7-server-1 ~]$ sudo systemctl stop postgresql-9.4.service

Troque para o usuário de sistema do postgres.

[rsoares@rhel7-server-1 ~]$ sudo su - postgres

Altere o pg_hba.conf (espécie de “firewall” do Postgres) para permitir o acesso externo através da rede da VM.

-bash-4.2$ vim /var/lib/pgsql/9.4/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.122.0/24 md5

Altere o binding do serviço para aceitar conexões em qualquer endereço IP da VM.

-bash-4.2$ vim /var/lib/pgsql/9.4/data/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;

Logout do postgres user.

Ctrl + D no terminal

Reinicie o serviço postgres

[rsoares@rhel7-server-1 ~]$ sudo systemctl start postgresql-9.4.service
[rsoares@rhel7-server-1 ~]$ sudo systemctl status postgresql-9.4.service
postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled)
Active: active (running) since Thu 2014-11-06 17:21:38 BRST; 1s ago
Process: 24832 ExecStop=/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 25292 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
Process: 25286 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 25296 (postgres)
CGroup: /system.slice/postgresql-9.4.service
├─25296 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
├─25297 postgres: logger process
├─25299 postgres: checkpointer process
├─25300 postgres: writer process
├─25301 postgres: wal writer process
├─25302 postgres: autovacuum launcher process
└─25303 postgres: stats collector process

Confira o binding do serviço na porta TCP padrão so Postgres (5432)

[rsoares@rhel7-server-1 ~]$ netstat -tanp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:55970 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN -
tcp 0 0 192.168.122.65:22 192.168.122.1:39634 ESTABLISHED -
tcp6 0 0 ::1:25 :::* LISTEN -
tcp6 0 0 :::47021 :::* LISTEN -
tcp6 0 0 :::111 :::* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -
tcp6 0 0 ::1:631 :::* LISTEN -
tcp6 0 0 :::5432 :::* LISTEN -

Crie um novo DB User

-bash-4.2$ createuser -d -l -P --interactive NEW_DB_USER
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Crie um novo Data Base

-bash-4.2$ createdb -e -O NEW_DB_USER NEW_DB "New DataBase"
CREATE DATABASE "NEW_DB" OWNER "NEW_DB_USER";
COMMENT ON DATABASE "NEW_DB" IS 'New DataBase';

Paginando resultados com limit e offset

2015-04-02T11:05:00.003-07:00

Neste post, veremos como utilizar o LIMIT e o OFFSET para paginar resultados de uma SQL.

A cláusula LIMIT é utilizada para limitar o número de resultados de uma SQL. Então, se sua SQL retornar 1000 linhas, mas você quer apenas as 10 primeiras, você deve executar uma instrução mais ou menos assim:

SELECT coluna FROM tabela LIMIT 10;

Agora, vamos supor que você quer somente os resultados de 11 a 20. Com a instrução OFFSET fica fácil, basta proceder da seguinte forma:

SELECT coluna FROM tabela LIMIT 10 OFFSET 10;

O comando OFFSET indica o início da leitura, e o LIMIT o máximo de registros a serem lidos. Para os registros de 61 a 75, por exemplo:

SELECT coluna FROM tabela LIMIT 15 OFFSET 60;

Com este recurso, fica fácil paginar os resultados de uma SQL e mostrar ao usuário apenas a página, ao invés de retornar todos os registros da tabela. Uma tabela com 2000 registros, por exemplo, fica muito melhor mostrar ao usuário de 10 em 10, por exemplo, e diminui a carga no banco de dados, melhorando a sua performance.

Alterando Tablespace de Tabelas e Indices no PostgreSQL

2015-03-25T11:14:00.003-07:00

ALTERANDO AS TABELAS

– Cria TableSpace

CREATE TABLESPACE “banco_data” OWNER postgres LOCATION ‘/postgres/pg825/dados/pg_tblspc/banco_data';

– verifica se as tablespaces foram criadas

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

– Gera Script para alterar tabelas

SELECT ‘ALTER TABLE’ ,n.nspname AS schemaname,’.’, c.relname AS tablename, ‘SET TABLESPACE banco_data;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r'::”char”
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname

– Confere alteracao das tabelas

SELECT n.nspname AS schemaname, c.relname AS tablename, t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r'::”char”
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname, c.relname

– Verifica tabelas sem tablespace

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

ALTERANDO OS INDICES

– Cria TableSpace

CREATE TABLESPACE “banco_idx” OWNER postgres LOCATION ‘/postgres/pg825/dados/pg_tblspc/banco_idx';

– verifica se as tablespaces foram criadas

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

– Verifica quais sao os indices ( Nao primarios) e o tamanho

SELECT n.nspname AS schemaname,c.relname AS tablename,
c.relpages::numeric * 4.096 / 1024::numeric AS espaco_mb
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i'::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname

– Gera Script para alterar indices

SELECT ‘ALTER INDEX’, n.nspname AS schemaname , ‘.’ ,c.relname AS tablename, ‘SET TABLESPACE banco_idx;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i'::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname

– Confere alteracao dos indices

SELECT n.nspname AS schemaname ,c.relname AS tablename,t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i'::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname

– Verifica indice sem tablespace

SELECT n.nspname AS schemaname ,c.relname AS tablename,t.spcname AS “Tablespace”
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i'::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
AND t.spcname IS NULL
ORDER BY t.spcname DESC
– Verifica tamanho da tablespace

SELECT spcname AS “Tablespace”,
pg_size_pretty(pg_tablespace_size (spcname)) AS “Tamanho”,
spclocation as “Caminho”
FROM pg_tableSpace;

Upgrade de PostgreSQL 9.3 para 9.4 (em CentOS 7)

2015-02-10T10:45:00.001-08:00

[root@localhost ~]# su - postgres
Last login: Thu Dec 25 04:31:05 EST 2014 on pts/0
-bash-4.2$ pg_dumpall > backup.sql
-bash-4.2$ exit
logout
[root@localhost ~]# wget http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
--2014-12-25 04:33:01--  http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
Resolving yum.postgresql.org (yum.postgresql.org)... 174.143.35.196, 2001:4800:1501:1::196
Connecting to yum.postgresql.org (yum.postgresql.org)|174.143.35.196|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5328 (5.2K) [application/x-redhat-package-manager]
Saving to: ‘pgdg-centos94-9.4-1.noarch.rpm’

100%[==============================================>] 5,328       --.-K/s   in 0.002s  

2014-12-25 04:33:08 (2.46 MB/s) - ‘pgdg-centos94-9.4-1.noarch.rpm’ saved [5328/5328]

[root@localhost ~]# rpm -ivh pgdg-centos94-9.4-1.noarch.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:pgdg-centos94-9.4-1              ################################# [100%]
[root@localhost ~]# systemctl stop postgresql-9.3
[root@localhost ~]# yum -y install postgresql94 postgresql94-devel postgresql94-contrib postgresql94-libs postgresql94-test postgresql94-server postgresql94-docs
Loaded plugins: fastestmirror
base                                                                                                                                            | 3.6 kB  00:00:00     
extras                                                                                                                                          | 3.4 kB  00:00:00     
pgdg93                                                                                                                                          | 3.6 kB  00:00:00     
pgdg94                                                                                                                                          | 3.6 kB  00:00:00     
updates                                                                                                                                         | 3.4 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirror.powerhost.cl
 * extras: centos.ufms.br
 * updates: centos.ufms.br
Resolving Dependencies
--> Running transaction check
---> Package postgresql94.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
---> Package postgresql94-contrib.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
---> Package postgresql94-devel.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
---> Package postgresql94-docs.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
---> Package postgresql94-libs.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
---> Package postgresql94-server.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
---> Package postgresql94-test.x86_64 0:9.4.0-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================
 Package                                        Arch                             Version                                        Repository                        Size
====================================================================================
Installing:
 postgresql94                                   x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                           1.0 M
 postgresql94-contrib                           x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                           601 k
 postgresql94-devel                             x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                           1.6 M
 postgresql94-docs                              x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                            13 M
 postgresql94-libs                              x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                           201 k
 postgresql94-server                            x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                           3.8 M
 postgresql94-test                              x86_64                           9.4.0-1PGDG.rhel7                              pgdg94                           1.3 M

Transaction Summary
==========================================================================================
Install  7 Packages

Total download size: 22 M
Installed size: 72 M
Downloading packages:
(1/7): postgresql94-contrib-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                        | 601 kB  00:00:02     
(2/7): postgresql94-devel-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                          | 1.6 MB  00:00:01     
(3/7): postgresql94-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                                | 1.0 MB  00:00:17     
(4/7): postgresql94-docs-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                           |  13 MB  00:00:13     
(5/7): postgresql94-libs-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                           | 201 kB  00:00:00     
(6/7): postgresql94-server-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                         | 3.8 MB  00:00:04     
(7/7): postgresql94-test-9.4.0-1PGDG.rhel7.x86_64.rpm                                                                                           | 1.3 MB  00:00:05     
---------------------------------------------------------------------------------------
Total                                                                                                                                  933 kB/s |  22 MB  00:00:23     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql94-libs-9.4.0-1PGDG.rhel7.x86_64                                                                                                          1/7 
  Installing : postgresql94-9.4.0-1PGDG.rhel7.x86_64                                                                                                               2/7 
  Installing : postgresql94-devel-9.4.0-1PGDG.rhel7.x86_64                                                                                                         3/7 
  Installing : postgresql94-server-9.4.0-1PGDG.rhel7.x86_64                                                                                                        4/7 
  Installing : postgresql94-test-9.4.0-1PGDG.rhel7.x86_64                                                                                                          5/7 
  Installing : postgresql94-contrib-9.4.0-1PGDG.rhel7.x86_64                                                                                                       6/7 
  Installing : postgresql94-docs-9.4.0-1PGDG.rhel7.x86_64                                                                                                          7/7 
  Verifying  : postgresql94-contrib-9.4.0-1PGDG.rhel7.x86_64                                                                                                       1/7 
  Verifying  : postgresql94-test-9.4.0-1PGDG.rhel7.x86_64                                                                                                          2/7 
  Verifying  : postgresql94-9.4.0-1PGDG.rhel7.x86_64                                                                                                               3/7 
  Verifying  : postgresql94-devel-9.4.0-1PGDG.rhel7.x86_64                                                                                                         4/7 
  Verifying  : postgresql94-docs-9.4.0-1PGDG.rhel7.x86_64                                                                                                          5/7 
  Verifying  : postgresql94-server-9.4.0-1PGDG.rhel7.x86_64                                                                                                        6/7 
  Verifying  : postgresql94-libs-9.4.0-1PGDG.rhel7.x86_64                                                                                                          7/7 

Installed:
  postgresql94.x86_64 0:9.4.0-1PGDG.rhel7              postgresql94-contrib.x86_64 0:9.4.0-1PGDG.rhel7         postgresql94-devel.x86_64 0:9.4.0-1PGDG.rhel7         
  postgresql94-docs.x86_64 0:9.4.0-1PGDG.rhel7         postgresql94-libs.x86_64 0:9.4.0-1PGDG.rhel7            postgresql94-server.x86_64 0:9.4.0-1PGDG.rhel7        
  postgresql94-test.x86_64 0:9.4.0-1PGDG.rhel7        

Complete!
[root@localhost ~]# /usr/pgsql-9.4/bin/postgresql94-setup initdb
Initializing database ... OK

[root@localhost ~]# systemctl enable postgresql-9.4
ln -s '/usr/lib/systemd/system/postgresql-9.4.service' '/etc/systemd/system/multi-user.target.wants/postgresql-9.4.service'
[root@localhost ~]# systemctl disable postgresql-9.3
rm '/etc/systemd/system/multi-user.target.wants/postgresql-9.3.service'
[root@localhost ~]# systemctl start postgresql-9.4
[root@localhost ~]# su - postgres
Last login: Thu Dec 25 04:31:31 EST 2014 on pts/0
-bash-4.2$ psql
psql (9.4.0)
Type "help" for help.

postgres=# \q
-bash-4.2$ psql < backup.sql 
SET
SET
SET
ERROR:  role "postgres" already exists
ALTER ROLE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT
-bash-4.2$

Como instalar PostgreSQL 9.4 no CentOS e sistemas derivados

2015-02-10T10:42:00.002-08:00

Para instalar o programa no CentOS 6.4, 6.5 e 7, e ainda poder receber automaticamente as futuras atualizações dele, você deve fazer o seguinte:

Passo 1. Abra um terminal;
Passo 2. Confira se o seu sistema é de 32 bits ou 64 bits, para isso, use o seguinte comando no terminal:

uname -m

Passo 3. Se seu sistema é um CentOS 6.x de 32 bits, use o comando abaixo para adicionar o repositório do PostgreSQL 9.4:

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-centos94-9.4-1.noarch.rpm

Passo 4. Se seu sistema é um CentOS 6.x de 64 bits, use o comando abaixo para adicionar o repositório do PostgreSQL 9.4:

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Passo 5. Se seu sistema é um CentOS 7 de 64 bits, use o comando abaixo para adicionar o repositório do PostgreSQL 9.4:

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Passo 6. Atualize o gerenciador de pacotes com o comando:

yum update

Passo 7. Agora use o comando abaixo para instalar o programa;

yum install postgresql94-server postgresql94-contrib

Pronto! Agora, se o banco não for inicializado na instalação, reinicie o sistema e quando quiser, verifique a versão do PostgreSQL usando o seguinte comando em um terminal:

psql --version