在CentOS 7上安装并启动PostgreSQL
我将记录在CentOS 7.5上安装和启动PostgreSQL的配置步骤。
用户添加
安裝PostgreSQL時需要更改環境設定，所以需要添加一個新用戶。
使用useradd命令添加用戶，並使用passwd命令設置密碼。
我們使用useradd命令執行以下操作。
$sudo useradd -d /home/xxxxxxxx xxxxxxxx
$cd ..
$ls -lrt
total 4
drwx------. 3 aaaaaa aaaaaa 95 Jan 1 06:19 aaaaaa
drwx------. 10 bbbbbbbbb bbbbbbbbb 4096 Jan 22 12:35 bbbbbbbbb
drwx------. 2 xxxxxxxxx xxxxxxxxx 62 Feb 12 09:51 xxxxxxxxx
我在passwd上进行了以下操作。
$sudo passwd xxxxxxxx
Changing password for user xxxxxxxx.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
给新创建的用户赋予管理员权限。这样就可以无需输入密码使用sudo。
使用visudo命令编辑已指定用户权限的/etc/sudoers文件。
$sudo visudo
以以下方式注册用户名xxxxxxxx。
## Allow root to run any commands anywhere
root ALL=(ALL) ALL
xxxxxxxx ALL=(ALL) ALL
## Same thing without a password
# %wheel ALL=(ALL) NOPASSWD: ALL
%xxxxxxxx ALL=(ALL) NOPASSWD: ALL
通过这个，可以在没有密码的情况下运行sudo。
在中国的方式，只需要一种选项来重新表达：
安装PostgreSQL
成为新添加的用户。
$su xxxxxxxx
通过以下命令无法确认 PostgreSQL 是否已被安装。不会有任何输出。
$rpm -qa | grep postgres
请使用yum命令按照以下方式进行安装。
$ sudo yum install postgresql-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp-srv2.kddilabs.jp
* extras: ftp-srv2.kddilabs.jp
* updates: ftp-srv2.kddilabs.jp
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.x86_64 0:9.2.24-7.el7_9 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-7.el7_9 for package: postgresql-server-9.2.24-7.el7_9.x86_64
--> Processing Dependency: postgresql(x86-64) = 9.2.24-7.el7_9 for package: postgresql-server-9.2.24-7.el7_9.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-9.2.24-7.el7_9.x86_64
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.24-7.el7_9 will be installed
---> Package postgresql-libs.x86_64 0:9.2.24-7.el7_9 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql-server x86_64 9.2.24-7.el7_9 updates 3.8 M
Installing for dependencies:
postgresql x86_64 9.2.24-7.el7_9 updates 3.0 M
postgresql-libs x86_64 9.2.24-7.el7_9 updates 235 k
Transaction Summary
================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 7.1 M
Installed size: 33 M
Is this ok [y/d/N]: y
Downloading packages:
(1/3): postgresql-libs-9.2.24-7.el7_9.x86_64.rpm | 235 kB 00:00
(2/3): postgresql-9.2.24-7.el7_9.x86_64.rpm | 3.0 MB 00:00
(3/3): postgresql-server-9.2.24-7.el7_9.x86_64.rpm | 3.8 MB 00:00
--------------------------------------------------------------------------------
Total 6.9 MB/s | 7.1 MB 00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : postgresql-libs-9.2.24-7.el7_9.x86_64 1/3
Installing : postgresql-9.2.24-7.el7_9.x86_64 2/3
Installing : postgresql-server-9.2.24-7.el7_9.x86_64 3/3
Verifying : postgresql-9.2.24-7.el7_9.x86_64 1/3
Verifying : postgresql-libs-9.2.24-7.el7_9.x86_64 2/3
Verifying : postgresql-server-9.2.24-7.el7_9.x86_64 3/3
Installed:
postgresql-server.x86_64 0:9.2.24-7.el7_9
Dependency Installed:
postgresql.x86_64 0:9.2.24-7.el7_9 postgresql-libs.x86_64 0:9.2.24-7.el7_9
Complete!
可以确认安装已完成，方法如下。
$ rpm -qa | grep postgres
postgresql-9.2.24-7.el7_9.x86_64
postgresql-libs-9.2.24-7.el7_9.x86_64
postgresql-server-9.2.24-7.el7_9.x86_64
你可以通过以下方式检查PostgreSQL的版本。
$ postgres --version
postgres (PostgreSQL) 9.2.24
可以确认已创建了名为postgres的账户。
$ id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
可以确认 psql 命令位于以下位置。
$ which psql
/usr/bin/psql
然而，/var/lib/pgsql/data仍然不存在。
$ sudo ls /var/lib/pgsql/data/
启动PostgreSQL
在安装完毕后，由于未设置Postgres的密码，所以需要进行设置。
$ sudo passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
如果尝试通过systemctl start postgresql在此处启动PostgreSQL，则会出现错误。
$ systemctl start postgresql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: Cloud User (centos)
Password:
polkit-agent-helper-1: pam_authenticate failed: Authentication failure
==== AUTHENTICATION FAILED ===
Failed to start postgresql.service: Access denied
See system logs and 'systemctl status postgresql.service' for details.
出现错误是因为初始设置没有完成。
为了进行初始设置，需要将用户更改为postgres。以下是登录的步骤：使用sudo命令的-u选项来指定用户名，-i选项用于加载登录shell $HOME/.bash_profile。
$ sudo -i -u postgres
-bash-4.2$
在这里，按照以下方式进行初始设置。使用-D选项指定数据库集群应存储的目录。
-bash-4.2$ initdb -D '/var/lib/pgsql/data/'
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 locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
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:
postgres -D /var/lib/pgsql/data
or
pg_ctl -D /var/lib/pgsql/data -l logfile start
-bash-4.2$
一旦退出postgres。
-bash-4.2$ exit
logout
在这种情况下，会创建/var/lib/pgsql/data/文件夹。
$ sudo ls /var/lib/pgsql/data/
base pg_hba.conf pg_notify pg_stat_tmp pg_twophase postgresql.conf
global pg_ident.conf pg_serial pg_subtrans PG_VERSION
pg_clog pg_multixact pg_snapshots pg_tblspc pg_xlog
将此位置设为环境变量$PGDATA。在$HOME/.bash_profile中添加以下内容。
export PGDATA=/var/lib/pgsql/data
只需要一种选择，将以下内容用中文进行翻译：那样做的话，就可以像下面这样。
$ . ~/.bash_profile
$ echo $PGDATA
/var/lib/pgsql/data
$ env | grep PGDATA
PGDATA=/var/lib/pgsql/data
以以下方式启动PostgreSQL，并确认其状态。
$ sudo systemctl start postgresql
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2022-02-12 14:23:21 JST; 10s ago
Process: 17108 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 17103 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 17111 (postgres)
CGroup: /system.slice/postgresql.service
tq17111 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
tq17112 postgres: logger process
tq17114 postgres: checkpointer process
tq17115 postgres: writer process
tq17116 postgres: wal writer process
tq17117 postgres: autovacuum launcher process
mq17118 postgres: stats collector process
Feb 12 14:23:20 i-17100000245070 systemd[1]: Starting PostgreSQL database se....
Feb 12 14:23:21 i-17100000245070 systemd[1]: Started PostgreSQL database server.
Hint: Some lines were ellipsized, use -l to show in full.
请按照以下方式设置，让PostgreSQL在系统启动时自动启动。
$ sudo systemctl enable postgresql
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
确认数据库的运行情况
确认数据库可以正常运行，需要进行以下操作：
1. 使用用户名为”postgres”的用户进行登录。
$ su - postgres
Password:
Last failed login: Sat Feb 12 12:33:47 JST 2022 on pts/6
There were 41 failed login attempts since the last successful login.
-bash-4.2$
确认下面这个数据库的构建方式。
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileg
es
-----------+----------+----------+-------------+-------------+------------------
-----
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/post
gres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | | postgres=CTc/post
gres
(3 rows)
-bash-4.2$
参考文章
尝试在 CentOS 上使用 PostgreSQL 吧！
https://lets.postgresql.jp/documents/tutorial/centos/1
开源数据库标准教科书
PostgreSQL的初始设置 ～需要身份验证～错误的解决方法
初始化数据库
