「CentOSにPostgreSQLをインストールする」

tail -5 /etc/passwd 現在の

[root@ik1-331-25935 sato]# tail -5 /etc/passwd
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
ntp:x:38:38::/etc/ntp:/sbin/nologin
chrony:x:995:993::/var/lib/chrony:/sbin/nologin
tcpdump:x:72:72::/:/sbin/nologin
sato:x:1000:1000::/home/sato:/bin/bash


yum -y install postgresql-server

[root@ik1-331-25935 sato]# yum -y install postgresql-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: ftp.iij.ad.jp
 * epel: mirror.dmmlabs.jp
 * extras: ftp.iij.ad.jp
 * updates: ftp.iij.ad.jp
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.x86_64 0:9.2.23-3.el7_4 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.23-3.el7_4 for package: postgresql-server-9.2.23-3.el7_4.x86_64
--> Processing Dependency: postgresql(x86-64) = 9.2.23-3.el7_4 for package: postgresql-server-9.2.23-3.el7_4.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-9.2.23-3.el7_4.x86_64
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.23-3.el7_4 will be installed
---> Package postgresql-libs.x86_64 0:9.2.23-3.el7_4 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                 Arch         Version               Repository     Size
================================================================================
Installing:
 postgresql-server       x86_64       9.2.23-3.el7_4        updates       3.8 M
Installing for dependencies:
 postgresql              x86_64       9.2.23-3.el7_4        updates       3.0 M
 postgresql-libs         x86_64       9.2.23-3.el7_4        updates       234 k

Transaction Summary
================================================================================
Install  1 Package (+2 Dependent packages)

Total download size: 7.0 M
Installed size: 33 M
Downloading packages:
(1/3): postgresql-libs-9.2.23-3.el7_4.x86_64.rpm           | 234 kB   00:01
(2/3): postgresql-9.2.23-3.el7_4.x86_64.rpm                | 3.0 MB   00:02
(3/3): postgresql-server-9.2.23-3.el7_4.x86_64.rpm         | 3.8 MB   00:01
--------------------------------------------------------------------------------
Total                                              2.2 MB/s | 7.0 MB  00:03
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql-libs-9.2.23-3.el7_4.x86_64                        1/3
  Installing : postgresql-9.2.23-3.el7_4.x86_64                             2/3
  Installing : postgresql-server-9.2.23-3.el7_4.x86_64                      3/3
  Verifying  : postgresql-9.2.23-3.el7_4.x86_64                             1/3
  Verifying  : postgresql-server-9.2.23-3.el7_4.x86_64                      2/3
  Verifying  : postgresql-libs-9.2.23-3.el7_4.x86_64                        3/3

Installed:
  postgresql-server.x86_64 0:9.2.23-3.el7_4

Dependency Installed:
  postgresql.x86_64 0:9.2.23-3.el7_4   postgresql-libs.x86_64 0:9.2.23-3.el7_4

Complete!

[root@ik1-331-25935 sato]# tail -5 /etc/passwd
ntp:x:38:38::/etc/ntp:/sbin/nologin
chrony:x:995:993::/var/lib/chrony:/sbin/nologin
tcpdump:x:72:72::/:/sbin/nologin
sato:x:1000:1000::/home/sato:/bin/bash
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

[root@ik1-331-25935 sato]# su postgres
bash-4.2$
bash-4.2$ whoami
postgres

bash-4.2$ cd /var/lib/pgsql/data/

postgresql-setup initdb　初期化する

bash-4.2$ cp postgresql.conf postgresql.conf.org
postgresql.confを編集するために、バックアップファイルを作る。

bash-4.2$ echo "listen_addresses = '*'" >> /var/lib/pgsql/data/postgresql.conf
postgresql.confの最終行に追記する。

cp pg_hba.conf pg_hba.conf.org
```

bash-4.2$ echo "# PostgreSQL Client Authentication Configuration File" >  ./pg_hba.conf
bash-4.2$ echo "# ===================================================" >> ./pg_hba.conf
bash-4.2$ echo "local all all              trust"                      >> ./pg_hba.conf
bash-4.2$ echo "host  all all 127.0.0.1/32 trust"                      >> ./pg_hba.conf
bash-4.2$ echo "host  all all ::1/128      trust"                      >> ./pg_hba.conf

exit
service postgresql restart　これでPostgreSQLの起動が完了

[root@ik1-331-25935 sato]# service postgresql restart
Redirecting to /bin/systemctl restart postgresql.service


psql -U postgres　PostgreSQLにログイン
```
[root@ik1-331-25935 sato]# psql -U postgres
psql: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
[root@ik1-331-25935 sato]# su postgres
bash-4.2$ cd /var/lib/pgsql/data/
bash-4.2$ ls
PG_VERSION pg_hba.conf pg_notify pg_subtrans postgresql.conf
base pg_ident.conf pg_serial pg_tblspc postgresql.conf.org
global pg_log pg_snapshots pg_twophase postmaster.opts
pg_clog pg_multixact pg_stat_tmp pg_xlog postmaster.pid
bash-4.2$ cat pg_hba.conf

PostgreSQL Client Authentication Configuration File

===================================================

host all all ::1/128 trust
bash-4.2$ vi pg_hba.conf
bash-4.2$ exit
exit
[root@ik1-331-25935 sato]# psql -U postgres
psql: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
[root@ik1-331-25935 sato]# service postgresql restart
Redirecting to /bin/systemctl restart postgresql.service
[root@ik1-331-25935 sato]# psql -U postgres
psql (9.2.23)
Type "help" for help.

postgres=#
```

create database sales;　新規にデータベースを作る
```
postgres=# create database sales;
CREATE DATABASE

create table userlist(
    id int,
    name varchar(20),
    address varchar(30),
    age int
    );

postgres=# create table userlist(
postgres(# id int,
postgres(# name varchar(20),
postgres(# address varchar(30),
postgres(# age int
postgres(# );
CREATE TABLE

select * from userlist;

```

insert into userlist values(1, 'sato','tokyo',18);
insert into userlist values(2,'nao','kyoto',22);
insert into userlist values(3,'john','newyork',34);
insert into userlist values(4,'lisa','paris',24);
insert into userlist values(5,'kaoru','nagoya',15);
insert into userlist values(6,'ryo','fuhuoka',40);

\q