Re: replication primary writting infinite number of WAL files

[Les]

Peter J. Holzer  (2023. nov. 26., V, 12:30):

>
> > nov 24 10:20:19 docker02 33d8b96b9062[1200]: 2023-11-24 10:20:19.691 CET
> [35] > LOG:  checkpoints are occurring too frequently (23 seconds apart)
> > nov 24 10:20:42 docker02 33d8b96b9062[1200]: 2023-11-24 10:20:42.938 CET
> [35] > LOG:  checkpoints are occurring too frequently (23 seconds apart)
>
> Interesting. If the database writes 1.5 GB/s of WALs and max_wal_size is
> the default of 1GB, shouldn't there be a checkpoint about every 0.7
> seconds instead of just every 22 seconds?
>
>
That log is from the beginning of the problem, 10:20. It started slowly,
then ramped up.

[image: image.png]

I'm not sure what happened at 10:32. At 10:38 there was the first server
restart + shut down all clients. After recovery at 10:40, writing again. At
10:44 we dropped the slot and it went down to 5MB/sec within one minute.

On the second occasion it was writing at 3GB/sec for some time, but then we
acted very quickly:

[image: image.png]

Network I/O was always about half of the disk I/O, very consistently (One
half of the data was going to one standby, the other half could not be sent
because of the slow network).

After dropping the slot, writing always went down within about one minute.
It was also very consistent, stopped exactly after dropping the slot.

After separating DEV and PROD networks completely, yesterday we have
created a new standby again (third try). No problems so far. I hope it will
remain that way. Still investigating the applications (pg clients), looking
for bugs.

Thank you for your help!

   Laszlo

Re: replication primary writting infinite number of WAL files

[Les]

>
>
> A single sequence for all id columns across all tables?
>
> How is the sequence value landing in the id column?
>
In most cases it is by using "nextval(seq_name)" in the SQL statement. But
sometimes the sequence value is taken first, and then multiple inserts are
sent with fixed increasing values. (Sometimes records reference each other,
such records are inserted within a short transaction with deferred foreign
key constraints.)

>
>> It might be possible to "select nextval" 1M times per second from an
> Pl/SQL function that does nothing else in an infinite loop. But that would
> not write too much data on the disk.
>
> On an old laptop:
>
> create table insert_test(id int);
>
> insert into insert_test select val from generate_series(1, 100) as
> t(val);
> INSERT 0 100
> Time: 943.918 ms
>
I would  say there is more then just the id being inserted, unless all the
> other fields allow NULL.
>
>
>
>>
>> And there is nothing in the logs in that time period besides "checkpoints
>> are happening too frequently"?
>>
>>
>
> The "set transaction" warning is due to a bug in an application that calls
> SET TRANSACTION before starting a transaction.
>
> And what is that app doing when it does SET TRANSACTION?
>
It is the main app that is using the database, using jdbc. Multiple
instances were running when the sequence jumped.

>
>
>
>> How are the snapshots being taken?
>>
> zfs snapshot is taken on the standby, then zfs clone is created on the
> snapshot, and a new postgresql instance is started on top of the clone. It
> recovers within one minute. In a very few cases (two or three times in a
> year), it fails to recover. Then we use a different snapshot. When the dev
> instance starts up, then we immediately delete everything from repmgr
> nodes, and disable repmgr completely. Today we noticed that the dev db was
> created in a network that made it possible to connect to the prod primary.
> (dev db's network was not separated from the prod db's network, fixed
> today). The dev db might have connected to the prod/primary after startup.
> But that dev instance was created 7 days ago, so probably it is not related.
>
> What I know about ZFS would fit in the navel of flea, so someone else will
> have to comment on this.
>
>
> Dev connected to prod/primary how?
>
When the dev db starts up (from the cloned data directory), it still has
the old repmgr conf. That config is deleted, and repmgr is disabled right
after the startup, but there is the possibility that the dev db has
connected the primary when it was cloned (7 days ago), because at the
beginning of startup, it is the exact clone of the standby from a previous
point of time.

   Laszlo

Re: replication primary writting infinite number of WAL files

[Les]

>
>
> >> Sequence is incremented by 100,  so for example, between 2023-11-24
> >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it
> >> is not possible to insert 62188671 rows into a table. A psql function
> >> might be able to increment a sequence 62M times / minute, I'm not sure.
>
> Am I correct in assuming id has as it's default nextval()?
>
All primary keys are "id int8 NOT NULL". They don't have a default, but all
of these identifiers are generated from the same (global) sequence. We are
using a single global sequence, because there are only a few writes. 99% of
I/O operations are read.

>
> If so it would seem to me something was doing a lot of INSERTS between
> 2023-11-24 10:20:00.000 and 2023-11-24 10:21:00.000.
>
> Yes. But is it possible to insert 62M rows within 1 minute?

It might be possible to "select nextval" 1M times per second from an Pl/SQL
function that does nothing else in an infinite loop. But that would not
write too much data on the disk.


>
> And there is nothing in the logs in that time period besides "checkpoints
> are happening too frequently"?
>
>
Here are all the logs we have from that minute.

╰─# journalctl CONTAINER_ID=33d8b96b9062 --since '2023-11-24 10:18:00'
--until '2023-11-24 10:21:00.000'
nov 24 10:18:02 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:02.836 CET
[296607] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:02 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:02.911 CET
[286342] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:02 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:02.990 CET
[286342] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:03 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:03.150 CET
[296607] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:03 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:03.733 CET
[294644] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:03 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:03.741 CET
[294644] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:04 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:04.517 CET
[35] LOG:  checkpoints are occurring too frequently (22 seconds apart)
nov 24 10:18:04 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:04.517 CET
[35] HINT:  Consider increasing the configuration parameter "max_wal_size".
nov 24 10:18:06 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:06.831 CET
[294595] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:07 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:07.096 CET
[294595] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:07 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:07.541 CET
[271126] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:07 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:07.549 CET
[271126] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:17 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:17.335 CET
[293994] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:17 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:17.930 CET
[293994] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:18 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:18.743 CET
[271126] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:18 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:18.750 CET
[271126] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:20 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:20.612 CET
[296546] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:20 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:20.622 CET
[286567] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:20 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:20.623 CET
[286567] ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any
query
nov 24 10:18:20 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:20.623 CET
[286567] STATEMENT:  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
nov 24 10:18:20 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:20.915 CET
[296546] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:21 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:21.706 CET
[296760] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:21 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:21.890 CET
[296760] WARNING:  SET TRANSACTION can only be used in transaction blocks
nov 24 10:18:26 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:26.776 CET
[35] LOG:  checkpoints are occurring too frequently (22 seconds apart)
nov 24 10:18:26 docker02 33d8b96b9062[1200]: 2023-11-24 10:18:26.776 CET
[35] HINT:  Consider increasing the configuration parameter "max_wal_size".
nov 24 10:18:33 docker02 33d8b96b9062[1200]: 2023-11-2

Re: replication primary writting infinite number of WAL files

[Les]

>
>
>
> I have no explanation for that, except a coincidence.
> Replication slots don't generate WAL.
>
The problem stopped exactly when I dropped the slot. It happened three
times in a row. :-(

Re: replication primary writting infinite number of WAL files

[Les]

Laurenz Albe   (2023. nov. 24., P, 16:00):

> On Fri, 2023-11-24 at 12:39 +0100, Les wrote:
> > Under normal circumstances, the number of write operations is relatively
> low, with an
> > average of 4-5 MB/sec total write speed on the disk associated with the
> data directory.
> > Yesterday, the primary server suddenly started writing to the pg_wal
> directory at a
> > crazy pace, 1.5GB/sec, but sometimes it went up to over 3GB/sec.
> > [...]
> > Upon further analysis of the database, we found that we did not see any
> mass data
> > changes in any of the tables. The only exception is a sequence value
> that was moved
> > millions of steps within a single minute.
>
> That looks like some application went crazy and inserted millions of rows,
> but the
> inserts were rolled back.  But it is hard to be certain with the clues
> given.
>

Writing of WAL files continued after we shut down all clients, and
restarted the primary PostgreSQL server.

The order was:

1. shut down all clients
2. stop the primary
3. start the primary
4. primary started to write like mad again
5. removed replication slot
6. primary stopped madness and deleted all WAL files (except for a few)

How can the primary server generate more and more WAL files (writes) after
all clients have been shut down and the server was restarted? My only bet
was the autovacuum. But I ruled that out, because removing a replication
slot has no effect on the autovacuum (am I wrong?). Now you are saying that
this looks like a huge rollback. Does rolling back changes require even
more data to be written to the WAL after server restart? As far as I know,
if something was not written to the WAL, then it is not something that can
be rolled back. Does removing a replication slot lessen the amount of data
needed to be written for a rollback (or for anything else)? It is a fact
that the primary stopped writing at 1.5GB/sec the moment we removed the
slot.

I'm not saying that you are wrong. Maybe there was a crazy application. I'm
just saying that a crazy application cannot be the whole picture. It cannot
explain this behaviour as a whole. Or maybe I have a deep misunderstanding
about how WAL files work.  On the second occasion, the primary was running
for a few minutes when pg_wal started to increase. We noticed that early,
and shut down all clients, then restarted the primary server. After the
restart, the primary was writing out more WAL files for many more minutes,
until we dropped the slot again. E.g. it was writing much more data after
the restart than before the restart; and it only stopped (exactly) when we
removed the slot.

Regards,

   Laszlo

Re: replication primary writting infinite number of WAL files

[Les]

>
> > First I was also thinking about vacuum.  But removing a replication
> > slot should have no effect on vacuum on the primary (AFAIK). Please
> > correct me if I'm wrong.
> >
>
> yeah, depends. there are 2 processes:
>
> * 1 process generating the wal's, maybe a VACUUM
> * an inactive slot holding the wals
>
> For instance, if a standby not reachable the wal's will accumulated
> within the slot, till the standby is reachable again.
>

I understand that an unreachable standby can cause WAL files accumulated in
the pg_wal directory. This has happened before, and it is expected. What I
don't get is the amount and the speed. Write speed went up from the normal
5MB/sec to 1500MB/sec within a minute. When the slot was removed,  it went
down to normal again. We could have easily solved the problem of a
disconnected standby, because free disk space is monitored. But in this
case, there was not enough time to react. PostgreSQL filled up the
remaining 40% free disk space in a matter of minutes. By the time we got
the alert message and logged into the server, it was already too late, the
disk was full.

There is a strong correlation between the speed/amount of data written, and
the existence of that replication slot. If we drop the slot, then write
speed goes down immediately. If we add that slot again, then after some
time the problem comes back. (All I can say is that it happened three
times.) Interestingly, it does not happen with the other standby - that one
is still connected, and works flawlessly. I don't know of any normal
PostgreSQL mechanism that could cause this behaviour. We already ruled out
client applications, because all client apps were shut down, volume size
increased and then PostgreSQL restarted, but did not solve the problem.

   Laszlo

Re: replication primary writting infinite number of WAL files

[Les]

Andreas Kretschmer  wrote (2023. nov. 24., P,
13:22):

>
>
> Am 24.11.23 um 12:39 schrieb Les:
> >
> > Hello,
> >
>
> please check the database log, a VACUUM can also lead to massive wal
> generation. Can you find other related messages? by the way, the picture
> is hard to read, please post text instead of pictures.
>

First I was also thinking about vacuum.  But removing a replication slot
should have no effect on vacuum on the primary (AFAIK). Please correct me
if I'm wrong. And yet, removing the replication slot solved the problem
immediately, including write speed (went down from 1.5GB/sec to 5MB/sec)
and pg_wal directory size (PostgreSQL deleted 100G+ files within a few
minutes, only a single WAL segment remained in pg_wal). This is not by
coincidence. This happened three times, and in all cases, dropping the slot
was the only thing that "solved" the problem. Maybe the slot itself was not
the direct cause, but it is strongly correlated. Unfortunately, it would be
problematic to try this out again. We could only observe the problem on the
prod server, never on the dev servers; and we can't play with the prod
servers.

I'm sorry about the picture, I did not have this in plain text, only on a
screenshot. (I still have the WAL files, but not the full data directory,
and I don't know how to examine the pg_wal directory without the containing
data directory. pg_waldump requires a PGDATA directory.)

   Laszlo

pgsql --echo-errors --quiet and setval

[Les]

  Dear fellow PostgreSQL users,

Today I caught this with postgresql v15, while restoring a database with
psql  --echo-errors --quiet. (The dump was made using pg_dump -Fp).

It logged lots of these messages:

 setval

   1001
(1 row)

In other words, it logs **some** things that are not errors, even though
--quiet was specified. Is this the expected behaviour? I would argue that
with --quiet --echo-errors, only errors should be logged, and setting the
value of a sequence is not an error.

Thank you,

Laszlo

pg_restore unexpected end of file

[Les]

I'm trying to migrate a database from version 11 to version 15.

I have created a dump file on v11:

pg_dump --dbname=not_telling -Fc --no-owner > dump

Then I was trying to import it on v15:

pg_restore -v --dbname=not_telling --no-user /restore/dump
pg_restore: error: could not read from input file: end of file

The file is 525MB. First I thought that maybe the dump is corrupt, so I
re-dumped the db again, but I got the same results. pg_dump does not report
any error, but pg_restore cannot import it.

I can try to export into SQL format, but there is another (much larger)
database that I need to migrate, and it has large binary blobs inside. I
would prefer the custom format.

Is the custom dump format of v11 compatible with v15?

Thank you,

   Laszlo

Re: invalid value for parameter "default_text_search_config": "public.pg"

[Les]

>
> > I'm not sure what applied="public.pg", error="setting could not be
> applied"
> > means.
>
> No, "applied" is "f" (false), meaning that the setting is not actually
> usable.
> I'm a little surprised that it seems to have gotten into your live session
> anyway, although perhaps that's because the postmaster can't really
> validate it at startup.
>
> > I can change it in the config file, no problem. I just would like to
> > know if this is a simple configuration error, or a software installation
> > error. (Is public.pg a built-in config that should always exist?)
>
> "public.pg" is certainly not a standard (as in built-in) text search
> configuration name.  Maybe that value was installed by some bit of
> software that failed to install the configuration to go with it,
> or only put the configuration into one database not everywhere in
> your installation?
>

All right, then it means that this is a configuration error, and it is not
a problem with the database instance and its live data. That was my main
concern. I'll just simply change this in the conf file and reload the
config.

This is all I needed, thank you for your help!

   Laszlo

Re: invalid value for parameter "default_text_search_config": "public.pg"

[Les]

>
> However, the parallel worker should just be absorbing the same
> configuration settings your main session is using.  So what remains
> to be explained is why you aren't seeing the same complaint when
> starting a fresh session.  It might be useful to look at the
> output of
>
> show default_text_search_config;
>
> and
>
> select * from pg_file_settings where name = 'default_text_search_config';
>
> psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.

mydatabase=# show default_text_search_config;
 default_text_search_config

 public.pg
(1 row)

mydatabase=# select * from pg_file_settings where name =
'default_text_search_config';
 sourcefile | sourceline | seqno |
   name|  setting  | applied |error
++---++---+-+--
 /opt/postgresql/data/conf.d/06_locale.conf | 17 |24 |
default_text_search_config | public.pg | f   | setting could not be
applied
(1 row)

mydatabase=#

I'm not sure what applied="public.pg", error="setting could not be applied"
means. I can change it in the config file, no problem. I just would like to
know if this is a simple configuration error, or a software installation
error. (Is public.pg a built-in config that should always exist?)

Regards,

 Laszlo

Re: invalid value for parameter "default_text_search_config": "public.pg"

[Les]

>> > SQL Error [22023]: ERROR: invalid value for parameter
>> > "default_text_search_config": "public.pg "
>> >Where: while setting parameter "default_text_search_config" to
>> > "public.pg " parallel worker
>>
>> What client are you using to run the commands?
>>
>> I just checked and it also throws this error when I execute the following
>> SELECT from plain psql:
>
>
>  select
> pft.id as file_type_id,
> pft.code as file_type_code,
> pfs.id as file_status_id,
> pfs.code as file_status_code,
> count(pf.id) as cnt
> from
> product.product_file pf
> inner join product.product p on p.id=pf.product_id
>
> inner join product.product_file_type pft  on
> pft.id = pf.product_file_type_id
> inner join product.product_file_status pfs  on
> pfs.id = pf.product_file_status_id
> group by
> pft.id, pfs.id;
> ERROR:  invalid value for parameter "default_text_search_config": "
> public.pg"
> CONTEXT:  while setting parameter "default_text_search_config" to "
> public.pg"
> parallel worker
>
> After reading the documentation here:

https://www.postgresql.org/docs/current/textsearch-configuration.html

I found out that "public.pg" is not a valid configuration indeed. Here is
how it looks in this db:

mydatabase=#
mydatabase=# \dF
   List of text search configurations
   Schema   |Name|  Description
++---
 pg_catalog | arabic | configuration for arabic language
 pg_catalog | armenian   | configuration for armenian language
 pg_catalog | basque | configuration for basque language
 pg_catalog | catalan| configuration for catalan language
 pg_catalog | danish | configuration for danish language
 pg_catalog | dutch  | configuration for dutch language
 pg_catalog | english| configuration for english language
 pg_catalog | finnish| configuration for finnish language
 pg_catalog | french | configuration for french language
 pg_catalog | german | configuration for german language
 pg_catalog | greek  | configuration for greek language
 pg_catalog | hindi  | configuration for hindi language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language
 pg_catalog | irish  | configuration for irish language
 pg_catalog | italian| configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language
 pg_catalog | nepali | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian| configuration for russian language
 pg_catalog | serbian| configuration for serbian language
 pg_catalog | simple | simple configuration
 pg_catalog | spanish| configuration for spanish language
 pg_catalog | swedish| configuration for swedish language
 pg_catalog | tamil  | configuration for tamil language
 pg_catalog | turkish| configuration for turkish language
 pg_catalog | yiddish| configuration for yiddish language
(29 rows)

mydatabase=# SET default_text_search_config = 'pg_catalog.english';
SET
mydatabase=#

After setting it to pg_catalog.english, it works.

But I still don't know how it affects a query that does not use any
tsvector or tsquery. In fact, tsvector and tsquery types are not used in
any of my tables at all.


>
>

Re: invalid value for parameter "default_text_search_config": "public.pg"

[Les]

>
>
> > SQL Error [22023]: ERROR: invalid value for parameter
> > "default_text_search_config": "public.pg "
> >Where: while setting parameter "default_text_search_config" to
> > "public.pg " parallel worker
>
> What client are you using to run the commands?
>
> I just checked and it also throws this error when I execute the following
> SELECT from plain psql:


 select
pft.id as file_type_id,
pft.code as file_type_code,
pfs.id as file_status_id,
pfs.code as file_status_code,
count(pf.id) as cnt
from
product.product_file pf
inner join product.product p on p.id=pf.product_id
inner join product.product_file_type pft  on pft.id
= pf.product_file_type_id
inner join product.product_file_status pfs  on
pfs.id = pf.product_file_status_id
group by
pft.id, pfs.id;
ERROR:  invalid value for parameter "default_text_search_config": "public.pg
"
CONTEXT:  while setting parameter "default_text_search_config" to "public.pg
"
parallel worker

Re: invalid value for parameter "default_text_search_config": "public.pg"

[Les]

Version:

PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Extensions installed:

oid  |extname
|extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
-++++--+--+-++
13540|plpgsql |  10|  11|false |1.0   |NULL
|NULL|
25139|pgcrypto|   16385|2200|true  |1.3   |NULL
|NULL|
25193|hstore  |   16385|2200|true  |1.8   |NULL
|NULL|



David G. Johnston  ezt írta (időpont: 2023.
júl. 21., P, 7:41):

> On Thursday, July 20, 2023, Les  wrote:
>
>> I try to execute this on a very simple table, in a production database:
>>
>
> Please provide version information and any extensions you may have
> installed.
>
> David J.
>
>

invalid value for parameter "default_text_search_config": "public.pg"

[Les]

I try to execute this on a very simple table, in a production database:

alter table product.product add proc_order int8;
create index idx_product_proc_order_status on product.product(proc_order
nulls last, product_status_id /* this has type uuid */ );

and I get this error:

SQL Error [22023]: ERROR: invalid value for parameter
"default_text_search_config": "public.pg"
  Where: while setting parameter "default_text_search_config" to "public.pg"
parallel worker

I have been using this database for many weeks now, and never got a similar
message. I don't understand how the creation of an index on an int8 and an
uuid field is related to full text search.

Thanks,

Laszlo

Re: foreign keys on multiple parent table

[Les]

>
> .
>


> From programming point of view and also to reduce the number of objects in
> DB could be convinient create just an audit table with a structure like:
>
>- auditi id
>- reference_uuid (the key of the main table)
>- table_name
>- list of audit data
>
>
Could work, but is there a way to set a reference key over the uuid of all
> the tables?
>

For existing solution, check out
https://github.com/2ndQuadrant/audit-trigger

Regarding fk constraints, a single fk constraint can only reference the
primary key of a single table.

But, if you want to be serious about audit logs, then you need to keep logs
of deletions too, and for those, foreign key constraints would not work
anyway.

You may also want to consider bulk insert speed. Foreign key constraint
checking can reduce speed.

  Laszlo






>

how to return data from insert into ... on conflict ... returning ... into

[Les]

Consider this example


drop table if exists tbl;

drop sequence if exists seq;

create sequence seq;

create table tbl(

id int8 not null primary key,

d bytea not null,

h bytea not null, -- hash of the data, calculated automatically

dummy byte default 0 -- dummy value, see below...

);

alter table tbl add constraint uidx_tbl_h unique(h);


create or replace function trg() returns trigger language plpgsql as

$function$

begin

new.h = sha256(new.d); -- auto-hash

if new.id is null then

new.id = nextval('seq');

end if;

return new;

end;

$function$;

create trigger trg before insert or update on tbl for each row execute
procedure trg();

The  hash "h" is calculated automatically for each data value "d", and it
is a unique value. Let's suppose that for technical reasons, we want the
primary key to be an int8 value, and foreign keys in other tables will be
referencing tbl records using the sequentially generated tbl.id values.

The basic idea is that for already existing "d" values, we do not insert a
new record, but use the existing record and its identifier.

This code actually works:

do

$body$

declare

aid int8;

begin

insert into tbl(d) values ('1') returning id into aid; -- ok

raise notice '1->%', aid; -- ok

insert into tbl(d) values ('2') returning id into aid; -- ok

raise notice '2->%', aid; -- ok

insert into tbl(d) values ('1') on conflict(h) do update set dummy=0
returning id into aid; -- ok;

raise notice '1->%', aid; -- ok

insert into tbl(d) values ('2') on conflict(h) do update set dummy=0
returning id into aid; -- ok;

raise notice '2->%', aid; -- ok

end;

$body$;

It will display the same id value for the same data values. But it updates
the record even when it does not need to be updated.

This code below does not work:

delete from tbl;

do

$body$

declare

aid int8;

begin

insert into tbl(d) values ('1') returning id into aid; -- ok

raise notice '1->%', aid; -- ok

insert into tbl(d) values ('2') returning id into aid; -- ok

raise notice '2->%', aid; -- ok

insert into tbl(d) values ('1') on conflict(h) do nothing returning id into
aid; -- ok;

raise notice '1->%', aid; -- null ??

insert into tbl(d) values ('1') on conflict(h) do nothing returning id into
aid; -- ok;

raise notice '2->%', aid; -- null ??

end;

$body$;

First it displays two non-null identifiers, then it displays two NULL
values.

The alternative would be something like this:


delete from tbl;

do

$body$

declare

aid int8;

begin

insert into tbl(d) values ('1') returning id into aid; -- ok

raise notice '1->%', aid; -- ok

insert into tbl(d) values ('2') returning id into aid; -- ok

raise notice '2->%', aid; -- ok

select id from tbl into aid where h = sha256('1');

if not found then

insert into tbl(d) values ('1') on conflict(h) do update set id=id+0
returning id into aid;

end if;

raise notice '1->%', aid; -- null ??

select id from tbl into aid where h = sha256('2');

if not found then

insert into tbl(d) values ('2') on conflict(h) do update set id=id+0
returning id into aid;

end if;

raise notice '2->%', aid; -- null ??

end;

$body$;


But there are several problems with this "solution":

1. Running select to check for existence introduces a race condition. Of
course it also depends on the transaction isolation, but in general it is
not guaranteed that the insert won't fail with the unique constraint on h,
even if the select did not find a matching record. (It might also introduce
a deadlock?) I might be wrong on this, I don't really know how plpgsql
procedures are executed, but I suspect that they can run in parallel.
2. Notice how the code changed. The calculation of h is done in the trigger
and also at two other places. This was a very trivial example, but in a
real world scenario, the calculation can be costly, and even if the above
solution works, it must calculate the possibly conflicting values twice. It
is ineffective, and it also requires to factor out the calculations to
separate functions (or even worse, duplicate the code for the
calculations). Even if calculations are not costly, this "solution" may
introduce a dependency hell, because the trg() trigger can access field
values that are calculated by other triggers that depend on each other. One
would have to pre-calculate everything at every place where "insert into"
is needed for the table, duplicating code and/or factoring the calculations
out to a function with many parameters.
3. This trivial example only had a single unique constraint, but there
could be more. When you have many unique constraints, then you have to
write multiple SELECT statements to check for existence before doing the
INSERT. Then the above "solution" becomes ugly and questionable.

Please note that adding a dummy byte does not solve the problem, because it
will always update the record, even if it does not need to be updated.
Degrades performance, possibly executes other triggers that do unwanted
modifications to the database.

I have r

Re: possible bug

[Les]

> Which PG version is this exactly?  Given the Memoize node shown
> in your plan, I suppose 14.something, but is it up to date?
> There were Memoize-related bug fixes in 14.2 and 14.4, and the
> one in 14.2 looks particularly likely to be relevant.
>
> If you are on the current minor release, does "set enable_memoize = off"
> change the behavior?
>
As always, you hit the nail on the head. set enable_memoize = on fixes the
problem!

Version is PostgreSQL 14.1, time to upgrade...

I'm sorry that I wasted your time.

Re: possible bug

[Les]

>
>
>
> So what happens if query the table directly?:
>
> select * from wf.workflow  where head_table_id::float8::int8 = 25408438504;
>
> vs
>
> select * from wf.workflow  where head_table_id = 25408438504;
>
>
Both return lots of rows. The same number of rows.

select count(*) from wf.workflow  where head_table_id::float8::int8 =
25408438504;
count|
-+
62260|

select count(*) from wf.workflow  where head_table_id = 25408438504;
count|
-+
62260|

Both of them use seq scan.

QUERY PLAN |
---+
Aggregate  (cost=2985.00..2985.01 rows=1 width=8)  |
  ->  Seq Scan on workflow  (cost=0.00..2829.07 rows=62369 width=0)|
Filter: (head_table_id = '25408438504'::bigint)|

QUERY PLAN
  |
-+
Aggregate  (cost=3289.86..3289.87 rows=1 width=8)
 |
  ->  Seq Scan on workflow  (cost=0.00..3288.70 rows=460 width=0)
 |
Filter: (((head_table_id)::double precision)::bigint =
'25408438504'::bigint)|

Re: possible bug

[Les]

Not that I know of.

I just tried this:

reindex table kap.course;
reindex table kap.course_user;
reindex table wf.workflow;
reindex table kap.training_type;

But it is still wrong.



Adrian Klaver  ezt írta (időpont: 2022. okt.
21., P, 19:57):

> On 10/21/22 10:50 AM, Les wrote:
> > Hello,
> >
> > We had a support request today, and we have narrowed down the problem to
> > a query that behaves very strangely. The actual query was much more
> > complicated, but I came up with this minimal example.
> >
> > This is what we have seen inside our application:
> >
> > select * from test where id in (26643094740, 26437091668);
> >
> > id |code|regno|col_3|
> > ---++-+-+
> > 26437091668|TA-T| 2632|1|
> > 26643094740|PEG | 2905|1|
> >
> > select * from test where id = 26643094740;
> >
> > id |code|regno|col_3|
> > ---++-+-+
> > 26643094740|PEG | 2905|0|
> >
> > The problem: value of col_3 changes for id=26643094740 if I query two
> > rows vs. one row. This is without changing any data. The problem is 100%
> > repeatable, if I query two rows from the same view, then I get different
> > data for one of the rows.
> >
> > I suspect that this is a bug. But I might be wrong. Please help me!
>
> I suspect an index problem. Have you tried reindexing the source table,
> kap.course if I am following correctly.
>
> Have there been any issues with the database lately, e.g. crash or other
> significant event?
>
> >
> > The actual test view looks like this:
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: possible bug

[Les]

One of my colleagues pointed out, that they query returns a different
result, if I cast the head_table_id condition to float8 and then back to
int8.

SELECT
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND
w_1.head_table_id::float8::int8 = 25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id =
25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id
where c.id in (26437091668, 26643094740)

Returns:

id |code|regno|col_3|
---++-+-+
26437091668|TA-T| 2632|1|
26643094740|PEG | 2905|0|

Although all identifier columns are defined as int8.

Les  ezt írta (időpont: 2022. okt. 21., P, 19:50):

> Hello,
>
> We had a support request today, and we have narrowed down the problem to a
> query that behaves very strangely. The actual query was much more
> complicated, but I came up with this minimal example.
>
> This is what we have seen inside our application:
>
> select * from test where id in (26643094740, 26437091668);
>
> id |code|regno|col_3|
> ---++-+-+
> 26437091668|TA-T| 2632|1|
> 26643094740|PEG | 2905|1|
>
> select * from test where id = 26643094740;
>
> id |code|regno|col_3|
> ---++-+-+
> 26643094740|PEG | 2905|0|
>
> The problem: value of col_3 changes for id=26643094740 if I query two rows
> vs. one row. This is without changing any data. The problem is 100%
> repeatable, if I query two rows from the same view, then I get different
> data for one of the rows.
>
> I suspect that this is a bug. But I might be wrong. Please help me!
>
> The actual test view looks like this:
>
> create view test as
> SELECT
> c.id,
> tt.code,
> c.regno,
> (
> select count(*)
> FROM kap.course_user cu
> JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id
> = 25408438504
> where cu.is_active AND cu.course_id = c.id AND w_1.station_id =
> 25406740434
> ) AS col_3
> FROM
> kap.course c
> INNER JOIN kap.training_type tt ON tt.id = c.training_type_id;
>
> Below are some DDL s (simplified, the actual tables contain much more
> fields).
>
>
> Do you think that this might be a bug? If not, then can somebody please
> explain how this can happen?
>
>Laszlo
>
> explain analyze select * from test where id in (26643094740, 26437091668);
>
> QUERY PLAN
>
> |
>
> ---+
> Hash Join  (cost=16.08..124.99 rows=2 width=29) (actual time=0.067..0.107
> rows=2 loops=1)
>  |
>   Hash Cond: (tt.id = c.training_type_id)
>
>  |
>   ->  Seq Scan on training_type tt  (cost=0.00..12.71 rows=71 width=13)
> (actual time=0.004..0.033 rows=71 loops=1)
> |
>   ->  Hash  (cost=16.05..16.05 rows=2 width=24) (actual time=0.024..0.025
> rows=2 loops=1)
>  |
> Buckets: 1024  Batches: 1  Memory Usage: 9kB
>
> |
> ->  Bitmap Heap Scan on course c  (cost=8.58..16.05 rows=2
> width=24) (actual time=0.018..0.021 rows=2 loops=1)
> |
>   Recheck Cond: (id = ANY
> ('{26643094740,26437091668}'::bigint[]))
>   |
>   Heap Blocks: exact=2
>
> |
>   ->  Bitmap Index Scan on pk_course  (cost=0.00..8.58 rows=2
> width=0) (actual time=0.012..0.012 rows=2 loops=1)
>   |
> Index Cond: (id = ANY
> ('{26643094740,26437091668}'::bigint[]))
>   |
>   SubPlan 1
>
>  |
> ->  Aggregate  (cost=47.91..47.92 rows=1 width=8) (actual
> time=0.014..0.014 rows=1 loops=2)
>  |
>   ->  Nested Loop  (cost=0.59..47.90 rows=1 width=0) (actual
> time=0.010..0.012 rows=1 loops=2)
>   |
> ->  Index Scan using workflow_idx_station on workflow w_1
>  (cost=0.29..22.90 rows=3 width=8) (actual time=0.005..0.007 rows=1
> loops=2) |
>   Index Cond: (station_id = '25406740434'::bigint)
>
> |
>   Filter: (head_table_id = '25408438504'::bigint)
>
>  |
> ->  Memoize  (cost=0.30..8.32 rows=1 width=8) (actual
> time=0.004..0.004 rows=1 loops=2)
>  |
>   Cache Key: w_1.rec_id
>
>  |
>   Hits: 1  Misses: 1  Evictions: 0  Overflows: 0
>  Memory Usage: 1kB
>|
>   -

possible bug

[Les]

Hello,

We had a support request today, and we have narrowed down the problem to a
query that behaves very strangely. The actual query was much more
complicated, but I came up with this minimal example.

This is what we have seen inside our application:

select * from test where id in (26643094740, 26437091668);

id |code|regno|col_3|
---++-+-+
26437091668|TA-T| 2632|1|
26643094740|PEG | 2905|1|

select * from test where id = 26643094740;

id |code|regno|col_3|
---++-+-+
26643094740|PEG | 2905|0|

The problem: value of col_3 changes for id=26643094740 if I query two rows
vs. one row. This is without changing any data. The problem is 100%
repeatable, if I query two rows from the same view, then I get different
data for one of the rows.

I suspect that this is a bug. But I might be wrong. Please help me!

The actual test view looks like this:

create view test as
SELECT
c.id,
tt.code,
c.regno,
(
select count(*)
FROM kap.course_user cu
JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id =
25408438504
where cu.is_active AND cu.course_id = c.id AND w_1.station_id =
25406740434
) AS col_3
FROM
kap.course c
INNER JOIN kap.training_type tt ON tt.id = c.training_type_id;

Below are some DDL s (simplified, the actual tables contain much more
fields).


Do you think that this might be a bug? If not, then can somebody please
explain how this can happen?

   Laszlo

explain analyze select * from test where id in (26643094740, 26437091668);

QUERY PLAN

|
---+
Hash Join  (cost=16.08..124.99 rows=2 width=29) (actual time=0.067..0.107
rows=2 loops=1)
 |
  Hash Cond: (tt.id = c.training_type_id)
   |
  ->  Seq Scan on training_type tt  (cost=0.00..12.71 rows=71 width=13)
(actual time=0.004..0.033 rows=71 loops=1)
|
  ->  Hash  (cost=16.05..16.05 rows=2 width=24) (actual time=0.024..0.025
rows=2 loops=1)
 |
Buckets: 1024  Batches: 1  Memory Usage: 9kB

|
->  Bitmap Heap Scan on course c  (cost=8.58..16.05 rows=2
width=24) (actual time=0.018..0.021 rows=2 loops=1)
|
  Recheck Cond: (id = ANY
('{26643094740,26437091668}'::bigint[]))
  |
  Heap Blocks: exact=2

|
  ->  Bitmap Index Scan on pk_course  (cost=0.00..8.58 rows=2
width=0) (actual time=0.012..0.012 rows=2 loops=1)
  |
Index Cond: (id = ANY
('{26643094740,26437091668}'::bigint[]))
  |
  SubPlan 1
   |
->  Aggregate  (cost=47.91..47.92 rows=1 width=8) (actual
time=0.014..0.014 rows=1 loops=2)
 |
  ->  Nested Loop  (cost=0.59..47.90 rows=1 width=0) (actual
time=0.010..0.012 rows=1 loops=2)
  |
->  Index Scan using workflow_idx_station on workflow w_1
 (cost=0.29..22.90 rows=3 width=8) (actual time=0.005..0.007 rows=1
loops=2) |
  Index Cond: (station_id = '25406740434'::bigint)

|
  Filter: (head_table_id = '25408438504'::bigint)
   |
->  Memoize  (cost=0.30..8.32 rows=1 width=8) (actual
time=0.004..0.004 rows=1 loops=2)
 |
  Cache Key: w_1.rec_id
   |
  Hits: 1  Misses: 1  Evictions: 0  Overflows: 0
 Memory Usage: 1kB
   |
  ->  Index Scan using pk_course_user on course_user cu
 (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)|
Index Cond: (id = w_1.rec_id)
   |
Filter: (is_active AND (course_id = c.id))

|
Planning Time: 0.527 ms
   |
Execution Time: 0.175 ms

|

explain analyze select * from test where id in (26643094740)

QUERY PLAN

|
---+
Nested Loop  (cost=0.42..64.60 rows=1 width=29) (actual time=0.033..0.035
rows=1 loops=1)
 |
  ->  Index Scan using pk_course on course c  (cost=0.28..8.30 rows=1
width=24) (actual time=0.007..0.008 rows=1 loops=1)
 |
Index Cond: (id = '26643094740'::bigint)

|
  ->  Index Scan using pk_training_type on training_type tt
 (cost=0.14..8.16 rows=1 width=13) (actual time=0.002..0.002 rows=1
loops=1)   |
Index Cond: (id = c.training_type_id)
   

RE: WAL File Recovery on Standby Server Stops Before End of WAL Files

[Ryan, Les]

Hi Kyotaro and Dilip,

Thank you for getting back to me.

Kyotaro: I ran pg_dump and the output was "pg_waldump: fatal: could not read 
file "00010419005A": read 50 of 8192".  I'm guessing that it means 
that wal file 00010419005A is corrupted and that is why the 
recovery process stops there.  Is there any way to fix the file?

Dilip:   setting the log level to debug2 did not provide any additional 
information.  Here are the log entries:

2021-10-28 06:51:06.166 MDT [7556] LOG:  restored log file 
"000104190059" from archive
2021-10-28 06:51:06.464 MDT [7556] DEBUG:  got WAL segment from archive
2021-10-28 06:51:06.579 MDT [7556] LOG:  restored log file 
"00010419005A" from archive
2021-10-28 06:51:06.854 MDT [7556] DEBUG:  got WAL segment from archive
2021-10-28 06:51:07.107 MDT [7556] LOG:  consistent recovery state reached at 
419/5AB8
2021-10-28 06:51:07.107 MDT [7556] DEBUG:  switched WAL source from archive to 
stream after failure
2021-10-28 06:51:07.109 MDT [7844] LOG:  database system is ready to accept 
read only connections
2021-10-28 06:51:07.152 MDT [7844] DEBUG:  forked new backend, pid=6900 
socket=6068

I set the log level to debug5 and here is what I got:

2021-10-28 06:25:41.262 MDT [6288] CONTEXT:  WAL redo at 419/5ABFFF60 for 
Btree/INSERT_LEAF: off 130
2021-10-28 06:25:41.262 MDT [6288] DEBUG:  record known xact 33776257 
latestObservedXid 33776257
2021-10-28 06:25:41.262 MDT [6288] CONTEXT:  WAL redo at 419/5ABFFFA0 for 
Heap/INSERT: off 95 flags 0x00
2021-10-28 06:25:41.262 MDT [6288] LOG:  consistent recovery state reached at 
419/5AB8
2021-10-28 06:25:41.263 MDT [6288] DEBUG:  switched WAL source from archive to 
stream after failure
2021-10-28 06:25:41.264 MDT [5512] LOG:  database system is ready to accept 
read only connections

Does the "switched WAL source from archive to stream after failure" indicate a 
problem with the WAL file?

Anyway, it looks like I need to restore the standby server from a new backup.  
Thank you both for your help.

Sincerely,
-Les

-Original Message-
From: Dilip Kumar 
Sent: October 27, 2021 10:29 PM
To: Kyotaro Horiguchi 
Cc: Ryan, Les ; pgsql-generallists.postgresql.org 

Subject: Re: WAL File Recovery on Standby Server Stops Before End of WAL Files

On Thu, Oct 28, 2021 at 7:28 AM Kyotaro Horiguchi  
wrote:
>
> At Wed, 27 Oct 2021 16:42:52 +, "Ryan, Les" 
> wrote in
> > 2021-10-27 10:26:31.467 MDT [2012] LOG:  redo starts at 419/5229A858
> ...
> > 2021-10-27 10:26:36.188 MDT [2012] LOG:  restored log file
> > "00010419005A" from archive
> > 2021-10-27 10:26:36.750 MDT [2012] LOG:  consistent recovery state
> > reached at 419/5AB8
> > 2021-10-27 10:26:36.752 MDT [6204] LOG:  database system is ready to
> > accept read only connections
> > 2021-10-27 10:26:36.823 MDT [6040] LOG:  started streaming WAL from
> > primary at 419/5A00 on timeline 1
> >
> >   *   There are many more WAL files available starting with 
> > 00010419005B but the restore process always stops at 
> > 00010419005A.
> >
> > I have two questions:
> >
> >   *   Why does the WAL file recovery process now stop after it reads 
> > 00010419005A?
> >   *   What do I need to do to get PostgreSQL to recover the rest of the 
> > available WAL files.
>
> The info above alone donesn't clearly suggest anything about the
> reason. Could you show the result of "pg_waldump
> 00010419005A 2>&1 | tail -5"?  What I'm expecting to see
> is an error message from pg_waldump before the end of the file. It
> would be the immediate cause of the problem.

+1, that will be the best place to start with, additionally, you can
enable DEBUG2 message so that from logs we can identify why it could not 
continue recovery from the archive.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com




NOTICE: This communication and any attachments ("this message") may contain 
information which is privileged, confidential, proprietary or otherwise subject 
to restricted disclosure under applicable law. This message is for the sole use 
of the intended recipient(s). Any unauthorized use, disclosure, viewing, 
copying, alteration, dissemination or distribution of, or reliance on, this 
message is strictly prohibited. If you have received this message in error, or 
you are not an authorized or intended recipient, please notify the sender 
immediately by replying to this message, delete this message and all copies 
from your e-mail system and destroy any printed copies. You are receiving this 
communication because you are listed as a current WSP contact. Should you have 
any quest

WAL File Recovery on Standby Server Stops Before End of WAL Files

[Ryan, Les]

Hello,

I'm hoping to get some suggestions on what to do here.  I am running PostgreSQL 
version 13.2 and am shipping the WAL files to a standby server.  Once a day I 
restart the standby server and it recovers the new WAL files that have been 
shipped to it.  Everything was working great until yesterday.  My problem is 
that the WAL recovery is now stopping before it recovers all of the available 
WAL files.  This happened once before and the only way I could get the WAL 
recovery to go past that file was to create a fresh back and restore that.  I'm 
hoping to avoid that as it takes about a week to create the backup.

Here are the specifics:

  *   PostgreSQL version 13.2
  *   The primary server creates the WAL files and a scheduled process copies 
them to a folder on the standby server.
  *   Once a day, the standby server is restared using the following command:
 *   "C:\Program Files\PostgreSQL\13\bin\pg_ctl" restart -D .\
  *   The log contains the following:
2021-10-27 10:26:30.508 MDT [6204] LOG:  starting PostgreSQL 13.2, compiled by 
Visual C++ build 1914, 64-bit
2021-10-27 10:26:30.509 MDT [6204] LOG:  listening on IPv6 address "::", port 
5432
2021-10-27 10:26:30.510 MDT [6204] LOG:  listening on IPv4 address "0.0.0.0", 
port 5432
2021-10-27 10:26:30.585 MDT [2012] LOG:  database system was shut down in 
recovery at 2021-10-27 10:26:29 MDT
2021-10-27 10:26:30.701 MDT [2012] LOG:  entering standby mode
2021-10-27 10:26:30.821 MDT [2012] LOG:  restored log file 
"000104190056" from archive
2021-10-27 10:26:31.158 MDT [2012] LOG:  restored log file 
"000104190052" from archive
2021-10-27 10:26:31.467 MDT [2012] LOG:  redo starts at 419/5229A858
2021-10-27 10:26:31.561 MDT [2012] LOG:  restored log file 
"000104190053" from archive
2021-10-27 10:26:32.108 MDT [2012] LOG:  restored log file 
"000104190054" from archive
2021-10-27 10:26:32.849 MDT [2012] LOG:  restored log file 
"000104190055" from archive
2021-10-27 10:26:33.612 MDT [2012] LOG:  restored log file 
"000104190056" from archive
2021-10-27 10:26:34.342 MDT [2012] LOG:  restored log file 
"000104190057" from archive
2021-10-27 10:26:35.146 MDT [2012] LOG:  restored log file 
"000104190058" from archive
2021-10-27 10:26:35.718 MDT [2012] LOG:  restored log file 
"000104190059" from archive
2021-10-27 10:26:36.188 MDT [2012] LOG:  restored log file 
"00010419005A" from archive
2021-10-27 10:26:36.750 MDT [2012] LOG:  consistent recovery state reached at 
419/5AB8
2021-10-27 10:26:36.752 MDT [6204] LOG:  database system is ready to accept 
read only connections
2021-10-27 10:26:36.823 MDT [6040] LOG:  started streaming WAL from primary at 
419/5A00 on timeline 1

  *   There are many more WAL files available starting with 
00010419005B but the restore process always stops at 
00010419005A.

I have two questions:

  *   Why does the WAL file recovery process now stop after it reads 
00010419005A?
  *   What do I need to do to get PostgreSQL to recover the rest of the 
available WAL files.

Thanks in advance for any suggestions.

Sincerely,
-Les


Les Ryan, P.Eng | WSP

SCADA Engineer
Energy, Resources & Industry

T +1 403-813-6327
E les.r...@wsp.com<mailto:les.r...@wsp.com>
O 405 18 St SE. Calgary, Alberta T2E 
6J5<https://www.google.ca/maps/place/405+18+St+SE,+Calgary,+AB+T2E+6J5/>





NOTICE: This communication and any attachments ("this message") may contain 
information which is privileged, confidential, proprietary or otherwise subject 
to restricted disclosure under applicable law. This message is for the sole use 
of the intended recipient(s). Any unauthorized use, disclosure, viewing, 
copying, alteration, dissemination or distribution of, or reliance on, this 
message is strictly prohibited. If you have received this message in error, or 
you are not an authorized or intended recipient, please notify the sender 
immediately by replying to this message, delete this message and all copies 
from your e-mail system and destroy any printed copies. You are receiving this 
communication because you are listed as a current WSP contact. Should you have 
any questions regarding WSP's electronic communications policy, please consult 
our Anti-Spam Commitment at www.wsp.com/casl<http://www.wsp.com/casl>. For any 
concern or if you believe you should not be receiving this message, please 
forward this message to caslcomplia...@wsp.com<mailto:caslcomplia...@wsp.com> 
so that we can promptly address your request. Note that not all messages sent 
by WSP qualify as commercial electronic messages.

AVIS : Ce message, incluant tout fichier l'accompagnant (< le