2017/03/05
FreeBSDのpkgコマンドでお手軽にPostgreSQLをインストール、セットアップする。
portsでソースからコンパイルするより速いし、ちゃちゃっと準備するにはおすすめ。
今回は ezjail でJailをこしらえてその中にインストールします。Jailではなくても可能だけどできれば分離することをお勧めします。
Jailにインストールする場合、事前に設定しておく内容。
既にJailインスタンス pg が作成され起動しているものとします。
Jailの通信許可と共有メモリ利用許可を出します。
Jailにインストールする場合はJailにログインしておきます。
pkgコマンドの searchサブコマンドで postgres を検索します。
root@pg:~ # pkg search postgres exim-postgresql-4.88 High performance MTA for Unix systems on the Internet fpc-postgres-3.0.0_1 Free Pascal interface to PostGreSQL libgda4-postgresql-4.2.12_2 Provides PostgreSQL access for the libgda4 library libgda5-postgresql-5.2.4 Provides postgresql access for the libgda5 library luasql-postgres-2.3.3 Lua interface to PostgreSQL mysqlphp2postgres-0.95 Convert MySQL calls in a PHP page into PostgreSQL calls nagios-check_postgres-2.22.0 Monitor various attributes of your PostgreSQL database nagios-check_postgres_replication-2010.12.16 Nagios plugin to check the lag between a postgresql replica and master opensmtpd-extras-table-postgresql-201606230001 PostgreSQL table support for OpenSMTPD p5-PostgreSQL-PLPerl-Call-1.006_1 Simple interface for calling SQL functions from PostgreSQL PL/Perl p5-PostgreSQL-PLPerl-Trace-1.001_1 Simple way to trace execution of Perl statements in PL/Perl p5-Test-postgresql-0.09_1 Perl extension of postgresql runner for tests pgtcl-postgresql92-2.0.0_1 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql93-2.0.0_1 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql94-2.0.0_1 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql95-2.0.0_1 TCL extension for accessing a PostgreSQL server (PGTCL-NG) pgtcl-postgresql96-2.0.0_1 TCL extension for accessing a PostgreSQL server (PGTCL-NG) postgresql-jdbc-9.2.1004 The Java JDBC implementation for PostgreSQL postgresql-libpgeasy-3.0.4_1 Easy-to-use C interface to PostgreSQL postgresql-libpqxx-4.0.1_1 New C++ interface for PostgreSQL postgresql-libpqxx3-3.1.1_1 New C++ interface for PostgreSQL postgresql-odbc-09.05.0400 PostgreSQL ODBC driver postgresql-plproxy-2.5_1 PL/Proxy - database partitioning system postgresql-relay-1.3.2_1 Multiplex multiple PostgreSQL databases to one relay postgresql-repmgr-3.1.5 PostgreSQL replication manager postgresql-repmgr2-2.0.3_1 PostgreSQL replication manager postgresql92-client-9.2.19_1 PostgreSQL database (client) postgresql92-contrib-9.2.19_1 The contrib utilities from the PostgreSQL distribution postgresql92-docs-9.2.19_1 The PostgreSQL documentation set postgresql92-plperl-9.2.19_1 Write SQL functions for PostgreSQL using Perl5 postgresql92-plpython-9.2.19_1 Module for using Python to write SQL functions postgresql92-pltcl-9.2.19_1 Module for using Tcl to write SQL functions postgresql92-server-9.2.19_1 PostgreSQL is the most advanced open-source database available anywhere postgresql93-client-9.3.15_1 PostgreSQL database (client) postgresql93-contrib-9.3.15_1 The contrib utilities from the PostgreSQL distribution postgresql93-docs-9.3.15_1 The PostgreSQL documentation set postgresql93-plperl-9.3.15_1 Write SQL functions for PostgreSQL using Perl5 postgresql93-plpython-9.3.15_1 Module for using Python to write SQL functions postgresql93-pltcl-9.3.15_1 Module for using Tcl to write SQL functions postgresql93-plv8js-1.4.8 PL/v8js procedural language for PostgreSQL database postgresql93-server-9.3.15_1 PostgreSQL is the most advanced open-source database available anywhere postgresql94-client-9.4.10_1 PostgreSQL database (client) postgresql94-contrib-9.4.10_1 The contrib utilities from the PostgreSQL distribution postgresql94-docs-9.4.10_1 The PostgreSQL documentation set postgresql94-plperl-9.4.10_1 Write SQL functions for PostgreSQL using Perl5 postgresql94-plpython-9.4.10_1 Module for using Python to write SQL functions postgresql94-pltcl-9.4.10_1 Module for using Tcl to write SQL functions postgresql94-server-9.4.10_1 PostgreSQL is the most advanced open-source database available anywhere postgresql95-client-9.5.5_1 PostgreSQL database (client) postgresql95-contrib-9.5.5 The contrib utilities from the PostgreSQL distribution postgresql95-docs-9.5.5_1 The PostgreSQL documentation set postgresql95-plperl-9.5.5_1 Write SQL functions for PostgreSQL using Perl5 postgresql95-plpython-9.5.5_1 Module for using Python to write SQL functions postgresql95-pltcl-9.5.5_1 Module for using Tcl to write SQL functions postgresql95-server-9.5.5_1 PostgreSQL is the most advanced open-source database available anywhere postgresql96-client-9.6.1 PostgreSQL database (client) postgresql96-contrib-9.6.1 The contrib utilities from the PostgreSQL distribution postgresql96-docs-9.6.1_1 The PostgreSQL documentation set postgresql96-plperl-9.6.1_1 Write SQL functions for PostgreSQL using Perl5 postgresql96-plpython-9.6.1_1 Module for using Python to write SQL functions postgresql96-pltcl-9.6.1_1 Module for using Tcl to write SQL functions postgresql96-server-9.6.1_1 PostgreSQL is the most advanced open-source database available anywhere proftpd-mod_sql_postgres-1.3.5b PostgreSQL ProFTPD module py35-postgresql-1.1.0_3 Python 3 compatible PostgreSQL database driver and tools rubygem-dm-postgres-adapter-1.2.0 PostgreSQL Adapter for DataMapper rubygem-do_postgres-0.10.13 Ruby DataObjects driver for PostgreSQL rubygem-postgres_ext-3.0.0 PostgreSQL data types extension for ActiveRecord root@pg:root@pg:~ #
古いバージョンを使う理由がないならこのリストで最新の postgresql96-server-9.6.1_1 をインストールしましょう。 依存関係のあるパッケージも同時にインストールされます。
root@pg:~ # pkg install postgresql96-server-9.6.1_1
Updating FreeBSD repository catalogue...
FreeBSD repository is up-to-date.
All repositories are up-to-date.
The following 8 package(s) will be affected (of 0 checked):
New packages to be INSTALLED:
postgresql96-server: 9.6.1_1
libxml2: 2.9.4
icu: 58.2,1
gettext-runtime: 0.19.8.1_1
indexinfo: 0.2.6
postgresql96-client: 9.6.1
perl5: 5.24.1.r4_1
readline: 6.3.8
Number of packages to be installed: 8
The process will require 130 MiB more space.
30 MiB to be downloaded.
Proceed with this action? [y/N]: y
[pg] Fetching postgresql96-server-9.6.1_1.txz: 100% 4 MiB 1.3MB/s 00:03
[pg] Fetching libxml2-2.9.4.txz: 100% 802 KiB 821.1kB/s 00:01
[pg] Fetching icu-58.2,1.txz: 100% 9 MiB 2.4MB/s 00:04
[pg] Fetching gettext-runtime-0.19.8.1_1.txz: 100% 148 KiB 151.1kB/s 00:01
[pg] Fetching indexinfo-0.2.6.txz: 100% 5 KiB 5.3kB/s 00:01
[pg] Fetching postgresql96-client-9.6.1.txz: 100% 2 MiB 1.2MB/s 00:02
[pg] Fetching perl5-5.24.1.r4_1.txz: 100% 13 MiB 2.3MB/s 00:06
[pg] Fetching readline-6.3.8.txz: 100% 318 KiB 325.7kB/s 00:01
Checking integrity... done (0 conflicting)
[pg] [1/8] Installing indexinfo-0.2.6...
[pg] [1/8] Extracting indexinfo-0.2.6: 100%
[pg] [2/8] Installing gettext-runtime-0.19.8.1_1...
[pg] [2/8] Extracting gettext-runtime-0.19.8.1_1: 100%
[pg] [3/8] Installing perl5-5.24.1.r4_1...
[pg] [3/8] Extracting perl5-5.24.1.r4_1: 100%
[pg] [4/8] Installing readline-6.3.8...
[pg] [4/8] Extracting readline-6.3.8: 100%
[pg] [5/8] Installing libxml2-2.9.4...
[pg] [5/8] Extracting libxml2-2.9.4: 100%
[pg] [6/8] Installing icu-58.2,1...
[pg] [6/8] Extracting icu-58.2,1: 100%
[pg] [7/8] Installing postgresql96-client-9.6.1...
[pg] [7/8] Extracting postgresql96-client-9.6.1: 100%
[pg] [8/8] Installing postgresql96-server-9.6.1_1...
===> Creating groups.
Creating group 'postgres' with gid '770'.
===> Creating users
Creating user 'postgres' with uid '770'.
=========== BACKUP YOUR DATA! =============
As always, backup your data before
upgrading. If the upgrade leads to a higher
minor revision (e.g. 8.3.x -> 8.4), a dump
and restore of all databases is
required. This is *NOT* done by the port!
===========================================
[pg] [8/8] Extracting postgresql96-server-9.6.1_1: 100%
Message from perl5-5.24.1.r4_1:
The /usr/bin/perl symlink has been removed starting with Perl 5.20.
For shebangs, you should either use:
#!/usr/local/bin/perl
or
#!/usr/bin/env perl
The first one will only work if you have a /usr/local/bin/perl,
the second will work as long as perl is in PATH.
Message from postgresql96-client-9.6.1:
The PostgreSQL port has a collection of "side orders":
postgresql-docs
For all of the html documentation
p5-Pg
A perl5 API for client access to PostgreSQL databases.
postgresql-tcltk
If you want tcl/tk client support.
postgresql-jdbc
For Java JDBC support.
postgresql-odbc
For client access from unix applications using ODBC as access
method. Not needed to access unix PostgreSQL servers from Win32
using ODBC. See below.
ruby-postgres, py-PyGreSQL
For client access to PostgreSQL databases using the ruby & python
languages.
postgresql-plperl, postgresql-pltcl & postgresql-plruby
For using perl5, tcl & ruby as procedural languages.
postgresql-contrib
Lots of contributed utilities, postgresql functions and
datatypes. There you find pg_standby, pgcrypto and many other cool
things.
etc...
Message from postgresql96-server-9.6.1_1:
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 perform vacuum on all
databases nightly. Per default, it performs `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~pgsql/data/postgresql.conf.
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.d/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
root@pg:~ #
ユーザ postgres が作成されています。PostgreSQLの設定ファイルの修正等はこのユーザにログインして実施します。
指示に従い /etc/rc.conf に追記します。
postgresql_enable="YES"
PostgreSQLのインストール後の説明は一部古い部分があるので適宜置き換えして実行してください。もちろん、説明のまま実行しても問題はありませんが。
以下は説明中にある
/usr/local/etc/rc.d/postgresql initdb
に対応するものです。
root@pg:~ # service postgresql initdb
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".
Data page checksums are disabled.
creating directory /var/db/postgres/data96 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/bin/pg_ctl -D /var/db/postgres/data96 -l logfile start
root@pg:~ #
/var/db/postgres/data96 にデータベースが作成されたようです。
なお、共有メモリの利用許可がないとこんなエラーが出ます。
root@pg:/usr/local/etc/rc.d # service postgresql initdb 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". Data page checksums are disabled. creating directory /var/db/postgres/data96 ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 400kB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... FATAL: could not create shared memory segment: Function not implemented DETAIL: Failed system call was shmget(key=1, size=48, 03600). child process exited with exit code 1 initdb: removing data directory "/var/db/postgres/data96" root@pg:/usr/local/etc/rc.d #
デフォルトでは外部からの接続ができないので pg_hba.conf を編集して接続許可を出します。
root@pg:/var/db/postgres/data96 # ll total 120 -rw------- 1 postgres postgres 4 Mar 4 19:12 PG_VERSION drwx------ 5 postgres postgres 512 Mar 4 19:12 base/ drwx------ 2 postgres postgres 1024 Mar 4 19:31 global/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_clog/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_commit_ts/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_dynshmem/ -rw------- 1 postgres postgres 4608 Mar 4 19:29 pg_hba.conf -rw------- 1 postgres postgres 1636 Mar 4 19:12 pg_ident.conf drwx------ 4 postgres postgres 512 Mar 4 19:12 pg_logical/ drwx------ 4 postgres postgres 512 Mar 4 19:12 pg_multixact/ drwx------ 2 postgres postgres 512 Mar 4 19:31 pg_notify/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_replslot/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_serial/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_snapshots/ drwx------ 2 postgres postgres 512 Mar 4 19:31 pg_stat/ drwx------ 2 postgres postgres 512 Mar 5 07:11 pg_stat_tmp/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_subtrans/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_tblspc/ drwx------ 2 postgres postgres 512 Mar 4 19:12 pg_twophase/ drwx------ 3 postgres postgres 512 Mar 4 19:12 pg_xlog/ -rw------- 1 postgres postgres 88 Mar 4 19:12 postgresql.auto.conf -rw------- 1 postgres postgres 22330 Mar 4 19:12 postgresql.conf -rw------- 1 postgres postgres 55 Mar 4 19:31 postmaster.opts -rw------- 1 postgres postgres 81 Mar 4 19:31 postmaster.pid root@pg:/var/db/postgres/data96 # su postgres $ vi pg_hba.conf
以下の2行を追加してください。
host all all 192.168.1.30/32 trust host all all 192.168.1.0/24 trust
最初の行はJailに割り当てたIPアドレスそのもの。
次の行はPostgreSQLに接続可能なIPアドレス範囲です。各環境に合わせて変更してください。
以下は説明中にある
/usr/local/etc/rc.d/postgresql start
に対応するものです。
$ ^D root@pg:~ # service postgresql start LOG: could not create IPv6 socket: Protocol not supported LOG: ending log output to stderr HINT: Future log output will go to log destination "syslog". root@pg:~ #
接続に使うユーザ kamo と、データベース kamonohashi を作成してみます。
root@pg:~ # su postgres
$ createuser --help
createuser creates a new PostgreSQL role.
Usage:
createuser [OPTION]... [ROLENAME]
Options:
-c, --connection-limit=N connection limit for role (default: no limit)
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases (default)
-e, --echo show the commands being sent to the server
-E, --encrypted encrypt stored password
-g, --role=ROLE new role will be a member of this role
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-l, --login role can login (default)
-L, --no-login role cannot login
-N, --unencrypted do not encrypt stored password
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles (default)
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser (default)
-V, --version output version information, then exit
--interactive prompt for missing role name and attributes rather
than using defaults
--replication role can initiate replication
--no-replication role cannot initiate replication
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to create)
-w, --no-password never prompt for password
-W, --password force password prompt
Report bugs to <pgsql-bugs@postgresql.org>.
$ createuser kamo
$ createdb --help
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-e, --echo show the commands being sent to the server
-E, --encoding=ENCODING encoding for the database
-l, --locale=LOCALE locale settings for the database
--lc-collate=LOCALE LC_COLLATE setting for the database
--lc-ctype=LOCALE LC_CTYPE setting for the database
-O, --owner=OWNER database user to own the new database
-T, --template=TEMPLATE template database to copy
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
By default, a database with the same name as the current user is created.
Report bugs to <pgsql-bugs@postgresql.org>.
$ createdb -E utf-8 -O kamo kamonohashi
$
データベースのユーザ kamo にはパスワードを設定していません。
必要ならpsqlコマンドで接続して \password コマンドで設定するか createuser コマンドに -P オプションを付けてユーザ作成時にパスワードも指定してください。
データベース kamonohashi のオーナーを kamo にしてあります。
psqlコマンドでデータベース kamonohashi へ接続してみます。
$ psql -n kamonohashi kamo
psql (9.6.1)
Type "help" for help.
kamonohashi=> \d
No relations found.
kamonohashi=> create table demo(
kamonohashi(> id numeric(10,1),
kamonohashi(> str varchar(10)
kamonohashi(> );
CREATE TABLE
kamonohashi=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | demo | table | kamo
(1 row)
kamonohashi=> insert into demo values(1,'ABCD');
INSERT 0 1
kamonohashi=> select * from demo;
id | str
-----+------
1.0 | ABCD
(1 row)
kamonohashi=> insert into demo values(1,'あいうえ');
ERROR: invalid byte sequence for encoding "UTF8": 0xa4
kamonohashi=> \encoding euc-jp
kamonohashi=> insert into demo values(1,'あいうえ');
INSERT 0 1
kamonohashi=> select * from demo;
id | str
-----+----------
1.0 | ABCD
1.0 | あいうえ
(2 rows)
kamonohashi=>
端末がeuc-jpなので、\encoding コマンドでエンコーディングを指定しています。