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

[root@ik1-321-20522 home]# 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

# yum -y install postgresql-server
これすると自動にユーザーが追加

root@ik1-321-20522 home]# 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-321-20522 home]# su postgres

bash-4.2$ whoami
postgres

bash-4.2$ cd /var/lib/pgsql/data/
bash-4.2$ ls -l
total 0

bash-4.2$ postgresql-setup initdb
Initializing database ... OK

bash-4.2$ ls -l
total 40
-rw------- 1 postgres postgres     4 Apr 12 13:19 PG_VERSION
drwx------ 5 postgres postgres    38 Apr 12 13:19 base
drwx------ 2 postgres postgres  4096 Apr 12 13:19 global
drwx------ 2 postgres postgres    17 Apr 12 13:19 pg_clog
-rw------- 1 postgres postgres  4232 Apr 12 13:19 pg_hba.conf
-rw------- 1 postgres postgres  1636 Apr 12 13:19 pg_ident.conf
drwx------ 2 postgres postgres     6 Apr 12 13:19 pg_log
drwx------ 4 postgres postgres    34 Apr 12 13:19 pg_multixact
drwx------ 2 postgres postgres    17 Apr 12 13:19 pg_notify
drwx------ 2 postgres postgres     6 Apr 12 13:19 pg_serial
drwx------ 2 postgres postgres     6 Apr 12 13:19 pg_snapshots
drwx------ 2 postgres postgres     6 Apr 12 13:19 pg_stat_tmp
drwx------ 2 postgres postgres    17 Apr 12 13:19 pg_subtrans
drwx------ 2 postgres postgres     6 Apr 12 13:19 pg_tblspc
drwx------ 2 postgres postgres     6 Apr 12 13:19 pg_twophase
drwx------ 3 postgres postgres    58 Apr 12 13:19 pg_xlog
-rw------- 1 postgres postgres 19809 Apr 12 13:19 postgresql.conf

bash-4.2$ cp postgresql.conf postgresql.conf.org
bash-4.2$ echo "listen_addresses = '*'" >> /var/lib/pgsql/data/postgresql.conf

bash-4.2$ ls -l
total 28
-rw------- 1 postgres postgres    4 Apr 12 13:19 PG_VERSION
drwx------ 5 postgres postgres   38 Apr 12 13:19 base
drwx------ 2 postgres postgres 4096 Apr 12 13:19 global
drwx------ 2 postgres postgres   17 Apr 12 13:19 pg_clog
-rw------- 1 postgres postgres 4232 Apr 12 13:19 pg_hba.conf
-rw------- 1 postgres postgres 1636 Apr 12 13:19 pg_ident.conf
drwx------ 2 postgres postgres    6 Apr 12 13:19 pg_log
drwx------ 4 postgres postgres   34 Apr 12 13:19 pg_multixact
drwx------ 2 postgres postgres   17 Apr 12 13:19 pg_notify
drwx------ 2 postgres postgres    6 Apr 12 13:19 pg_serial
drwx------ 2 postgres postgres    6 Apr 12 13:19 pg_snapshots
drwx------ 2 postgres postgres    6 Apr 12 13:19 pg_stat_tmp
drwx------ 2 postgres postgres   17 Apr 12 13:19 pg_subtrans
drwx------ 2 postgres postgres    6 Apr 12 13:19 pg_tblspc
drwx------ 2 postgres postgres    6 Apr 12 13:19 pg_twophase
drwx------ 3 postgres postgres   58 Apr 12 13:19 pg_xlog
-rw-r--r-- 1 postgres postgres   46 Apr 12 13:23 postgresql.conf
-rw-r--r-- 1 postgres postgres   23 Apr 12 13:23 postgresql.conf.org

bash-4.2$　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

bash-4.2$　$ exit

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

PostgreSQLにログイン
[root@ik1-321-20522 home]# psql -U postgres
psql (9.2.23)
Type "help" for help.

postgres=#

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(3 rows)

postgres-# \q

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

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 postgres  | postgres | SQL_ASCII | C       | C     |
 sales     | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(4 rows)

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

postgres=# select * from userlist ;
 id | name | address | age
----+------+---------+-----
(0 rows)

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

postgres=# select * from userlist ;
 id | name  | address | age
----+-------+---------+-----
  1 | sato  | tokyo   |  18
  2 | nao   | kyoto   |  22
  3 | john  | newyork |  34
  4 | lisa  | paris   |  24
  5 | kaoru | nagoya  |  15
  6 | ryo   | fuhuoka |  40
(6 rows)

postgres=# select name,address from userlist ;
 name  | address
-------+---------
 sato  | tokyo
 nao   | kyoto
 john  | newyork
 lisa  | paris
 kaoru | nagoya
 ryo   | fuhuoka
(6 rows)

postgres=# select * from userlist where age >= 20 ;
 id | name | address | age
----+------+---------+-----
  2 | nao  | kyoto   |  22
  3 | john | newyork |  34
  4 | lisa | paris   |  24
  6 | ryo  | fuhuoka |  40
(4 rows)

postgres=# select * from userlist where age between 20 and 25 ;
 id | name | address | age
----+------+---------+-----
  2 | nao  | kyoto   |  22
  4 | lisa | paris   |  24
(2 rows)

postgres=# select avg(age) from userlist ;
         avg
---------------------
 25.5000000000000000
(1 row)

postgres=# select sum(age) from userlist ;
 sum
-----
 153
(1 row)

postgres=# select avg(age) from userlist where age >= 20 ;
         avg
---------------------
 30.0000000000000000
(1 row)

postgres=# select sum(age) from userlist where age >=20 ;
 sum
-----
 120
(1 row)

postgres=# select * from userlist order by age ;
 id | name  | address | age
----+-------+---------+-----
  5 | kaoru | nagoya  |  15
  5 | kaoru | nagoya  |  15
  1 | sato  | tokyo   |  18
  2 | nao   | kyoto   |  22
  4 | lisa  | paris   |  24
  3 | john  | newyork |  34
  6 | ryo   | fuhuoka |  40
(7 rows)

postgres=# select * from userlist order by age desc ;
 id | name  | address | age
----+-------+---------+-----
  6 | ryo   | fuhuoka |  40
  3 | john  | newyork |  34
  4 | lisa  | paris   |  24
  2 | nao   | kyoto   |  22
  1 | sato  | tokyo   |  18
  5 | kaoru | nagoya  |  15
  5 | kaoru | nagoya  |  15
(7 rows)

postgres=# drop table userlist;
DROP TABLE

postgres=# drop database sales;drop database sales;
DROP DATABASE

[root@ik1-321-20522 etc]# alias lld='ls l|grep ^d'