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
0answers
18 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 ...
0
votes
0answers
15 views

PostgreSQL Streaming Replication with Switchover

I'm trying to provide streaming replication between a master (T1) and slave (T2) and swapping their roles when necessary (i.e letting T1 be a slave to T2). So far I am able to get this working if I'm ...
2
votes
2answers
49 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
28 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
35 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
20 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
33 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
33 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
161 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
82 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 ...
5
votes
1answer
122 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
36 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 - ...
0
votes
1answer
88 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
257 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
141 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 ...
1
vote
2answers
152 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
355 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'...
3
votes
2answers
3k 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
169 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
685 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
282 views

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

Is there any possibility restore a single database with WAL files.
2
votes
1answer
800 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
35 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
71 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 ...
4
votes
1answer
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
675 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
352 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
206 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
315 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
54 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
165 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
60 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
408 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
126 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
864 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
589 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
67 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
248 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
623 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 ...
2
votes
1answer
4k 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
373 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 ...
0
votes
1answer
152 views

Does Write-Ahead-Logging allow me to make concurrent changes to a table?

In Sqlite3, Write-Ahead-Logging mode allows one process to read a table, while another is writing to it. It doesn't say anything about one process writing and another deleting/updating. Is this ...
1
vote
0answers
1k views

Postgresql sometimes gets high IO issue

I have a Sanic app using asyncpg (0.13) to work with postgresql (9.5). My server now facing a problem which sometimes postgresql seems to be freezed and my app can't even read from db and it seems ...
1
vote
1answer
83 views

File creation dates of archived WAL files not in line with archive_timeout in PostgreSQL 9.6

I have configured archive_timeout = 5 (i.e. 5 seconds) for a PostgreSQL 9.6 database in order to conduct tests around point-in-time recovery. My archive_command invokes cp into a local archive ...
1
vote
1answer
306 views

How to change the Write Ahead Log directory on Windows

On postgres 9.6 for Windows server 2008. How do I change the Write Ahead Log directory? Thanks.
2
votes
2answers
637 views

WAL archiving w/ streaming replication

I'm running postgresql 9.3. I've currently got WAL archiving configured, but I'm doubting it's utility based on my configuration. On the master, I've got archive_command set to rsync -az --ignore-...
4
votes
2answers
6k views

Unable to receive WAL files with barman

I've been scratching my head to solve the following error, but haven't made much progress: $ barman switch-wal --force --archive --archive-timeout 180 pg The WAL file 0000000100000059000000FF has ...
1
vote
2answers
3k views

Postgresql: what are the disadvantages of unlogged table

To make update faster i am using below command. ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ; I want to know what are the drawbacks of this command.
3
votes
1answer
2k views

PostgreSQL: Why is CREATE TABLE AS faster than CREATE … INSERT INTO?

Below are the two different syntaxs for the same thing. With a COPY TABLE AS SELECT (CTAS). CREATE TABLE main AS SELECT * FROM other; As separate statements with CREATE TABLE and INSERT INTO ...
1
vote
1answer
736 views

postgres: archive_command can't write to an nfs share

I've enabled the archive_mode with wal_level = hot_standby on a master server. I intended to send the wal logs to a nfs share with: archive_command = 'test ! -f /mnt/psql_wal/%f && cp -i %p /...