TOP ▲ itcore TOPTIPSpostgres.php

PostgreSQL インストール centOS6 | itcore 2018年

インストール

$ sudo -s
# yum -y install postgresql-server
# chkconfig postgresql on
# service postgresql initdb
# service postgresql start
# yum -y install php-pgsql
# service httpd restart
# vi /var/lib/pgsql/data/pg_hba.conf
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

確認

# psql -U postgres postgres
psql (8.4.20)
"help" でヘルプを表示します.

postgres=# select version();
                                                      version
------------------------------------------------------------------------------------------------ -------------------
 PostgreSQL 8.4.20 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

postgres=# \l
                                           データベース一覧
     名前     |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |      アクセス権
--------------+----------+------------------+-------------+-------------------+----------------- ------
 postgres     | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0    | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres : postgres=CTc/pos tgres
 template1    | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | postgres=CTc/postgres

postgres=# \dn
         スキーマ一覧
        名前        |  所有者
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres

postgres=# select * from pg_stat_sys_tables limit 1;
 relid | schemaname |   relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_i
ns | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autova
cuum | last_analyze | last_autoanalyze
-------+------------+-------------+----------+--------------+----------+---------------+--------
---+-----------+-----------+---------------+------------+------------+-------------+------------
-----+--------------+------------------
  2753 | pg_catalog | pg_opfamily |        0 |            0 |        0 |             0 |
 0 |         0 |         0 |             0 |          0 |          0 |             |
     |              |

postgres=# set search_path to pg_catalog;
SET

postgres=# \d pg_opfamily
テーブル "pg_catalog.pg_opfamily"
    カラム    |  型  |  修飾語
--------------+------+----------
 opfmethod    | oid  | not null
 opfname      | name | not null
 opfnamespace | oid  | not null
 opfowner     | oid  | not null
インデックス:
    "pg_opfamily_am_name_nsp_index" UNIQUE, btree (opfmethod, opfname, opfnamespace)
    "pg_opfamily_oid_index" UNIQUE, btree (oid)

postgres=# \q

バックアップ

pg_dumpall -c -U postgres > all.sql

リストア

psql -f all.sql postgres