CentOS with GNOME desktop on Vagrant

I made a CentOS vagrant box which contains GNOME desktop environment, so I will show how to use and to make it.

PLpgSQL QUIZ

I found an interesting quiz about PLpgSQL.

Multi-GTM for Postgres XC

Five years ago, I was interested in Postgres-XC , currenty called Postgres-X2, and made a multi-GTM (global transaction manager) system for it.

Though I had forgotten this work for a long time, I found its executable files from my old PC a month ago (unfortunately the source code of them was lost). They perfectly run, so I provide a Vagrant box to run my old program on your PC.

Read full post

How to relocate tablespace directory

I’ll demonstrate how to relocate a tablespace directory without the reconstruction of databases.

Read full post

New WAL format in PostgreSQL 9.5

This post is a part of my document.

The XLOG data format has changed in version 9.5.

Read full post

WAL segment Management in PostgreSQL 9.5

This post is a part of my document.

In version 9.5, management policy for WAL segment files has improved. PostgreSQL 9.5 always holds the appropriate number of WAL files in pg_xlog depending on server activity.

Read full post

How streaming replication works

This post is a part of my document.

I briefly explain that how streaming replication works in the following.

Read full post

Stat 01

SQL ratio per table

SELECT 
s.schemaname, s.relname, 
CASE WHEN s.total = 0 THEN 0.00
 ELSE round((100*cast(s.n_tup_ins AS numeric)/s.total) ,2) END AS ins_ratio,
CASE WHEN s.total = 0 THEN 0.00
 ELSE round((100*cast(s.n_tup_upd AS numeric)/s.total) ,2) END AS upd_ratio,
CASE WHEN s.total = 0 THEN 0.00
 ELSE round((100*cast(s.n_tup_del AS numeric)/s.total) ,2) END AS del_ratio
FROM (SELECT schemaname,relname, 
       n_tup_ins, n_tup_upd, n_tup_del,
       cast((n_tup_ins + n_tup_upd + n_tup_del) AS numeric) AS total
       FROM pg_stat_user_tables) AS s
ORDER BY relname;
 schemaname |     relname      | ins_ratio | upd_ratio | del_ratio 
------------+------------------+-----------+-----------+-----------
 public     | pgbench_accounts |     99.70 |      0.30 |      0.00
 public     | pgbench_branches |      0.33 |     99.67 |      0.00
 public     | pgbench_history  |     99.35 |      0.65 |      0.00
 public     | pgbench_tellers  |      3.17 |     96.83 |      0.00
 public     | userlist         |     50.04 |      0.25 |     49.71
(5 rows)

cache hit ratio per table

SELECT schemaname, relname, heap_blks_read, heap_blks_hit,
  CASE WHEN heap_blks_read = 0 THEN 0.00
   ELSE round(100*heap_blks_read/(heap_blks_read+heap_blks_hit),2)
  END AS hit_ratio
   FROM pg_statio_user_tables
ORDER BY relname;
sampledb-# ORDER BY relname;
 schemaname |     relname      | heap_blks_read | heap_blks_hit | hit_ratio 
------------+------------------+----------------+---------------+-----------
 public     | pgbench_accounts |          14738 |         25730 |     36.00
 public     | pgbench_branches |             25 |           769 |      3.00
 public     | pgbench_history  |             25 |           359 |      6.00
 public     | pgbench_tellers  |             25 |           747 |      3.00
 public     | userlist         |             10 |          1289 |      0.00
(5 rows)

index

SELECT
 schemaname, relname, seq_scan,
 CASE WHEN idx_scan IS NULL THEN 0 ELSE idx_scan END AS idx_scan,
 CASE WHEN idx_scan IS NULL  THEN 0.00
   ELSE round(100 * idx_scan/(seq_scan + idx_scan), 2) END AS idx_scan_ratio
  FROM pg_stat_user_tables
ORDER BY relname;
 schemaname |     relname      | seq_scan | idx_scan | idx_scan_ratio 
------------+------------------+----------+----------+----------------
 public     | pgbench_accounts |        0 |     2230 |         100.00
 public     | pgbench_branches |     1024 |        0 |           0.00
 public     | pgbench_history  |        0 |        0 |           0.00
 public     | pgbench_tellers  |      890 |        0 |           0.00
 public     | userlist         |       24 |    18611 |          99.00
(5 rows)

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
   FROM pg_stat_user_indexes
   ORDER BY relname;
 schemaname |     relname      |     indexrelname      | idx_scan | idx_tup_read | idx_tup_fetch 
------------+------------------+-----------------------+----------+--------------+---------------
 public     | pgbench_accounts | pgbench_accounts_pkey |     2220 |         2919 |          2220
 public     | pgbench_branches | pgbench_branches_pkey |        0 |            0 |             0
 public     | pgbench_tellers  | pgbench_tellers_pkey  |        0 |            0 |             0
 public     | userlist         | userlist_pkey         |    18611 |        18611 |         18611
 public     | userlist         | userlist_attr_idx     |        0 |            0 |             0
(5 rows)

HOT

SELECT schemaname, relname, n_tup_upd, n_tup_hot_upd,
       round(100*cast(cast(n_tup_hot_upd as numeric)/n_tup_upd as numeric), 2)
        AS hot_upd_ratio
     FROM pg_stat_user_tables WHERE n_tup_upd > 0
     ORDER BY relname;
 schemaname |     relname      | n_tup_upd | n_tup_hot_upd | hot_upd_ratio 
------------+------------------+-----------+---------------+---------------
 public     | pgbench_accounts |       395 |            21 |          5.32
 public     | pgbench_branches |       395 |           395 |        100.00
 public     | pgbench_history  |         5 |             5 |        100.00
 public     | pgbench_tellers  |       395 |           395 |        100.00
 public     | userlist         |        15 |            15 |        100.00
(5 rows)

table definition and size

sampledb=# \d pgbench_accounts
   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       | 
 abalance | integer       | 
 filler   | character(84) | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

SELECT	schemaname, relname, 
   	pg_size_pretty(pg_relation_size(relid)) AS "table size",
   	pg_size_pretty(pg_total_relation_size(relid)) AS "total size"
   FROM pg_statio_user_tables
   ORDER BY relname;
 schemaname |     relname      | table size | total size 
------------+------------------+------------+------------
 public     | pgbench_accounts | 13 MB      | 15 MB
 public     | pgbench_branches | 8192 bytes | 56 kB
 public     | pgbench_history  | 0 bytes    | 0 bytes
 public     | pgbench_tellers  | 8192 bytes | 56 kB
 public     | userlist         | 8192 bytes | 48 kB
(5 rows)


SELECT	schemaname, relname, indexrelname,
   	pg_size_pretty(pg_relation_size(indexrelid)) AS "index size"
   FROM pg_statio_user_indexes
   ORDER BY relname;
 schemaname |     relname      |     indexrelname      | index size 
------------+------------------+-----------------------+------------
 public     | pgbench_accounts | pgbench_accounts_pkey | 2208 kB
 public     | pgbench_branches | pgbench_branches_pkey | 16 kB
 public     | pgbench_tellers  | pgbench_tellers_pkey  | 16 kB
 public     | userlist         | userlist_pkey         | 32 kB
 public     | userlist         | userlist_attr_idx     | 40 kB
(5 rows)

Session role

SELECT datname,usename, pid, backend_start, 
(current_timestamp - backend_start)::interval(3) AS "login time"
FROM pg_stat_activity;
 datname  | usename  |  pid  |         backend_start         |  login time  
----------+----------+-------+-------------------------------+--------------
 postgres | postgres |  2385 | 2014-02-15 02:30:05.1476+09   | 00:22:00.204
 sampledb | postgres | 30413 | 2014-02-15 01:53:39.242691+09 | 00:58:26.109
 sampledb | postgres |  5549 | 2014-02-15 02:52:03.047571+09 | 00:00:02.304
 sampledb | postgres |  5550 | 2014-02-15 02:52:03.056086+09 | 00:00:02.295
 sampledb | postgres |  5551 | 2014-02-15 02:52:03.070362+09 | 00:00:02.281
 sampledb | postgres |  5552 | 2014-02-15 02:52:03.090171+09 | 00:00:02.261
(6 rows)

SQL

SELECT datname, usename, pid,
       (current_timestamp - xact_start)::interval(3) AS duration, 
       waiting, query
         FROM pg_stat_activity WHERE pid != pg_backend_pid();
-[ RECORD 1 ]--------------------------------------------------------------------------
datname  | postgres
usename  | postgres
pid      | 2385
duration | 
waiting  | f
query    | SELECT statsinfo.sample()
-[ RECORD 2 ]--------------------------------------------------------------------------
datname  | postgres
usename  | postgres
pid      | 5649
duration | 
waiting  | f
query    | INSERT INTO statsrepo.autoanalyze VALUES 
	    	         ($1, $2::timestamptz - interval '1sec' * $6, $3, $4, $5, $6)
-[ RECORD 3 ]--------------------------------------------------------------------------
datname  | sampledb
usename  | postgres
pid      | 5728
duration | 00:00:00.004
waiting  | f
query    | UPDATE pgbench_accounts SET abalance = abalance + 4944 WHERE aid = 61886;
-[ RECORD 4 ]--------------------------------------------------------------------------
datname  | sampledb
usename  | postgres
pid      | 5730
duration | 00:00:00.008
waiting  | f
query    | UPDATE pgbench_branches SET bbalance = bbalance + -4699 WHERE bid = 1;
-[ RECORD 5 ]--------------------------------------------------------------------------
datname  | sampledb
usename  | postgres
pid      | 5731
duration | 00:00:00.005
waiting  | f
query    | UPDATE pgbench_tellers SET tbalance = tbalance + 3034 WHERE tid = 7;

Transactions

Version 9.2 or later,

Read full post

postgresql.conf parser in Go

This is a sample code….

https://gist.github.com/s-hironobu/8dc0e355c20ebf8d7708

how to use

Read full post

What is Full-page writes

This post is the fisrt section of my document and is for beginners.

Read full post