PostgreSQL

PostgreSQL: Script to generate audit trail tables

Posted on fevereiro 27, 2013. Filed under: PHP, PostgreSQL | Tags:, , |

Introduction
I created a php script to generate all tables/functions and triggers needed to audit trail as shown in the article Audit Trail for Postgres

The script

These script will connect to your database and write out the sql commands to create the tables/functions and triggers needed for audit of all tables. Adapt the script to your needs.

<?php
try {
        // configure database access parameters
        $host = "";
        $dbname = "";
        $user = "";
        $pass = "";
        $dbh = new PDO("pgsql:host=$host;dbname=$dbname", $user, $pass);

        $table_list = get_all_table_list();
        foreach($table_list as $table) {
                echo "--- TABLE $table";
                echo PHP_EOL;
                echo PHP_EOL;
                echo gen_create_table_audit($table);
                echo gen_create_function_audit($table);
                echo gen_create_trigger_audit($table);
                echo PHP_EOL;
        }

} catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
}

/**
 * Returns all table list from database
 */
function get_all_table_list() {
        global $dbh;

        $sql = "SELECT relname FROM pg_class
                 WHERE relname !~ '^(pg_|sql_)' AND relname !~ '_audit$'
                   AND relkind = 'r'";

        $table_list = array();
        foreach($dbh->query($sql) as $row) {
                array_push($table_list, $row['relname']);
        }
        return $table_list;
}

/**
 * generate create table for audit table
 */
function gen_create_table_audit($tablename) {
        global $dbh;

        $sql = "SELECT ordinal_position,
                 column_name,
                 data_type,
                 column_default,
                 is_nullable,
                 character_maximum_length,
                 numeric_precision
            FROM information_schema.columns
           WHERE table_name = '$tablename'
        ORDER BY ordinal_position";

        $tablename_audit = "{$tablename}_audit";

        $s = "--- Create table $tablename_audit" . PHP_EOL;
        $s .= "CREATE TABLE {$tablename_audit} (" . PHP_EOL;
        $s .= "\taudit_id serial PRIMARY KEY," . PHP_EOL;
        $s .= "\toperation char(1) NOT NULL," . PHP_EOL;
        $s .= "\tstamp timestamp NOT NULL," . PHP_EOL;
        $s .= "\tdbuser text NOT NULL," . PHP_EOL;

        foreach($dbh->query($sql) as $row) {
                extract($row);
                $character_maximum_length = trim($character_maximum_length);
                if (!empty($character_maximum_length)) {
                        $size = "(" . $character_maximum_length . ")";
                } else {
                        $size = "";
                }

                $null = ($is_nullable == "YES") ? "NULL" : "NOT NULL";
                $s .= "\t$column_name {$data_type}{$size} $null," . PHP_EOL;
        }

        $s = substr($s, 0, -2) . PHP_EOL;
        $s .= ");" . PHP_EOL;
        return $s;
}

/**
 * Generate create function for audit table
 */
function gen_create_function_audit($tablename) {

        $tablename_audit = "{$tablename}_audit";
        $functionname = $tablename_audit;

        $s = "--- Create function $functionname
CREATE OR REPLACE FUNCTION $functionname() RETURNS TRIGGER AS \$audit\$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO $tablename_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO $tablename_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO $tablename_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
\$audit\$ LANGUAGE plpgsql;";
        $s .= PHP_EOL;
        return $s;
}

/**
 * Generate create trigger for audit table
 */
function gen_create_trigger_audit($tablename) {
        $tablename_audit = "{$tablename}_audit";
        $triggername = $tablename_audit . "t";
        $functionname = $tablename_audit;

        $s = "--- Create trigger $triggername" . PHP_EOL;
        $s .= "CREATE TRIGGER $triggername AFTER INSERT OR UPDATE OR DELETE ON $tablename FOR EACH ROW EXECUTE PROCEDURE $functionname();";
        $s .= PHP_EOL;
        return $s;
}

Example of how to use the script:

$ php audit.php > audit.sql

Example Output:

--- TABLE minuser

--- Create table minuser_audit
CREATE TABLE minuser_audit (
        audit_id serial PRIMARY KEY,
        operation char(1) NOT NULL,
        stamp timestamp NOT NULL,
        dbuser text NOT NULL,
        user_id uuid NOT NULL,
        user_name character varying(50) NOT NULL,
        user_password character varying(50) NOT NULL,
        user_email character varying(50) NULL,
        user_role character varying(50) NULL
);
--- Create function minuser_audit
CREATE OR REPLACE FUNCTION minuser_audit() RETURNS TRIGGER AS $audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO minuser_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO minuser_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO minuser_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$audit$ LANGUAGE plpgsql;
--- Create trigger minuser_auditt
CREATE TRIGGER minuser_auditt AFTER INSERT OR UPDATE OR DELETE ON minuser FOR EACH ROW EXECUTE PROCEDURE minuser_audit();
Ler Post Completo | Make a Comment ( None so far )

PHP Warning: PHP Startup: Unable to load dynamic library ‘C:\php\ext\php_pgsql.dll’

Posted on julho 20, 2012. Filed under: PHP, PostgreSQL | Tags:, , |

Precisei criar um ambiente de desenvolvimento local, no caso Windows, para php+apache+postgres. Para facilitar o trabalho, eu instalei o Wamp para aproveitar o apache e o php. E de modo independente, instalei o PostgreSQL versão 9.1.4. Ao tentar subir o php acessando o postgres, o php não conseguiu subir os módulos do postgres, mesmo estando descomentado as linhas no php.ini e as dlls existindo no local específico do extension_dir:

extension=php_pdo_pgsql.dll
extension=php_pgsql.dll

A mensagem de erro:
Unable to load dynamic library 'C:\php\ext\php_pgsql.dll"

Para corrigir o problema, foi necessário entrar no diretório bin de instalação do postgres, no meu caso: C:\Program Files\PostgreSQL\9.1\bin, copiar todas bibliotecas (extensão .ddl) para o diretório raiz do Windows: C:\Windows

Após isso, reiniciar o Apache pelo Wamp. Neste momento, a extensão deve ter sido carregada com sucesso.

Para testar se o postgres foi corretamento carregado. Criar um arquivo .php com a função phpinfo(). Abrir a página e checar a seção referente ao postgres.

Ler Post Completo | Make a Comment ( 3 so far )

Monitorando consultas lentas no PostgreSQL

Posted on setembro 30, 2011. Filed under: PostgreSQL, Tips, Tuning |

Em um post anterior já vimos como monitorar consultas lentas no MySQL, vamos agora ver como habilitar o log de consultas lentas no PostgreSQL. Como já foi discutido, esse recurso é extremamente útil para identificar gargalos em aplicações, principalmente aquelas que recebem um alto tráfego de acesso. Identificar e otimizar consultas com alta duração, como por exemplo criando indíces, irá diminuir a carga de processamento do seu servidor.

Para habilitar o log de consultas lentas no postgres é bem simples. Edite o arquivo postgresql.conf, normalmente localizado em /etc/postgresql/8.4/main/postgresql.conf no caso do Ubuntu com postgresql versão 8.4 e identifique a linha:

#log_min_duration_statement = -1

Substitua por:

log_min_duration_statement = 500

Recarregue a configuração do postgresql:

service postgresql reload

Perceba que colocamos para logar todas consultas que durarem mais que meio segundo (500ms). Você pode ajustar esse valor de acordo com sua necessidade. Por exemplo, caso você tenha um site com alto tráfego de acesso e tiver várias consultas que durem mais que meio segundo, neste caso pode fazer uma grande diferença otimizar essas consultas. Sinta-se livre para aumentar ou diminuir esse valor de acordo com o ambiente de sua aplicação.

Para visualizar o log de consultas lentas, vá para o diretório de log do postgres e edite o arquivo de log mais recente. No meu caso o arquivo é /var/log/postgresql/postgresql-8.4-main.log. Abaixo é um exemplo de um registro que gerou o log de uma consulta com duração de 2041.457 ms:

2011-09-29 11:27:39 BRT LOG: duration: 2051.457 ms execute pdo_stmt_000000b4: select * from backuplog WHERE message LIKE '%xyz%'

Referências

http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html

Ler Post Completo | Make a Comment ( None so far )

Postgres Tips: Arquivo de senha – pgpass

Posted on agosto 27, 2011. Filed under: PostgreSQL, Tips |

Alguns dias atrás precisei criar um script automático (shell script) de backup/restore de uma base do postgres. Os comandos que usei foram o pg_dump, para criar o backup, e psql para restaurar backup quando necessário. O banco de dados estava com as permissões de acesso configurado para solicitar senha, até mesmo se logado com o usuário postgres, o quê não acontece normalmente. Normalmente o usuário postgres acessa o banco sem a necessidade de solicitar a senha de acordo com a configuração padrão no pg_hba.conf. Os comandos do postgres, pg_dump, psql, etc.. não permitem especificar a senha através da linha de comando. Se você esta acostumado com o MySQL vai perceber a diferença. O postgres força o usuário à criar um ambiente seguro, não permitindo a especificação da senha na linha de comando como acontece nos comandos do MySQL. (mysqldump, mysql, etc.)

O postgres disponibiliza um mecanismo seguro para que você possa utilizar os comandos sem que eles solicitem a entrada de senha na linha de comando, ideal para scripts automatizados, através do arquivo .pgpass. Crie o arquivo .pgpass no seu diretório home com a permissão 0600:

$ chmod 0600 ~/.pgpass

O formato do arquivo .pgpass é o seguinte:

hostname:port:database:username:password

Exemplo:

localhost:5432:base:foo:mudar123

Agora você pode rodar os comandos do postgres sem necessidade de entrar com a senha no prompt:

$ pg_dump -c -U foo base> ~/base.sql
$ psql -U foo -d base < ~/base.sql
Ler Post Completo | Make a Comment ( 4 so far )

PostgreSQL Dicas

Posted on agosto 27, 2010. Filed under: PostgreSQL |

Neste rápido artigo procurei reunir algumas dicas úteis ao se trabalhar com o banco de dados PostgreSQL.

Setando uma chave estrangeira (foreign key) em um campo já existente na tabela:

ALTER TABLE endereco ADD CONSTRAINT endereco_usuario_id_fkey FOREIGN KEY (usuario_id) REFERENCES usuario(id);

No exemplo anterior, foi criado uma constraint no campo usuario_id da tabela endereco referenciando a tabela usuario campo id.

Alterando a senha do usuário postgres do banco de dados:

Entrar no banco de dados template1
psql -d template1 -U postgres
Executar a query para alterar a senha:
alter user postgres with password 'senha';

Os comandos anteriores de mudança de senha são válidos somente para as versões 7.3 e mais recentes do PostgreSQL.

Renomenado tabelas:

Renomear tabelas no Postgres é bem simples:

ALTER TABLE “nome_tabela” RENAME TO “novo_nome”

Exemplos:
ALTER TABLE user RENAME TO usuario;
ALTER TABLE employee RENAME TO funcionario;

Criando indexes:

Para criar um indíce no campo nome da tabela usuario:
create index idx_nome on usuario(nome);

O PostgreSQL permite também criar indíces usando expressões. Exemplo:
create index idx_lower_nome on usuario(lower(nome));

Você pode rodar queries usando o indíce criado no exemplo acima:
select * from usuario WHERE lower(nome) = 'alberto';

Exibir resultado das queries em formato modo extendido no psql:

Para habilitar o formato modo extendido no psql, utilize o comando abaixo:
\x

Esta função é útil quando você precisa printar na tela valores de campos muito longos. Os nomes de campos são exibidos em uma coluna à esquerda e os valores em outra coluna à direita.

Para listar os nomes dos banco de dados no psql:
\l

Para listar os nomes de tabelas ou os detalhes de uma tabela no psql:
\d <tabela>

Ler Post Completo | Make a Comment ( None so far )

Campo Incremental no PostgreSQL

Posted on julho 17, 2010. Filed under: PostgreSQL |

Neste artigo vamos demonstar como trabalhar com campos “sequences” ou “incremental” no PostgreSQL. Os sequences no PostgreSQL são equivalentes aos campos auto_increment do MySQL. Programadores acostumados somente com MySQL podem encontrar dificuldades quando precisam trabalhar com tabelas do Postgres usando campos incrementais. Este artigo tem a finalidade de ajudar esses novos programadores que estão iniciando no postgres.

Criando um Sequence no Postgres:

CREATE SEQUENCE usuario_id_seq;

Obtendo o próximo valor do Sequence:

SELECT NEXTVAL('usuario_id_seq');
nextval
---------
1
(1 row)

Você pode usar o sequence para ajudar a criar um campo incremental na sua tabela.
No exemplo abaixo, o campo usuario_id vai receber como padrão o próximo valor da sequence no momento de cada inserção:

CREATE TABLE usuario (
usuario_id INTEGER DEFAULT NEXTVAL('usuario_id_seq'),
nome VARCHAR(255),
sobrenome VARCHAR(255),
email VARCHAR(100)
);

Inserindo um registro na tabela usuario:

INSERT INTO usuario (nome, sobrenome, email) VALUES('Douglas', 'Pasqua', 'douglas.pasqua@gmail.com');

Veja que não especificamos o campo usuario_id. Dessa forma, o campo usuario_id irá receber o valor padrão, que é “NEXTVAL(‘usuario_id_seq’)”

SELECT usuario_id FROM usuario;
usuario_id
------------
2
(1 row)

Vamos demonstrar como criar um sequence em um tabela já existente no banco de dados. Vamos utilizar como exemplo a seguinte tabela chamada ‘produto’:

CREATE TABLE produto (
nome VARCHAR(255),
marca VARCHAR(100),
modelo VARCHAR(100)
);

Inserindo algumas linhas na tabela para ajudar nos próximos exemplos:

INSERT INTO produto (nome, marca, modelo) VALUES ('carro', 'Ford', 'Fiesta');
INSERT INTO produto (nome, marca, modelo) VALUES ('computador', 'Dell', 'Inspirion');
INSERT INTO produto (nome, marca, modelo) VALUES ('celular', 'Motorola', 'V5');

A tabela ‘produto’, já existente, não exista um campo incremental, portanto vamos criá-lo. Primeiro criamos o sequence:

CREATE SEQUENCE produto_id_seq;

Depois criamos o campo na tabela produto que receberá o valor incremental do sequence anterior:

ALTER TABLE produto ADD produto_id INT UNIQUE;

Depois especifique que o campo produto_id receberá o valor da sequence como padrão:

ALTER TABLE produto ALTER COLUMN produto_id SET DEFAULT NEXTVAL('produto_id_seq');

Pronto. Tudo certo ? Bom, ainda não. Já temos nosso campo incremental na tabela produto. Porém os registros já existentes antes da alteração não serão populados pela sequence automaticamente. Por padrão o valor da coluna para esses registros serão “null”. Verificando:

SELECT * FROM produto;
nome | marca | modelo | produto_id
------------+----------+-----------+------------
carro | Ford | Fiesta |
computador| Dell | Inspirion |
celular | Motorola | V5 |
(3 rows)

Para corrigir isto, execute o UPDATE:

UPDATE produto SET produto_id = NEXTVAL('produto_id_seq');

Verificando novamente os dados na tabela:

nome | marca | modelo | produto_id
------------+----------+-----------+------------
carro | Ford | Fiesta | 1
computador | Dell | Inspirion | 2
celular | Motorola | V5 | 3
(3 rows)

Legal né. Você já esta apto a trabalhar com campos incrementais no postgresql. Para finalizar, vamos demonstrar um exemplo de como excluir a sequence utilizada em um campo incremental:

Primeiro faremos o ALTER TABLE:

ALTER TABLE produto ALTER COLUMN produto_id SET DEFAULT NULL;

Depois excluímos o sequence:

DROP SEQUENCE produto_id_seq;

Ok, é isto ae!

Ler Post Completo | Make a Comment ( 1 so far )

Liked it here?
Why not try sites on the blogroll...

%d blogueiros gostam disto: