setup dsa greening db

1. 建立role.sql

    CREATE ROLE "SSchool_Admin"
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
    CREATE ROLE "SSchool_Service"
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
    CREATE ROLE "SSchool_User"
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
    CREATE ROLE "STeacher_Admin"
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   
    CREATE ROLE "Service_Greening"
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
   
    CREATE ROLE ischool LOGIN
      ENCRYPTED PASSWORD 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
    GRANT "SSchool_Admin" TO ischool;
    GRANT "SSchool_User" TO ischool;
   
    CREATE ROLE ischool_admin LOGIN
      ENCRYPTED PASSWORD 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
      NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
    GRANT "SSchool_Admin" TO ischool_admin;
    GRANT "SSchool_User" TO ischool_admin;
   
    CREATE ROLE ischool_service LOGIN
      ENCRYPTED PASSWORD 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
      SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
    GRANT "SSchool_Admin" TO ischool_service;
    GRANT "SSchool_Service" TO ischool_service;
    GRANT "SSchool_User" TO ischool_service;
   
    CREATE ROLE pgsql LOGIN
      SUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
   
    ALTER ROLE pgsql WITH PASSWORD 'xxxx';
   

1. 建立資料表

   • [注1]：資料庫名稱，例：dsa_server

    [ischool@xxx ~]$ sudo su - postgres
    Last login: Thu Jan 29 04:53:01 UTC 2015 on pts/0
    -bash-4.1$ psql -d "postgres" -f "[role.sql真實路徑]"
    -bash-4.1$ psql
    psql (9.2.9)
    Type "help" for help.
   
    postgres=# CREATE DATABASE [注3];
    CREATE DATABASE
    postgres=# \q
    -bash-4.1$ exit
    logout
   

2. 還原資料庫

   • 下載 備份檔

      pg_restore -U "postgres" --dbname "[注3]" --verbose "[備份檔來源位置]"
     

   • 應只有發生一個lock_timeout錯誤