ユーザ用ツール

サイト用ツール


serverapps:nextcloud:02.postgresql_server

文書の過去の版を表示しています。


02.PostgreSQLサーバへのインストール

PostgreSQL

PostgreSQLは別マシンへ

# pkg install postgresql13-client postgresql13-server
New packages to be INSTALLED:
      icu: 73.2,1
      libedit: 3.1.20221030,1
      libffi: 3.4.4
      liblz4: 1.9.4,1
      llvm15: 15.0.7_5
      lua53: 5.3.6
      mpdecimal: 2.5.1
      perl5: 5.32.1_3
      postgresql13-client: 13.11
      postgresql13-server: 13.11
      python39: 3.9.17
      readline: 8.2.1
      zstd: 1.5.5

インストールの最後に表示されるPostgreSQL設定について

  • バキューム:普通Autoなのでいらんけど、独自にやりたいなら以下参照
    • /usr/local/etc/periodic/daily/502.pgsql
  • バックアップ:以下が役に立つかも
    • /usr/local/etc/periodic/daily/502.pgsql

設定

PostgreSQL

rc.conf

/etc/rc.confまたは/etc/rc.conf.localに記述

postgresql_enable="YES"
postgresql_data="/usr/local/pgsql/DATA"

data領域はzfsで別にとるので、disk追加してpool作成

# zpool create zdata /dev/adaxx
# zfs create -o mountpoint=/usr/local/pgsql zdata/pgsql
# mkdir /usr/local/pgsql/DATA
# chown postgres:postgres /usr/local/pgsql/DATA

初期化

# 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 locales
  COLLATE:  C
  CTYPE:    C.UTF-8
  MESSAGES: C.UTF-8
  MONETARY: C.UTF-8
  NUMERIC:  C.UTF-8
  TIME:     C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/DATA ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Japan
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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 /usr/local/pgsql/DATA -l logfile start

pg_hba.conf の編集

外部からの接続を /usr/local/pgsql/data/pg_hba.conf に設定します1)

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
host    all             all             192.168.0.0/24          md5

postgresql.confのlisten_addresses も変更

listen_addresses = '*'

起動

PostgrSQLを起動

# service postgresql start

NextCloud用設定

# psql -h localhost -U postgres
psql (13.11)
Type "help" for help.

postgres=# CREATE ROLE ncadm WITH LOGIN PASSWORD 'ncadmpassword';
CREATE ROLE
postgres=# CREATE DATABASE nc WITH OWNER ncadm TEMPLATE template0 
postgres=# ENCODING UTF8 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8';
CREATE DATABASE
1)
標準では自分自身からの接続のみを許可しています
serverapps/nextcloud/02.postgresql_server.1691378317.txt.gz · 最終更新: 2023/08/07 03:18 by hayashi