Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [write-ahead-logging]

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

0
votes
0answers
26 views

Steps for write ahead log protocol

What are the steps for write ahead log protocol? I know these are the steps, but I don't know the order they occur in. a. Updated data is persisted on disk. b. Data is updated in memory. c. The ...
0
votes
1answer
56 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'...
2
votes
1answer
261 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 ...
0
votes
0answers
60 views

Fixing problems with postgreSQL wal archives

i just took over working postgre sql server, and i noticed in the logs, that are in: /var/log/postgresql/postgresql-9.4-main.log spitting out this warnings: cp: cannot stat 'pg_xlog/...
1
vote
1answer
52 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. ...
1
vote
1answer
106 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
71 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
132 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
28 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
33 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 ...
3
votes
1answer
824 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
304 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
153 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
135 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
165 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
34 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
103 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
44 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
268 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
78 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 ...
1
vote
1answer
412 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
316 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
45 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
113 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
389 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 ...
1
vote
1answer
2k 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
216 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
121 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
541 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
36 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
183 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
459 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
3k 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
1answer
932 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
544 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 /...
0
votes
1answer
2k views

Too many WAL files in archive folder PostgreSQL Streaming Replication

I have two Postgresql 9.5 servers configured in streaming replication. There are no errors in log files and the servers seem to be in sync. MASTER postgresl.config archive_command = 'test ! -f mnt/...
2
votes
2answers
1k views

PostgreSQL checkpoint log explained

I know what PostgreSQL checkpoint is and when it is happening. I need some additional information about the logs produced by the log_checkpoints = on parameter, so please explain some points of it to ...
1
vote
0answers
187 views

Backup postgresql docker image from WAL using wal-e

I have an image which has wal-e installed and working. It successfully on backup-push sends my WAL to the remote drive. However when I run backup-fetch, I get the error wal_e.main INFO MSG:...
2
votes
1answer
100 views

Is there a way to view WAL updates on a Hot Standby server?

We are trying to debug some of the problems we are having on our production database server. One thing we would like to enable is pg_stat_statements, but we are holding off on restarting the service. ...
1
vote
1answer
1k views

PostgreSQL 9.6 log shipping replication stuck on startup

Overview We have been trying to restore replication between a Master and Replication server for a while, but the individual who had set up has left and we've been working at it for a while. In brief, ...
1
vote
1answer
175 views

Postgresql roll backward recovery

Im familiar with postresql WAL file and its purpose. Every transaction and the commit records of each transaction will be logged in this file .While recovery, it replays from the last checkpoint till ...
0
votes
1answer
150 views

postgres PITR from WAL without changes

I'm trying to do Pont In Time Recovery from wal archive using postgres 9.6 and i got question: when i'm doing recovery procedure first time everything went ok, but when i'm doing some changes in ...
1
vote
1answer
38 views

How can we execute a log file from pg_xlogs directory postgresql?

Is it possible to execute a Wal log that is copied from a machines pg_xlogs directory and all the queries inside it are executed on another machine? What command might be used to carry out this action ...
0
votes
1answer
260 views

pg_start_backup and small max_wal_size

What will happen, if max_wal_size gets exceeded during backup? Specifically, let's consider the following scenario on master: Set max_wal_size to a very low number. Run SELECT pg_start_backup('label'...
5
votes
1answer
2k views

Postgres: understanding amount of WAL files/size generated

I'm constantly caught by surprised how certain operations generate me huge amount of WAL files. I want these WAL files for point in time recovery (I also perform a nightly full dump in addition) so ...
5
votes
1answer
6k views

Postgresql: invalid record length at 29/E4624520 but replication looks fine

What is this error? I see that replication is working fine, but I am not so sure if the data is being corrupted or not. Can I safely ignore this error?
0
votes
1answer
112 views

WAL log entry for CREATE TABLE statement

In postgresql, for queries like INSERT/UPDATE/DELETE, a WAL record with required information about the tuples is created in the WAL buffer, before doing the required changes in the data buffer. Is it ...
11
votes
1answer
12k views

Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

Default values are, according to documentation for min_wal_size and max_wal_size parameters: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this parameters ...
1
vote
3answers
2k views

Specify cleanup by file age - PostgreSQL 9.2

I want to keep my wal_files for 30 days, using the pg_archivecleanup. I've followed these scripts: https://stackoverflow.com/questions/31346606/postgresql-automatic-archive-purge-not-occuring and ...