Putting pg_wal on a separate device - PostgreSQL

 

Putting pg_wal on a separate device

You may seek advice about placing the pg_wal  directory on a separate device for performance reasons. This sounds very similar to tablespaces, though there is no explicit command to do this once you have a running database, and files in pg_wal  are frequently written. So you must perform the steps outlined in the following example:

Stop the database server:

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /u02/PostgreSQL/15_cluster_db1 stop

waiting for server to shut down.... done

server stopped

-bash-4.2$ 

-bash-4.2$ 

Move pg_wal  to a location supported by a different disk device:

-bash-4.2$ ls -lrt

total 136

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_twophase

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_tblspc

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_snapshots

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_serial

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_replslot

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_notify

drwx------ 4 postgres postgres  4096 Jan 21 14:48 pg_multixact

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_dynshmem

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_commit_ts

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_stat_tmp

-rw------- 1 postgres postgres     3 Jan 21 14:48 PG_VERSION

-rw------- 1 postgres postgres    88 Jan 21 14:48 postgresql.auto.conf

-rw------- 1 postgres postgres  4789 Jan 21 14:48 pg_hba.conf

-rw------- 1 postgres postgres  1636 Jan 21 14:48 pg_ident.conf

drwx------ 3 postgres postgres  4096 Jan 21 14:48 pg_wal

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_xact

drwx------ 2 postgres postgres  4096 Jan 21 14:48 pg_subtrans

drwx------ 5 postgres postgres  4096 Jan 21 14:49 base

drwx------ 2 postgres postgres  4096 Jan 21 14:49 log

-rw------- 1 postgres postgres 29457 Jan 21 15:00 postgresql.conf

-rw------- 1 postgres postgres   374 Jan 21 15:00 logfile

-rw------- 1 postgres postgres    65 Jan 21 15:00 postmaster.opts

-rw------- 1 postgres postgres    30 Jan 21 15:00 current_logfiles

drwx------ 2 postgres postgres  4096 Jan 21 15:12 global

drwx------ 4 postgres postgres  4096 Jan 21 23:28 pg_logical

drwx------ 2 postgres postgres  4096 Jan 21 23:28 pg_stat

-bash-4.2$ mv pg_wal /u02/PostgreSQL/15_cluster_db1_pg_wal/

Create a symbolic link from the old location to the new location:

-bash-4.2$ 

-bash-4.2$ ln -s /u02/PostgreSQL/15_cluster_db1_pg_wal/pg_wal /u02/PostgreSQL/15_cluster_db1/pg_wal

-bash-4.2$ 

-bash-4.2$ 

Restart the database server:

-bash-4.2$ 

-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /u02/PostgreSQL/15_cluster_db1 start

waiting for server to start....2024-01-21 23:34:04.095 IST [75227] LOG:  redirecting log output to logging collector process

2024-01-21 23:34:04.095 IST [75227] HINT:  Future log output will appear in directory "log".

 done

server started

-bash-4.2$