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
0 answers
19 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 ...
user avatar
1 vote
1 answer
26 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 ...
user avatar
  • 111
0 votes
1 answer
18 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 ...
user avatar
  • 101
0 votes
1 answer
56 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 ...
user avatar
  • 223
1 vote
1 answer
32 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 ...
user avatar
  • 13
0 votes
2 answers
96 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 ...
user avatar
0 votes
1 answer
59 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 (...
user avatar
  • 101
0 votes
1 answer
141 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 ...
user avatar
  • 135
2 votes
0 answers
418 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 ...
user avatar
0 votes
1 answer
135 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 ...
user avatar
3 votes
2 answers
1k 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 ...
user avatar
2 votes
1 answer
184 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 ...
user avatar
0 votes
1 answer
167 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/...
user avatar
3 votes
1 answer
194 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 %...
user avatar
  • 137
1 vote
2 answers
54 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 ...
user avatar
1 vote
1 answer
218 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
0 votes
2 answers
107 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 ...
user avatar
1 vote
0 answers
24 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 ...
user avatar
0 votes
1 answer
270 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 ...
user avatar
  • 101
0 votes
0 answers
81 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 ...
user avatar
  • 306
2 votes
2 answers
1k 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 ...
user avatar
  • 1,001
1 vote
0 answers
258 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 ...
user avatar
  • 11
0 votes
2 answers
273 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 ...
user avatar
0 votes
1 answer
138 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, ...
user avatar
1 vote
1 answer
299 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 ...
user avatar
0 votes
2 answers
162 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 ...
user avatar
  • 327
0 votes
0 answers
627 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 ...
user avatar
0 votes
1 answer
658 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 ...
user avatar
  • 55
7 votes
1 answer
715 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 ...
user avatar
0 votes
1 answer
147 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 - ...
user avatar
  • 105
1 vote
1 answer
361 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 ...
user avatar
  • 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 ...
user avatar
  • 157
3 votes
2 answers
414 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 ...
user avatar
  • 157
2 votes
2 answers
1k 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: ...
user avatar
-1 votes
2 answers
1k 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'...
user avatar
7 votes
2 answers
7k 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 ...
user avatar
2 votes
1 answer
349 views

What happened to pg_xlog?

There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. ...
user avatar
  • 56.2k
2 votes
1 answer
1k views

Streaming replication archive folder taking all space- Postgresql- 9.4

We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine. We ...
user avatar
  • 53
1 vote
0 answers
738 views

Restore single database (or table-space) using PITR with PostgreSQL?

Is there any possibility restore a single database with WAL files.
user avatar
3 votes
1 answer
2k views

postgresql configuring wal_archive directory

I had a problem with PostgreSQL server. I took over PostgreSQL server from my former colleague, and today my server crashed due to filled up disk space with wal_archives. Usually wal segments were ...
user avatar
1 vote
0 answers
43 views

Hosting postgres wal logs on a separate dedicated server with failover setup

a postgres novice here, so sorry if my questions don't sound meaningful. My postgres server setup goes as follows: Master(on server A), Upstream(on B, receiving wal from A), Downstream(on C, ...
user avatar
1 vote
1 answer
92 views

postgres pg_xlog explodes during set tablespace

I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and ...
user avatar
  • 11
8 votes
2 answers
3k views

Postgres 9.4 wal_level=logical increases disk space

I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance. Database Migration Service requires the wal_level to be set as logical ...
user avatar
0 votes
2 answers
2k views

Postgres replication not cleaning up WAL files

I'm not that great with postgres but i was able to setup replication following this tutorial: https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-...
user avatar
  • 101
2 votes
1 answer
643 views

How to make pg_basebackup use temporary replication slots

This article https://blog.hagander.net/another-couple-of-steps-on-my-backup-crusade-235/ states that temporary replication slots can be "used to prevent the wal on the server from being removed while ...
user avatar
  • 293
0 votes
0 answers
391 views

Wal-e : " pg_stop_backup still waiting for all required WAL segments to be archived"

I've been trying to use wal-e to take incremental backup of my postgresql database by following this blog. I've a database on my postgresql and using an AWS S3 bucket. while I'm using the following ...
user avatar
2 votes
0 answers
508 views

Postgres pg_dump restore performance. Is using pg_dump to back up large databases an acceptable solution

Folks, I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs. My one concern is restoring Postgres from a dump. I believe that the ...
user avatar
  • 293
0 votes
1 answer
63 views

Wal position to wal archive number

I want to manage the copy of archiving to remote location. For that I want to delete all the archives I copied unless they are required by the standby. So, for running pg_archivecleanup $ARCLOC $...
user avatar
  • 393
1 vote
0 answers
268 views

Does barman require continuous execution of receive-wal on a slot for streaming replication to work?

I am new to barman and am trying to implement barman on my server. I have followed each and every step as mentioned in the barman docs. Everything works fine till the receive-wal command in running in ...
user avatar
0 votes
2 answers
74 views

Storing logical WAL in the same database

I have a simple Postgres setup (one machine, one disk) and would like to record a changelog for some of the tables. As I would like to access that changelog from the same web app that uses the ...
user avatar
  • 101