postgresql - postgresql 服务无法识别 initb -D 和已更改的 data_direectoy

Question

我想更改我的 postgresql 数据库集群的 data_directory。我找到了两种方法来做到这一点，但没有一种对我有用。

根据文档我所做的是:

yum install postgresql-server
create new linux user "postgres"
sudo mkdir /home2
sudo mkdir /home2/data
sudo chown postgres:postgres /home2
sudo chown postgres:postgres /home2/data

现在两种情况的麻烦都开始了:

变体 1:

✘ root@localhost /var/lib/pgsql/data # postgresql-setup initdb
Initializing database ... OK

✘ root@localhost /var/lib/pgsql/data # l
total 44K
drwx------. 15 postgres postgres 4.0K May 17 08:02 .
drwx------.  4 postgres postgres   72 May 16 15:17 ..
drwx------.  5 postgres postgres   41 May 17 08:02 base
drwx------.  2 postgres postgres 4.0K May 17 08:02 global
drwx------.  2 postgres postgres   18 May 17 08:02 pg_clog
-rw-------.  1 postgres postgres 4.2K May 17 08:02 pg_hba.conf
-rw-------.  1 postgres postgres 1.6K May 17 08:02 pg_ident.conf
drwx------.  2 postgres postgres    6 May 17 08:02 pg_log
drwx------.  4 postgres postgres   36 May 17 08:02 pg_multixact
drwx------.  2 postgres postgres   18 May 17 08:02 pg_notify
drwx------.  2 postgres postgres    6 May 17 08:02 pg_serial
drwx------.  2 postgres postgres    6 May 17 08:02 pg_snapshots
drwx------.  2 postgres postgres    6 May 17 08:02 pg_stat_tmp
drwx------.  2 postgres postgres   18 May 17 08:02 pg_subtrans
drwx------.  2 postgres postgres    6 May 17 08:02 pg_tblspc
drwx------.  2 postgres postgres    6 May 17 08:02 pg_twophase
-rw-------.  1 postgres postgres    4 May 17 08:02 PG_VERSION
drwx------.  3 postgres postgres   60 May 17 08:02 pg_xlog
-rw-------.  1 postgres postgres  20K May 17 08:02 postgresql.conf
root@localhost /var/lib/pgsql/data # 

以 postgres 用户身份启动终端:

-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.

postgres=# SHOW data_directory;
   data_directory    
---------------------
 /var/lib/pgsql/data
(1 row)

postgres=#

我执行了 systemctl stop psotgresql，编辑了 postgresql.conf 并更改了 data_directory = '/home2/data'。当我执行 systemctl start psotgresql 时，我得到了

FATAL:  "/home2/data" is not a valid data directory
DETAIL:  File "/home2/data/PG_VERSION" is missing.

我也是这样

-bash-4.2$ initdb -D /home2/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 /home2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /home2/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 /home2/data
or
    pg_ctl -D /home2/data -l logfile start

-bash-4.2$

作为 postgres 用户。当我尝试使用 systemctl start postgresql 再次启动 postgresql 服务器时，终端无法完成

root@localhost /var/lib/pgsql/data # systemctl start postgresql

但服务器正在运行，我可以以 postgres 用户身份登录

-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.

postgres=# SHOW data_directory;
 data_directory 
----------------
 /home2/data
(1 row)

postgres=#

这里出了什么问题？为什么服务“提示”不完成？一段时间后，服务超时并返回。数据库不再运行。

Job for postgresql.service failed because a timeout was exceeded. See "systemctl status postgresql.service" and "journalctl -xe" for details.
✘ root@localhost /var/lib/pgsql/data #

---

变体 2:

新的本地虚拟机从头开始执行这些步骤，直到出现问题:

-bash-4.2$ initdb -D /home2/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 /home2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /home2/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 /home2/data
or
    pg_ctl -D /home2/data -l logfile start

-bash-4.2$ ls -l /home2/data/
total 40
drwx------. 5 postgres postgres    41 May 17 08:20 base
drwx------. 2 postgres postgres  4096 May 17 08:20 global
drwx------. 2 postgres postgres    18 May 17 08:20 pg_clog
-rw-------. 1 postgres postgres  4476 May 17 08:20 pg_hba.conf
-rw-------. 1 postgres postgres  1636 May 17 08:20 pg_ident.conf
drwx------. 4 postgres postgres    36 May 17 08:20 pg_multixact
drwx------. 2 postgres postgres    18 May 17 08:20 pg_notify
drwx------. 2 postgres postgres     6 May 17 08:20 pg_serial
drwx------. 2 postgres postgres     6 May 17 08:20 pg_snapshots
drwx------. 2 postgres postgres     6 May 17 08:20 pg_stat_tmp
drwx------. 2 postgres postgres    18 May 17 08:20 pg_subtrans
drwx------. 2 postgres postgres     6 May 17 08:20 pg_tblspc
drwx------. 2 postgres postgres     6 May 17 08:20 pg_twophase
-rw-------. 1 postgres postgres     4 May 17 08:20 PG_VERSION
drwx------. 3 postgres postgres    60 May 17 08:20 pg_xlog
-rw-------. 1 postgres postgres 19865 May 17 08:20 postgresql.conf
-bash-4.2$ 

尝试启动postgresql服务时

✘ root@localhost /var/lib/pgsql/data # systemctl restart postgresql
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
✘ root@localhost /var/lib/pgsql/data # journalctl -xe
...
May 17 08:20:58 localhost.localdomain postgresql-check-db-dir[15283]: "/var/lib/pgsql/data" is missing or empty.
May 17 08:20:58 localhost.localdomain postgresql-check-db-dir[15283]: Use "postgresql-setup initdb" to initialize the database cluster.
May 17 08:20:58 localhost.localdomain postgresql-check-db-dir[15283]: See /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information.
May 17 08:20:58 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1
May 17 08:20:58 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server.
...

所以 postgresql 服务看不到我已经执行了 initdb。当我执行 postgresql-setup initdb 时，它只是在默认位置下创建数据目录。以 postgres 用户 postgres -D/home2/data 身份运行 postgresql 确实有效，但我必须使用此命令创建某种服务，这样我就不必让我的终端保持打开状态。

环境:CentOS 7
我正在本地 vagrant box 中进行第一次测试安装。在执行此操作时，我在 ansible 中编写代码。所以通常我不使用 root 用户 ;)

Answer 1

可能是我的问题的解决方案

经过一些研究，我发现了一个 guide这使我对解决方案有了更进一步的了解。在查看了我的 cat/usr/lib/systemd/system/postgresql.service 之后，有一部分说

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql.service",
# containing
#   .include /lib/systemd/system/postgresql.service
#   ...make your changes here...

所以我这样做了:

# vi /etc/systemd/system/postgresql.service
.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGDATA=/home2/data

最后我可以简单地执行 postgresql-setup initdb 并且我的数据库集群被安装到正确的目录中，我可以像预期的那样使用我的系统服务。

一旦我确认数据库运行正常且没有任何问题，我就会将其标记为解决方案。