Este comando utilíssimo reorganiza os índices de um banco de dados. Pode ser utilizado para indexar tabelas e seus índices, índices individualmente ou até um banco de dados inteiro.
A reorganização de índices está associada a ganhos de espaço livre em disco e a melhorias substanciais de desempenho nas aplicações de banco de dados.
Sintaxe:
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE
Aconselha-se o uso de reindex em tabelas de bom tamanho que apresentem muitas alterações e/ou muitas consultas que demandem índices rápidos.
Exemplos:
1 - Reorganiza índice citado explicitamente
- reindex index nomeíndice;
2 - Reorganiza tabela citada explicitamente
- reindex table nometabela;
3 - Reorganiza banco de dados fornecido
- reindex database nomedatabase;
4 - Reorganiza tabelas de sistema do banco de dados
- reindex system nomedatabase;
Obs.: A cláusula FORCE é obsoleta e ignorada na execução.
quarta-feira, 16 de maio de 2007
terça-feira, 15 de maio de 2007
Criação de Usuários - II
Como saber se os usuários foram devidamente criados?
A maneira mais simples é tentar se conectar ao banco de dados utilizando o novo usuário.
Uma opção mais requintada seria uma consulta à tabela de sistema pg_authid.
A maneira mais simples é tentar se conectar ao banco de dados utilizando o novo usuário.
Uma opção mais requintada seria uma consulta à tabela de sistema pg_authid.
Criação de Usuários
O comando CREATE USER permite a criação de usuários e a definição de suas permissões de acesso no ato do cadastramento. O processo é relativamente simples.
Abaixo, a sua sintaxe do comando:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| IN GROUP rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| USER rolename [, ...]
| SYSID uid
Por padrão a opção LOGIN é automaticamente definida.
O exemplo abaixo cria um usuáreio com permissão de login e sem senha.
create user usr1;
O exemplo abaixo cria um usuáreio com senha. A cláusula WITH é opcional.
create user usr2 WITH PASSWORD 'lalala';
O exemplo abaixo cria um usuário com limite de conexão até um determinado timestamp.
create user usr3 VALID UNTIL '12/31/2007';
Criação de um superusuário com permissões de criação de objetos no banco de dados.
create user usr4 SUPERUSER CREATEDB INHERIT;
Usuário com password sem a cláusula WITH. Ao se cadastrar um usuário, a senha é por padrão criptografada.
create user usr5 PASSWORD 'lalala';
A sintaxe abaixo cadastra um usuário com senha não criptografada.
create user usr6 UNENCRYPTED PASSWORD 'lalala';
Abaixo, a sua sintaxe do comando:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| IN GROUP rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| USER rolename [, ...]
| SYSID uid
Por padrão a opção LOGIN é automaticamente definida.
O exemplo abaixo cria um usuáreio com permissão de login e sem senha.
create user usr1;
O exemplo abaixo cria um usuáreio com senha. A cláusula WITH é opcional.
create user usr2 WITH PASSWORD 'lalala';
O exemplo abaixo cria um usuário com limite de conexão até um determinado timestamp.
create user usr3 VALID UNTIL '12/31/2007';
Criação de um superusuário com permissões de criação de objetos no banco de dados.
create user usr4 SUPERUSER CREATEDB INHERIT;
Usuário com password sem a cláusula WITH. Ao se cadastrar um usuário, a senha é por padrão criptografada.
create user usr5 PASSWORD 'lalala';
A sintaxe abaixo cadastra um usuário com senha não criptografada.
create user usr6 UNENCRYPTED PASSWORD 'lalala';
sexta-feira, 11 de maio de 2007
Configurando o Acesso ao Banco - O Arquivo pg_hba
O arquivo pg_hba armazena a lista de endereços IP que possuem permissão de acesso ao s bancos de dados armazenados no PostgreSQL e ainda informações sobre que bancos de dados e usuários podem fazer acesso aos mesmos.
ao se estringir os endereços IP autorizados a conectar com o servidor, consegue-se um grande ganho de segurança. A configuração do arquivo é bastante fácio de utilizar.
No windows, se encontra no caminho:
- C:\Program Files\PostgreSQL\8.*\data\pg_hba.conf
No Linux, pode ser visto na pasta:
- /etc/postgresql/pg_hba.conf
Basicamente, o pg_hba é um arquivo texto que possui em cada linha um registro de dados de autorização com os seguintes campos:
- Tipo de conexão
- Banco de Dados Permitido
- Usuário autorizado a acesso ao banco
- Endereço de conexão
- Método de autenticação
- Opção - Opções. Campo opcional dependendo do valor do campo Método de Autenticação.
- Tipo de conexão - Pode ser local, host, hostssl, hostnossl. Local - conexão Unix via socket, Host - conexão TCP/ IP criptogrsfada ou não (com ou sem SSL), Hostssl - conexão TCP/ IP sem criptografia.
- Banco de Dados Permitido - Nome do banco ou termo "all", "sameuser", "samerole", um nome de banco de dados ou uma lista de bancos de dados separada por vírgula.
- Usuário autorizado a acesso ao banco - Pode ser "all", um nome de usuário, um nome de grupo com o prefixo "+", ou uma lista separada por vírgula.
Para os campos Banco de Dados e Usuário, pode ser utilizado um nome de arquivo com prefixo "@", o qual será lido para o prrenchimento dos dados. Desta forma, é possível se alterar bancos e usuários sem liberar o acesso ao pg_hba.
- Endereço de conexão - Também chamado de CIDR-ADDRESS, é um campo opcional para conexão tipo local, sendo obrigatíorio nos demais. Corresponde a um endereço IP e uma máscara CIDR que é um inteiro entre 0 e 32 (IPv4) ou 128 (IPv6) que especifica o número de bits significativos da máscara.
Ex: 192.168.12.10/32
- Método de autenticação - Indica se a autenticação terá um nível maior ou menor de segurança. Pode ser: trust (sem verificação de password ou qualquer restrição), reject (rejeita conexões dos usuários citados com os bancos que constam na lista), md5 (requer senha criptografada para conexões), crypt (requer senha criptografada para conexões. Indicada para suceder o md5, nas versões acima da 7.2 do PostgreSQL),
password (demanda senha não criptografada para conexão), krb5 (utiliza Kerberos V5 para autenticar os usuários), ident (utiliza para a autenticação o login de sistema do usuário para conexões locais), pam (autenticação usando Pluggable Authentication Modules (PAM) do sistema operacional) .
Abaixo, alguns exemplos da internet:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# se for a minha própria máquina, não precisa autenticar...
host all all 10.15.21.244 trust
# deixar todos da rede 10.* se conectarem sem senha (perigo!)
#host all all 10.0.0.0 255.0.0.0 trust
# todos da rede 10.* com autenticação MD5
#host all all 10.0.0.0/8 md5
# o mesmo que acima, só que na rede 10.15.*
host all all 10.15.0.0/16 md5
# localmente, permitir tudo a todos sem exigir senha
local all all trust
Obs.: As alterações na configuração só são ser efetivadas ao se fazer o reload do pg_hba ou o restart do banco de dados.
ao se estringir os endereços IP autorizados a conectar com o servidor, consegue-se um grande ganho de segurança. A configuração do arquivo é bastante fácio de utilizar.
No windows, se encontra no caminho:
- C:\Program Files\PostgreSQL\8.*\data\pg_hba.conf
No Linux, pode ser visto na pasta:
- /etc/postgresql/pg_hba.conf
Basicamente, o pg_hba é um arquivo texto que possui em cada linha um registro de dados de autorização com os seguintes campos:
- Tipo de conexão
- Banco de Dados Permitido
- Usuário autorizado a acesso ao banco
- Endereço de conexão
- Método de autenticação
- Opção - Opções. Campo opcional dependendo do valor do campo Método de Autenticação.
- Tipo de conexão - Pode ser local, host, hostssl, hostnossl. Local - conexão Unix via socket, Host - conexão TCP/ IP criptogrsfada ou não (com ou sem SSL), Hostssl - conexão TCP/ IP sem criptografia.
- Banco de Dados Permitido - Nome do banco ou termo "all", "sameuser", "samerole", um nome de banco de dados ou uma lista de bancos de dados separada por vírgula.
- Usuário autorizado a acesso ao banco - Pode ser "all", um nome de usuário, um nome de grupo com o prefixo "+", ou uma lista separada por vírgula.
Para os campos Banco de Dados e Usuário, pode ser utilizado um nome de arquivo com prefixo "@", o qual será lido para o prrenchimento dos dados. Desta forma, é possível se alterar bancos e usuários sem liberar o acesso ao pg_hba.
- Endereço de conexão - Também chamado de CIDR-ADDRESS, é um campo opcional para conexão tipo local, sendo obrigatíorio nos demais. Corresponde a um endereço IP e uma máscara CIDR que é um inteiro entre 0 e 32 (IPv4) ou 128 (IPv6) que especifica o número de bits significativos da máscara.
Ex: 192.168.12.10/32
- Método de autenticação - Indica se a autenticação terá um nível maior ou menor de segurança. Pode ser: trust (sem verificação de password ou qualquer restrição), reject (rejeita conexões dos usuários citados com os bancos que constam na lista), md5 (requer senha criptografada para conexões), crypt (requer senha criptografada para conexões. Indicada para suceder o md5, nas versões acima da 7.2 do PostgreSQL),
password (demanda senha não criptografada para conexão), krb5 (utiliza Kerberos V5 para autenticar os usuários), ident (utiliza para a autenticação o login de sistema do usuário para conexões locais), pam (autenticação usando Pluggable Authentication Modules (PAM) do sistema operacional) .
Abaixo, alguns exemplos da internet:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# se for a minha própria máquina, não precisa autenticar...
host all all 10.15.21.244 trust
# deixar todos da rede 10.* se conectarem sem senha (perigo!)
#host all all 10.0.0.0 255.0.0.0 trust
# todos da rede 10.* com autenticação MD5
#host all all 10.0.0.0/8 md5
# o mesmo que acima, só que na rede 10.15.*
host all all 10.15.0.0/16 md5
# localmente, permitir tudo a todos sem exigir senha
local all all trust
Obs.: As alterações na configuração só são ser efetivadas ao se fazer o reload do pg_hba ou o restart do banco de dados.
quinta-feira, 10 de maio de 2007
Restaurando Backups...
Ao se fazer um backup com pg_dump e pg_dumpall, geralmente o resultado é armazenado em arquivos texto. Neste caso, não há problemas em se restaurar o backup. Basta se colar o texto do arquivo em algum programa e submetê-lo ao PostgreSQL.
No entanto, ao se fazer o backup, podem ter sido escolhidas opções de compactação que gerem um backup binário.
Nestas situações, pode ser utilizado o utilitário pg_restore para se recuperar os dados de sua cópia de segurança.
Caso o nome do banco de dados seja especificado, o utilitário recupera os dados dentro do banco. Do contrário, o mesmo transforma os dados recuperados em texto, gravando em um arquivo que pode ser utilizado para fazer uma restauração manual.
A sintaxe abaixo restaura um arquivo para o banco de dados:
pg_restore (caminho do arquivo de backup) -u -d (nome do banco de dados)
-u - faz com que o sistema solicite um usuário com permissões para realizar o restore (criação de objetos, por exemplo)
-d - indica o nome do banco de dados que vai receber o restore
Agora, um exemplo de restore para um arquivo texto:
pg_restore (caminho do arquivo de backup) -u -f (nome do arquivo de destino do restore)
-f - indica o caminho do arquivo texto que vai receber o restore na forma de script SQL
A geração para arquivo texto pode ser aconselhável quando se quer ter maior controle sobre as operações realizadas. No entanto, a descompactação dos dados pode exigir uma boa quantidade de espaço em disco. Meus testes revelaram que o arquivo restaurado chega facilmente a 20 vezes o tamanho do arquivo de backup original.
No entanto, ao se fazer o backup, podem ter sido escolhidas opções de compactação que gerem um backup binário.
Nestas situações, pode ser utilizado o utilitário pg_restore para se recuperar os dados de sua cópia de segurança.
Caso o nome do banco de dados seja especificado, o utilitário recupera os dados dentro do banco. Do contrário, o mesmo transforma os dados recuperados em texto, gravando em um arquivo que pode ser utilizado para fazer uma restauração manual.
A sintaxe abaixo restaura um arquivo para o banco de dados:
pg_restore (caminho do arquivo de backup) -u -d (nome do banco de dados)
-u - faz com que o sistema solicite um usuário com permissões para realizar o restore (criação de objetos, por exemplo)
-d - indica o nome do banco de dados que vai receber o restore
Agora, um exemplo de restore para um arquivo texto:
pg_restore (caminho do arquivo de backup) -u -f (nome do arquivo de destino do restore)
-f - indica o caminho do arquivo texto que vai receber o restore na forma de script SQL
A geração para arquivo texto pode ser aconselhável quando se quer ter maior controle sobre as operações realizadas. No entanto, a descompactação dos dados pode exigir uma boa quantidade de espaço em disco. Meus testes revelaram que o arquivo restaurado chega facilmente a 20 vezes o tamanho do arquivo de backup original.
segunda-feira, 7 de maio de 2007
Recuperando subconjuntos de linhas...
Em certas situações precisamos recuperar os dados em grupos de registros, por exemplo em pesquisas apresentadas ao usuário. Quem faz sites para a web se depara freqüentemente com esta situação. O PostgreSQL apresenta variações do comando select que permitem que se selecione exatamente os dados a serem buscados, com ganho de desempenho.
A sintaxe abaixo, recupera os primeiros 10 registros, utilizando a cláusula LIMIT:
select *
from TABELA
order by evento
limit 10 <==== Ao invés do número 10, pode ser atribuído qualquer número inteiro positivo.
A sintaxe abaixo, recupera os primeiros 10 registros, a partir do décimo quinto, utilizando as cláusulas LIMIT e OFFSET:
select *
from TABELA
order by evento
limit 10 offset 15
O emprego destas sinmtaxes economiza muito tempo de desenvolvimento e o desempenho das aplicações. No entanto, deve ser evitado caso sua aplicação seja portável, pois outros bancos de dados não suportam esta funcionalidade, ou a implementam utilizando outra sintaxe.
Obs: Scripts testados no PostgreSQL 8.2.
A sintaxe abaixo, recupera os primeiros 10 registros, utilizando a cláusula LIMIT:
select *
from TABELA
order by evento
limit 10 <==== Ao invés do número 10, pode ser atribuído qualquer número inteiro positivo.
A sintaxe abaixo, recupera os primeiros 10 registros, a partir do décimo quinto, utilizando as cláusulas LIMIT e OFFSET:
select *
from TABELA
order by evento
limit 10 offset 15
O emprego destas sinmtaxes economiza muito tempo de desenvolvimento e o desempenho das aplicações. No entanto, deve ser evitado caso sua aplicação seja portável, pois outros bancos de dados não suportam esta funcionalidade, ou a implementam utilizando outra sintaxe.
Obs: Scripts testados no PostgreSQL 8.2.
Backups no PostgreSQL - III
O utilitário pg_dumpall é o modo mais fácil para se fazer o backup de um banco todo, incluindo dados e metadados.
A sintaxe que mais utilizo é:
- pg_dumpall -U super_usuario_do_BD > nome_do_arquivo_de_backup_gerado
Simples assim.
O utilitário pedirá a senha do uusário digitado para realizar o backup e mais nada. Para melhor desempenho, sugiro que se crie o backup em um outro disco. Desta forma a segurança do banco também será aumentada.
Existem ainda várias opções adicionais que podem ser empregadas. Para conhecer todas, tecle:
- pg_dumpall --help
Chega de backups por enquanto!
A sintaxe que mais utilizo é:
- pg_dumpall -U super_usuario_do_BD > nome_do_arquivo_de_backup_gerado
Simples assim.
O utilitário pedirá a senha do uusário digitado para realizar o backup e mais nada. Para melhor desempenho, sugiro que se crie o backup em um outro disco. Desta forma a segurança do banco também será aumentada.
Existem ainda várias opções adicionais que podem ser empregadas. Para conhecer todas, tecle:
- pg_dumpall --help
Chega de backups por enquanto!
sexta-feira, 27 de abril de 2007
Backups no PostgreSQL - II
Backups no PostgreSQL - II
A utilização do utilitário de backup pg_dump é bem simples:
- Entrar na pasta do seu executável. No windows geralmente corresponde ao caminho:
Pasta
C:\Program Files\PostgreSQL\8.0\bin
Entrar com o comando de backup:
- pg_dump nomebanco -u > nomearquivogerado
O aplicativo pedirá o login e senha do banco de dados e após sua entrada, será gerado backup em arquivo texto no diretório desejado.
O backup englobará apenas os objetos do banco colocado como parâmetro.
Obs.: Você pode direcionar o backup para um disco diferente do que armazena seus dados. Desta forma, há um ganho de desempenho na operação pelo paralelismo de entrada/ saída na mídia de armazenamento.
Obs 2.: O uso do pg_dump permite se fazer backups menos demorados e que ocupem menos espaço em disco, em servidores com vários bancos de dados grandes. O utilitário pg_dumpall faz o backup de todos os bancos de dados do servidor, o que pode ser demorado e exceder o espaço de backup disponível.
A utilização do utilitário de backup pg_dump é bem simples:
- Entrar na pasta do seu executável. No windows geralmente corresponde ao caminho:
Pasta
C:\Program Files\PostgreSQL\8.0\bin
Entrar com o comando de backup:
- pg_dump nomebanco -u > nomearquivogerado
O aplicativo pedirá o login e senha do banco de dados e após sua entrada, será gerado backup em arquivo texto no diretório desejado.
O backup englobará apenas os objetos do banco colocado como parâmetro.
Obs.: Você pode direcionar o backup para um disco diferente do que armazena seus dados. Desta forma, há um ganho de desempenho na operação pelo paralelismo de entrada/ saída na mídia de armazenamento.
Obs 2.: O uso do pg_dump permite se fazer backups menos demorados e que ocupem menos espaço em disco, em servidores com vários bancos de dados grandes. O utilitário pg_dumpall faz o backup de todos os bancos de dados do servidor, o que pode ser demorado e exceder o espaço de backup disponível.
segunda-feira, 16 de abril de 2007
Backups no PostgreSQL - I
Backups no PostgreSQL - I
A maneira mais fácil de se fazer backup no PostgreSQL é utilizando os utilitários pg-dump e pg_dumpall (backup) e pg_restore (recuperação de backup).
Para quem não gosta de utilizar as linhas de comando, que realmente são chatas, pode utilizar estes recursos utilizando o botão direito do seu mouse no utliitário PG_ADMIN.
A maneira mais fácil de se fazer backup no PostgreSQL é utilizando os utilitários pg-dump e pg_dumpall (backup) e pg_restore (recuperação de backup).
Para quem não gosta de utilizar as linhas de comando, que realmente são chatas, pode utilizar estes recursos utilizando o botão direito do seu mouse no utliitário PG_ADMIN.
segunda-feira, 9 de abril de 2007
PGADMINIII - Relatórios...
Uma das características mais interessantes do PGADMIN III na versão 8.2 são os relatórios. São oferecidos relatórios de propriedades e estatísticas para os objetos do banco, entre outros.
A saída pode ser em XHTML ou XML, facilitando a leitura em qualquer navegador e a interoperabilidade. A geraçaõ pode ser disparada clicando o botão direito e selecionando a opção REPORTS.
quinta-feira, 5 de abril de 2007
Você sabe pronunciar o nome PostgreSQL?
Já vi muita gente falar deste banco com as pronúncias mais diversas. É engraçado de ouvir, mas é interessante se conhecer a pronúncia correta.
O link abaixo aponta para um arquivo de áudio com a pronúncia correta do nome do nosso SGBD:
http://www.dextra.com.br/img/postgresql.mp3
Atualização 06/2011: É bem comum chamar o PostgreSQL simplesmente de Postgres. Isso não é considerado errado e a pronúncia é mais fácil!
O link abaixo aponta para um arquivo de áudio com a pronúncia correta do nome do nosso SGBD:
http://www.dextra.com.br/img/postgresql.mp3
Atualização 06/2011: É bem comum chamar o PostgreSQL simplesmente de Postgres. Isso não é considerado errado e a pronúncia é mais fácil!
quarta-feira, 4 de abril de 2007
Postagem de primeiro de abril atrasada...
Abaixo, o correio da mailing list do PostgreSQL de primeiro de abril.
Destaque para as brincadeiras como a retirada do suporte ao SQL, o desenvolvedor vendendo sua alma paraa Oracle , a mudança de nome do banco e o link para empregos apontando para... Bom, você pode clicar para ver, heheheh.
============================================================
== PostgreSQL Daily News - April 01 2007 ==
PostgreSQL 10.0, now called PREGRES, was released today. Some new
features include:
- Close integration with libdwim.
- De-supporting SQL. Postgres will now use POSTQUEL exclusively.
"Eleven years of SQL is enough," said Bruce Momjian, "It's time to
get back to our roots."
- A new table type: PgISAM. Rather than using the cumbersome and
complicated MVCC model, it simply acquires global database locks on
read and write. "It took ten years to create a benchmark that would
make this one look good," said Peter Eisentraut, in explanation for
the delay.
Devrim GUNDUZ has left Command Prompt for Oracle. "I sold my soul for
money," he cackled.
== PostgreSQL Product News ==
Products? We don't need no steenkin' products!
== PostgreSQL Jobs for April ==
http://tbe.taleo.net/NA3/ats/careers/searchResults.jsp?org=MYSQL&cws=1
== PostgreSQL Local ==
Registration is open for PGCon 2007
http://www.pgcon.org/2007/registration.php
== PostgreSQL in the News ==
PostgreSQL 10.0 is the new engine for all of Microsoft's operations.
== Applied Patches ==
The CVS server is now open for public write access. Go to it!
Assinar:
Postagens (Atom)