Обсуждение: postgres wal log configuration question

postgres wal log configuration question

hello experts,

I have couple of question on postgres wal log configuration.

how do we know current wal log location  and current wal segment name in postgres ? is there any query to to check location ?

I ran the switch wal log command, as below 

postgres=# select pg_switch_wal();

 pg_switch_wal

---------------

 C/E000158

(1 row)

how do i understand the output ? Is this  wal segment name ? If yes, when i look at wal log location i do see below output. how do i correlate query output with os command output ?

postgres=# select pg_switch_wal();

 pg_switch_wal

---------------

 C/E000158

(1 row)

postgres=# \! ls -lart /oradbaudit/pg_data_dir/pg_wal|tail -5  >>>> last wal segments generated on wal location. 

lrwxrwxrwx. 1 postgres postgres 23 May  6 16:06 /oradbaudit/pg_data_dir/pg_wal -> /oradbaudit/pg_wal_dir/

postgres=# \! ls -lart /oradbaudit/pg_data_dir/pg_wal/|tail -5

-rw-------.  1 postgres postgres 16777216 May  6 17:37 000000020000000C00000048

drwx------.  3 postgres postgres    12288 May  6 17:41 .

-rw-------.  1 postgres postgres 16777216 May  6 18:57 000000020000000C0000000E

drwx------.  2 postgres postgres      125 May  6 18:57 archive_status

-rw-------.  1 postgres postgres 16777216 May  6 18:57 000000020000000C0000000F

Is there any way to provide meaning full sequence number for wal segments? looks like wal segment file name is generating random names. is there any way to control name format ?

I noticed that file wal file name generated on $PGDATA/pg_wal location and archived location seem different. is this expected behavior ?

please help me to understand this concept.

--

Regards,

#!  Pavan Kumar
-----------------------------------------------
Sr. Database Administrator..!
NEXT GENERATION PROFESSIONALS, LLC
Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  

India   # 9000459083

Take Risks; if you win, you will be very happy. If you lose you will be Wise  

On May 7, 2019, at 10:58 AM, Pavan Kumar <pavan.dba27@gmail.com> wrote:

how do i understand the output ? Is this  wal segment name ? If yes, when i look at wal log location i do see below output. how do i correlate query output with os command output ?

postgres=# select pg_switch_wal();

 pg_switch_wal

---------------

 C/E000158

(1 row)

Use pg_walfile_name(); i.e. 

enso=# select pg_switch_wal();

 pg_switch_wal

---------------

 29CF/4D14B48

(1 row)

enso=# select pg_walfile_name('29CF/4D14B48');

     pg_walfile_name

--------------------------

 00000001000029CF00000000

(1 row)

 

enso=# select pg_walfile_name(pg_switch_wal());

     pg_walfile_name

--------------------------

 00000001000029CF00000001

(1 row)