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.

 

SEE ALSO: