Postgres 数据库安装、配置、备份还原及存储过程

一、前提
1、CenTos操作系统，安装系统时选择安装Postgres数据库。
二、安装数据库及配置
1、初始化数据库TestDB
      由于安装系统时选择安装了Postgres数据库，因此系统自带postgres用户名，该用户名是针对Postgres数据库的管理员。
（1）使用系统自带的数据库用户名称postgres，在root权限下执行。

mkdir /usr/local/pgsql/data

chown postgres /usr/local/pgsql/data

su postgres

initdb –D /usr/local/pgsql/data

                postmaster –D /usr/local/pgsql/data/ &
（2）手动创建数据库用户名称，比如postgresql ，在root权限下执行。

adduser postgresql

mkdir /usr/local/pgsql/data

chown postgresql /usr/local/pgsql/data

su postgresql

initdb –D /usr/local/pgsql/data

               postmaster –D /usr/local/pgsql/data/ &

2、配置数据库文件
（1）root权限下进如pgsql下的data目录，修改pg_hba.conf，在其后追加内容如下，其中IP为数据库服务器的IP地址。
           host  all  all  192.168.0.1/24   md5
（2）修改同一目录下的postgresql.conf文件，去掉文件listen_address和port前的#号，然后修改
           listen_address = '*'
（3）使用postgres或postgresql管理员添加用户postuser及对应密码。
经过这样配置，就可以使用postuser及密码远程连接服务器了。

3、初始化数据库TestDB
（1）切换到postgres用户下，创建数据库
          createdb TestDB;
（2）进入数据库
          psql TestDB
4、备份及还原数据库
（1）切换到数据库用户postgres下，执行下面命令：

备份：Pg_dump –h localhost –U postgres –n public –Ft TestDB > /home/postgres/[名称].tar

还原：

切换到数据库用户postgresql下，先删除原数据库"TestDB"（如果存在），再以"TestDB"的名称重新建立数据库，执行下面命令

psql TestDB

create language plpgSQL;

/q

pg_restore –h localhost –U postgres –d TestDB [路径]/ [名称].tar

vacuumdb -a –z

5、添加用户postuser
        createuser postuser -P

6、开机自启动数据库

(1)    用root用户登录服务器，然后切换到/etc/rc.d/目录下。

(2)    使用vi命令打开rc.local文件，在其后追加命令如下：

su - postgresql -c "postmaster -D /usr/local/pgsql/data/&"

7、存储过程

（1）存储过程以plpgsql语言书写，因此在书写存储过程前，先把plpgsql语言安装进数据库TestDB中。

        psql TestDB
                 create language plpgSQL;

（2）创建表举例
CREATE TABLE "CallRecordTable"
(
"CallIndex" INTEGER PRIMARY KEY,
"CallerNumber" VARCHAR(32) NOT NULL DEFAULT 'anonymous',
"CalleeNumber" VARCHAR(32) NOT NULL DEFAULT 'anonymous',
"StartTime" VARCHAR(16) NOT NULL,
"StopTime" VARCHAR(16) NOT NULL,
"StoreFlag" SMALLINT NOT NULL CONSTRAINT Record_Flag_Value CHECK("StoreFlag" >= 1 AND "StoreFlag" <= 10) DEFAULT 1,
"ReGenrationID" VARCHAR(32) NOT NULL REFERENCES "RegenServiceInfoTable" ("ReGenrationID") ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT Caller_Start_OnlyOne UNIQUE("CallerNumber","StartTime"),
CONSTRAINT Callee_Start_OnlyOne UNIQUE("CalleeNumber","StartTime"),
CHECK("StartTime" <= "StopTime")
);
（3）创建存储过程举例
CREATE OR REPLACE FUNCTION "funSelectRecordByDateCall"(Number VARCHAR(32),StartTime VARCHAR(16),EndTime VARCHAR(16))
RETURNS refcursor AS
$$
DECLARE recordcur refcursor;
BEGIN
OPEN recordcur FOR
SELECT
    "CallIndex",
    "CallerNumber",
    "CalleeNumber",
    TO_TIMESTAMP("StartTime",'YYYYMMDDHH24MISS') AS "StartTime",
    TO_TIMESTAMP("StopTime",'YYYYMMDDHH24MISS') AS "StopTime",
    ( TO_TIMESTAMP("StopTime",'YYYYMMDDHH24MISS') - TO_TIMESTAMP("StartTime",'YYYYMMDDHH24MISS') ) AS "ContinuingTime",
    "StoreFlag",
    CR."ReGenrationID",
    "Ip",
    "Port"
FROM
    "CallRecordTable" AS CR,
    "RegenServiceInfoTable" AS RS
WHERE
    ( "StartTime" BETWEEN StartTime AND EndTime )
    AND( "CallerNumber" = Number )
    AND( CR."ReGenrationID" = RS."ReGenrationID" )
    OR( "StartTime" BETWEEN StartTime AND EndTime )
    AND( "CalleeNumber" = Number )
    AND( CR."ReGenrationID" = RS."ReGenrationID" )
ORDER BY "StartTime" DESC;
RETURN recordcur;
END;
$$
LANGUAGE PLpgSQL;

最后，postgres数据库参考文档：http://www.pgsqldb.org/pgsqldoc-8.1c/