Change PostgreSQL WAL Directory Path (pg_wal)

Table of Contents

0. Aim
1. Verify Existing pg_wal Directory
2. Create the New Directory
3. Stop PostgreSQL Service
4. Copy WAL Files to New Location
5. Backup Old WAL Directory
6. Create Symlink
7. Fix Permissions
8. Start PostgreSQL Service
9. Verify WAL Functionality
10. Remove Old WAL Directory (Optional)

0. Aim

To change the PostgreSQL 17 WAL File directory from its default location to new mount point

From : /var/lib/pgsql/17/data

TO : /pgData/pgsql17/data

1. Verify Existing pg_wal directory


[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <----- 
(1 row)

postgres=# exit
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgData/pgsql17/data/pg_wal
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/pg_wal
48M     /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$

2. Create the New Directory on a new disk

[root@lxicbpgdsgv01 ~]# mkdir -p /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]# chmod 700 /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]#

3. Stop PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        6887    6721  0 16:08 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

4. Copy Existing WAL Files to New Location

[postgres@lxicbpgdsgv01 ~]$ nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1 &
[1] 6943
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$
[1]+  Done                    nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat rsync_pgwal.log
nohup: ignoring input
sending incremental file list
./
000000010000000000000006
         16.78M 100%   81.89MB/s    0:00:00 (xfr#1, to-chk=5/7)
000000010000000000000007
         16.78M 100%   43.84MB/s    0:00:00 (xfr#2, to-chk=4/7)
000000010000000000000008
         16.78M 100%   31.07MB/s    0:00:00 (xfr#3, to-chk=3/7)
archive_status/
archive_status/000000010000000000000006.done
              0 100%    0.00kB/s    0:00:00 (xfr#4, to-chk=0/7)
summaries/

sent 50.34M bytes  received 107 bytes  33.56M bytes/sec
total size is 50.33M  speedup is 1.00
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgWal/pgsql17/wal/
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
48M     /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

5. Move old directory as backup

[postgres@lxicbpgdsgv01 ~]$ mv /pgData/pgsql17/data/pg_wal /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
ls: cannot access '/pgData/pgsql17/data/pg_wal': No such file or directory
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

6. Create symlink

[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  8 16:16 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$

7. Fix permissions (if required)

[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal

8. Start PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Wed 2025-10-08 16:20:46 +08; 7s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 7079 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 7084 (postgres)
      Tasks: 8 (limit: 15835)
     Memory: 18.1M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─7084 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─7086 "postgres: logger "
             ├─7087 "postgres: checkpointer "
             ├─7088 "postgres: background writer "
             ├─7090 "postgres: walwriter "
             ├─7091 "postgres: autovacuum launcher "
             ├─7092 "postgres: archiver "
             └─7093 "postgres: logical replication launcher "

Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com postgres[7084]: 2025-10-08 16:20:46.162 +08 [7084] LOG:  redirecting log output to logging collector process
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com postgres[7084]: 2025-10-08 16:20:46.162 +08 [7084] HINT:  Future log output will appear in directory "log".
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

9. Verify

-- Load WAL File generation (Testing)

postgres=# -- Create test table
DROP TABLE IF EXISTS wal_test;
CREATE TABLE wal_test (
    id serial PRIMARY KEY,
    data text
);

-- Generate WAL traffic
DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..50 LOOP
        -- INSERT: 10,000 rows
        INSERT INTO wal_test (data)
        SELECT repeat('wal_test_data_', 50)
        FROM generate_series(1, 10000);

        -- UPDATE: 5,000 rows using CTE with LIMIT
        WITH to_update AS (
            SELECT id FROM wal_test WHERE id % 2 = 0 LIMIT 5000
        )
        UPDATE wal_test
        SET data = data || '_updated'
        WHERE id IN (SELECT id FROM to_update);

        -- DELETE: 5,000 rows using CTE with LIMIT
        WITH to_delete AS (
            SELECT id FROM wal_test WHERE id % 3 = 0 LIMIT 5000
        )
        DELETE FROM wal_test
        WHERE id IN (SELECT id FROM to_delete);

        -- Commit to flush WAL
        COMMIT;

        -- Optional pause to slow down the loop
        PERFORM pg_sleep(0.5);
    END LOOP;
END$$;
DROP TABLE
CREATE TABLE
DO
postgres=# exit
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -lrth  /pgWal/pgsql17/wal
total 752M
drwx------. 2 postgres postgres    6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres  16M Oct  8 16:36 000000010000000000000035
drwx------. 2 postgres postgres 4.0K Oct  8 16:36 archive_status
-rw-------. 1 postgres postgres  16M Oct  8 16:36 xlogtemp.7289
[postgres@lxicbpgdsgv01 ~]$

10. Remove Old WAL Directory (Optional, later)

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/