solaris10安装postgresql9(SMF)

1.下载 postgresql


2.解压安装
cat /etc/passwd,检查是否已有postgres用户(默认已建好)
usermod -d /export/home/postgres postgres

groupadd -g 90 postgres
useradd -u 90 -g postgres -d /export/home/postgres -s /usr/bin/bash postgres
passwd postgres
密码设为:post
cd /export/home
bzcat /export/home/feifan/soft/postgresql-9.0.3-S10.i386-32.tar.bz2 | tar -xvf -

3.配制环境变量
#su - postgres
.profile

PGLIB=$HOME/pgsql/lib;export PGLIB
PGDATA=$HOME/data;export PGDATA
PATH=$PATH:$HOME/pgsql/bin;export PATH
umask 022

4.初始化数据库
#su - postgres
$ln -s 9.0-pgdg pgsql
$cd pgsql/bin
$ ./initdb --encoding=utf8 -D $PGDATA

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default text search configuration will be set to "english".

creating directory /export/home/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /export/home/postgres/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

./postgres -D /export/home/postgres/data
or
./pg_ctl -D /export/home/postgres/data -l logfile start


cd /export/home/postgres/pgsql/bin
./pg_ctl -D $PGDATA -l $PGDATA/pg_log start

5.启动脚本
vi /lib/svc/method/postgres

#!/sbin/sh
#
# Copyright 2008 Sun Microsystems, Inc.  All rights reserved.
# Use is subject to license terms.
#
#ident  "@(#)postgresql 9.0.3     2011/02/15 SMI"

. /lib/svc/share/smf_include.sh

# SMF_FMRI is the name of the target service. This allows multiple instances
# to use the same script.

getproparg() {
        val=`svcprop -p $1 $SMF_FMRI`
        [ -n "$val" ] && echo $val
}

check_data_dir() {
        if [ ! -d $PGDATA ]; then
                echo "Error: postgresql/data directory $PGDATA does not exist"
                exit $SMF_EXIT_ERR_CONFIG
        fi

        if [ ! -w $PGDATA ]; then
                echo "Error: postgresql/data directory $PGDATA is not writable by postgres"
                exit $SMF_EXIT_ERR_CONFIG
        fi

        if [ ! -d $PGDATA/base -o ! -d $PGDATA/global -o ! -f $PGDATA/PG_VERSION ]; then
                # If the directory is empty we can create the database files
                # on behalf of the user using initdb
                if [ `ls -a $PGDATA | wc -w` -le 2 ]; then
                        echo "Notice: postgresql/data directory $PGDATA is empty"
                        echo "Calling '$PGBIN/initdb -D $PGDATA' to initialize"

                        $PGBIN/initdb -D $PGDATA
                        if [ $? -ne 0 ]; then
                                echo "Error: initdb failed"
                                exit $SMF_EXIT_ERR
                        fi
                else
                        echo "Error: postgresql/data directory $PGDATA is not empty, nor is it a valid PostgreSQL data directory"
                        exit $SMF_EXIT_ERR_CONFIG
                fi
        fi
}

PGBIN=`getproparg postgresql/bin`
PGDATA=`getproparg postgresql/data`
PGLOG=`getproparg postgresql/log`

if [ -z $SMF_FMRI ]; then
        echo "Error: SMF framework variables are not initialized"
        exit $SMF_EXIT_ERR
fi

if [ -z $PGDATA ]; then
        echo "Error: postgresql/data property not set"
        exit $SMF_EXIT_ERR_CONFIG
fi

if [ -z $PGLOG ]; then
        echo "Error: postgresql/log property not set"
        exit $SMF_EXIT_ERR_CONFIG
fi

case "$1" in
'start')
        check_data_dir
        $PGBIN/pg_ctl -D $PGDATA -l $PGDATA/$PGLOG start
        ;;

'stop')
        $PGBIN/pg_ctl -D $PGDATA stop
        ;;

'refresh')
        $PGBIN/pg_ctl -D $PGDATA reload
        ;;

*)
        echo "Usage: $0 {start|stop|refresh}"
        exit 1
        ;;

esac
exit $SMF_EXIT_OK

chown root:bin /lib/svc/method/postgres
chmod 555 /lib/svc/method/postgres

6.加入SMF
mkdir /var/svc/manifest/application/database
cd /var/svc/manifest/application/database
vi postgresql.xml

<?xml version="1.0"?>
<!DOCTYPE service_bundle SYSTEM "/usr/share/lib/xml/dtd/service_bundle.dtd.1">
<!--
        ident       "@(#)postgresql.xml   9.0.3     11/02/15 SMI"
        By feifan@CU
-->

<service_bundle type='manifest' name='postgresql'>

<service
        name='application/database/postgresql'
        type='service'
        version='1'>

        <!--
           Wait for all local filesystems to be mounted.
        -->
        <dependency
                name='filesystem-local'
                grouping='require_all'
                restart_on='none'
                type='service'>
                <service_fmri value='svc:/system/filesystem/local:default' />
        </dependency>

       
                        name='loopback'
                grouping='require_all'
                restart_on='none'
                type='service'>
               
       

        <exec_method
                type='method'
                name='start'
                exec='/lib/svc/method/postgres start'
                timeout_seconds='200' />

        <exec_method
                type='method'
                name='stop'
                exec='/lib/svc/method/postgres stop'
                timeout_seconds='200' />

        <exec_method
                type='method'
                name='refresh'
                exec='/lib/svc/method/postgres refresh'
                timeout_seconds='60' />

        <property_group name='general' type='framework'>
                <propval name='value_authorization' type='astring'
                        value='solaris.smf.value.postgres' />
                <propval name='action_authorization' type='astring'
                        value='solaris.smf.manage.postgres' />
        </property_group>

        <instance name='pgsql_32bit' enabled='false'>

                <method_context>
                        <method_credential user='postgres' group='postgres' />
                </method_context>

                <property_group name='postgresql' type='application'>
                        <propval name='bin' type='astring' value='/export/home/postgres/pgsql/bin' />
                        <propval name='data' type='astring' value='/export/home/postgres/data' />
                        <propval name='log' type='astring' value='postmaster.log' />
                        <propval name='value_authorization' type='astring' value='solaris.smf.value.postgres' />
                </property_group>

        </instance>

        <stability value='Evolving' />

        <template>
                <common_name>
                        <loctext xml:lang='C'>
                                PostgreSQL RDBMS
                        </loctext>
                </common_name>
                <documentation>
                        <manpage title='postgres' section='5' />
                        <doc_link name='postgresql.org'
                                uri='' />
                </documentation>
        </template>

</service>
</service_bundle>

chown root:sys /var/svc/manifest/application/database/postgresql.xml
chmod 444 /var/svc/manifest/application/database/postgresql.xml
#检查文件格式
svccfg -v validate postgresql.xml
#导入smf数据库
svccfg -v import postgresql.xml

#启动
svcadm enable postgresql

7.修改配制(重启应用生效)
#postgresql.conf
vi $PGDATA/postgresql.conf
修改

listen_addresses = '*'

#pg_hba.conf
添加

host    mdcpg             mdcts           192.168.195.0/24        md5

数据库部分
1.创建角色(用户)
创建角色ftp,不允许此角色创建其他角色,不允许此角色创建数据库；不允许此角色创建角色,角色不是超级用户

createuser -D -R -S -P ftp

注:程序 createuser 和 dropuser 提供了对了CREATE ROLE和DROP ROLE SQL 命令的封装,createuser命令默认用户具有login权限,create role没有,需指定

postgres=# create role mdcts login password 'mdc';

2.建表空间

postgres=# create tablespace mdc_data owner mdcts location '/export/home/postgres/data/pg_tblspc';

3.建库

postgres=# create database mdcpg owner=mdcts tablespace mdc_data;

4.建模式--模式（schema）是对数据库（database）逻辑分割
用mdcts用户登录建schema

mdcpg=> create schema mdcts authorization mdcts;

选项 AUTHORIZATION:
1、当创建SCHEMA时，可以包含创建对象的子命令，这个命令是分离的，和你先建SCHEMA，后建对象是一样的效果;
2、如果有指定AUTHORIZATION user_name，所有创建的对象（包括子命令的对象）都属于user_name, 否则属于执行该语句的用户


参考文档
819-5150.pdf
solaris-smf-manifest-wp-167902.pdf
PostgreSQL-9-Admin-Cookbook.pdf