在CentOS上安装PostgreSQL
tail -5 /etc/passwd 現在的是：
[root@ik1-331-25935 sato]# tail -5 /etc/passwd
sshd:x:74:74:特权分离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 安装 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$ 切换到 /var/lib/pgsql/data/ 目录。
进行 PostgreSQL 初始化
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の最終行に追記する。
复制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
退出
重新启动PostgreSQL服务，此时PostgreSQL的启动已完成。
[root@ik1-331-25935 sato]# 重新启动PostgreSQL服务
重定向到/bin/systemctl重新启动postgresql.service
[root@ik1-331-25935 sato]# psql -U postgres
psql: FATAL: 对于主机”[local]”，用户”postgres”，数据库”postgres”，未找到pg_hba.conf条目，SSL关闭
[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
[root@ik1-331-25935 sato]# psql -U postgres
致命错误: 对于主机”[local]”，用户”postgres”，数据库”postgres”，未找到pg_hba.conf条目，SSL关闭
[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 客户端认证配置文件
请用中文将以下内容进行释义，仅需提供一种选项：
主机所有所有 ::1/128 信任
bash-4.2$ vi pg_hba.conf
bash-4.2$ exit
退出
[root@ik1-331-25935 sato]# psql -U postgres
psql: 致命错误: “pg_hba.conf” 中没有针对主机 “[local]”、用户 “postgres”、数据库 “postgres”、关闭了SSL 的条目
[root@ik1-331-25935 sato]# service postgresql restart
正在重定向到 /bin/systemctl restart postgresql.service
[root@ik1-331-25935 sato]# psql -U postgres
psql (9.2.23)
输入 “help” 获取帮助。
“`
postgres=# PostgreSQL shell is ready for input.
“`
postgres=# 新規にデータベースを作る
create table userlist(
id int,
name varchar(20),
address varchar(30),
age int
);
postgres=# 创建表 userlist(
postgres(# id int,
postgres(# name varchar(20),
postgres(# address varchar(30),
postgres(# age int
postgres(# );
创建表
从 userlist 中选择*;
插入数据到userlist表中：
1. insert into userlist values(1, ‘佐藤’, ‘东京’, 18);
2. insert into userlist values(2, ‘奈绪’, ‘京都’, 22);
3. insert into userlist values(3, ‘约翰’, ‘纽约’, 34);
4. insert into userlist values(4, ‘丽莎’, ‘巴黎’, 24);
5. insert into userlist values(5, ‘薰’, ‘名古屋’, 15);
6. insert into userlist values(6, ‘亮’, ‘福冈’, 40);
以下是中文本地化的释义，只需要一个选项：
请提供一个问题。
