postgres wal log configuration question

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

postgres wal log configuration question

Pavan Kumar-3
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  
Reply | Threaded
Open this post in threaded view
|

Re: postgres wal log configuration question

Pavan Kumar-3
Hello Experts,

Kindly provide feedback on my questions.

Thank you.


On Mon, May 6, 2019 at 2:25 PM Pavan Kumar <[hidden email]> wrote:
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  


--
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  
Reply | Threaded
Open this post in threaded view
|

Re: postgres wal log configuration question

Rui DeSousa


On May 7, 2019, at 10:58 AM, Pavan Kumar <[hidden email]> 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)




Reply | Threaded
Open this post in threaded view
|

Re: postgres wal log configuration question

Pavan Kumar-3
Hello Rui,

My apologies for delayed response. 

Thank you so much for the information, it was helpful. another question I Have is , is there anyway to control wal segment naming format.  wal segment file is not generating in sequential format.



On Tue, May 7, 2019 at 10:36 AM Rui DeSousa <[hidden email]> wrote:


On May 7, 2019, at 10:58 AM, Pavan Kumar <[hidden email]> 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)






--
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  
Reply | Threaded
Open this post in threaded view
|

Re: postgres wal log configuration question

Rui DeSousa


On May 9, 2019, at 8:32 AM, Pavan Kumar <[hidden email]> wrote:

 is there anyway to control wal segment naming format.  wal segment file is not generating in sequential format.

Not really; however the file names are sequential based on the timeline and log sequence.  If you change the WAL segment size then the WAL file name sequence will change; i.e. 16MB vs 128MB WAL files will generate different sequences.

Here’s a article that covers the three parts of the filename; timeline_id, block id, and segment id: