documents:quick:quick-0001
目次
PostgreSQLクイックセットアップ
2017/03/05
FreeBSDパッケージでの設定方法を作成。
2008年08月04日 01時53分27秒
FreeBSD/amd64 7.0 Release で PostgreSQL 8.3 インストールのメモ。
設定
jail環境へインストールする場合の注意
jail環境下で共用メモリの使用は推奨されていない。 →FreeBSD QandA 1866
これをふまえ、覚悟があるなら
# sysctl -w security.jail.sysvipc_allowed=1
を親環境で実行するなり、親環境の /etc/sysctl.conf へ記述するなりしておく。
※FreeBSD QandA 1866の 'sysctl -w jail.sysvipc_allowed=1'は誤り
PostgreSQLをインストール(ports)
ports でインストールしました。make; make install でな。 とりあえずコンパイル終了時のメッセージは読んでおけ。
======================================================================
For procedural languages and postgresql functions, please note that
you might have to update them when updating the server.
If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.
The port is set up to use autovacuum for new databases, but you might
also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
you may find useful. You can use it to backup and perfom vacuum on all
databases nightly. Per default, it perfoms `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~pgsql/data/postgresql.conf.
To allow many simultaneous connections to your PostgreSQL server, you
should raise the SystemV shared memory limits in your kernel. Here are
example values for allowing up to 180 clients (configurations in
postgresql.conf also needed, of course):
options SYSVSHM
options SYSVSEM
options SYSVMSG
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120
If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.
Please note that if you use the rc script,
/usr/local/etc/rc.conf/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default. Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.
To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_class="postgres"' to /etc/rc.conf.
======================================================================
To initialize the database, run
/usr/local/etc/rc.d/postgresql initdb
You can then start PostgreSQL by running:
/usr/local/etc/rc.d/postgresql start
For postmaster settings, see ~pgsql/data/postgresql.conf
NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~pgsql/data/postgresql.conf for more info
======================================================================
To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf
======================================================================
===> Installing rc.d startup script(s)
===> Registering installation for postgresql-server-8.3.3
===> SECURITY REPORT:
This port has installed the following files which may act as network
servers and may therefore pose a remote security risk to the system.
/usr/local/bin/postgres
This port has installed the following startup scripts which may cause
these network services to be started at boot time.
/usr/local/etc/rc.d/postgresql
If there are vulnerabilities in these programs there may be a security
risk to the system. FreeBSD makes no guarantee about the security of
ports included in the Ports Collection. Please type 'make deinstall'
to deinstall the port if this is a concern.
For more information, and contact details about the security
status of this software, see the following webpage:
http://www.postgresql.org/
#
/etc/rc.conf への追記
メッセージにあった
To run PostgreSQL at startup, add 'postgresql_enable="YES"' to /etc/rc.conf
に従い、 /etc/rc.conf へ以下を追記する。/etc/rc.conf 自体がなければ多分jail環境だ。新規で作れ。
postgresql_enable="YES" postgresql_data="/usr/local/pgsql/DATA"
/usr/local/pgsql は、インストールで作成されたOSユーザ、 pgsql のホームディレクトリ。 データベースインスタンスを /usr/local/pgsql/DATA に格納するため、ここに定義しておく。 ※postgresql_data は自動起動の際にインスタンスを格納したディレクトリを指定するために使用する。
カーネルパラメタ調整
動くなら実施の必要は無い。 今回は、PostgreSQL 8.3.3文書 17.4. カーネルリソースの管理 を参照して そこにあるパラメタを定義した。
/etc/sysctl.confへの記述
kern.ipc.shmall=32768 kern.ipc.shmmax=134217728 kern.ipc.semmap=256
/boot/loader.conf への記述
kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256
データベースインスタンスの新規作成
データベースインスタンスをここで作成する。
ユーザ pgsql でログイン
ＯＳのユーザ pgsql が作成されている。このユーザでログインする。
# su -l pgsql $ pwd /usr/local/pgsql $ ls -l total 0 $
環境変数 PGDATA を設定
.profile に定義しておくのが楽。
$ cat .profile PGDATA=/usr/local/pgsql/DATA; export PGDATA $ . .profile $
これは、データベースインスタンスのデータ格納場所 /usr/local/pgsql/DATA を指示するために使う。
initdbでデータベースインスタンスを作成（初期化）
initdbコマンドを実行する。
$ initdb
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.
The database cluster will be initialized with locale C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".
creating directory /usr/local/pgsql/DATA ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/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
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 /usr/local/pgsql/DATA
or
pg_ctl -D /usr/local/pgsql/DATA -l logfile start
$
これでインスタンス（のデータ）を作成できた。
DATA/pg_hba.conf の編集(jail環境下）
jail環境下では、 127.0.0.1 のループバックアドレスが使用できない。割り当てたIPアドレス(例では192.168.1.10）に書き換えする。
# IPv4 local connections: host all all 192.168.1.10/32 trust
このファイルで接続可能なホストを定義している。デフォルトでは外部ホストからの接続を一切許可しない。
DATA/postgresql.conf の編集(jail環境下）
listenアドレスを明示的に割り当てたIPアドレスと定義する。
listen_addresses = '192.168.1.10' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
このファイルにデータベースインスタンスの基本的設定が記述されている。
PostgreSQL起動
以下のコマンドで起動する。またはサーバ機を再起動してもいい。
/usr/local/etc/postgresql start
カーネルパラメタのうち、書き換え不可のパラメタに手を入れていればOSの再起動が必要なので忘れず。
データベースの設定
データベースのユーザ、及びデータベース自体を作成する。
データベースとユーザの作成
ＯＳの pgsql ユーザでログインする。 createdb コマンドでデータベースを、 createuser コマンドでユーザを作る。
$ createuser User1 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) y $ createdb -O User1 www $
データベースユーザ User1 を作成する。 次にデータベース www を作成し、オーナーにUser1を指定する。 インスタンスの配下にデータベースがある。イメージはSQLServerに近い感じ。
psqlコマンド
$ psql --help
This is psql 8.3.3, the PostgreSQL interactive terminal.
Usage:
psql [OPTIONS]... [DBNAME [USERNAME]]
General options:
-d DBNAME specify database name to connect to (default: "pgsql")
-c COMMAND run only single command (SQL or internal) and exit
-f FILENAME execute commands from file, then exit
-1 ("one") execute command file as a single transaction
-l list available databases, then exit
-v NAME=VALUE set psql variable NAME to VALUE
-X do not read startup file (~/.psqlrc)
--help show this help, then exit
--version output version information, then exit
Input and output options:
-a echo all input from script
-e echo commands sent to server
-E display queries that internal commands generate
-q run quietly (no messages, only query output)
-o FILENAME send query results to file (or |pipe)
-n disable enhanced command line editing (readline)
-s single-step mode (confirm each query)
-S single-line mode (end of line terminates SQL command)
-L FILENAME send session log to file
Output format options:
-A unaligned table output mode (-P format=unaligned)
-H HTML table output mode (-P format=html)
-t print rows only (-P tuples_only)
-T TEXT set HTML table tag attributes (width, border) (-P tableattr=)
-x turn on expanded table output (-P expanded)
-P VAR[=ARG] set printing option VAR to ARG (see \pset command)
-F STRING set field separator (default: "|") (-P fieldsep=)
-R STRING set record separator (default: newline) (-P recordsep=)
Connection options:
-h HOSTNAME database server host or socket directory (default: "local socket")
-p PORT database server port (default: "5432")
-U NAME database user name (default: "pgsql")
-W force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.
Report bugs to <pgsql-bugs@postgresql.org>.
$
psqlでデータベースへ接続してみる
データベース www へ接続する。
※コマンド実行時に データベース指定がない場合、OSユーザのアカウントが指定データベースとして使用される。
$ psql
psql: FATAL: database "pgsql" does not exist
$ psql www
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
www=# \d
No relations found.
www=# create table sample(a dec(2,1), b char(3));
CREATE TABLE
www=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------
public | sample | table | pgsql
(1 row)
www=# \d sample
Table "public.sample"
Column | Type | Modifiers
--------+--------------+-----------
a | numeric(2,1) |
b | character(3) |
www=# \q
$
psqlコマンドの注意（エンコーディング）
クライアント側のエンコーディング（文字コード種別）を明確にしない場合、表示が化けたりクエリが通らなかったりする。
$ psql -n www
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
www=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | 篏???弝??| table | www
(1 row)
www=# \encoding euc-jp
www=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | 住所データ | table | www
(1 row)
www=# \q
$
データベース側のエンコーディングがUTF-8(UNICODE)であるが、クライアント側のエンコーディングが EUC-JPを使っていたため文字化けがおきていた。 \encodingコマンドでクライアント側のコードを明示したため、正しくコード変換が行われた。
SQLに記述する漢字でも同様に化けることがあるので必ず明示する。 データベースのエンコーディングとクライアントのエンコーディングが一致していればこの問題は発生しない。
リンク
- 日本PostgreSQLユーザ会 ここからPostgreSQLのインストーラをダウンロードし、パッケージ中の ODBC（OLEDB）ドライバをインストールしました
- pgAdmin III リモートで管理するために導入。
- A5:SQL Mk-2 ER図をかいたり生成したり出来る。
documents/quick/quick-0001.txt · 最終更新: by 127.0.0.1