could not link file in wal restore lines
Hi, I'm testing pgbackrest restore. It works well, but in the pg logs I get the following lines: could not link file "pg_wal/000101560098" to "pg_wal/00010157006E": File exists In total, I get 18 lines of "could not link file" and 932 lines of "restored log file" lines. At the end it finishes with: redo done at 15A/A001710 last completed transaction was at log time 2022-07-15 19:41:05.175573+00 restored log file "0003015A000A" from archive selected new timeline ID: 4 archive recovery complete restored log file "0003.history" from archive database system is ready to accept connections Before going in production, I wanted to ask, if this is correct like this? I mean are those "could not link file" lines anything to worry about? Thanks and regards, Zsolt
Re: could not link file in wal restore lines
Can someone with knowledge about PG tell me if this is a bug and if there is any need to worry about data corruption? Here is the issue which I've opened for pgbackrest, the developer confirmed that pgbackrest doesn't change those files at all. https://github.com/pgbackrest/pgbackrest/issues/1815 It's PG 12 on Ubuntu 18.04. I always start with a clean data folder and can replicate this 100% any time I do a restore. Single master, no cluster or replication, backup/restore is from the same partition. Thanks, Zsolt On 15. Jul 2022 at 22:11:09, Zsolt Ero wrote: > Hi, > > I'm testing pgbackrest restore. > > It works well, but in the pg logs I get the following lines: > > could not link file "pg_wal/000101560098" to > "pg_wal/00010157006E": File exists > > In total, I get 18 lines of "could not link file" and 932 lines of > "restored log file" lines. > > At the end it finishes with: > > redo done at 15A/A001710 > last completed transaction was at log time 2022-07-15 19:41:05.175573+00 > restored log file "0003015A000A" from archive > selected new timeline ID: 4 > archive recovery complete > restored log file "0003.history" from archive > database system is ready to accept connections > > Before going in production, I wanted to ask, if this is correct like this? > I mean are those "could not link file" lines anything to worry about? > > Thanks and regards, > Zsolt > > > >
refresh materialized view concurrently alternatives
Hi, I'm using refresh materialized view concurrently at the moment. I have a few problems with it: 1. It requires adding a unique index, even if it's never actually used. This can just create wasted space and bad cache utilization. 2. It locks the table so that two refresh commands cannot be run at the same time. 3. It's slower than without concurrently. My idea is the following approach: DROP MATERIALIZED VIEW IF EXISTS tmp.my_mat_view; CREATE MATERIALIZED VIEW tmp.my_mat_view AS SELECT ... BEGIN; DROP MATERIALIZED VIEW IF EXISTS my_mat_view; ALTER MATERIALIZED VIEW tmp.my_mat_view SET SCHEMA public; COMMIT; Would this approach work? From my testing this approach doesn't result in any kind of locking, and it's very fast and also it doesn't require the unique index condition. Are there any problems with this? In what situations would refresh mat view or refresh mat view concurrently has advantages over this? Probably it's important to note how my DB works, inserts are pretty much 100% controlled. They happen once per hour, after which all views are refreshed. Zsolt
