How to migrate the PostgreSQL data directory
Check the current installed data directory
[ec2-user@ip-192-168-93-151 ~]$ psql -h 192.168.28.223 -U postgres -d postgres
Password for user postgres:
postgres=# SHOW config_file;
config_file
-------------------------------------
/var/lib/pgsql/data/postgresql.conf
(1 row)
postgres=# SHOW data_directory;
data_directory
---------------------
/var/lib/pgsql/data
(1 row)
Create a new data directory
[ec2-user@ip-192-168-28-223 ~]$ sudo mkfs.ext4 /dev/nvme6n1
[ec2-user@ip-192-168-28-223 ~]$ sudo mkdir /mnt/pgdata
[ec2-user@ip-192-168-28-223 ~]$ sudo mount /dev/nvme6n1 /mnt/pgdata
[ec2-user@ip-192-168-28-223 ~]$ sudo chown -R postgres:postgres /mnt/pgdata
[ec2-user@ip-192-168-28-223 ~]$ sudo chmod 700 /mnt/pgdata/
Stop the PostgreSQL
[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl stop postgresql
Migrate the data
[ec2-user@ip-192-168-28-223 ~]$ sudo yum install rsync
[ec2-user@ip-192-168-28-223 ~]$ sudo rsync -av /var/lib/pgsql/data /mnt/pgdata
Modify the PostgreSQL configuration file
Modify the PostgreSQL configuration file:
[ec2-user@ip-192-168-28-223 ~]$ sudo vim /var/lib/pgsql/data/postgresql.conf
data_directory = '/mnt/pgdata/data' # use data in another directory
Modify the system service configuration file:
[ec2-user@ip-192-168-28-223 ~]$ sudo vim /lib/systemd/system/postgresql.service
Environment=PGDATA=/mnt/pgdata/data
Restart the PostgreSQL service:
[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl daemon-reload
[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl start postgresql
[ec2-user@ip-192-168-28-223 ~]$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2023-05-27 20:09:13 UTC; 4s ago
Process: 23901 ExecStartPre=/usr/libexec/postgresql-check-db-dir %N (code=exited, status=0/SUCCESS)
Main PID: 23904 (postmaster)
CGroup: /system.slice/postgresql.service
├─23904 /usr/bin/postmaster -D /mnt/pgdata/data
├─23907 postgres: logger
├─23909 postgres: checkpointer
├─23910 postgres: background writer
├─23911 postgres: walwriter
├─23912 postgres: autovacuum launcher
├─23913 postgres: stats collector
└─23914 postgres: logical replication launcher
Verify the new data directory
[ec2-user@ip-192-168-93-151 ~]$ psql -h 192.168.28.223 -U postgres -d postgres
Password for user postgres:
psql (14.3)
Type "help" for help.
postgres=# SHOW config_file;
config_file
----------------------------------
/mnt/pgdata/data/postgresql.conf
(1 row)
postgres=# SHOW data_directory;
data_directory
------------------
/mnt/pgdata/data
(1 row)