PostgreSQL9.1.3をCentOS6.2にインストール、運用。

PostgreSQLのインストール

CentOS6.2には、最初PostgreSQL ver.8.4.9が入っていますが、ここでは最新のPostgreSQL9.1.3をインストールします。

もしすでにデータベースを作成している場合、ファイルをバックアップします。

なお、CentOSは64ビット版です。

[root@localhost ~]# su - postgres
-bash-4.1$ pg_dumpall > dumpfile
-bash-4.1$ exit
[root@localhost ~]# service postgresql stop
[root@localhost ~]# mv /var/lib/pgsql/dumpfile /var/lib/pgsql_back

上記のコマンドでファイルをバックアップします。

[root@localhost ~]# yum -y remove postgresql postgresql-server //ver.8.4.9をアンインストール

yumリポジトリの追加
[root@localhost ~]# cd /usr/src
[root@localhost ~]# wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
[root@localhost ~]# rpm -ivh pgdg-centos91-9.1-4.noarch.rpm
[root@localhost ~]# less /etc/yum.repos.d/pgdg-91-centos.repo  //repoを確認
PostgreSQL関係のパッケージを除外するように設定
[root@localhost ~]# vi /etc/yum.repos.d/CentOS-Base.repo
[base]

exclude=postgresql*

[updates]

exclude=postgresql*
PostgreSQLのインストール
[root@localhost ~]# yum -y install postgresql postgresql-server postgresql-libs 

PostgreSQL起動

[root@localhost ~]# service postgresql-9.1 initdb //postgresqlを初期化
データベースを初期化中:                                      [  OK  ]
[root@localhost ~]# service postgresql-9.1 start
postgresql-9.1 サービスを開始中:                               [  OK  ]
//自動起動設定
[root@localhost ~]# chkconfig postgresql-9.1 on

バックアップファイルをリストアする場合

[root@localhost ~]# mv /var/lib/pgsql_back/dumpfile /var/lib/pgsql/
[root@localhost ~]# su - postgres
-bash-4.1$ psql  dumpfile
-bash-4.1$ exit
[root@localhost ~]# rm -rf /var/lib/pgsql_back
[root@localhost ~]# rm /var/lib/pgsql/dumpfile

データベース毎のダンプ、リストア

[root@localhost ~]# su - postgres
-bash-4.1$ pg_dump -h localhost -U ユーザー名 -c データベース名 > 任意のファイル名.dump
-bash-4.1$ createdb データベース名 -E UTF-8 -O ユーザー名 //データベースを作成
//次でリストアします。
-bash-4.1$ psql -h localhost -U ユーザー名 -f /var/lib/pgsql/任意のファイル名.dump データベース名
-bash-4.1$ exit
[root@localhost ~]# rm /var/lib/pgsql/任意のファイル名.dump

任意の文字コードを設定します。

[root@localhost ~]# su - postgres //postgresユーザになる
-bash-4.1$ echo $LANG ja_JP.UTF-8
-bash-4.1$ exit
[root@localhost ~]# service postgresql-9.1 restart
postgresql-9.1 サービスを停止中:                               [  OK  ]
postgresql-9.1 サービスを開始中:                               [  OK  ]

postgresユーザにパスワード設定

[root@localhost~]# passwd postgres
  Changing password for user postgres.
  New password: //パスワード入力
  Retype new password: //パスワード確認
  passwd: all authentication tokens updated successfully.
※上記の手続きでシステム上のパスワードが出来ました。

//PostgreSQL上のpostgresユーザにパスワードを設定
[root@localhost~]# su - postgres //postgresユーザになる

-bash-4.1$ psql template1 //psqlコマンドでPostgreSQLに接続
psql (9.1.3)
"help" でヘルプを表示します.

template1=# alter user postgres with password 'パスワード'; //PostgreSQL上のpostgresユーザにパスワード設定
ALTER USER

template1=# q //終了

-bash-4.1$ exit  

ローカル、内部からは入室できるように設定

[root@localhost ~]# vi /var/lib/pgsql/9.1/data/pg_hba.conf
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
  local   all         all                               md5  //ローカルの設定。Webサーバーとpostgresqlが同じマシンならtrustでも可。
# IPv4 local connections:
# host    all         all         127.0.0.1/32          ident sameuser
  host    all         all         127.0.0.1/32          md5 //内部からのアクセス許可
# IPv6 local connections:
  host    all         all         ::1/128               md5

trustは無条件で許可、md5はパスワードを暗号化。

PostgreSQLの設定

[root@localhost ~]# vi /var/lib/pgsql/9.1/data/postgresql.conf
shared_buffers = 128MB
max_connections = 500
random_page_cost = 2.0
work_mem = 2MB
effective_cache_size = 512MB
(上記の設定はサーバースペックによって適正値が異なります。上記の例は、サーバーメモリ2Gを想定して作成しました。)
listen_addresses = 'localhost' //コメントアウトを外す 他ホストに接続する場合localhostを*に変更
port = 5432 //コメントアウトを外してポートを開放

PostgreSQLを再起動して設定を反映

[root@localhost ~]# service postgresql-9.1 restart
postgresql-9.1 サービスを停止中:                               [  OK  ]
postgresql-9.1 サービスを開始中:                               [  OK  ]

新規ユーザーを作成

[root@localhost ~]# su - postgres
-bash-4.1$ createuser -P ユーザー名 //user名を入力
Enter password for new role:  //任意のパスワードを入力
Enter it again:  //パスワードの確認
Shall the new role be a superuser? (y/n) n  //スーパーユーザーの権限
Shall the new role be allowed to create databases? (y/n) y //データベース作成権限
Shall the new role be allowed to create more new roles? (y/n) n //別ユーザー作成権限
CREATE ROLE
 //ユーザーを削除する場合
-bash-3.2$ dropuser ユーザー名

データベースを作成

-bash-4.1$ createdb データベース名 -E UTF-8(任意の文字コードを入力。ここではUTF-8) -O ユーザー名(先ほど作成したユーザー)
 //データベースを削除する場合
-bash-4.1$ dropdb データベース名

作成したデータベースを確認

-bash-4.1$ psql template1
psql (9.1.3)
"help" でヘルプを表示します.

template1=# l //このコマンドで確認
  List of databases
  Name         |  Owner   | Encoding
  ----------------------+----------+----------
  db(新規)             | user     | UTF8
  postgres             | postgres | UTF8
  template0            | postgres | UTF8
  template1            | postgres | UTF8
  (4 rows)
template1=# q //退室
-bash-4.1$ exit //ログアウト

phppgadminをインストール

PostgreSQLの管理用にphppgadminをインストールします。

[root@localhost ~]# yum install phpPgAdmin[root@localhost ~]# chown -R nginx:nginx /usr/share/phpPgAdmin //アップデートの都度所有者をnginxにする必要がある。webサーバーがapacheの場合は所有者をapacheに
[root@localhost ~]# cp /usr/share/phpPgAdmin/conf/config.inc.php-dist /usr/share/phpPgAdmin/conf/config.inc.php
[root@localhost ~]# vi /usr/share/phpPgAdmin/conf/config.inc.php
// サーバーホスト名を指定
$conf['servers'][0]['host'] = '';

$conf['servers'][0]['host'] = 'localhost';
に変更
// postgresユーザでのログインを許可
$conf['extra_login_security'] = true;

$conf['extra_login_security'] = false;
に変更
// ログインユーザ所有のデータベース以外は表示しないようにする
$conf['owned_only'] = false;

$conf['owned_only'] = true;
に変更

phppgadmin用Apache設定ファイル作成

[root@localhost ~]# vi /etc/httpd/conf.d/phppgadmin.conf
Alias /phppgadmin /usr/share/phpPgAdmin
<Directory "/usr/share/phpPgAdmin">
AllowOverride None
Order deny,allow
Allow from 127.0.0.1
Allow from 192.168.1
Allow from インターネット環境のローカルIPアドレス
php_value post_max_size        32M
php_value upload_max_filesize  32M  //phpのアップロードを32Mまで許可
</Directory>

上記の設定でphppgadminにIP制限をかけます。

#service httpd restart でapacheを再起動

http://IPアドレス/phppgadminでphpPgAdminをブラウザで表示。作成したユーザー、パスワードでログイン。先の/usr/share/phpPgAdmin/conf/config.inc.phpの編集で、postgresでも入室できます。

phppgadmin用Nginx設定ファイル作成

nginxの場合はセキュリティを高めるためサブドメインで運用します。

DNSサーバーにphppgadmin.example.comのサブドメインを追加の上、nginx.confに次の記述を追加します。

[root@localhost ~]# vi /etc/nginx/conf.d/phppgadmin.conf
server {
    listen       80;
    server_name phppgadmin.example.com;
        location / {
           root   /usr/share/phpPgAdmin;
           index  index.php;
           allow 127.0.0.1;
           allow PCのIPアドレスを入力;
           deny  all;
            secure_link_secret "secret"; //ページURLがMD5ハッシュ値に変換する設定。
            if ($secure_link = "0") {
            return 403;
          }
         rewrite ^/$secure_link break;
         }
        location  ~ .php$ {
            fastcgi_pass   phpfpm;
            fastcgi_index  index.php;
            fastcgi_param  SCRIPT_FILENAME  /usr/share/phpPgAdmin$fastcgi_script_name;
            fastcgi_param PHP_VALUE "
session.save_handler = files
session.save_path = /var/lib/php/session
";
            fastcgi_intercept_errors on;
            include        fastcgi_params;
            }
}

PostgreSQLのデータベースをphpPgAdminでエクスポート,インポートする方法

エクスポート方法

  1. phpPgAdminにアクセス。
  2. 左側の画面でデータベース名を選択。
  3. 右側フレームのエクスポートをクリック。
  4. フォーマットSQLを選択するとdump.sqlというファイル名でパソコン上に保存可能です。

インポート方法

  1. phpPgAdminにアクセスします。
  2. 左側の画面でデータベース名を選択。
  3. 右側フレームのSQL欄の参照を押下してインポートしたいsqlファイルを選択します。
  4. 実行をクリック。

エラーログの設定

デフォルトではエラーの出力を行いませんので、次のように設定します。

[root@localhost ~]# vi /var/lib/pgsql/9.1/data/postgresql.conf
ログの出力先設定
log_destination = 'syslog'

syslog_facility = 'LOCAL0'

syslog_ident = 'postgres'

log_min_messages = warning

log_min_error_statement = error

[root@localhost ~]# vi /etc/rsyslog.conf
//最終行辺りにログファイルの設定を記述します。
local0.*  /var/log/postgresql.log
[root@localhost ~]# /etc/init.d/rsyslog restart
システムロガーを停止中:                                    [  OK  ]
システムロガーを起動中:                                    [  OK  ]
[root@localhost ~]# service postgresql-9.1 restart
postgresql-9.1 サービスを停止中:                           [  OK  ]
postgresql-9.1 サービスを開始中:                           [  OK  ]
vi /etc/logrotate.d/syslog
/var/log/cron
/var/log/maillog
/var/log/messages
/var/log/secure
/var/log/spooler
/var/log/postgresql.log //追加

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です