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
1answer
25 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
2answers
255 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 ...
1
vote
1answer
57 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 ...
0
votes
1answer
53 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
1answer
129 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 %...
1
vote
2answers
33 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 ...
0
votes
1answer
56 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 ...
0
votes
2answers
88 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
0answers
23 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 ...
0
votes
1answer
128 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 ...
0
votes
0answers
28 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 ...
2
votes
2answers
611 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
vote
0answers
126 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 ...
0
votes
2answers
135 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 ...
0
votes
1answer
51 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, ...
1
vote
1answer
204 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 ...
0
votes
2answers
107 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 ...
0
votes
0answers
401 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
1answer
391 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 ...
7
votes
1answer
463 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 ...
0
votes
1answer
68 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 - ...
1
vote
1answer
223 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 ...
1
vote
1answer
714 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 ...
3
votes
2answers
305 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 ...
2
votes
2answers
652 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: ...
-1
votes
2answers
708 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'...
6
votes
2answers
5k 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 ...
2
votes
1answer
255 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. ...
2
votes
1answer
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 ...
0
votes
0answers
449 views

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

Is there any possibility restore a single database with WAL files.
3
votes
1answer
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 ...
1
vote
0answers
37 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, ...
1
vote
1answer
80 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 ...
5
votes
2answers
2k 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 ...
0
votes
2answers
1k 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-...
2
votes
1answer
521 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 ...
0
votes
0answers
306 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 ...
1
vote
0answers
437 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 ...
0
votes
1answer
57 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 $...
1
vote
0answers
230 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 ...
0
votes
2answers
70 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 ...
2
votes
1answer
421 views

Why PostgreSQL remove or add WAL files?

I have experiences of administration with MySQL and Oracle Database. I started to work with PostgreSQL recently and cannot find the reason why PostgreSQL managing WAL files by removing or adding ...
0
votes
1answer
133 views

Restore server instance using logical backup and wal files

Is it possible to restore a database instance using logical backup and wal files? A senior SQL Server DBA asked me to implement below scenario in PostgreSQL took the logical backup using pg_dumpall ...
2
votes
1answer
1k views

When Master is being auto vacuumed, Is slave gettting exclusive lock on its table to apply WAL?

I've met the very curious pattern that triggers a lot of wait queue on slaves. And I think I finally find it's because of auto vacuum. However, I've never got such description on official document nor ...
1
vote
1answer
695 views

Postgresql: Streaming Replication - determining wal sender process client port

My experience with asynchronous streaming replication has been great so far, both as a HA solution and as a backup strategy. One issue is delaying me from using streaming replication in production - ...
4
votes
1answer
79 views

Create WAL at interval, but only if data changed

I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure. ...
0
votes
1answer
335 views

How to purge old files from pg_xlogs directory

I have an archive command copying WAL files from pg_xlogs to my archive directory. However, I notice that the pg_xlogs directory is growing in size with (but is limiting itself to around 7 files). ...
0
votes
1answer
721 views

PostgreSQL warm standby: sync WAL files

I'm new to PostgreSQL (using ver 8.4.20) and need to setup a master and standby SQL server setup. I am planning to use warm standby setup. I have read the docs on PG and understand the basics, but a ...
3
votes
1answer
5k views

unable to start the standby postgres database

Situation I have 2 separate instances. Both running on Ubuntu 14.04 server. Both have installed postgres 9.5 the following way: sudo apt-get install software-properties-common python-software-...
1
vote
0answers
482 views

How can I get the size of WAL per hour?

I want to get statistics wal. We need to stop replication for a while so l want to know query for getting how much size of WAL produce per hour. In Oracle it just get redo log switch count from ...