Re: [HACKERS] proposal: get oldest LSN - function
On 11.03.2016 16:33, Kartyshov Ivan wrote: On 28.02.2016 11:36, Michael Paquier wrote: On Sun, Feb 28, 2016 at 4:40 PM, Kartyshov Ivan wrote: It will not satisfy our purposes and our administrators for three reasons. 1) DBA set me task to get the oldest number that present in WAL, not last Yeah I got that. 2) Surely we can choose the oldest segment from list "pg_ls_dir('pg_xlog')" of segments and calculate the first LSN by hand, but it is not what we want to do manually. That's where I am not following. In any case you are just one SQL query away from the result. And actually your patch is incorrect I think. If you care about the oldest record available you should look for the first LSN position of the oldest segment, no? What you are calculating now is the oldest virtual LSN position in local pg_xlog. Sorry, for long not answering and thank you for time you spent reviewing this patch, please clarify what you meen, when you say "virtual LSN position". In whole you are right, my main goal is to get the oldest LSN position in local pg_xlog, and i agree with you that it isn`t too hard to get it by SQL query. But it will be more usefull, to have easy access in userspace. You wrote "If you care about the oldest record available you should look for the first LSN position of the oldest segment, no?" Yes I do it exactly this way. -- -- Ivan Kartyshov Postgres Professional: www.postgrespro.com Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: get oldest LSN - function
On 28.02.2016 11:36, Michael Paquier wrote: On Sun, Feb 28, 2016 at 4:40 PM, Kartyshov Ivan wrote: It will not satisfy our purposes and our administrators for three reasons. 1) DBA set me task to get the oldest number that present in WAL, not last Yeah I got that. 2) Surely we can choose the oldest segment from list "pg_ls_dir('pg_xlog')" of segments and calculate the first LSN by hand, but it is not what we want to do manually. That's where I am not following. In any case you are just one SQL query away from the result. And actually your patch is incorrect I think. If you care about the oldest record available you should look for the first LSN position of the oldest segment, no? What you are calculating now is the oldest virtual LSN position in local pg_xlog. Sorry, for long not answering and thank you for time you spent reviewing this patch, please clarify what you meen, when you say "virtual LSN position". In whole you are right, my main goal is to get the oldest LSN position in local pg_xlog, and i agree with you that it isn`t too hard to get it by SQL query. But it will be more usefull, to have easy access in userspace. -- Ivan Kartyshov Postgres Professional: www.postgrespro.com Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: get oldest LSN - function
On 27.02.2016 16:09, Michael Paquier wrote: On Sat, Feb 27, 2016 at 3:52 PM, Kartyshov Ivan wrote: Maybe i wasn't too accurate in terms, because I newbie, but: We can get information about xlog, using big amout of support function (pg_current_xlog_location(), pg_current_xlog_insert_location(), pg_xlogfile_name_offset(), pg_xlogfile_name(), pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ... etc) they helps to get get useful information about xlog files and its content. So, this patch extends this amount of functions. It is useful additional tool for DBA (we can get replicationSlotMinLSN, so why not in master), it can show us, if xlog replication or wal-sender is working properly or indicate if replication on startup can get up to date with master, or after long turnoff must be recovered from archive. What pg_ls_dir('pg_xlog') couldn't do here if you need to know the last WAL segment present on master? Hi Michael, It will not satisfy our purposes and our administrators for three reasons. 1) DBA set me task to get the oldest number that present in WAL, not last 2) Surely we can choose the oldest segment from list "pg_ls_dir('pg_xlog')" of segments and calculate the first LSN by hand, but it is not what we want to do manually. 3) Soon will be commitfest and our administrators wants to enhance their tool case for debug with usable features. Thank you for comment. -- Ivan Kartyshov Postgres Professional: www.postgrespro.com Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: get oldest LSN - function
On 27.02.2016 22:36, Yury Zhuravlev wrote: Hello. You forgot to declare a GetXLogOldestLSNPtr function in a header file (in xlog.h I think). Some compilers refuse to compile this code (MSVC for example). Now I see warning from GCC: xlog.c:10627:1: warning: no previous prototype for «GetXLogOldestLSNPtr» [-Wmissing-prototypes] GetXLogOldestLSNPtr(void) Thanks. Hi Yury, thank for valuable remark. Changes applied -- Ivan Kartyshov Postgres Professional: www.postgrespro.com Russian Postgres Company diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f9eea76..f774233 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -16908,6 +16908,8 @@ SELECT set_config('log_statement_stats', 'off', false); pg_current_xlog_location + +pg_oldest_xlog_location pg_start_backup @@ -16981,6 +16983,13 @@ SELECT set_config('log_statement_stats', 'off', false); +pg_oldest_xlog_location() + + pg_lsn + Get the oldest WAL LSN (log sequence number) + + + pg_start_backup(label text , fast boolean ) pg_lsn @@ -17096,6 +17105,7 @@ postgres=# select pg_start_backup('label_goes_here'); +pg_oldest_xlog_location displays the oldest WAL LSN. pg_current_xlog_location displays the current transaction log write location in the same format used by the above functions. Similarly, pg_current_xlog_insert_location displays the current transaction log diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 6cb690c..5a0e887 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -860,6 +860,8 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' The last WAL receive location in the standby is also displayed in the process status of the WAL receiver process, displayed using the ps command (see for details). + Also we can get the oldest WAL LSN (Log Sequence Number) + pg_oldest_xlog_location, it can give us a useful tool for DBA, additionally it can show us, if xlog replication or wal-sender is working properly or indicate if replication on startup can can get up to date with master, or after long turnoff must be recovered from archive. You can retrieve a list of WAL sender processes via the diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 94b79ac..067d51c 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -10669,6 +10669,18 @@ GetXLogWriteRecPtr(void) } /* + * Get oldest WAL write pointer + */ +XLogRecPtr +GetXLogOldestLSNPtr(void) +{ + XLogRecPtr result; + + XLogSegNoOffsetToRecPtr(XLogGetLastRemovedSegno()+1, 1, result); + return result; +} + +/* * Returns the redo pointer of the last checkpoint or restartpoint. This is * the oldest point in WAL that we still need, if we have to restart recovery. */ diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c index 31cbb01..44e01e1 100644 --- a/src/backend/access/transam/xlogfuncs.c +++ b/src/backend/access/transam/xlogfuncs.c @@ -195,6 +195,27 @@ pg_current_xlog_location(PG_FUNCTION_ARGS) } /* + * Report the oldest WAL write location (same format as pg_start_backup etc) + * + * This is useful for determining the first LSN in existing sequences + */ +Datum +pg_oldest_xlog_location(PG_FUNCTION_ARGS) +{ + XLogRecPtr oldest_recptr; + + if (RecoveryInProgress()) + ereport(ERROR, +(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("WAL control functions cannot be executed during recovery."))); + + oldest_recptr = GetXLogOldestLSNPtr(); + + PG_RETURN_LSN(oldest_recptr); +} + +/* * Report the current WAL insert location (same format as pg_start_backup etc) * * This function is mostly for debugging purposes. diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index ecd30ce..bc7ce6c 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -236,6 +236,7 @@ extern void GetXLogReceiptTime(TimestampTz *rtime, bool *fromStream); extern XLogRecPtr GetXLogReplayRecPtr(TimeLineID *replayTLI); extern XLogRecPtr GetXLogInsertRecPtr(void); extern XLogRecPtr GetXLogWriteRecPtr(void); +extern XLogRecPtr GetXLogOldestLSNPtr(void); extern bool RecoveryIsPaused(void); extern void SetRecoveryPause(bool recoveryPause); extern TimestampTz GetLatestXTime(void); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b24e434..3c2cefb 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -5202,6 +5202,10 @@ DESCR("get an individual replication origin's replication progress"); DATA(insert OID = 6014 ( pg_show_replication_origin_status PGNSP PGUID 12 1 100 0 0 f f f f f t v r 0 0 2249 "" "{26,25,3220,322
Re: [HACKERS] proposal: get oldest LSN - function
On 27.02.2016 03:07, Andres Freund wrote How does it help with any of that? Hi, thank you for fast answer. Maybe i wasn't too accurate in terms, because I newbie, but: We can get information about xlog, using big amout of support function (pg_current_xlog_location(), pg_current_xlog_insert_location(), pg_xlogfile_name_offset(), pg_xlogfile_name(), pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ... etc) they helps to get get useful information about xlog files and its content. So, this patch extends this amount of functions. This function is mostly for debugging purposes. -- Ivan Kartyshov Postgres Professional: www.postgrespro.com Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal: get oldest LSN - function
Hello, I want to suggest a client-side little function, implemented in the attached patch. Function pg_oldest_xlog_location gets us the oldest LSN (Log Sequence Number) in xlog. It is useful additional tool for DBA (we can get replicationSlotMinLSN, so why not in master), it can show us, if xlog replication or wal-sender is working properly or indicate if replication on startup can get up to date with master, or after long turnoff must be recovered from archive. Anyway, does it look useful enough to be part of postgres? I guess I should push this to commitfest if that's the case. Best regards, -- Ivan Kartyshov Postgres Professional: www.postgrespro.com Russian Postgres Company diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f9eea76..f774233 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -16908,6 +16908,8 @@ SELECT set_config('log_statement_stats', 'off', false); pg_current_xlog_location + +pg_oldest_xlog_location pg_start_backup @@ -16981,6 +16983,13 @@ SELECT set_config('log_statement_stats', 'off', false); +pg_oldest_xlog_location() + + pg_lsn + Get the oldest WAL LSN (log sequence number) + + + pg_start_backup(label text , fast boolean ) pg_lsn @@ -17096,6 +17105,7 @@ postgres=# select pg_start_backup('label_goes_here'); +pg_oldest_xlog_location displays the oldest WAL LSN. pg_current_xlog_location displays the current transaction log write location in the same format used by the above functions. Similarly, pg_current_xlog_insert_location displays the current transaction log diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 6cb690c..5a0e887 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -860,6 +860,8 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' The last WAL receive location in the standby is also displayed in the process status of the WAL receiver process, displayed using the ps command (see for details). + Also we can get the oldest WAL LSN (Log Sequence Number) + pg_oldest_xlog_location, it can give us a useful tool for DBA, additionally it can show us, if xlog replication or wal-sender is working properly or indicate if replication on startup can can get up to date with master, or after long turnoff must be recovered from archive. You can retrieve a list of WAL sender processes via the diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 94b79ac..067d51c 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -10669,6 +10669,18 @@ GetXLogWriteRecPtr(void) } /* + * Get oldest WAL write pointer + */ +XLogRecPtr +GetXLogOldestLSNPtr(void) +{ + XLogRecPtr result; + + XLogSegNoOffsetToRecPtr(XLogGetLastRemovedSegno()+1, 1, result); + return result; +} + +/* * Returns the redo pointer of the last checkpoint or restartpoint. This is * the oldest point in WAL that we still need, if we have to restart recovery. */ diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c index 31cbb01..44e01e1 100644 --- a/src/backend/access/transam/xlogfuncs.c +++ b/src/backend/access/transam/xlogfuncs.c @@ -195,6 +195,27 @@ pg_current_xlog_location(PG_FUNCTION_ARGS) } /* + * Report the oldest WAL write location (same format as pg_start_backup etc) + * + * This is useful for determining the first LSN in existing sequences + */ +Datum +pg_oldest_xlog_location(PG_FUNCTION_ARGS) +{ + XLogRecPtr oldest_recptr; + + if (RecoveryInProgress()) + ereport(ERROR, +(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("WAL control functions cannot be executed during recovery."))); + + oldest_recptr = GetXLogOldestLSNPtr(); + + PG_RETURN_LSN(oldest_recptr); +} + +/* * Report the current WAL insert location (same format as pg_start_backup etc) * * This function is mostly for debugging purposes. diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b24e434..3c2cefb 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -5202,6 +5202,10 @@ DESCR("get an individual replication origin's replication progress"); DATA(insert OID = 6014 ( pg_show_replication_origin_status PGNSP PGUID 12 1 100 0 0 f f f f f t v r 0 0 2249 "" "{26,25,3220,3220}" "{o,o,o,o}" "{local_id, external_id, remote_lsn, local_lsn}" _null_ _null_ pg_show_replication_origin_status _null_ _null_ _null_ )); DESCR("get progress for all replication origins"); + +DATA(insert OID = 6015 ( pg_oldest_xlog_location PGNSP PGUID 12 1 0 0 0 f f f f t f v s 0 0 3220 "" _null_ _null_ _null_ _null_ _null_ pg_oldest_xlog_location _null_ _null_ _null_ )); +DESCR("pg oldest