Questions tagged [write-ahead-logging]
Write-Ahead Logging (WAL) is a standard method for ensuring data integrity.
115
questions
0
votes
2
answers
33
views
About "wal_buffers" of Postgresql-11 with "synchronous_commit=off"
Last days I tuned my PG11 for a lots of huge writing transactions, and got a amazing result.
The only warry in my mind is that whether there is more risk of lossing data when I turned off ...
- 205
0
votes
1
answer
46
views
WALs increase for some reason
This is new to me. I have been searching the net but no result.
I have a server, Server A, that has been running for a few weeks, it is a new server. I set up FDW for this server to access all tables ...
- 137
0
votes
1
answer
305
views
Archiving SQLite DB with WAL mode
I have an SQLite database gapongasi.db configured using WAL mode and I want to have daily archive with tar(1). With WAL, there are two files being used (1) gapongasi.db-wal and (2)gapongasi.db-shm, ...
0
votes
1
answer
247
views
understanding pg_wal and archiving
So I inherited this database. At the very beginning, there were already some files on pg_wal.
Then, after some weeks, I do count SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{...
- 137
3
votes
0
answers
45
views
More low-level faster replication schemes for PostgreSQL?
From this question: in PostgreSQL REPMGR Master-Slave environment is it necessary to have same H/W config for HOT STANDBY setup?, I was reminded of my experience and disappointment with the hot-...
- 415
0
votes
1
answer
905
views
Delete WAL archive files
I have around 200Gb of WAL archives. Can I just delete them all without considering any database restoration? I am just asking whether it is safe to delete them all or at least 50% of them.
I read a ...
- 137
0
votes
1
answer
3k
views
could not write to file "pg_wal/xlogtemp.2399" no space left on device
I am generating dummy data with this query :
INSERT INTO tbltest1 (id, kol1, kol2)
SELECT generate_series(1,10000), substr(md5(random()::text), 1, 25), substr(md5(random()::text), 1, 45)
FROM ...
- 137
0
votes
0
answers
22
views
Why there are no WAL files formed in subscriber
I have logical replication set between 2 servers. The replication works fine, all tables have the same data.
But then I notice that the subscriber WAL files do not increase in number, it is always 128....
- 137
0
votes
1
answer
89
views
Archive failed but new WAL never generated
Currently I am experiencing a weird situation. Archive failed_count soaring from 0 to 2475.
Here some parameter that I check :
select * from pg_stat_archiver;
archived_count 3567;
last_archived_time ...
- 137
0
votes
0
answers
209
views
Postgres archiving not running
I am using logical replication. Currently I am using stored procedure to pump 3K data per hour to see if the WAL files increase. It does increase by 1 file every 12k records. But the WAL archiving is ...
- 137
0
votes
0
answers
57
views
Logical Replication and Archiving
Is it necessary to turn on archiving when replication is being used ?
I notice that replication generates WAL files. Yes it can grow large, but what if hard disk space is not a problem ?
I'm using ...
- 137
-2
votes
1
answer
157
views
Can min_wal_size bigger than max_wal_size?
is it possible if min_wal_size bigger than max_wal_size ? What happen if it does ? What are the circumstances when min_wal is bigger than max_wal ?
I am using PG 12.
Thanks
- 137
0
votes
0
answers
39
views
postgres archiver not working
I have a problem with archiving. Here are some parameters :
`SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'`; -- 4048 files
checkpoint_completion_target : -- 0.9
...
- 137
0
votes
1
answer
325
views
WAL files management
I want to have advice about WAL management. I am expecting a large numbers of wal files (the database is very large and it has like 30 - 40 millions of records in totals) since I am setting up a ...
- 137
0
votes
0
answers
523
views
Transaction Logs generated per hr /transaction log disk usage per min/hr in PostgreSQL AWS RDS
Need to estimate the space allocation for transaction logs in AWS RDS PostgreSQL based on the number of logs generated/disk space used for transaction logs per day. I know this information is ...
- 1
1
vote
1
answer
92
views
Is there a way to see which tables contribute most to WAL size?
I'm maintaining a postgresql database and am considering the use of clustering so that update queries have to touch fewer pages.
With clustering based on indices I'm seeing a significant speedup for ...
- 111
0
votes
1
answer
23
views
How can MySQL be optimized for unattended operation on unreliable hardware?
I run some personal services on a not-entirely-reliable single-board computer. Sometimes it locks up and needs to be power cycled.
One of these services is currently configured to use MySQL with ...
- 101
0
votes
1
answer
268
views
Increasing TransactionLogsDiskUsage in postgresql 11.12 RDS instance [duplicate]
We have a test RDS postgresql 11.12 instance, configured for logical replication. The only thing connected to it is a 3rd party service, Hevo Data, which is managing sync to a separate Redshift ...
- 233
1
vote
1
answer
192
views
What happens to written WAL logs if a transaction is aborted in the middle? [duplicate]
I was thinking about scenario below.
BEGIN TRANSACTION;
INSERT SOMETHING;
INSERT SOMETHING;
##CLIENT COMPUTER EXPLODES
If i understand correctly, after each INSERT statement, a log entry is written ...
- 13
0
votes
2
answers
443
views
Postgres: Show sql´s of wal segments
I am trying to set up wal archiving for the first time. I did add the folder with correct permissions:
backup/wal
Then i added these entries to the postgresql.conf:
wal_level=archive
archive_mode=on
...
- 103
0
votes
1
answer
71
views
Why do database need logging?
Most databases today have WAL (undo, redo), but why do databases need them?
Take a simple case of RocksDB deployment. I would imagine a storage/database cluster will have a certain redundancy scheme (...
- 101
0
votes
1
answer
466
views
Ordering of LSN in postgres WAL files
There is a mysterious comment in the source code of debezium, which says that LSNs need not be ordered in the WAL file. How can that be possible? If LSN represents the byte offset in the WAL, how can ...
- 135
4
votes
1
answer
1k
views
Postgresql 11: terminating walsender process due to replication timeout
I have found some questions about the same error, but didn't find any of them answering my problem.
The setup is that I have two Postgres11 clusters (A and B) and they are making use of publication ...
- 61
0
votes
1
answer
303
views
Recovery of uncommitted transactions after checkpoint
After reading from several resources including:
COMMIT (Oracle Database SQL Reference)
Checkpoints (Oracle Programmer Reference)
Difference between commit and checkpoint (on this site)
ORACLE ...
3
votes
2
answers
2k
views
Postgresql 13 Change WAL segment size
On my Ubuntu 20 server, I installed PostgreSQL 13 using the apt manager. I read some articles on performance tuning of PostgreSQL [1] and thought of increasing the WAL segment size (from default of ...
- 141
2
votes
1
answer
321
views
PostgreSQL Reduce Writes on Flash Storage (TBW) [closed]
I'm currently a startup and using non server grade NVMEs/SSD on my database server.
My motherboard , CPU , DDR (ECC) etc are server grade except for the flash part.
I'm looking for ways to reduce the ...
- 123
0
votes
1
answer
297
views
postgresql replication: WAL rotated before segment could be archived?
Using PostgreSQL 10.7
Checking my logs this morning, I'm seeing this error a lot:
archive command failed with exit code 1
The failed archive command was: test ! -f /opt/bitnami/postgresql/archive/...
3
votes
1
answer
266
views
archive_command: Can hardlink (ln) be used instead of cp?
We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool pgrsync.
The archive_command was initially set to cp %...
- 147
1
vote
2
answers
98
views
PostgreSQL - Base Backups and PITR
According to the PostgreSQL documentation for producing Standalone Hot Backups:
These are backups that cannot be used for point-in-time recovery, yet
are typically much faster to backup and restore ...
- 35
1
vote
1
answer
701
views
What makes it so much faster to write to the "WAL" compared to directly to the "real database"?
PostgreSQL works like this, if I have understood things correctly:
I make an INSERT, DELETE or UPDATE query to a table in my PG database.
PG immediately writes this query and its parameters to the ...
user214931
1
vote
2
answers
186
views
How is it technically possible for PostgreSQL to keep small tables "in RAM"?
I used to ask about performance and people would tell me that PostgreSQL keeps small-enough tables entirely in RAM automatically, as to not stress the HDD and thus enable super-fast reading/writing of ...
1
vote
0
answers
26
views
Required WAL settings to permit large queries to run on server which is replicating to standby server
What WAL/conf settings are required to permit large queries in PostgreSQL without breaking replication to the replica server?
Is it only a matter of ensuring that:
the WAL writes don't time out?
the ...
- 11
0
votes
1
answer
493
views
PostgreSQL WAL Archival Best Practices?
The postgresql documentation gives an example archive_command of
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' # Unix
but adds the disclaimer ...
- 101
0
votes
0
answers
160
views
High RAM usage during Postgres PITR
We're currently running Postgres 10 in our infrastructure with Barman managing weekly full backups and holding onto the WALs. Because of the size of our DB, the WAL volume, and resource constraints ...
- 306
2
votes
2
answers
2k
views
Increasing WAL Segment Size?
Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?
The reason for wanting to increase it from 16 MB ...
- 1,081
1
vote
0
answers
367
views
Difficulty Optimizing Postgres Write Throughput
I'm trying to index a collection of roughly 127K files using a
PostgreSQL 9.6 server on RDS. I had expected the process of writing these
documents to the DB to take about 8 hours, but over time I ...
- 11
1
vote
2
answers
362
views
How should I configure my postgresql.conf file to backup the database once per hour?
My goal is to back up the PostgreSQL 10 database on a Ubuntu 18.04 VPS once per hour.
However, I clearly do not understand how to achieve this. Here is what I've done.
I modify the postgresql.conf ...
- 11
0
votes
1
answer
279
views
How do I correctly use %p and %f in postgresql.conf to make the Write Ahead Log?
I am attempting to modify postgresql.conf (Postgre SQL 10.0 under Ubuntu 18.04 VPS) to setup the Write Ahead Log (WAL).
AIUI, the commands should be:
wal_level = replica # minimal, ...
- 11
1
vote
1
answer
382
views
PostgreSQL - WAL segment file creation and naming conventions
I was under the impression that WAL segments files are normally generated before they are being written to and archived, and are continuously renamed and reused to save disk space. I was wondering if ...
- 35
0
votes
2
answers
222
views
On a typical COW filesystem, is the database WAL redundant (in theory)?
EDIT: I was incorrectly assuming that all COW filesystems have a WAL. IT seems that BTRFS doesn't, so this question essentially applies only to ZFS.
Typical COW filesystems, like ZFS, have their own ...
- 327
1
vote
0
answers
1k
views
SQLite: Extract data from the `-wal` file
Yesterday I accidentally deleted an SMS banking notification thread on my Android device.
A classic case: I didn't backup the messages for very long time.
After flashing the recovery and rooting the ...
0
votes
1
answer
1k
views
How do i disable or limit Postgres Replication creating causing too many WAL files generated?
On AWS RDS PostgreSQL 10.7,
I turned on logical replication for a one specific table
.
on publisher:
CREATE PUBLICATION mypublication FOR TABLE <mytable>;
on subscriber:
CREATE SUBSCRIPTION ...
- 55
7
votes
1
answer
938
views
Expecting Postgresql WAL to shrink in size but it's not
We set our max_wal_size to 24 GB recently (from the 1 GB default), did some testing, and then set it to 12 GB followed by a server restart. When I query the size of the WAL on the filesystem (total ...
- 71
0
votes
1
answer
238
views
Barman shows huge variance in WAL size
I'm using barman to take backups from postgresql. This is the result of list-backup:
prod 20190831T000005 - Sat Aug 31 00:04:58 2019 - Size: 16.0 GiB - WAL Size: 654.2 MiB
prod 20190830T000005 - ...
- 105
1
vote
1
answer
526
views
Why Postgres replication write_lag is measured in seconds
I'm running pg_receivewal locally, without compression, it stores segments on the same drive with the db files. Here's the replication statistic:
imbolc=# select * from pg_stat_replication;
-[ RECORD ...
- 157
1
vote
1
answer
1k
views
How to deal with `.partial` WAL segments generated by Postgres `pg_receivewal` command?
pg_receivewal creates a .partial file for incomplete WAL segment. My question is how to deal with this file during recovery. If I just leave it as it is, last transactions don't recover. If I rename ...
- 157
3
votes
2
answers
657
views
Is it possible to prevent data loss with Postgres WAL archiving?
As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout.
So if the database crashes, all the data from the incomplete, currently ...
- 157
2
votes
2
answers
2k
views
copy entire PostgreSQL cluster to another (identical) server
I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind:
...
- 451
-1
votes
2
answers
2k
views
Heartbeat frequency in AWS DMS (Database Migration Service)?
Within "PostgreSQL Source WAL Heartbeat https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html#CHAP_ReleaseNotes.DMS230", what does heartbeat frequency represents? The AWS docs doesn'...
8
votes
2
answers
8k
views
differences between hot standby vs warm standby postgresql？
I am confused about the Differences BETWEEN the database replication methods mentioned in a wiki page of postgres, which is best for normal situation?
warm-standby/Continuous archiving/log shipping
...
- 183