Dicas postgreSQL

Retornar o número de usuários conectados
select count(*) from pg_stat_activity

Mostrar uso dos índices dos bancos de dados:

select * from pg_statio_user_indexes;

select * from pg_stat_user_indexes;

Mostra estatística de uso das tabelas e manutenção:
select * from pg_stat_all_tables;

Mostra todas as tabelas do atual esquema do atual banco:
select * from pg_stat_user_tables;

Visualizar os processos do PostgreSQL:

ps auxww | grep ^postgres

Tamanho de Tabela, Banco ou Todos os Bancos - PostgreSQL

Tamanho de Banco de Dados:

select pg_database_size(‘nomebanco’);

Tamanho de Tabela
select pg_tablespace_size(‘nometabela’);

Tamanho de todos os bancos de dados do SGBD:
select (sum(relpages) * 8) / 1024 || ‘ MB’ as tamanho from pg_class where relowner > 1;

Ou

select (sum(relpages) / 2^7) :: int || ‘ MB’ as tamanho from pg_class where relowner > 1;

Dicas úteis no Postgres

Existem algumas mensagens de erro que são decorrentes de simples esquecimentos na hora de configurar o Postgres. Vejamos algumas:

Um erro ocorreu:

Error connecting to the server: FATAL: autenticação do tipo password falhou para usuário "postgres"

FATAL: autenticação do tipo password falhou para usuário "postgres"

Causa: O que ocorre é que o usuário postgres, embora exista no servidor, não possui uma senha criada no banco de dados.

Solução: Criar uma senha para o usuário postgres no SGBD.

1. Logue-se como root (ou postgres) no servidor do SGBD
2. Caso tenha se logado como root, torne-se usuário postgres com o comando:
   • su – postgres
3. Digite:
•  psql -c "ALTER ROLE postgresql WITH ENCRYPTED PASSWORD ‘senha‘;  

A autenticação do cliente é controlada pelo arquivo pg_hba.conf (host-based authentication). O formato geral do arquivo pg_hba.conf é um conjunto de registros, sendo um por linha.

Resetar senha root no MySQL

Pare o MySQL:

sudo /etc/init.d/mysql stop

Inicicie esta configuração mysql:  

mysqld_safe --skip-grant-tables &

Efetue o login no MySQL:

mysql -u root mysql 

Alterando o password:

UPDATE user SET Password=PASSWORD('SUASENHAAQUI') WHERE User='root';

FLUSH PRIVILEGES;

Mudando encoding do Postgres 9.3 para UTF8

Alterando o encoding do Postgres 9.3 para UTF-8.

Testado em CentOS 6 e Rad Hat Enterprise 6.

1º - Fazer backup das bases.

2º - Fazer backup dos arquivos “.conf”:

# cd /var/lib/pgsql/9.3/data
# cp -a pg_hba.conf postgresql.conf ../backups

3º - Parar o banco:

#service postgresql-9.3 stop

4º - Remover o conteúdo da pasta data:

# cd /var/lib/pgsql/9.3
# rm -rf data/*

* Obs.: Muito cuidado com este comando!

5º - Recriar o data:

$ su postgres
# env LANG=UTF8  /usr/pgsql-9.3/bin/initdb --locale=pt_BR.UTF-8 --encoding=UTF8 -D /var/lib/pgsql/9.3/data

6º - Retornar com os “.conf”:

# cd /var/lib/pgsql/9.3/backups
# cp -a pg_hba.conf postgresql.conf ../data

7º - Restartar o Postgres:

#service postgresql-9.3 restart

8º - Recriar senha do usuário postgres:

# sudo -u postgres psql
postgres=# alter user postgres with encrypted password 'senha';

9º - Testando:

# psql -h localhost -U postgres

Senha para usuário postgres:
psql (8.4.9)

Digite "help" para ajuda.

postgres=# \l

Compactando pastas no Linux usando Gzip ou Bzip

Compactando pasta no linux: 

tar -zcf nome.tar.gz NOME_DA_PASTA

Descompactando pasta no linux: 

tar -vzxf data21072014.tar.gz

Outro comando legal é saber como colocar data no arquivo para isso você coloca no final do arquivo o seguinte:

$(date +%Y-%m-%d_%H-%M-%S)

Exemplo:

tar -zcf nome_$(date +%Y-%m-%d_%H-%M-%S).tar.gz NOME_DA_PASTA

Verificando tamanho do work_men - PostgreSQL

Para cada select, é reservado um espaço na memória para o "order by"... se o order by for maior que esse espaço reservado, vai usar arquivo em disco.
O valor padrão para o WORK_MEN é 1MB.

Logado no servidor de banco de dados, execute o comando abaixo: 

fernando=# show work_mem;

work_mem= 1MB -> Retorno do comando

SQL Manager for PostgreSQL

O EMS SQL Manager para PostgreSQL é uma ferramenta de alta performance para desenvolvimento e administração de bancos de dados PostgreSQL. Trabalhando com as versões mais recentes do PostgreSQL, ele permite gerenciar todos os recursos deste banco de dados como: triggers, views, stored procedures, funções e suporta todos seus tipos de dados (inclusive XML e UUID). O SQL Manager para PostgreSQL permite que você crie ou edite todos os objetos do Postgre SQL, crie seus bancos de dados visualmente, execute scripts SQL, importe e exporte dados do banco de dados, gerencie usuários/privilégios e execute outras inúmeras operações tornando a administração do seu banco de dados PostgreSQL muito mais eficiente.

O SQL Manager para PostgreSQL possui uma excelente interface gráfica e vários assistentes, tão simples de utilizar que mesmo um novado conseguirá executar as maiores das tarefas sem dificuldade.

Listando processos em execução no PostgreSQL

Em alguns momentos é interessante você listar quais processos estão atualmente em execução. No PostgreSQL você poderá através de uma simples query saber o que o servidor está executando no momento.

Veja o exemplo abaixo.

SELECT datname, procpid, usename, current_query, query_start FROM pg_stat_activity;

Vamos as definições das colunas

datname é o banco de dados que está sendo executado a query.

procpid é o id do processo no sistema operacional.

usename é o usuário utilizado para conectar no banco de dados.

current_query é a query que está sendo executada

query_start é a data e hora que a query foi iniciada.

Se a query não aparecer na coluna current_query, então você deve fazer uma alteração no arquivo postgresql.conf ativando o parametro stats_command_string conforme abaixo

stats_command_string = on

Essa alteração no arquivo de configuração requer que o banco de dados seja reiniciado.

Aprendendo a criar e gerenciar usuários no PostgreSQL(Primeiros Passos)

Criando seu 1° super usuário no PostgreSQL.

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.

Simples, na máquina com o banco instalado siga os seguintes passos, existem varias maneiras de fazer isso, e essa foi a maneira mais básica/simples que eu achei para explicar.

Logue-se como root, digite “su root” ou “sudo -i” ou “sudo su” (no terminal a mensagem padrão deve terminar com #).
Entre como usuário postgres com o comando:

?

1	# su postgres

Conecte ao Shell do postgres (por padrão você se conecta ao DB postgres) comando:

?

1

$ psql

Agora deve aparecer uma frase de boas vindas (Bem vindo ao psql…) e você está no terminal do posgres, note que o shell mudou antes era root@pc # agora ficou postgres=#
Isso quer dizer que agora você está no shell do PostgreSQL conectado no banco postgres.

Para criar nosso superuser, digite o comando:

?

1	CREATE USER nomedousuario SUPERUSER INHERIT CREATEDB CREATEROLE;

Depois temos que alterar a senha, então digite  o comando:

?

1	ALTER USER nomedousuario PASSWORD 'senha';

Olha que maravilha! Super Usuário criado.

Comandos básicos, para você que está acostumado com o golfinho, o Mamute é bem diferente.

# psql
\l => Lista todos os bancos de dados
\c nome-banco  => Conecta no banco nome-banco
\d  => Lista as tabelas do banco de dados conectado
\d  table-name => Descrimina as colunas da tabela
\dg => Lista usuários existentes, as permissões e grupos que o usuário pertence
\password [usuario] => Troca a senha do usuário com segurança, ou seja você não vê a seha que foi digitada e não é possível ver através do histórico de comandos.
select * from table-name  => Lista o conteúdo das tabelas (o.O)

Poderíamos usar essas opções da mesma forma, mas criando um usuário comum e colocando as permissões mais restritivas.

?

1	CREATE USER jesuejunior WITH PASSWORD 'supersecret';

Para conceder a permissão de executar somente Update ou Select ou Insert ou Delete ou Rule(dono)

?

1	GRANT UPDATE ON nomedatabela to jesuejunior

?

1	GRANT SELECT ON nomedatabela to jesuejunior

?

1	GRANT INSERT ON nomedatabela to jesuejunior

?

1	GRANT DELETE ON nomedatabela to jesuejunior

?

1	GRANT RULE ON nomedatabela to jesuejunior

E para dar permissão para fazer todas as operações.

?

1	GRANT ALL PRIVILEGES ON nomedatabela to public

Partindo do principio que você já está conectado com o usuário postgres.
Para apagar um usuário:

?

1	$ dropuser jesuejunior