pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres wal log configuration question
5+ messages / 2 participants
[nested] [flat]
* postgres wal log configuration question
@ 2019-05-06 19:25 Pavan Kumar <pavan.dba27@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pavan Kumar @ 2019-05-06 19:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 *
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres wal log configuration question
@ 2019-05-07 14:58 Pavan Kumar <pavan.dba27@gmail.com>
parent: Pavan Kumar <pavan.dba27@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pavan Kumar @ 2019-05-07 14:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello Experts,
Kindly provide feedback on my questions.
Thank you.
On Mon, May 6, 2019 at 2:25 PM Pavan Kumar <pavan.dba27@gmail.com> 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 *
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres wal log configuration question
@ 2019-05-07 15:36 Rui DeSousa <rui@crazybean.net>
parent: Pavan Kumar <pavan.dba27@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rui DeSousa @ 2019-05-07 15:36 UTC (permalink / raw)
To: Pavan Kumar <pavan.dba27@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres wal log configuration question
@ 2019-05-09 12:32 Pavan Kumar <pavan.dba27@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 5+ messages in thread
From: Pavan Kumar @ 2019-05-09 12:32 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: pgsql-admin@lists.postgresql.org
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 <rui@crazybean.net> wrote:
>
>
> 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)
>
>
>
>
>
--
*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 *
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgres wal log configuration question
@ 2019-05-09 14:16 Rui DeSousa <rui@crazybean.net>
parent: Pavan Kumar <pavan.dba27@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rui DeSousa @ 2019-05-09 14:16 UTC (permalink / raw)
To: Pavan Kumar <pavan.dba27@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On May 9, 2019, at 8:32 AM, Pavan Kumar <pavan.dba27@gmail.com> 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:
https://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-pg_xlogdump/ <https://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-pg_xlogdump/;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-05-09 14:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-06 19:25 postgres wal log configuration question Pavan Kumar <pavan.dba27@gmail.com>
2019-05-07 14:58 ` Pavan Kumar <pavan.dba27@gmail.com>
2019-05-07 15:36 ` Rui DeSousa <rui@crazybean.net>
2019-05-09 12:32 ` Pavan Kumar <pavan.dba27@gmail.com>
2019-05-09 14:16 ` Rui DeSousa <rui@crazybean.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox