quinta-feira, 30 de agosto de 2012

Email pelo terminal com sendEmail e Gmail

No dia a dia de DBA temos que automatizar as rotinas, para não perder tempo e se antecipar a possíveis problemas e enviar email pelo terminal do linux é muito útil. Mais para frente vou mostras como automatizar algumas tarefas utilizando o sendEmail.

Vou mostrar como instalar e configurar o sendEmail com conta do gmail.


  • Baixar o sendEmail

  • Vamos precisar do perl e de algumas bibliotecas.
# yum install perl.x86_64 perl-Net-SSLeay perl-IO-Socket-SS

  • Descompactar o pacote do sendEmail
# tar -zxvf sendEmail-v1.56.tar.gz

  • Copiar sendEmail para /usr/local/bin
# cp -a sendEmail-v1.56/sendEmail /usr/local/bin


  • Agora com tudo instalado vamos mandar email
[glauco@bancoteste]# sendEmail \
> -xu glauco@gmail.com \
> -xp senha \
> -s smtp.gmail.com:587 \
> -o tls=yes \
> -l /tmp/sendEmail.log \
> -f "glauco@gmail.com" \
> -t "chefe@gmail.com;cliente@empresa.com.br" \
> -u "Assunto do Email" \
> -m "Mensagem do Email"
> -a /home/glauco/teste.txt ( Anexo )

Para ver mais funções do sendEmail, no console digite sendEmail.

Faça por sua conta e risco, faça primeiro em um servidor de teste
para só depois ir para um servidor de produção.



pgFouine - a PostgreSQL log analyzer


O pgfouine é um analisador de log do Postgres, foi umas das primeiras tarefas que me deram no mundo do Postgres. Recomento fazer em um servidor de testes varias vezes antes de mexer em um servidor de produção.
Faça por sua conta e risco.


Baixar o PgFouine.


Descompactar o PgFouine.

# tar -zxvf pgfouine-1.1.tar.gz 

Mover a PgFouine

# mv pgfouine-1.1 /usr/local/pgfouine 

Configurar o Postgres para gravar Log.

Eu usei esta configuração para o Postgres ( isso não quer dizer que é a mais correta )


#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_000000.log' # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = off          # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#silent_mode = off                      # Run server silently.
                                        # DO NOT USE without syslog or
                                        # logging_collector
                                        # (change requires restart)
# - When to Log -
#client_min_messages = notice           # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   log
                                        #   notice
                                        #   warning
                                        #   error
log_min_messages = info                 # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic
log_min_error_statement = notice        # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic (effectively off)
log_min_duration_statement = 100  # -1 is disabled, 0 logs all statements
                                  # and their durations, > 0 logs only
                                  # statements running at least this number
                                  # of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
log_duration = on
log_error_verbosity = verbose           # terse, default, or verbose messages
#log_hostname = on
log_line_prefix = '%t [%p]: [%l-1] '    # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d> '
log_lock_waits = off                    # log lock waits >= deadlock_timeout
log_statement = 'all'                   # none, ddl, mod, all
#log_temp_files = -1                    # log temporary files equal or larger
                                        # than the specified size in kilobytes;
                                        # -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'

-------------------------------------------------------------------------------


Criar a pasta analizer dentro do apache


# mkdir /var/www/analizer/

Reinicie o apache


# apachectl restart 



Exemplo para gerar relatório


# pgfouine.php -file /dados/pgsql/data/pg_log/postgresql-2012-08-10_135031.log > /var/www/analizer/relatorio_pgbench.html -debug -logtype stderr

Boa Sorte!


Já tem uma ferramenta nova e já fiz um novo post sobre o pgBadger


Apresentação







Nesse espaço vou postar as correrias do meu dia dia de aprendiz de DBA e tentar compartilhar o que estou aprendendo. Aqui vou postar dicas de linux das distro CentOS e Red Hat e BD PostgreSQL.