PG資料庫定時任務：PgAgent編譯安裝使用

概述

pgagent 作為postgresql的一個任務排程代理，在postgresql 9.0 以前 是附帶在pgadmin 包下面的，只是預設不安裝，9.0之後作為了一個單獨是的安裝包。所以要使用pgagent定時任務排程的話還得單獨安裝。

因為某些系統的yum源中不含有pgaent包，或者版本不適配。可以進行編譯安裝

需要下載

這裡我選擇的軟體版本分別是：

postgresql 10.3
pgagent-3.4.0下載地址：https://www.pgadmin.org/download/pgagent.php
cmake-3.12.2 下載地址：http://cmake.org/download/
wxWidgets-3.1.1 下載地址：http://www.wxwidgets.org/downloads/

安裝步驟

postgresql的安裝

（略）

cmake安裝

將安裝包上傳至伺服器並解壓，解壓後進入解壓後生成的cmake-3.12.2目錄，執行以下命令。

[root@lsrac97 ~]# tar zxvf cmake-3.12.2.tar.gz
[root@lsrac97 ~]# cd cmake-3.12.2
[root@lsrac97 cmake-2.8.5]# ./bootstrap && gmake && gmake install

wxWidgets的安裝

先配置.bash_profile檔案

[root@lsrac97 ~]#vi .bash_profile
#加入以下內容：

PATH=$PATH:$HOME/bin:/usr/local/wxWidgets-3.1.1/bin
export LD_LIBRARY_PATH=/usr/local/wxWidgets-3.1.1/lib:$LD_LIBRARY_PATH
#並生效引數檔案，命令如下：
[root@lsrac97 ~]#source .bash_profile

接著解壓該安裝包並進入其目錄，然後編譯和安裝

[root@lsrac97 wxGTK-2.8.12 ]# ./configure --enable-shared=no --enable-unicode=yes --prefix=/usr/local/wxWidgets-3.1.1
[root@lsrac97 wxGTK-2.8.12 ]# make && make install

可能會報錯

configure: error: 
The development files for GTK+ were not found. For GTK+ 2, please
ensure that pkg-config is in the path and that gtk+-2.0.pc is
installed. For GTK+ 1.2 please check that gtk-config is in the path,
and that the version is 1.2.3 or above. Also check that the
libraries returned by `pkg-config gtk+-2.0 --libs` or `gtk-config
--libs` are in the LD_LIBRARY_PATH or equivalent.

解決方法

yum install gtk*

顯示以下內容表示編譯安裝完成

/usr/bin/install -c -m 644 ./include/$f /usr/local/wxWidgets-3.1.1/include/wx-3.1/$f; 
done

 
 ------------------------------------------------------
 
 The installation of wxWidgets is finished.  On certain
 platforms (e.g. Linux) you`ll now have to run ldconfig
 if you installed a shared library and also modify the
 LD_LIBRARY_PATH (or equivalent) environment variable.
 
 wxWidgets comes with no guarantees and doesn`t claim
 to be suitable for any purpose.
 
 Read the wxWindows Licence on licencing conditions.
 
 ------------------------------------------------------
 
[root@stephen wxWidgets-3.1.1]# 

pgagent的安裝

上傳安裝包至伺服器並解壓,進入目錄執行如下命令：

[root@lsrac97 pgAgent-4.0.0-Source]# cmake ./

可能會報錯：

-- Found Threads: TRUE  
CMake Error at cmake/FindBoost.cmake:2044 (message):
  Unable to find the requested Boost libraries.

  Unable to find the Boost header files.  Please set BOOST_ROOT to the root
  directory containing Boost or BOOST_INCLUDEDIR to the directory containing
  Boost`s headers.
Call Stack (most recent call first):
  CMakeLists.txt:112 (FIND_PACKAGE)


CMake Error at CMakeLists.txt:118 (MESSAGE):
  Boost library not found.


-- Configuring incomplete, errors occurred!
See also "/tmp/pgAgent-4.0.0-Source/CMakeFiles/CMakeOutput.log".
See also "/tmp/pgAgent-4.0.0-Source/CMakeFiles/CMakeError.log".

解決方法

yum install boost*

再次執行cmake ./

[root@stephen pgAgent-4.0.0-Source]# cmake ./
-- Boost version: 1.53.0
-- Found the following Boost libraries:
--   filesystem
--   regex
--   date_time
--   thread
--   system
--   chrono
--   atomic
--  
-- ================================================================================
-- Configuration summary:
--  
--   Project                     : pgagent
--   Description                 : pgAgent is a job scheduling engine for PostgreSQL
--   Version                     : 4.0.0
--  
--   PostgreSQL version string   : PostgreSQL 9.6.0
--   PostgreSQL version parts    : 9.6
--   PostgreSQL path             : /usr/local/pgsql-9
--   PostgreSQL config binary    : /usr/local/pgsql-9/bin/pg_config
--   PostgreSQL include path     : /usr/local/pgsql-9.6/include
--   PostgreSQL library path     : /usr/local/pgsql-9.6/lib
--   PostgreSQL share path       : /usr/local/pgsql-9.6/share
--  
--   Boost version               : 1.53.0
--   Boost path                  : /usr/include
--   Boost include directory     : /usr/include
--   Boost library directory     : /usr/lib64
--   Boost Static linking        : NO
-- ================================================================================
--  
-- Configuring done
-- Generating done
-- Build files have been written to: /tmp/pgAgent-4.0.0-Source

編譯完成

然後編譯安裝 make && make install即可

[root@stephen pgAgent-4.0.0-Source]# make
Scanning dependencies of target run
[ 11%] Generating pgagent--4.0.sql, pgagent.control
[ 11%] Built target run
Scanning dependencies of target pgagent
[ 22%] Building CXX object CMakeFiles/pgagent.dir/connection.cpp.o
[ 33%] Building CXX object CMakeFiles/pgagent.dir/job.cpp.o
[ 44%] Building CXX object CMakeFiles/pgagent.dir/misc.cpp.o
[ 55%] Building CXX object CMakeFiles/pgagent.dir/pgAgent.cpp.o
[ 66%] Building CXX object CMakeFiles/pgagent.dir/precomp.cpp.o
[ 77%] Building CXX object CMakeFiles/pgagent.dir/unix.cpp.o
[ 88%] Building CXX object CMakeFiles/pgagent.dir/win32.cpp.o
[100%] Linking CXX executable pgagent
[100%] Built target pgagent
[root@stephen pgAgent-4.0.0-Source]# make install
[ 11%] Built target run
[100%] Built target pgagent
Install the project...
-- Install configuration: ""
-- Installing: /usr/local/bin/pgagent
-- Set runtime path of "/usr/local/bin/pgagent" to ""
-- Installing: /usr/local/share/pgagent.sql
-- Installing: /usr/local/./README
-- Installing: /usr/local/./LICENSE
-- Installing: /usr/local/pgsql-9.6/share/extension/pgagent--4.0.sql
-- Installing: /usr/local/pgsql-9.6/share/extension/pgagent.control
-- Installing: /usr/local/pgsql-9.6/share/extension/pgagent--3.4--4.0.sql
-- Installing: /usr/local/pgsql-9.6/share/extension/pgagent--unpackaged--4.0.sql

配置pgagent

建立擴充

postgres=# create extension pgagent ;
CREATE EXTENSION

啟動pgagent程式

postgres@stephen ~]$ pgagent  hostaddr=127.0.0.1  dbname=postgres user=postgres password=postgres
[postgres@stephen ~]$ 

配置定時任務

連線pgadmin

用你的賬號資訊連線

如果正確安裝了pgagent，就會有個pgagent任務

建立任務

步驟裡主要寫好基礎設定，和需要執行的程式碼

時間表的資訊就可以設定時間的重複。