[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;









張貼者：



於





沒有留言:
  

































        

      









訂閱：
文章 (Atom)