PostgreSQL VACUUM: Limpando o Banco de dados

É muito importante, principalmente para um DBA, conhecer e analisar as ferramentas que possam aumentar a performance do banco de dados, isso porque geralmente empresas que possuem DBA trabalham com uma grande quantidade de dados e a velocidade de resposta da aplicação torna-se crítica para seu negócio. Imagine, por exemplo, uma Operadora de Cartão de Crédito, como Visa ou Mastercard: quantos milhões de transações por segundo são realizadas em todo mundo? É fato que um index errado em um banco de dados dessa operadora pode ser fatal para perda de milhões de reais em poucos segundos.

VACUUM

Antes de entender como funciona tal técnica é importante conhecer o funcionamento interno do PostgreSQL para assim ver a real utilidade da mesma.

Você já percebeu que ao deletar registros do seu banco de dados ele não diminui ? Faça o teste: compare o tamanho atual do seu banco de dados com o tamanho após a deleção de muitos registros, você ficará surpreso ao perceber que nada mudou. Então você deve se perguntar: Mas se eu estou deletando, o mais lógico é diminuir o tamanho do banco, certo? Negativo. O PostgreSQL, assim como outros bancos, na verdade não deletam os registros e sim os marca como inúteis, técnica muito comum e utilizada em diversas aplicações, ou seja, se você fizer um “DELETE FROM funcionario WHERE id > 100” e este comando deletar 10 mil linhas, você na verdade estará marcando as 10 mil linhas como inúteis e não deletando fisicamente, o que demandaria muito mais tempo e recurso.

A lógica é a seguinte:é muito melhor ter uma operação rápida do que espaço em disco, sendo assim o banco ficará enorme mais sua performance compensará tal perda de espaço, que hoje em dia acaba não trazendo muito impacto, já que 'armazenamento digital' está barato e acessível.

Todo esse mecanismo é chamado de MVCC (Multiversion Concurrency Control) que garante uma performance melhor ao banco de dados, afinal performance é o ponto chave em aplicações críticas. Quando você realiza um UPDATE, o seu registro é atualizado, correto? Negativo. O que na verdade é feito é uma inserção de outra tupla na sua tabela, com os mesmos dados da tupla original, apenas alterando o que você solicitou no UPDATE, e a tupla anterior (não atualizada) é marcada como inútil, assim como explicamos no DELETE.

Dado todas as explicações acima, chegamos ao ponto chave do artigo: a utilização do VACUUM. Já que temos muitos registros que estão marcados como inúteis, precisamos em algum momento limpar estes, para garantir ainda mais performance em nosso banco e retirar toda sujeira de dados.

No momento em que o comando VACUUM é executado, é feita uma varredura em todo o banco a procura de registros inúteis, onde estes são fisicamente removidos, agora sim diminuindo o tamanho físico do banco. Mas além de apenas remover os registros, o comando VACUUM encarrega-se de organizar os registros que não foram deletados, garantindo que não fiquem espaços/lacunas em branco após a remoção dos registros inúteis.

Figura 1. Executando Vacuum

Na Figura 1 você pode notar um exemplo simples e prático de como funciona o Vacuum:

1. Temos na Listagem 1 a lista de todos os registros, incluindo os úteis e inúteis (marcados em vermelho).
2. O vacuum deleta todos os registros inúteis (em vermelho), mas após essas deleções serem realizadas, você pode perceber que ficam espaços em branco, exatamente o espaço onde estavam os registros inúteis.
3. Por fim, o vacuum encarrega-se de remover esses espaços, garantindo que os mesmos fiquem organizados e em uma disposição correta.

Há ainda um quarto e último passo realizado pelo vacuum, que não está descrito na figura acima. Ocorre que o vacuum também atualiza as estatísticas que são utilizadas pelo otimizador do PostgreSQL para determinar qual a melhor forma de realizar uma busca no banco de dados, porém, a atualização dessas estatísticas vai depender da forma em que o vacuum for executado, o que explicaremos mais a frente do porque.

Listagem 1. Sintaxe do Vacuum

  VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ tabela ]
  VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] 
  ANALYZE [ tabela [ (coluna  [, ...] ) ]]  

Acima você pode ver toda a parametrização do comando vacuum, com suas possíveis opções, então vamos explicar cada uma delas e sua utilidade:

• # FULL : Quando o vacuum é utilizado em conjunto com este parâmetro, então é feita uma limpeza completa de todo o banco, em todas as tabelas e colunas. Este processo geralmente é demorado e evita que qualquer outra operação no banco seja realizada, ou seja, ao realizar um VACUUM FULL você terá que esperar todo processo terminar até realizar um comando DLL ou DML.
• # VERBOSE: Ao ativar esse parâmetro você terá um relatório detalhado de tudo que está sendo feito no comando VACUUM.
• # ANALYSE: Você lembra que citamos anteriormente que o VACUUM em um último passo pode ou não atualizar as estatística que são utilizadas pelo otimizador do PostgreSQL para determinar o melhor método de consulta? Este parâmetro é responsável por habilitar ou desabilitar este tipo de atualização, em outras palavras, ao usar o ANALYSE junto ao seu comando VACUUM ele irá atualizar as estatística do banco de dados a fim de melhorar a performance das pesquisas.
• # tabela: Caso você queira realizar o VACUUM apenas em uma tabela, então você deve especificar explicitamente qual tabela será, caso contrário, apenas deixe este parâmetro em branco e todas as tabelas serão consideradas.
• # coluna: Seguindo o mesmo raciocínio da tabela, caso você deseje realizar o VACUUM em apenas algumas colunas, basta especificar quais são, caso contrário, deixe este parâmetro em branco e todas as colunas serão consideradas.

Algo que você pode ser perguntar é qual a diferença entre o VACUUM sem parâmetros (simples) e o VACUUM FULL (que exige o bloqueio exclusivo das tabelas, ou seja, nenhuma operação pode ser realizada enquanto este comando estiver em processamento). O VACUUM simples apenas remove as tuplas marcadas como inúteis/removidas em processos de UPDATE ou DELETE. Sendo assim, não há necessidade de bloquear as operações no banco. Por outro lado, o VACUUM FULL além de remover essas tuplas inúteis, ainda reorganiza as tabelas, retirando os espaços em branco que ficaram após a remoção dessas colunas, e para tal processo é necessário que o banco não esteja realizando nenhuma operação, por isso o bloqueio do mesmo é necessário.

Caso você utilize o pgAdmin como uma ferramenta para realizar o gerenciamento do seu PostgreSQL, então nele mesmo (sem linha de comando) você poderá realizar o VACUUM, assim como outros processos otimizadores de performance.

O processo é simples: basta você clicar com o botão direito em cima da sua base de dados e depois escolher a opção “Maintenance...”, então você verá uma janela como mostrada na Figura 2.

Figura 2. Vacuum no pgAdmin III

O Vacuum diminui consideravelmente o tamanho físico do banco, mas não só isso, ele também aumenta a performance do otimizador do banco de dados. Além dele, ainda existem outras ferramentas, como falamos em seções anteriores. No gráfico da Figura 3 você pode visualizar de forma mais abrangente a diferença física de espaço em seu banco após realizar processos de otimização do banco, tais como: vacuum, reindex e outros.

Figura 3. Gráfico PostgreSQL após otimização

O próprio PostgreSQL já possui um processo chamado autovacuum onde você pode deixar que o próprio banco realize o Vacuum Simples (sem o FULL) com frequência, o que é muito bom para base de dados, pois as deleções e atualizações de registros são constantes e você mantêm sua base sempre rápida e sem dados sujos. Por outro lado, a própria documentação original do PostgreSQL aconselha que o VACUUM FULL seja usado como muito cuidado e em casos raros, isso porque o processo demanda muito tempo e pode até causar baixa de performance no banco, em vez de melhor a mesma, isso porque o processo faz algo muito crítico, que é a reorganização de todas as tuplas, retirando os 'gaps' que ficaram após a remoção dos registros inúteis.

Com isso, este artigo teve como principal objetivo demonstrar técnicas mais avançadas do PostgreSQL para análise de performance e otimização do banco, assunto este essencial e obrigatório para DBA's. Vale ressaltar que todas as técnicas descritas neste artigo devem ser usadas com cautela e sempre mediante a uma análise prévia do problema, em outras palavras, não saia executando VACUUM em todas as suas tabelas e bancos antes de analisar a real necessidade de aplicar este procedimento. Como citamos anteriormente, a própria documentação do PostgreSQL aconselha o não uso do VACUUM FULL, que pode ser prejudicial ao banco de dados, se aplicado de forma errônea, obviamente.

PostgreSQL Prático/Metadados

Metadados são dados sobre dados.

Uma consulta normal retorna informações existentes em tabelas, já uma consulta sobre os metadados retorna informações sobre os bancos, os objetos dos bancos, os campos de tabelas, seus tipos de dados, seus atributos, suas constraints, etc.

Retornar Todas as Tabelas do banco e esquema atual

SELECT schemaname AS esquema, tablename AS tabela, tableowner AS dono
 FROM pg_catalog.pg_tables
 WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
 ORDER BY schemaname, tablename

Informações de Todos os Tablespaces

SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation
 FROM pg_catalog.pg_tablespace

Retornar banco, dono, codificação, comentários e tablespace

SELECT pdb.datname AS banco, 
 pu.usename AS dono, 
 pg_encoding_to_char(encoding) AS codificacao,
 (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid) AS comentario,
 (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=pdb.dattablespace) AS tablespace
 FROM pg_database pdb, pg_user pu WHERE pdb.datdba = pu.usesysid ORDER BY pdb.datname

Tabelas, donos, comentários, registros e tablespaces de um schema

SELECT c.relname as tabela, 
 pg_catalog.pg_get_userbyid(c.relowner) AS dono, 
 pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer as registros,
 (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = 'r' AND nspname='public'
 ORDER BY c.relname

Mostrar Sequences de um Esquema

SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment,
    (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
     FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
     WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid
     AND c.relkind = 'S' AND n.nspname='public' ORDER BY seqname

Mostrar Sequences de um Esquema

SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment,
    (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
     FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
     WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid
     AND c.relkind = 'S' AND n.nspname='public' ORDER BY seqname

Mostrar Esquemas e respectivas tabelas do Banco atual:

SELECT n.nspname as esquema, c.relname as tabela  FROM pg_namespace n, pg_class c
 WHERE n.oid = c.relnamespace
   and c.relkind = 'r'     -- no indices
   and n.nspname not like 'pg\\_%' -- no catalogs
   and n.nspname != 'information_schema' -- no information_schema
 ORDER BY nspname, relname

Dado o banco de dados, qual o seu diretório:

select datname, oid from pg_database;

Dado a tabela, qual o seu arquivo:

select relname, relfilenode from pg_class;

Tamanho em bytes de um banco:

select pg_database_size('banco');

Tamanho em bytes de uma tabela:

pg_total_relation_size('tabela')

Tamanho em bytes de tabela ou índice:

pg_relation_size('tabelaouindice')

Lista donos e bancos:

SELECT rolname as dono, datname as banco

FROM pg_roles, pg_database

WHERE pg_roles.oid = datdba

ORDER BY rolname, datname;

Nomes de bancos:

select datname from pg_database where datname not in ('template0','template1') order by 1

Alterar a base de dados padrão do MySQL

É muito comum querer alterar o diretório de databases do MySQL por diversos motivos, como para deixar em uma partição maior só para dados, para mudar o caminho padrão das bases de dados do MySQL etc. 

A pasta de dados padrão do MySQL é: /var/lib/mysql 

Vamos mudar para a partição: /mysql

1. Stop MySQL:

# /etc/init.d/mysql stop 

2. Crie o diretório de dados: 

# mkdir /mysql 

3. Dê permissão ao MySQL: 

# chown -R mysql.mysql /mysql 

4. Edite o "my.cnf" e aponte para o novo diretório de dados: 

# vi /etc/my.cnf 

[mysqld]
datadir=/mysql
socket=/mysql/mysql.sock

[client]
socket=/databases/mysql/mysql.sock

5. Aqui, temos 2 opções: 

5.1. Copiar o conteúdo antigo para a nova pasta: 

# cp -vRp /var/lib/mysql/* /mysql 

Ou: 

5.2. Criar um nova base de dados. Se o MySQL for novo, tiver acabado de ser instalado, então faça: 

# mysql_install_db 

Se ao iniciar o MySQL você receber o erro: 

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

Adicione a entrada abaixo no "my.cnf": 

[client]
socket=/mysql/mysql.sock

Por fim: 

# /etc/init.d/mysql start 

Formatando dados no PostgreSQL

É muito comum fazermos alguns selects e querermos que os dados retornem formadados, como por exemplo a data. Vou exemplificar aqui alguns exemplos mais comuns de formatação que usamos no nosso dia-a-dia.

Vou sempre colocar o exemplo do dado original seguido do exemplo do dado formatado.

Pegando apenas a data de um tipo timestamp.

postgres=# SELECT current_timestamp, to_char(current_timestamp, 'DD/MM/YYYY') ;
 now              |  to_char
-------------------------------+------------
 2010-05-05 15:05:49.981577-04 | 05/05/2010
(1 row)

Formatando hora

postgres=# SELECT current_timestamp, to_char(current_timestamp, 'HH24:MI:SS') ;
 now              | to_char
-------------------------------+----------
 2010-05-05 15:10:22.454159-04 | 15:10:22
(1 row)

Formatando data e hora

postgres=# SELECT current_timestamp, to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS') ;
 now              |       to_char
-------------------------------+---------------------
 2010-05-05 15:11:09.345058-04 | 05/05/2010 15:11:09
(1 row)

Formatando numero.

postgres=# SELECT to_char(125.8, '999D99');
 to_char
---------
 125,80
(1 row)

Backup de vários bancos PostgreSQL no Linux

Não tem coisa mais chata do que ficar fazendo backup. Mas o Linux nos dá um poder muito grande com seus scripts e agendamento de tarefas através do cron. No meu ambiente de trabalho, tenho um servidor de banco de dados que tem vários bancos. Se eu usar o pd_dumpall vou estar fazendo o backup de todos os bancos, mas não terei como restaurar apenas um banco caso eu precise. Para isso, criei um script que faz o backup individual dos bancos.

Para compactar, estou utilizando o bzip2. Apesar de levar mais tempo para compactar, o banco de dados compactado com gzip que fica com 1,7GB, passou para 1,2GB com bzip2. Se o backup puder rodar na madrugada como é o meu caso, então não terá problemas de perda de desempenho do servidor, pois ele consome mais processamento do que disco.

Vou colocar todo o código e vou explicando linha a linha para facilitar o entendimento. Coloque as linhas abaixo em um arquivo e torne-o executável (chmod 700 seuarquivo.sh).

### Data do backup
data=`date +%Y_%m_%d`

### Apaga os mais antigos, vamos apagar o que tiver mais de 15 dias,
### mantendo o espaço do disco
TIME="+15"
find $dire -type f -mtime $TIME | xargs rm -rf

### Lista de banco de dados para backup individual. Deve ser separado por espaço
BANCO="financeiro contabil vendas"

### Loop para fazer o backup individual
for banco in `echo $BANCO`
do

    ### Caminho completo e Nome do arquivo de backup
   arq="$dire$banco$data.sql"

    ### Fazendo o backup do banco
   /usr/local/pgsql/bin/pg_dump "$banco" > $arq

   ### Compacta o arquivo com tar e bzip2 para obter compactacao maxima
   tar -cf $arq.tar $arq
   bzip2 $arq.tar

   ### A compactacao nao apaga o original, vamos apagar manualmente
   rm -f $arq

done

### finaliza
exit 0

Feito isso, basta agendar no cron e ser feliz. Apenas acompanhe para ter certeza que seu disco não irá encher, nesse caso, diminua a quantidade de dias de backup que você irá guardar alterando a variável TIME.

Log de alterações de registros - PostgreSQL.

Muitas vezes nos deparamos com o sumiço de certos registros importantes e pra piorar a situação esse sumiço nunca tem culpado. Dependendo da situação, precisamos cadastrar o registro novamente e isso pode acabar trazendo consequencias pois um novo registro virá com um novo ID e normalmente não virá com todos os dados originais. Há também o caso do fulano que simplesmente apagou o conteúdo do campo e ninguém sabe o que tinha lá. Uma boa solução para isso é criarmos um log com a cópia do registro antes da atualização ser realizada, quer seja update ou delete.Podemos fazer isso de forma bem simples com uma trigger. O primeiro passo é criarmos uma tabela com a estrutura idêntica a tabela que queremos preservar os dados, vou citar como exemplo o cadastro de clientes.

No cadastro de clientes tenho os campos ID, nome, email e telefone. Na tabela que criarei para guardar os registros de alteração, além de ter esses campos, colocarei também o campo data_hora que armazenará quando o registro foi alterado ou excluido e também o campo operacao que irá informar se foi uma operação de exclusão ou alteração.

Precisamos criar uma função que pegará o registro atualizado da tabela de cliente (update ou delete) e copie para a tabela de log.

Agora que a função existe, vamos criar a trigger que irá disparar a execução da função logo após as operações de insert e delete.

Pronto. Agora, sempre que um usuário alterar ou excluir um registro da tabela cliente, a cópia desse registro irá para a tabela log_cliente. Com isso teremos uma cópia fiel do registro antes da cagad. digo, ação indevida do usuário ter sido executada. Vamos ao teste.

Primeiro eu fiz um select na tabela log_cliente, como eu acabei de criar, ela não tem nada.

Agora eu listei os registros da tabela cliente. Há dois registros.

Vamos apagar o registro cujo ID seja igual a 1, ou seja, o cliente Cleberson. Se olharmos na tabela cliente novamente, veremos que o registro foi realmente apagado.

Agora vamos ver na tabela log. Além de termos uma cópia do registro, temos a data e hora que a ação foi executada e também qual a ação foi executada, que nesse caso foi um DELETE.

A grande vantagem é termos o registro pronto para ser restaurado caso seja necessário. A desvantagem é que se a tabela sofre updates com muita frequencia, então a tabela log_cliente irá crescer rapidamente. Como você tem data e hora que foi executado, periodicamente você poderá fazer uma limpeza na tabela de log, deixando as alterações dos ultimos 6 meses ou 1 ano por exemplo.

ESSA DICA FOI RETIRADA DO BLOG DO CLEBERSONSILVA: http://www.clebersonsilva.com.br/

Listando processos em execução no PostgreSQL

No PostgreSQL você poderá através de uma simples query saber o que o servidor está executando no momento.

Exemplo:

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

Definições da 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.

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