Обсуждение: Postgresql 9.5 committing and log sequence number

Поиск
Список
Период
Сортировка

Postgresql 9.5 committing and log sequence number

От
Poul Kristensen
Дата:
Hi!

I/we use replication
Sender and receiver process is showing the log sequence
number. 
I assume that the log sequence is changing after every commit of an update or more updates.
I assume that the archive log is changing after every commit of an update or
more updates.

Going to test a PITR I need to know where to look for the log sequence number after PITR.

Thanks

--
Med venlig hilsen / Best regards
Poul Kristensen

Re: Postgresql 9.5 committing and log sequence number

От
Albe Laurenz
Дата:
Poul Kristensen wrote:
> I/we use replication
> Sender and receiver process is showing the log sequence
> number.
> I assume that the log sequence is changing after every commit of an update or more updates.
> I assume that the archive log is changing after every commit of an update or
> more updates.
> 
> Going to test a PITR I need to know where to look for the log sequence number after PITR.

The log sequence number is not a good way to measure the success of PITR,
since after a successful PITR PostgreSQL will switch to a new time line,
and the log sequence will change.

Two ideas:
- Get the transaction ID with
    SELECT txid_current();
  You can use that with the "recovery_target_xid" parameter in recovery.conf.

- Use pg_create_restore_point() to create a restore point and use that with
  "recovery_target_name" in recovery.conf.

Yours,
Laurenz Albe

Re: Postgresql 9.5 committing and log sequence number

От
Poul Kristensen
Дата:
Thanks a lot for fast responce.

How come that repeating 
SELECT txid_current();
makes 1 added to xid
shown by 
select * from pg_last_committed_xact();

repeating  
select * from pg_last_committed_xact();
does not add 1 for each repeat of 
select * from pg_last_committed_xact();

2016-11-11 12:56 GMT+01:00 Albe Laurenz <laurenz.albe@wien.gv.at>:
Poul Kristensen wrote:
> I/we use replication
> Sender and receiver process is showing the log sequence
> number.
> I assume that the log sequence is changing after every commit of an update or more updates.
> I assume that the archive log is changing after every commit of an update or
> more updates.
>
> Going to test a PITR I need to know where to look for the log sequence number after PITR.

The log sequence number is not a good way to measure the success of PITR,
since after a successful PITR PostgreSQL will switch to a new time line,
and the log sequence will change.

Two ideas:
- Get the transaction ID with
    SELECT txid_current();
  You can use that with the "recovery_target_xid" parameter in recovery.conf.

- Use pg_create_restore_point() to create a restore point and use that with
  "recovery_target_name" in recovery.conf.

Yours,
Laurenz Albe




Re: Postgresql 9.5 committing and log sequence number

От
Albe Laurenz
Дата:
Poul Kristensen wrote:
> How come that repeating
> SELECT txid_current();
> 
> makes 1 added to xid
> shown by
> select * from pg_last_committed_xact();
> 
> 
> repeating
> 
> select * from pg_last_committed_xact();
> 
> does not add 1 for each repeat of
> select * from pg_last_committed_xact();

The documentation says about txid_current():

  get current transaction ID, assigning a new one if the current transaction
  does not have one

Since the query runs in ist own transaction, each invocation
will assign a new transaction ID.

Yours,
Laurenz Albe