我想要更改我的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-user身份启动终端:
-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=#
这里出了什么问题?为什么服务“提示”没有结束?在相当长的一段时间后,该服务将超时并返回。那么数据库就不再运行了。
由于超过超时,postgresql.service的
作业失败。详见"systemctl status postgresql.service“和"journalctl -xe”。
数据根目录@localhost/var/lib/pgsql/✘#
变体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-service不会看到我已经做了initdb。当我执行postgresql-setup initdb
时,它只是在默认位置下创建数据目录。以postgres用户postgres -D /home2/data
的身份运行postgresql确实可以工作,但是我必须使用这个命令创建某种服务,这样我就不必一直打开我的终端了。
环境: CentOS 7
我正在做第一个测试-安装在当地的流浪汉盒子里。在这样做的时候,我用ansible编写代码。因此,我通常不使用root用户;)
发布于 2019-05-17 18:36:33
可能是我的问题的解决方案
在做了一些研究之后,我发现了一个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
,我的数据库集群将被安装到正确的目录中,我可以像预期的那样使用我的系统服务。
我将标记为解决方案,一旦我可以确认数据库运行良好,没有任何问题。
https://stackoverflow.com/questions/56180786
复制相似问题