postgre修改数据库data目录并进行数据迁移

首先查看当前存储位置

可以通过命令或查看配置文件
命令方式：首先进入数据库，然后通过语句

[root@localhost ~]# su postgres
[postgres@localhost root]$ cd /opt/pgsql
[postgres@localhost pgsql]$ ls
bin  data  data-bk  include  lib  share
[postgres@localhost pgsql]$  /opt/pgsql/bin/psql
psql (12.2)
Type "help" for help.

postgres=# show data_directory ;
 data_directory
----------------
 /data1/data
(1 row)

postgres=#

查看配置文件方式：

[postgres@localhost data]$ vi postgresql.conf
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/data1/data'
                                        # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''                 # write an extra PID file
                                        # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

data_directory = 这里就是存储位置

关闭数据库

/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l logfile stop
# 如果是yum方式安装的话可以使用service postgresql stop或systemctl stop postgresql

转移数据

用rsync方式把数据库内容拷贝到新位置

-a 保留的权限和其他目录属性, 能避免未来的权限问题
-v 提供详细输出，以便能够看到进度

[root@localhost ~]# rsync -av /现目录 /目标目录

或直接使用cp命令

修改配置文件

postgre.conf
改为目标目录

按照需求备份数据库原始文件

启动数据库

/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l logfile start

或使用sysytemctl方式注意目录
再次查看当前数据库存储目录位置，与目标目录一致就操作成功。