Postgresql uuid,pgtrashcan,pldebugger install

最近也许需要用到uuid，介绍一下插件安装

1.下载UUID插件

http://www.ossp.org/pkg/lib/uuid/

2.编译安装

cd uuid-1.6.2/

./configure 

make && make install

3.重新编译安装PG

./configure --prefix=/opt/pgsql9.5.2 --with-pgport=1921 --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-uuid=ossp --with-libs=/usr/local/lib --with-includes=/usr/local/include

gmake

gmake install-world

4.进入要使用的uuid的database,使用有超级权限的用户安装插件

create extension "uuid-ossp";

5.查看已经安装完毕

hank=> \dx

                                    List of installed extensions

     Name     | Version |   Schema   |                         Description                          

--------------+---------+------------+--------------------------------------------------------------

 dblink       | 1.1     | public     | connect to other PostgreSQL databases from within a database

 pageinspect  | 1.3     | public     | inspect the contents of database pages at a low level

 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language

 plpythonu    | 1.0     | pg_catalog | PL/PythonU untrusted procedural language

 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers

 uuid-ossp    | 1.0     | public     | generate universally unique identifiers (UUIDs)

有个业务需要pgtrashcan输出一个报告，个人感觉并没有什么卵用，生产环境安装后极为不方便

https://github.com/petere/pgtrashcan

下载安装该插件：

cd pgtrashcan

make PG_CONFIG=/opt/pgsql/bin/pg_config

make install PG_CONFIG=/opt/pgsql/bin/pg_config

vi postgresql.conf

加入以下参数，重新启动数据库即可

shared_preload_libraries = 'pgtrashcan'

以下是一个表的删除实例以及恢复：

其实原理比较简单，就是把删除的表放入到了一个叫Trash的schema下面，恢复的时候完了把表从Trash改回到原来的schema即可

hank=> \dt

       List of relations

 Schema | Name  | Type  | Owner 

--------+-------+-------+-------

 hank   | emp   | table | hank

 hank   | foo   | table | hank

 hank   | t1    | table | hank

 hank   | t2    | table | hank

 hank   | t3    | table | hank

 hank   | t4    | table | hank

 hank   | t_ret | table | hank

 hank   | test  | table | hank

(8 rows)

hank=> \c hank postgres

You are now connected to database "hank" as user "postgres".

hank=# drop table hank.test;

DROP TABLE

hank=# select count(*) from hank.test;

ERROR:  relation "hank.test" does not exist

LINE 1: select count(*) from hank.test;

                             ^

hank=# alter table "Trash".test set schema hank;      

ALTER TABLE

hank=# select count(*) from hank.test;          

 count 

-------

     0

(1 row)

应需求，开发需要pgadmin有调试功能，安装pldebugger模块

下载：

git clone git://git.postgresql.org/git/pldebugger.git

进入目录设置环境变量

cd pldebugger/

export PGHOME=/opt/pgsql

export PATH=$PGHOME/bin:$PATH

编译安装：

USE_PGXS=1 make

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I/opt/pgsql/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o plpgsql_debugger.o plpgsql_debugger.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o plugin_debugger.o plugin_debugger.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o dbgcomm.o dbgcomm.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pldbgapi.o pldbgapi.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o plugin_debugger.so plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o -L/opt/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags

USE_PGXS=1 make install

/bin/mkdir -p '/opt/pgsql/lib'

/bin/mkdir -p '/opt/pgsql/share/extension'

/bin/mkdir -p '/opt/pgsql/share/extension'

/bin/mkdir -p '/opt/pgsql/share/doc/extension'

/usr/bin/install -c -m 755  plugin_debugger.so '/opt/pgsql/lib/plugin_debugger.so'

/usr/bin/install -c -m 644 .//pldbgapi.control '/opt/pgsql/share/extension/'

/usr/bin/install -c -m 644 .//pldbgapi--1.0.sql .//pldbgapi--unpackaged--1.0.sql  '/opt/pgsql/share/extension/'

/usr/bin/install -c -m 644 .//README.pldebugger '/opt/pgsql/share/doc/extension/'

修改postgresql.conf中shared_preload_libraries = '$libdir/plugin_debugger'

重启数据库：

pg_ctl restart -m fast

超级用户连接后，创建extention 

\c xxxx xxxx  

create extension pldbgapi ;

安装后，pgadmin可见调试选项