[PostgreSQL]如何使用pl/python來連線到SQL Server

1. Install ODBC Libs
    1.1 #yum install unixODBC-devel
    1.2 install freetds
          #wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
          # cd freetds-0.91/
          # ./configure
          # make
          #  make install

2.config ODBC
    2.1 #vi /etc/odbcinst.ini
----------------------------------------------
[FreeTDS]
Driver = /usr/local/lib/libtdsodbc.so
----------------------------------------------

    2.2 #vi /usr/local/etc/freetds.conf
----------------------------------------------  
[SQLDemo]
        host = 172.16.1.125
        port = 1433
        tds version = 8.0
----------------------------------------------
    2.3 #vi /etc/odbc.ini
----------------------------------------------
[FreeTDS]
Description=my dsn
Driver=FreeTDS
Database=master
Servername=SQLDemo
----------------------------------------------

3.Install Python Libs
    3.1 install pip
        # wget https://bootstrap.pypa.io/get-pip.py
        # python get-pip.py
    3.2 install pypyodbc
        # pip install pypyodbc

4. create extension
    psql#> create extension plpythonu;

5. sample for plpythonu
    5.1 Select
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION selsqlserver()
  RETURNS text AS
$BODY$
  import pypyodbc

  conn = pypyodbc.connect("DSN=FreeTDS;UID=sa;PWD=1qaz2wsx3edc$")
  cr = conn.cursor()
  cr.execute('select name from tb1')
  a = ""
  for r in cr:
      a+=r[0]

  return a
$BODY$
  LANGUAGE plpythonu
--------------------------------------------------------------------------------------------
 
    5.2 Update
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION updsqlserver()
  RETURNS integer AS
$BODY$
  import pypyodbc

  conn = pypyodbc.connect("DSN=FreeTDS;UID=sa;PWD=1qaz2wsx3edc$")
  cr = conn.cursor()
  cr.execute("update tb1 set name = 'eric' where id = 2")
  conn.commit()

  return 1
$BODY$
  LANGUAGE plpythonu
--------------------------------------------------------------------------------------------

PostgreSQL 效能

• Cache and Hit ratio

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

Index Usage


SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 
ORDER BY 
  n_live_tup DESC;

Index Cache Hit ratio


SELECT 
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM 
  pg_statio_user_indexes;

[PostgreSQL]常見問題

1.如何查看Table or User的存取權限

// by user
SELECT (table_schema || '.' || table_name) AS table, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE grantee='user_name'
GROUP BY (table_schema || '.' || table_name);

// by table
SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE table_name='table_name'
GROUP BY grantee;

 
 2.如何查看Database大小

SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database 
ORDER BY pg_database_size DESC;

 3.如何查看各Table的大小

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC;

SELECT nspname || '.' || relname AS "relation", T.spcname,
    pg_size_pretty(pg_relation_size(C.oid)) AS "size",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_total_relation_size(C.oid) DESC

4.如何create 一個readonly的user

1.CREATE ROLE xxx LOGIN PASSWORD 'yyy';
2.GRANT CONNECT ON DATABASE mydb TO xxx;
3.GRANT USAGE ON SCHEMA my_schema | public TO xxx;
4.GRANT SELECT ON ALL TABLES IN SCHEMA my_schema | public TO xxx;

5.如何查到Lock的Statment

  SELECT bl.pid                 AS src_pid,
         a.usename              AS src_user,
         ka.query               AS src_stmt,
         now() - ka.query_start AS src_duration,
         kl.pid                 AS blk_pid,
         ka.usename             AS blk_user,
         a.query                AS blk_stmt,
         now() - a.query_start  AS blk_duration
  FROM  pg_locks         bl
   JOIN pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.granted;

6.pgxc如何查到table dist的Statment

 SELECT pg_class.relname relation,
        pgxc_class.pclocatortype distribution,
        pg_attribute.attname attr
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = pg_attribute.attnum
 UNION
 SELECT pg_class.relname relation,
        pgxc_class.pclocatortype distribution,
        'none' attr
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = 0

7.利用原有的db object來gen drop/ create stmt(alter table)

CREATE OR REPLACE FUNCTION gen_pk_stmt()
RETURNS TABLE(rtnDropStr text, rtnCreateStr text) AS $body$
DECLARE
    mviews RECORD;
    mviews2 RECORD;
    dropStr text;
    createStr text;
    rtn RECORD;
BEGIN
    FOR mviews in (SELECT nspname, relname, conname, spcname
            FROM pg_class C
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
            LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
            LEFT JOIN pg_constraint CT ON (C.oid = CT.conrelid)
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
              AND conname is not null
            ORDER BY pg_total_relation_size(C.oid) DESC) LOOP
    createStr = 'ALTER TABLE ' || mviews.nspname || '.' ||  mviews.relname || ' ADD CONSTRAINT ' || mviews.nspname || '.' || mviews.conname || ' PRIMARY KEY (';
    dropStr = 'ALTER TABLE ' || mviews.nspname || '.' ||  mviews.relname || ' DROP CONSTRAINT ' || mviews.nspname || '.' || mviews.conname;
        FOR mviews2 in (select kc.column_name
            from
                information_schema.table_constraints tc
                join information_schema.key_column_usage kc
                on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
            where tc.constraint_type = 'PRIMARY KEY'
              and tc.table_schema = mviews.nspname
              and tc.table_name = mviews.relname) LOOP
                createStr = createStr || mviews2.column_name || ',';
    END LOOP;
    createStr = rtrim(CreateStr, ',') || ')';
    if mviews.spcname is not null then
        createStr = createStr || ' USING INDEX TABLESPACE ' || mviews.spcname;
    end if;
   
    rtnDropStr = dropStr;
    rtnCreateStr = createStr;
    RETURN NEXT;
   
    END LOOP;
END
$body$ LANGUAGE PLPGSQL

[CBM] 自行開發Centralize Backup Server

1. NFS:
# yum install nfs-utils nfs-utils-lib
# chkconfig nfs on
# mkdir -p /data/xdbha1
# vi /etc/hosts
------------------------------------------------------------------------------------------------

192.168.38.121  xdbha1  # 加入db server的ip與名稱

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

# vi /etc/exports

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

/data/xdbha1/ xdbha1(rw,no_root_squash,no_all_squash,sync)

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

# service nfs start | stop | restart

於各PostgreSQL Database機器上:
# vi /etc/hosts
------------------------------------------------------------------------------------------------

192.168.38.99   CBM  # 加入CBM server的ip與名稱

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

# mkdir -p /cbm_data

# showmount -e CBM 

Export list for CBM:

/data/xdbha1 xdbha1

# mount -t nfs CBM:/data/xdbha1 /cbm_data

測試後如果沒問題，就設定開機mount
# vi /etc/fstab
------------------------------------------------------------------------------------------------

CBM:/data/xdbha1        /cbm_data               nfs     rw              0 0

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

2.PG_RMAN:

[SymmetricDS/CrystalDB CRS] SMR - DB2 to CrystalDB(PostgreSQL)

1.環境:
db2_server
--------------------------------------
ip : 192.168.38.22
帳號 : db2inst1 / 1qaz2wsx, tmp / tmp , crs /crs
ibm db2 udb (9.5)
共有兩個要做replication的schema: db2inst1, tmp
CRS使用的schema: crs
共有五個table要複製 :
DB2INST1.item
DB2INST1.item_selling_price
DB2INST1.sale_return_line_item
DB2INST1.sale_transaction
TMP.TB1
--------------------------------------

CrystalDB
--------------------------------------
ip : 192.168.38.121
帳號 : db2inst1 / 1qaz2wsx, tmp / tmp , crs /crs
CrystalDB (9.3 base on PostgreSQL 9.3)
共有三個schema對應db2上的三個, db2inst1, tmp, crs
--------------------------------------

[PostgreSQL/PPAS] How to do point in time recovery with PostgreSQL 9.3 - PITR

PITR一般在PostgreSQL中的應用有兩種:

• 用在Hot Streaming中shipping log用
• 用在Incremental backup做為Archive log用(continuous archiving)

實作PITR，從設定、備份到還原主要有幾個步驟，以下將實作每個步驟並針對各步驟說明。

0. 準備工作
    a. 建立Archive要存放的資料夾，並設定權限 (mkdir -p /pg_backup/arlog  && chown postgres.postgres /pg_backup/arlog)
    b. 建立Full Backup檔要存放的資料夾，並設定權限   (mkdir -p /pg_backup/fullbackup && chown postgres.postgres /pg_backup/fullbackup)
    c. 建立Restore要存放的資料夾，並設定權限 (mkdir -p /pg_backup/restore  && chown postgres.postgres /pg_backup/restore)

1.設定WAL(Write Ahead Log) archiving
於postgresql.conf內定義三個主要參數
wal_level = archive # or hot_standby for hot streaming...
archive_mode = on
archive_command = 'cp %p /pg_backup/arlog/%f'
設定完成後重啟Service當超過conf檔定義的wal log大小跟segement後就會開始copy到archive folder.

[PostgreSQL/PPAS] 利用Postgres_FDW + Materialized View來Stream及封裝Data

DB1--> testdb -> tb1 (實體Table)
# create database testdb;
# create table tb1 (a int, b text);
# insert into tb1 values (1,'a'),(2,'b');

DB2 -> testdb1 ->
// 先開啟postgres_fdw extension.
# create extension postgres_fdw;

// create remote server, remote那台的pg_hba.conf要開
# CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'DB1', dbname 'testdb', port '5432');

// create user mapping for remote server
# CREATE USER MAPPING for postgres SERVER myserver;

// create MView
# CREATE MATERIALIZED VIEW remote_tb1 AS SELECT * FROM tb1;

// refresh MView
# REFRESH MATERIALIZED VIEW remote_tb1;

**以目前PostgreSQL 9.3.2版的Materialized View並不支援Refresh Concurrently所以在refresh時原有的MView會被Lock!!

[PostgreSQL] 統計SQL記錄

1.列出前十筆最大的Table:

SELECT N.nspname || '.' || C.relname AS "table", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema','pg_toast')
ORDER BY pg_relation_size(C.oid) DESC LIMIT 10;

[MySQL] MySQL 5.6 Replication and Failover

Preparation :
1.準備四台CentOS 6.x 64bit的VM
2.在上面先裝好MySQL在每個VM裡

1.安裝MySQL Utilities 及 MySQL Connector/Python
// for utilties
#python setup.py install
// for connector/python
#rpm -ivh mysql-connector-python-1.0.12-1.el6.noarch.rpm

2.設定my.cnf在每台MySQL上
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
server-id=1  #每台機器都要不一樣

[Other] 如何打包.run在Linux環境

最近寫了PESI的Agent Code，想要讓安裝容易一些，所以把腦筋動到了Linux上的.run檔，經過Google大神的引導，讓我找到了makeself。

makeself裡面的東西就請大家自己到該網頁看囉!!這裡主要是記錄我如何打包PESIAgent。

1.先安裝好makeself...其實就是解壓縮XD
# ./makeself.run

2.準備安裝所有的檔案在同一個folder
# cd makeself-2.1.5/
# mkdir pesi_src
# cp PESIAgent.jar ./pesi_src/
# cp pesi.properties ./pesi_src/
# cp pesi.userparams.conf ./pesi_src/

3.編寫install shell...
# cd pesi_src
# vi pesi_install.sh
----------------------------
#!/bin/bash
Dir=`pwd`
cd ${Dir}
mkdir -p /opt/zabbix/bin
mkdir -p /opt/zabbix/etc

cp PESIAgent.jar /opt/zabbix/bin/
cp pesi.properties /opt/zabbix/etc/
cp pesi.userparams.conf /opt/zabbix/etc/

#sed -i `grep -n 'Include=' /opt/zabbix/etc/zabbix_agentd.conf | awk -F ":" '{ print $1 }' | tail -1`'a Include=/opt/zabbix/etc/pesi.userparams.conf' /opt/zabbix/etc/zabbix_agentd.conf

sed -i '/^# Include=$/ {
a\Include=/opt/zabbix/etc/pesi.userparams.conf
}' /opt/zabbix/etc/zabbix_agentd.conf
----------------------------

4.利用makeself來打包囉
格式如下:

makeself.sh [args] archive_dir file_name label startup_script [script_args]

# ./makeself.sh pesi_src pesi_agent_install.run "PESI Agent Install" ./pesi_install.sh

archive_dir:就是pesi_src,就是把你要打包的所有東西存放的folder < pesi_src >
file_name:打包出來的檔 < pesi_agent_install.run >
label:顯示在安裝時的訊息
start_script:安裝要執行script，必須是./開頭喔!!