Questions tagged [write-ahead-logging]

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity.

Filter by
Sorted by
Tagged with
0 votes
2 answers
48 views

Unable to restore from WAL files

I copied both the base backup and the WAL files from my primary Postgres database to a standby database and tried to restore from them but I'm getting this error: PostgreSQL Database directory appears ...
dokgu's user avatar
  • 113
0 votes
1 answer
24 views

How to create a pg_basebackup for without streaming?

I have a primary and a replica Postgres database that are never connected to each other. We have another layer of technology to store a base backup and the WAL files to and those will get synced to ...
dokgu's user avatar
  • 113
0 votes
1 answer
52 views

How to flush WAL files?

I'm trying to force WAL files to be flushed on to the disk so that I can copy them somewhere else. I believe CHECKPOINT is the command I'm interested in but it doesn't seem to work. I have a docker ...
dokgu's user avatar
  • 113
1 vote
1 answer
70 views

How to lock an entire Postgres database

I have a process (for backup and replication using binary logs) that works in MySQL that I want to replicate for Postgres databases (using WAL): Lock database (FLUSH TABLES WITH READ LOCK;) Disable ...
dokgu's user avatar
  • 113
1 vote
0 answers
30 views

Advice on postgreSQL table logging

I am thinking I can turn off logging for certain tables in my PostgreSQL analytic data warehouse. There are a core set of very large tables (between 50 and 500Gb) that are completely rebuilt at the ...
zambo's user avatar
  • 21
1 vote
1 answer
120 views

Shared buffers, WAL buffers and Checkpointers

I am taking this EDB PostgreSQL essential course and Instructor explained about PostgreSQL architecture referring to the diagram that, whenever a client make an update request and suppose data is ...
commonSense's user avatar
1 vote
1 answer
303 views

Why does PostgreSQL want max_wal_senders equal to 0 and greater than 0 at the same time?

We're running a backup process based on pg_basebackup of a Postgres 14.5 database that creates a lot of WAL archive files and using up huge amounts of disk space. As we don't really need the WAL ...
dokaspar's user avatar
  • 113
1 vote
1 answer
228 views

When are wal released or removed in postgresql?

I am relatively new to postgresql administration and I still have doubts about the operation or management of the wal files, specifically how these files are released or deleted, have the conception ...
miguel ramires's user avatar
0 votes
0 answers
75 views

Postgresql streaming replication, server closed the connection

I set up delayed replication (DB3), which receives WALs from another replication (DB2), it was working fine for few days then this happened: DB3 2023-04-29 05:51:34.483 CEST,,,7292,,6447d887.1c7c,2,,...
sh4rkyy's user avatar
  • 129
-1 votes
1 answer
330 views

General questions about write ahead logs and recovery

I've recently learned about write ahead logs and failure recovery in the context of key-value stores like Cassandra and have some follow-up questions: Do WALs get persisted into smaller snapshots so ...
ejtt's user avatar
  • 101
0 votes
2 answers
96 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 ...
Leon's user avatar
  • 337
0 votes
1 answer
81 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 ...
padjee's user avatar
  • 177
0 votes
1 answer
998 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, ...
ordinary_guy's user avatar
0 votes
1 answer
1k 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]{...
padjee's user avatar
  • 177
3 votes
0 answers
71 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-...
Gunther Schadow's user avatar
0 votes
1 answer
2k 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 ...
padjee's user avatar
  • 177
0 votes
1 answer
10k 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 ...
padjee's user avatar
  • 177
0 votes
1 answer
265 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 ...
padjee's user avatar
  • 177
0 votes
0 answers
531 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 ...
padjee's user avatar
  • 177
-2 votes
1 answer
374 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
padjee's user avatar
  • 177
0 votes
1 answer
1k 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 ...
padjee's user avatar
  • 177
1 vote
1 answer
357 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 ...
saolof's user avatar
  • 111
0 votes
1 answer
28 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 ...
interfect's user avatar
  • 101
0 votes
1 answer
1k 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 ...
JonoB's user avatar
  • 233
1 vote
1 answer
554 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 ...
Yusuf K's user avatar
  • 13
0 votes
2 answers
836 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 ...
Frankenstein's user avatar
0 votes
1 answer
91 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 (...
1a1a11a's user avatar
  • 101
0 votes
1 answer
733 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 ...
sha's user avatar
  • 137
4 votes
1 answer
3k 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 ...
Abhishek B's user avatar
0 votes
1 answer
458 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 ...
shantanu4raje's user avatar
4 votes
2 answers
4k 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 ...
tallharish's user avatar
2 votes
1 answer
522 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 ...
FlyingAtom's user avatar
0 votes
1 answer
412 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/...
HelpMeExitVim's user avatar
3 votes
1 answer
323 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 %...
rsmoorthy's user avatar
  • 157
1 vote
2 answers
170 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 ...
femonova's user avatar
3 votes
1 answer
1k 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 ...
user avatar
2 votes
2 answers
540 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 ...
Rhoman Tawadros's user avatar
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 ...
Bill Turris's user avatar
0 votes
1 answer
725 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 ...
bpaterni's user avatar
  • 101
0 votes
0 answers
211 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 ...
Sammitch's user avatar
  • 306
3 votes
2 answers
3k 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 ...
Nyxynyx's user avatar
  • 1,121
1 vote
0 answers
433 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 ...
anon's user avatar
  • 11
1 vote
2 answers
523 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 ...
user203748's user avatar
0 votes
1 answer
485 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, ...
user203748's user avatar
1 vote
1 answer
488 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 ...
femonova's user avatar
0 votes
2 answers
334 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 ...
Marcus's user avatar
  • 378
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 ...
terrorrussia-keeps-killing's user avatar
0 votes
1 answer
2k 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 ...
Sam.E's user avatar
  • 85
7 votes
1 answer
1k 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 ...
DBEnthusiast's user avatar
0 votes
1 answer
325 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 - ...
sazary's user avatar
  • 105