phpPgAdmin5.0 Usage 1

digoal

phpPgAdmin 图形化PostgreSQL管理工具,适合专业懒人使用,非专业人员慎用,否则后果自负.
2010-11-29 5.0版本放出，加入了一些新特性和BUG修正。今天刚好有空，试用了一下。
一、phpPgAdmin5.0 新特性介绍
二、phpPgAdmin5.0 部署

一、phpPgAdmin5.0 新特性介绍 (太长，不翻译了)
* Support for PostgreSQL 8.4 and 9.0
* Support for database-level collation for 8.4+
* Support for schema-level export
* Add ability to alter schema ownership
* Clean up domain support and improve interface
* Add support for commenting on functions
* Allow user to rename role/users and set new passwords at the same time
* Greatly enhanced Full-Text-Search capabilities (ioguix, Loomis_K)
* Overhauled Selenium Test suite to support multiple database versions
* Optimized application graphics (Limo Driver)
* Support for Column Level Privileges
* Allow users to specify a template database at database creation time
* Support killing processes
* Add ability to create indexes concurrently
* Much better support of autovacuum configuration
* Add an admin page for table level
* Refactored autocompletion:
* fix support for cross-schema objects
* support multi-field FK
* support for pagination of values in the auto-complete list
* Allow user to logicaly group their server under custom named node in the browser tree
* Add a theme switcher on the introduction page
* Add themes "Blue/Green" by Tomasz Pala and "Cappuccino" by ioguix
* Auto refresh Locks page
* Auto refresh Processes page
* Link in the bottom of the page to go to top of page
* Browsing on Foreign Keys (When browsing a table, clicking on a FK value, jump to the PK row)

注意事项:
1. PHP版本相关 :
Please, note that the parameter "date.timezone" should be set in your php.ini conf file. Since version 5.1, PHP might issue a NOTICE or a WARNING if your setup is not valid or missing.
2. phpPgadmin配置相关 :
Due to the large number of phpPgAdmin installations that have not set
   passwords on local connections, there is now a configuration file
   option called 'extra_login_security', which is TRUE by default.  While
   this option is enabled, you will be unable to log in to phpPgAdmin as
   the 'root', 'administrator', 'pgsql' or 'postgres' users and empty passwords
   will not work.(开启了这个安全的话超级用户或空密码用户将不被认证，报如下错误,所以建议新建一个DBA帐号)
phpPgAdmin5.0 Usage - 德哥@Digoal - The Heart,The World.


二、phpPgAdmin5.0 部署
软件需求:
postgresql 9+
httpd2.2.17
php-5.3.4
phpPgAdmin5.0
其他 gcc make flex tar ...

1. 安装postgresql
安装php时会用到,本例安装到目录 /opt/pgsql_9beta2_magent
安装过程略过

2. 安装httpd
官方地址 http://httpd.apache.org/
选择最新的稳定版本下载: 2.2.17 (released 2010-10-19)
解压 : tar -jxvf httpd-2.2.17.tar.bz2
mkdir -p /opt/apache/httpd
cd /opt/soft_bak/httpd-2.2.17
./configure  --prefix=/opt/apache/httpd --enable-so
make
make install
(确保IPTABLES配置允许你的浏览器所在电脑访问到80端口)
/opt/apache/httpd/bin/apachectl start
如果弹出 页面 It works! 表示正常.
/opt/apache/httpd/bin/apachectl stop

3. 安装php
官方地址 : http://cn.php.net/downloads.php
选择最新稳定版本下载: PHP 5.3.4 (tar.bz2) [10,551Kb] - 09 December 2010
mkdir -p /opt/php-5.3.4
tar -jxvf php-5.3.4.tar.bz2
cd php-5.3.4
./configure --with-apxs2=/opt/apache/httpd/bin/apxs --with-pgsql=/opt/pgsql_9beta2_magent --prefix=/opt/php-5.3.4
make
make test
make install

4. 配置php
可参考 : http://cn.php.net/manual/en/install.unix.apache2.php
cp /opt/soft_bak/php-5.3.4/php.ini-development /opt/php-5.3.4/lib
cd /opt/php-5.3.4/lib
mv php.ini-development php.ini

5. 配置phpPgAdmin
tar -jxvf phpPgAdmin-5.0.tar.bz2
mv phpPgAdmin-5.0 /opt/
cd /opt/phpPgAdmin-5.0/conf
vi config.inc.php
示例:
<?php
        /**
         * Central phpPgAdmin configuration.  As a user you may modify the
         * settings here for your particular configuration.
         *
         * $Id: config.inc.php-dist,v 1.55 2008/02/18 21:10:31 xzilla Exp $
         */
       // 这里配置了两个被管理的库,如果要加的话增加$conf['servers'][2]等....
        $conf['servers'][0]['desc'] = 'PostgreSQL';
        $conf['servers'][0]['host'] = '172.16.3.33';
        $conf['servers'][0]['port'] = 1921;
        $conf['servers'][0]['sslmode'] = 'allow';
        $conf['servers'][0]['defaultdb'] = 'template1';
        $conf['servers'][0]['pg_dump_path'] = '/opt/pgsql_9beta2_magent/bin/pg_dump';
        $conf['servers'][0]['pg_dumpall_path'] = '/opt/pgsql_9beta2_magent/bin/pg_dumpall';
        $conf['servers'][0]['slony_support'] = false;
        $conf['servers'][1]['desc'] = 'Test Server';
        $conf['servers'][1]['host'] = '172.16.3.39';
        $conf['servers'][1]['port'] = 1921;
        $conf['servers'][1]['sslmode'] = 'allow';
        $conf['servers'][1]['defaultdb'] = 'template1';
        $conf['servers'][1]['pg_dump_path'] = '/opt/pgsql_9beta2_magent/bin/pg_dump';
        $conf['servers'][1]['pg_dumpall_path'] = '/opt/pgsql_9beta2_magent/bin/pg_dumpall';
        $conf['servers'][1]['slony_support'] = false;

        $conf['default_lang'] = 'auto';
        $conf['autocomplete'] = 'default on';

        $conf['extra_login_security'] = false;   (因为我这里只是测试,所以允许postgres登录)
        $conf['owned_only'] = false;
        $conf['show_comments'] = true;
        $conf['show_advanced'] = false;
        $conf['show_system'] = false;
        $conf['show_reports'] = true;
        $conf['reports_db'] = 'phppgadmin';
        $conf['reports_schema'] = 'public';
        $conf['reports_table'] = 'ppa_reports';
        $conf['owned_reports_only'] = false;
        $conf['min_password_length'] = 1;
        $conf['left_width'] = 200;

        $conf['theme'] = 'default';

        $conf['show_oids'] = false;

        $conf['max_rows'] = 30;
        $conf['max_chars'] = 50;
        $conf['use_xhtml_strict'] = false;

        $conf['ajax_refresh'] = 3;

        /*****************************************
         * Don't modify anything below this line *
         *****************************************/
        $conf['version'] = 19;
?>

被管理的库如果需要用到phpPgAdmin的report功能,可以安装phpPgAdmin的安装文档和配置文件执行sql子目录中的脚本:

6. 配置httpd
vi /opt/apache/httpd/conf/httpd.conf
修改或加入如下参数
LoadModule php5_module        modules/libphp5.so

<FilesMatch "\.ph(p[2-6]?|tml)$">
    SetHandler application/x-httpd-php
</FilesMatch>

<FilesMatch "\.phps$">
    SetHandler application/x-httpd-php-source
</FilesMatch>

<Directory />
    Options Indexes FollowSymLinks
    AllowOverride None
    Order deny,allow
    Allow from all
    Satisfy all
</Directory>

<IfModule dir_module>
    DirectoryIndex index.php
</IfModule>

DocumentRoot "/opt/phpPgAdmin-5.0"