ばーろぐわにる

SIerからWEB系?インフラエンジニアにジョブチェンジした見習いの備忘録

RHELにPostgreSQLをインストールする

概要

Red Hat Enterprise Linux 7.4にインターネット経由でパッケージを落とさず、ローカル端末からパッケージをアップロードしてPostgreSQLを構築してみたメモ

パッケージ取得

"https://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/" ここから以下パッケージをローカル端末にダウンロード

    • postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64.rpm
    • postgresql94-9.4.15-1PGDG.rhel7.x86_64.rpm
    • postgresql94-server-9.4.15-1PGDG.rhel7.x86_64.rpm

ダウンロードしたらTeraTermでサーバに接続し、"ファイル" >> "SSH SCP..."を利用してダウンロードしたファイルをサーバにコピー

[root@ip-10-28-19-235 data]# ls -l /tmp | grep postgres
-rw-r--r-- 1 ec2-user ec2-user 1101744 Jan 10 02:32 postgresql94-9.4.15-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 ec2-user ec2-user 219092 Jan 10 02:32 postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 ec2-user ec2-user 4072168 Jan 10 02:32 postgresql94-server-9.4.15-1PGDG.rhel7.x86_64.rpm

インストール

[root@ip-10-28-19-235 ~]# cd /tmp
[root@ip-10-28-19-235 tmp]# yum localinstall postgresql94-*
Loaded plugins: amazon-id, rhui-lb, search-disabled-repos
Examining postgresql94-9.4.15-1PGDG.rhel7.x86_64.rpm: postgresql94-9.4.15-1PGDG.rhel7.x86_64
Marking postgresql94-9.4.15-1PGDG.rhel7.x86_64.rpm to be installed
Examining postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64.rpm: postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64
Marking postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64.rpm to be installed
Examining postgresql94-server-9.4.15-1PGDG.rhel7.x86_64.rpm: postgresql94-server-9.4.15-1PGDG.rhel7.x86_64
Marking postgresql94-server-9.4.15-1PGDG.rhel7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package postgresql94.x86_64 0:9.4.15-1PGDG.rhel7 will be installed
---> Package postgresql94-libs.x86_64 0:9.4.15-1PGDG.rhel7 will be installed
---> Package postgresql94-server.x86_64 0:9.4.15-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================================================
 Package                               Arch                     Version                                 Repository                                                        Size
===============================================================================================================================================================================
Installing:
 postgresql94                          x86_64                   9.4.15-1PGDG.rhel7                      /postgresql94-9.4.15-1PGDG.rhel7.x86_64                          5.4 M
 postgresql94-libs                     x86_64                   9.4.15-1PGDG.rhel7                      /postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64                     658 k
 postgresql94-server                   x86_64                   9.4.15-1PGDG.rhel7                      /postgresql94-server-9.4.15-1PGDG.rhel7.x86_64                    17 M

Transaction Summary
===============================================================================================================================================================================
Install  3 Packages

Total size: 23 M
Installed size: 23 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64                                                                                                                 1/3
  Installing : postgresql94-9.4.15-1PGDG.rhel7.x86_64                                                                                                                      2/3
  Installing : postgresql94-server-9.4.15-1PGDG.rhel7.x86_64                                                                                                               3/3
  Verifying  : postgresql94-libs-9.4.15-1PGDG.rhel7.x86_64                                                                                                                 1/3
  Verifying  : postgresql94-server-9.4.15-1PGDG.rhel7.x86_64                                                                                                               2/3
  Verifying  : postgresql94-9.4.15-1PGDG.rhel7.x86_64                                                                                                                      3/3

Installed:
  postgresql94.x86_64 0:9.4.15-1PGDG.rhel7              postgresql94-libs.x86_64 0:9.4.15-1PGDG.rhel7              postgresql94-server.x86_64 0:9.4.15-1PGDG.rhel7

Complete!

自動でpostgresユーザが追加される

[root@ip-10-28-19-235 tmp]# grep postgres /etc/passwd
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

DB初期化

[root@ip-10-28-19-235 tmp]# /usr/pgsql-9.4/bin/postgresql94-setup initdb
Initializing database ... OK

PATHを通す

[root@ip-10-28-19-235 ~]# vim /etc/profile
# 最終行に以下を追加
export PATH="$PATH:/usr/pgsql-9.4/bin/"

再ログインして反映させる

起動

最初ネットの記事をみながらpg_ctlでいろいろ思考錯誤してみたが、普通にsystemctlで起動できた

[root@ip-10-28-19-235 ~]# systemctl start postgresql-9.4
[root@ip-10-28-19-235 ~]# systemctl status postgresql-9.4
● postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2018-01-10 06:45:28 UTC; 28s ago
Docs: https://www.postgresql.org/docs/9.4/static/
Process: 2764 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
Process: 2759 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 2769 (postgres)
CGroup: /system.slice/postgresql-9.4.service
tq2769 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
tq2770 postgres: logger process
tq2772 postgres: checkpointer process
tq2773 postgres: writer process
tq2774 postgres: wal writer process
tq2775 postgres: autovacuum launcher process
mq2776 postgres: stats collector process
 
Jan 10 06:45:27 ip-10-28-19-235.ap-northeast-1.compute.internal systemd[1]: Starting PostgreSQL 9.4 database server...
Jan 10 06:45:27 ip-10-28-19-235.ap-northeast-1.compute.internal pg_ctl[2764]: < 2018-01-10 06:45:27.447 UTC >LOG: redirecting log output to logging collector process
Jan 10 06:45:27 ip-10-28-19-235.ap-northeast-1.compute.internal pg_ctl[2764]: < 2018-01-10 06:45:27.447 UTC >HINT: Future log output will appear in directory "pg_log".
Jan 10 06:45:28 ip-10-28-19-235.ap-northeast-1.compute.internal systemd[1]: Started PostgreSQL 9.4 database server.

テストDB作成

[root@ip-10-28-19-235 ~]# sudo -u postgres -i createdb --owner postgres testdb01

ログイン

怒られる

[root@ip-10-28-19-235 ~]# psql testdb01 -U postgres
psql: FATAL: Peer authentication failed for user "postgres"

peer認証がonになっており、同じOSユーザからでないとログインできない。

[root@ip-10-28-19-235 ~]# vim /var/lib/pgsql/9.4/data/pg_hba.conf
#local all all peer
local all all trust

initdbの際にパスワードを設定していなかったので、パスワードなしでログインできるようにpg_hba.confを修正 編集できたらpgsqlを再起動してデフォルトユーザ'postgres'でログイン

[root@ip-10-28-19-235 ~]# systemctl restart postgresql-9.4
[root@ip-10-28-19-235 ~]# psql -U postgres
psql (9.4.15)
Type "help" for help.
 
postgres=#

ログインできたのでパスワードを変更

postgres=# \password
Enter new password: 'パスワードを入力'
Enter it again: 'パスワードを入力'
postgres=# \q

パスワードが変更できたのでpg_hba.confを修正し、パスワード応答でログインできるように修正

[root@ip-10-28-19-235 ~]# vim /var/lib/pgsql/9.4/data/pg_hba.conf
# local all all trust
local all all md5
[root@ip-10-28-19-235 ~]# systemctl restart postgresql-9.4
[root@ip-10-28-19-235 ~]# psql -U postgres
Password for user postgres:
psql (9.4.15)
Type "help" for help.
 
postgres=#

テストテーブル作成

postgres=# \connect testdb01
You are now connected to database "testdb01" as user "postgres".
testdb01=# DROP TABLE t1;
DROP TABLE
testdb01=# CREATE TABLE t1 (id int, name varchar(80));
CREATE TABLE
testdb01=# INSERT INTO t1 (id, name) VALUES (1, 'Banana');
INSERT 0 1
testdb01=# INSERT INTO t1 (id, name) VALUES (2, 'Danana');
INSERT 0 1
testdb01=# INSERT INTO t1 (id, name) VALUES (3, 'Ranana');
INSERT 0 1
testdb01=# SELECT * from t1;
id | name
----+--------
1 | Banana
2 | Danana
3 | Ranana
(3 rows)

参考

https://qiita.com/mm36/items/1801573a478cb2865242 https://qa.atmarkit.co.jp/q/2604