Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

  • Créer un role pour utiliser pg bench

    AU départ la base est dans cet état:

    postgres=# drop database bench;
    DROP DATABASE
    postgres=# drop role bench;
    DROP ROLE


    postgres=# l  
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
     template0 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     test      | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
    (4 rows)

    postgres=# du
                                       List of roles
     Role name |                         Attributes                         | Member of
    -----------+------------------------------------------------------------+-----------
     postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

     

    1 - Commencer par créer un nouvel utilisateur puis une nouvelle base :

    [postgres@localhost ~]$ createuser -p 5432 -d bench
    [postgres@localhost ~]$ createdb bench -U bench
    [postgres@localhost ~]$ psql -U postgres
    psql (9.5.1)
    Type "help" for help.

    postgres=# l
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     bench     | bench    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
     postgres  | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
     template0 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     test      | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
    (5 rows)

    postgres=# du
                                       List of roles
     Role name |                         Attributes                         | Member of
    -----------+------------------------------------------------------------+-----------
     bench     | Create DB                                                  | {}
     postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

     

    2- Ensuite vous pouvez utiliser (enfin!) l'utilitaire pgbench pour alimenter votre base de données :

    [postgres@localhost ~]$ pgbench -i -s 10 -U bench bench


    NOTICE:  table "pgbench_history" does not exist, skipping
    NOTICE:  table "pgbench_tellers" does not exist, skipping
    NOTICE:  table "pgbench_accounts" does not exist, skipping
    NOTICE:  table "pgbench_branches" does not exist, skipping
    creating tables...
    100000 of 1000000 tuples (10%) done (elapsed 0.20 s, remaining 1.81 s)
    200000 of 1000000 tuples (20%) done (elapsed 0.37 s, remaining 1.48 s)
    300000 of 1000000 tuples (30%) done (elapsed 0.55 s, remaining 1.29 s)
    400000 of 1000000 tuples (40%) done (elapsed 0.81 s, remaining 1.22 s)
    500000 of 1000000 tuples (50%) done (elapsed 1.01 s, remaining 1.01 s)
    600000 of 1000000 tuples (60%) done (elapsed 1.39 s, remaining 0.93 s)
    700000 of 1000000 tuples (70%) done (elapsed 1.82 s, remaining 0.78 s)
    800000 of 1000000 tuples (80%) done (elapsed 2.06 s, remaining 0.52 s)
    900000 of 1000000 tuples (90%) done (elapsed 2.29 s, remaining 0.25 s)
    1000000 of 1000000 tuples (100%) done (elapsed 2.53 s, remaining 0.00 s)
    vacuum...
    set primary keys...
    done.

     

    -i : permet d'initialiser

    -s : permet de donner une valeur factorielle , dans mon exemple 10 , par défaut  1

    -U : nom du role autorisé à se connecter à la base de données bench

  • Binaires Postgresql

    Voici la liste des binaires qui permettent d'administrer Postgresql:

     

    [postgres@localhost bin]$ ls pg*
    pg_archivecleanup  pg_controldata  pg_isready      pg_restore      pg_upgrade
    pg_basebackup      pg_ctl          pg_receivexlog  pg_rewind       pg_xlogdump
    pgbench            pg_dump         pg_recvlogical  pg_test_fsync
    pg_config          pg_dumpall      pg_resetxlog    pg_test_timing

     

     

    Dans une prochaine note je m'attarderai plus longuement sur pgbench.

  • Installer Postgresql

    1-Télécharger Postgresql

    2- Le dé-tarer, puis taper les commandes suivantes :

    ./configure
    make

    su
    make install
    adduser postgres
    mkdir /usr/local/pgsql/data
    chown postgres /usr/local/pgsql/data
    su - postgres
    /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
    /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
    /usr/local/pgsql/bin/createdb test
    /usr/local/pgsql/bin/psql test

  • Réplication PostGresql

    Pour vérifier la réplication sur postgresql il faut utiliser la commande : 

     

    bench#select pg_xlog_location_diff(pg_last_xlog_receive_location(),pg_last_xlog_replay_location());

     

    source : 

    50ième minute 

  • RPM Postgresql

    Lire la suite

    Tags : rpm, postgresql

  • PgTune, PgFouine, Optimiser Postgresql

    pgtune pgtune - Monitor this package

    Filename Date Size D/L Arch Type
    pgtune-0.9.3.tar.gz 2009-10-29 01:19 34 KB 29,047 Any Source .gz
    Filename Date Size D/L Arch Type
    pgtune-0.9.2.tar.gz 2009-10-28 22:25 32 KB 630 Any Source .gz
    Filename Date Size D/L Arch Type
    pgtune-0.9.1.tar.gz 2009-10-28 05:52 31 KB 585 Any Source .gz
    Filename Date Size D/L Arch Type
    pgtune-0.9.0.tar.gz 2009-01-26 16:12 22 KB 4,100 Any .gz

    Lire la suite