Description:
This document outlines the steps required to change the data directory of a PostgreSQL database to a different location. The data directory is where PostgreSQL stores all of its database files, including tables, indexes, and configurations.
Find Data Directory:
Stop PostgreSQL service:
[root@postgres ~]# systemctl status postgresql-16● postgresql-16.service – PostgreSQL 16 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2024-02-18 16:22:41 IST; 2min 21s ago
Docs: https://www.postgresql.org/docs/16/static/
Process: 10098 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 10104 (postgres)
Tasks: 7 (limit: 48705)
Memory: 19.2M
CGroup: /system.slice/postgresql-16.service
├─10104 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
├─10105 postgres: logger
├─10106 postgres: checkpointer
├─10107 postgres: background writer
├─10109 postgres: walwriter
├─10110 postgres: autovacuum launcher
└─10111 postgres: logical replication launcher
Feb 18 16:22:41 postgres.net systemd[1]: Starting PostgreSQL 16 database server…
Feb 18 16:22:41 postgres.net postgres[10104]: 2024-02-18 16:22:41.237 IST [10104] LOG: redirecting log output to logging collector process
Feb 18 16:22:41 postgres.net postgres[10104]: 2024-02-18 16:22:41.237 IST [10104] HINT: Future log output will appear in directory “log”.
Feb 18 16:22:41 postgres.net systemd[1]: Started PostgreSQL 16 database server.
[root@postgres ~]#
[root@postgres ~]# systemctl stop postgresql-16
[root@postgres ~]#
[root@postgres ~]# systemctl status postgresql-16
● postgresql-16.service – PostgreSQL 16 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Sun 2024-02-18 16:25:17 IST; 3s ago
Docs: https://www.postgresql.org/docs/16/static/
Process: 10104 ExecStart=/usr/pgsql-16/bin/postgres -D ${PGDATA} (code=exited, status=0/SUCCESS)
Process: 10098 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 10104 (code=exited, status=0/SUCCESS)
Feb 18 16:22:41 postgres.net systemd[1]: Starting PostgreSQL 16 database server…
Feb 18 16:22:41 postgres.net postgres[10104]: 2024-02-18 16:22:41.237 IST [10104] LOG: redirecting log output to logging collector process
Feb 18 16:22:41 postgres.net postgres[10104]: 2024-02-18 16:22:41.237 IST [10104] HINT: Future log output will appear in directory “log”.
Feb 18 16:22:41 postgres.net systemd[1]: Started PostgreSQL 16 database server.
Feb 18 16:25:17 postgres.net systemd[1]: Stopping PostgreSQL 16 database server…
Feb 18 16:25:17 postgres.net systemd[1]: postgresql-16.service: Killing process 10105 (postgres) with signal SIGKILL.
Feb 18 16:25:17 postgres.net systemd[1]: postgresql-16.service: Succeeded.
Feb 18 16:25:17 postgres.net systemd[1]: Stopped PostgreSQL 16 database server.
RSYNC Old data directory to New data directory:
[root@postgres ~]# cd /data[root@postgres data]# mkdir PostgreSQL
[root@postgres data]# ls -lrt
total 0
drwxr-xr-x. 2 root root 6 Feb 18 16:31 PostgreSQL
[root@postgres data]#
[root@postgres data]# cd PostgreSQL/
[root@postgres PostgreSQL]# pwd
/data/PostgreSQL
[root@postgres PostgreSQL]#
[root@postgres PostgreSQL]# rsync -av /var/lib/pgsql /data/PostgreSQL
sending incremental file list
pgsql/
pgsql/.bash_history
pgsql/.bash_profile
pgsql/.psql_history
pgsql/.cache/
pgsql/16/
pgsql/16/initdb.log
pgsql/16/backups/
pgsql/16/data/
pgsql/16/data/PG_VERSION
pgsql/16/data/current_logfiles
pgsql/16/data/pg_hba.conf
pgsql/16/data/pg_ident.conf
pgsql/16/data/postgresql.auto.conf
pgsql/16/data/postgresql.conf
pgsql/16/data/postmaster.opts
pgsql/16/data/base/
.
.
.
etc
pgsql/16/data/pg_tblspc/
pgsql/16/data/pg_twophase/
pgsql/16/data/pg_wal/
pgsql/16/data/pg_wal/000000010000000000000001
pgsql/16/data/pg_wal/archive_status/
pgsql/16/data/pg_xact/
pgsql/16/data/pg_xact/0000
sent 41,117,395 bytes received 18,766 bytes 82,272,322.00 bytes/sec
total size is 41,051,849 speedup is 1.00
[root@postgres PostgreSQL]#
Rename name the Original Data Directory:
[root@postgres ~]# mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data_bkpChange the data directory in /usr/lib/systemd/system/postgresql-16.service file:
[root@postgres ~]# vi /usr/lib/systemd/system/postgresql-16.service#It’s not recommended to modify this file in-place, because it will be
#overwritten during package upgrades. It is recommended to use systemd
#”dropin” feature; i.e. create file with suffix .conf under
#/etc/systemd/system/postgresql-16.service.d directory overriding the
#unit’s defaults. You can also use “systemctl edit postgresql-16”
#Look at systemd.unit(5) manual page for more info.
#Note: changing PGDATA will typically require adjusting SELinux
configuration as well.
#Note: do not use a PGDATA pathname containing spaces, or you will
break postgresql-16-setup.
Documentation=https://www.postgresql.org/docs/16/static/
After=syslog.target
After=network-online.target [Service] Type=notify
User=postgres
Group=postgres
#Note: avoid inserting whitespace in these Environment= lines, or you may
break postgresql-setup.
#Location of database directory
Environment=PGDATA=/data/PostgreSQL/pgsql/16/data/
#Where to send early-startup messages from the server (before the logging
#options of postgresql.conf take effect)
#This is normally controlled by the global default set by systemd
#StandardOutput=syslog
#Disable OOM kill on postgres main process
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-16/bin/postgres -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
Reload daemon for Data directory Change:
[root@postgres ~]# systemctl daemon-reload
Start The PostgreSQL service:
[root@postgres ~]# systemctl daemon-reload[root@postgres ~]#
[root@postgres ~]# systemctl start postgresql-16
[root@postgres ~]# systemctl status postgresql-16
● postgresql-16.service – PostgreSQL 16 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2024-02-18 16:45:57 IST; 3s ago
Docs: https://www.postgresql.org/docs/16/static/
Process: 11173 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 11179 (postgres)
Tasks: 7 (limit: 48705)
Memory: 17.6M
CGroup: /system.slice/postgresql-16.service
├─11179 /usr/pgsql-16/bin/postgres -D /data/PostgreSQL/pgsql/16/data/
├─11180 postgres: logger
├─11181 postgres: checkpointer
├─11182 postgres: background writer
├─11184 postgres: walwriter
├─11185 postgres: autovacuum launcher
└─11186 postgres: logical replication launcher
Feb 18 16:45:57 postgres.net systemd[1]: Starting PostgreSQL 16 database server…
Feb 18 16:45:57 postgres.net postgres[11179]: 2024-02-18 16:45:57.768 IST [11179] LOG: redirecting log output to logging collector process
Feb 18 16:45:57 postgres.net postgres[11179]: 2024-02-18 16:45:57.768 IST [11179] HINT: Future log output will appear in directory “log”.
Feb 18 16:45:57 postgres.net systemd[1]: Started PostgreSQL 16 database server.
[root@postgres ~]#
Connect Database and Check Data Directory Location:
Remove Old data directory:
[root@postgres ~]# systemctl stop postgresql-16[root@postgres ~]# systemctl status postgresql-16
● postgresql-16.service – PostgreSQL 16 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Sun 2024-02-18 16:53:55 IST; 5s ago
Docs: https://www.postgresql.org/docs/16/static/
Process: 11842 ExecStart=/usr/pgsql-16/bin/postgres -D ${PGDATA} (code=exited, status=0/SUCCESS)
Process: 11836 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 11842 (code=exited, status=0/SUCCESS)
Feb 18 16:53:45 postgres.net systemd[1]: Starting PostgreSQL 16 database server…
Feb 18 16:53:45 postgres.net postgres[11842]: 2024-02-18 16:53:45.750 IST [11842] LOG: redirecting log output to logging collector process
Feb 18 16:53:45 postgres.net postgres[11842]: 2024-02-18 16:53:45.750 IST [11842] HINT: Future log output will appear in directory “log”.
Feb 18 16:53:45 postgres.net systemd[1]: Started PostgreSQL 16 database server.
Feb 18 16:53:55 postgres.net systemd[1]: Stopping PostgreSQL 16 database server…
Feb 18 16:53:55 postgres.net systemd[1]: postgresql-16.service: Killing process 11843 (postgres) with signal SIGKILL.
Feb 18 16:53:55 postgres.net systemd[1]: postgresql-16.service: Succeeded.
Feb 18 16:53:55 postgres.net systemd[1]: Stopped PostgreSQL 16 database server.
[root@postgres ~]#
[root@postgres ~]# cd /var/lib/pgsql/
[root@postgres pgsql]#
[root@postgres pgsql]# rm -fr *
[root@postgres pgsql]#
[root@postgres pgsql]# ls -lrt
total 0
[root@postgres pgsql]#
[root@postgres pgsql]# cd
[root@postgres ~]#
[root@postgres ~]# systemctl start postgresql-16
[root@postgres ~]# systemctl status postgresql-16
● postgresql-16.service – PostgreSQL 16 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2024-02-18 16:54:30 IST; 2s ago
Docs: https://www.postgresql.org/docs/16/static/
Process: 11883 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 11889 (postgres)
Tasks: 7 (limit: 48705)
Memory: 17.6M
CGroup: /system.slice/postgresql-16.service
├─11889 /usr/pgsql-16/bin/postgres -D /data/PostgreSQL/pgsql/16/data/
├─11890 postgres: logger
├─11891 postgres: checkpointer
├─11892 postgres: background writer
├─11894 postgres: walwriter
├─11895 postgres: autovacuum launcher
└─11896 postgres: logical replication launcher
Feb 18 16:54:30 postgres.net systemd[1]: Starting PostgreSQL 16 database server…
Feb 18 16:54:30 postgres.net postgres[11889]: 2024-02-18 16:54:30.900 IST [11889] LOG: redirecting log output to logging collector process
Feb 18 16:54:30 postgres.net postgres[11889]: 2024-02-18 16:54:30.900 IST [11889] HINT: Future log output will appear in directory “log”.
Feb 18 16:54:30 postgres.net systemd[1]: Started PostgreSQL 16 database server.
[root@postgres ~]#
Caution: The use of any content or information on this website is at your own risk. It is provided for educational purposes only. Before utilizing, be sure to execute it in your test environment.
