Below article explains how to change postgres data directory to a new location
existing data_directory – > /var/lib/edb/as11/data
New data_directory -> /oradata/pgdata
postgres os user – > enterprisedb
1. Check existing data directory
postgres=# show data_directory;
data_directory
------------------------
/var/lib/edb/as11/data
(1 row)
2. Create new directory structure
-bash-4.2$ mkdir -p /oradata/pgdata
-bash-4.2$ chmod 0700 /oradata/pgdata
Note – if 600 permission is not provided to the directory, then PG cluster service will fail with below error.
Active: failed (Result: exit-code) since Sun 2021-06-13 06:46:52 +03; 8s ago
Process: 761 ExecStart=/usr/edb/as11/bin/edb-postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
Process: 753 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 761 (code=exited, status=1/FAILURE)Jun 13 06:46:52 scdvlbss14 systemd[1]: Starting EDB Postgres Advanced Server 11…
Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 FATAL: data directory “/oradata/pgdata” has invalid permissions
Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service: main process exited, code=exited, status=1/FAILURE
Jun 13 06:46:52 scdvlbss14 systemd[1]: Failed to start EDB Postgres Advanced Server 11.
Jun 13 06:46:52 scdvlbss14 systemd[1]: Unit edb-as-11.service entered failed state.
Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service failed.
3. Stop postgres service
-bash-4.2$ systemctl status edb-as-11
● edb-as-11.service - EDB Postgres Advanced Server 11
Loaded: loaded (/usr/lib/systemd/system/edb-as-11.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2020-12-28 11:22:01 +03; 5 months 14 days ago
Main PID: 30787 (edb-postmaster)
Tasks: 10
Memory: 8.2G
CGroup: /system.slice/edb-as-11.service
├─25828 postgres: checkpointer
├─25829 postgres: background writer
├─25831 postgres: walwriter
├─25832 postgres: autovacuum launcher
├─25835 postgres: archiver last was 0000000100000010000000C8
├─25836 postgres: stats collector
├─25837 postgres: dbms_aq launcher
├─25838 postgres: logical replication launcher
├─30787 /usr/edb/as11/bin/edb-postmaster -D /var/lib/edb/as11/data
└─30790 postgres: logger
-bash-4.2$
-- Run stop command
-bash-4.2$ systemctl stop edb-as-11
4 . Copy the data directory to new location:
-bash-4.2$ cd /var/lib/edb/as11/data
-bash-4.2$ cp -r * /oradata/pgdata/
-bash-4.2$ ls -ltr /oradata/pgdata/
5. Update postgres service file:[ as root user ]
It is not advisable to edit service file from default location. So copy it to /etc/systemd/system location and edit it there.
[root]# cp /usr/lib/systemd/system/edb-as-11.service /etc/systemd/system/
[root]# cd /etc/systemd/system/
[rootsystem]# ls -ltr edb*
-rw-r--r-- 1 root root 1396 Jun 13 06:41 edb-as-11.service
-- Edit below path, pointing to new data_directory
[root@scdvlbss14 system]# vi edb-as-11.service
# Location of database directory
Environment=PGDATA=/oradata/pgdata
PIDFile=/oradata/pgdata/postmaster.pid
In our environment, data_directory, hba_file, ident_file parameters were commented in postgres.conf file, i.e the values are getting picked from service file. So we are not doing any change to postgres.conf file.
# 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 = 'ConfigDir' # 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)
So Please crosscheck postgres.conf file, If these parameters were defined and uncommented, then you need to update the path in this file also.
6 . Reload the systemctl daemon:
[root#] systemctl daemon-reload
7. Start the postgres service:
[root]# systemctl start edb-as-11
-- Check status:
[root]# systemctl status edb-as-11
● edb-as-11.service - EDB Postgres Advanced Server 11
Loaded: loaded (/etc/systemd/system/edb-as-11.service; disabled; vendor preset: disabled)
Active: active (running) since Sun 2021-06-13 06:47:34 +03; 6s ago
Process: 1741 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1748 (edb-postmaster)
Tasks: 10
Memory: 202.8M
CGroup: /system.slice/edb-as-11.service
├─1748 /usr/edb/as11/bin/edb-postmaster -D /oradata/pgdata
├─1751 postgres: logger
├─1753 postgres: checkpointer
├─1754 postgres: background writer
├─1755 postgres: walwriter
├─1756 postgres: autovacuum launcher
├─1757 postgres: archiver
├─1758 postgres: stats collector
├─1759 postgres: dbms_aq launcher
└─1760 postgres: logical replication launcher
Jun 13 06:47:33 dbhost1 systemd[1]: Starting EDB Postgres Advanced Server 11...
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG: listening on IPv4 address "0.0.0.0", port 5435
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG: listening on IPv6 address "::", port 5435
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG: listening on Unix socket "/tmp/.s.PGSQL.5435"
Jun 13 06:47:34 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:34 +03 LOG: redirecting log output to logging collector process
Jun 13 06:47:34 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:34 +03 HINT: Future log output will appear in directory "log".
Jun 13 06:47:34 dbhosg1 systemd[1]: Started EDB Postgres Advanced Server 11.
8. Check the data_directory value:
postgres=# show data_directory;
data_directory
-----------------
/oradata/pgdata
(1 row)
We have successfully changed the data_directory.
