How to migrate the PostgreSQL data directory

Posted on Edited on In , Views:

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)