• Adrian Hunter's avatar
    perf script: Add Python script to export to postgresql · 2987e32f
    Adrian Hunter authored
    Add a Python script to export to a postgresql database.
    
    The script requires the Python pyside module and the Qt PostgreSQL
    driver.  The packages needed are probably named "python-pyside" and
    "libqt4-sql-psql"
    
    The caller of the script must be able to create postgresql databases.
    
    The script takes the database name as a parameter.  The database and
    database tables are created.  Data is written to flat files which are
    then imported using SQL COPY FROM.
    
    Example:
    
      $ perf record ls
      ...
      $ perf script report export-to-postgresql lsdb
      2014-02-14 10:55:38.631431 Creating database...
      2014-02-14 10:55:39.291958 Writing to intermediate files...
      2014-02-14 10:55:39.350280 Copying to database...
      2014-02-14 10:55:39.358536 Removing intermediate files...
      2014-02-14 10:55:39.358665 Adding primary keys
      2014-02-14 10:55:39.658697 Adding foreign keys
      2014-02-14 10:55:39.667412 Done
      $ psql lsdb
      lsdb-# \d
                  List of relations
       Schema |      Name       | Type  | Owner
      --------+-----------------+-------+-------
       public | comm_threads    | table | acme
       public | comms           | table | acme
       public | dsos            | table | acme
       public | machines        | table | acme
       public | samples         | table | acme
       public | samples_view    | view  | acme
       public | selected_events | table | acme
       public | symbols         | table | acme
       public | threads         | table | acme
      (9 rows)
      lsdb-# \d samples
             Table "public.samples"
          Column     |  Type   | Modifiers
      ---------------+---------+-----------
       id            | bigint  | not null
       evsel_id      | bigint  |
       machine_id    | bigint  |
       thread_id     | bigint  |
       comm_id       | bigint  |
       dso_id        | bigint  |
       symbol_id     | bigint  |
       sym_offset    | bigint  |
       ip            | bigint  |
       time          | bigint  |
       cpu           | integer |
       to_dso_id     | bigint  |
       to_symbol_id  | bigint  |
       to_sym_offset | bigint  |
       to_ip         | bigint  |
       period        | bigint  |
       weight        | bigint  |
       transaction   | bigint  |
       data_src      | bigint  |
      Indexes:
          "samples_pkey" PRIMARY KEY, btree (id)
      Foreign-key constraints:
          "commfk" FOREIGN KEY (comm_id) REFERENCES comms(id)
          "dsofk" FOREIGN KEY (dso_id) REFERENCES dsos(id)
          "evselfk" FOREIGN KEY (evsel_id) REFERENCES selected_events(id)
          "machinefk" FOREIGN KEY (machine_id) REFERENCES machines(id)
          "symbolfk" FOREIGN KEY (symbol_id) REFERENCES symbols(id)
          "threadfk" FOREIGN KEY (thread_id) REFERENCES threads(id)
          "todsofk" FOREIGN KEY (to_dso_id) REFERENCES dsos(id)
          "tosymbolfk" FOREIGN KEY (to_symbol_id) REFERENCES symbols(id)
    
      lsdb-# \d samples_view
                     View "public.samples_view"
            Column       |          Type           | Modifiers
      -------------------+-------------------------+-----------
       id                | bigint                  |
       time              | bigint                  |
       cpu               | integer                 |
       pid               | integer                 |
       tid               | integer                 |
       command           | character varying(16)   |
       event             | character varying(80)   |
       ip_hex            | text                    |
       symbol            | character varying(2048) |
       sym_offset        | bigint                  |
       dso_short_name    | character varying(256)  |
       to_ip_hex         | text                    |
       to_symbol         | character varying(2048) |
       to_sym_offset     | bigint                  |
       to_dso_short_name | character varying(256)  |
    
        lsdb=# select * from samples_view;
    
       id| time       |cpu | pid  | tid  |command| event  |   ip_hex      |           symbol    |sym_off| dso_name|to_ip_hex|to_symbol|to_sym_off|to_dso_name
       --+------------+----+------+------+-------+--------+---------------+---------------------+-------+---------+---------+---------+----------+----------
       1 |12202825015 | -1 | 7339 | 7339 |:17339 | cycles | fffff8104d24a |native_write_msr_safe|    10 | [kernel]| 0       | unknown |         0| unknown
       2 |12203258804 | -1 | 7339 | 7339 |:17339 | cycles | fffff8104d24a |native_write_msr_safe|    10 | [kernel]| 0       | unknown |         0| unknown
       3 |12203988119 | -1 | 7339 | 7339 |:17339 | cycles | fffff8104d24a |native_write_msr_safe|    10 | [kernel]| 0       | unknown |         0| unknown
    
    My notes (which may be out-of-date) on setting up postgresql so you can
    create databases:
    
    fedora:
    
            $ sudo yum install postgresql postgresql-server python-pyside qt-postgresql
            $ sudo su - postgres -c initdb
            $ sudo service postgresql start
            $ sudo su - postgres
            $ createuser -s <your username>
    
    I used the the unix user name in createuser.
    
    If it fails, try createuser without -s and answer the following question
    to allow your user to create tables:
    
            Shall the new role be a superuser? (y/n) y
    
    ubuntu:
    
            $ sudo apt-get install postgresql
            $ sudo su - postgres
            $ createuser <your username>
            Shall the new role be a superuser? (y/n) y
    
    You may want to disable automatic startup.  One way is to edit
    /etc/postgresql/9.3/main/start.conf.  Another is to disable the init
    script e.g. sudo update-rc.d postgresql disable
    Signed-off-by: default avatarAdrian Hunter <adrian.hunter@intel.com>
    Cc: David Ahern <dsahern@gmail.com>
    Cc: Frederic Weisbecker <fweisbec@gmail.com>
    Cc: Jiri Olsa <jolsa@redhat.com>
    Cc: Namhyung Kim <namhyung@gmail.com>
    Cc: Paul Mackerras <paulus@samba.org>
    Cc: Peter Zijlstra <peterz@infradead.org>
    Cc: Stephane Eranian <eranian@google.com>
    Link: http://lkml.kernel.org/r/1414061124-26830-8-git-send-email-adrian.hunter@intel.comSigned-off-by: default avatarArnaldo Carvalho de Melo <acme@redhat.com>
    2987e32f
export-to-postgresql.py 12.3 KB