Stack Exchange Network

Stack Exchange network consists of 174 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 [wal]

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

1
vote
1answer
19 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
20 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
18 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
22 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
20 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
26 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
420 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
0answers
93 views

Postgresql Replication stopped unexpectedly Slave server's service is not starting. How can I Resume replication?

Postgres Replication was working fine with Postgresql version 9.5. when the template of a DB with 10gb of data was created and then the slave server stopped. Its Postgres service is not starting now. ...
0
votes
2answers
199 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
93 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
113 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
125 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
25 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
70 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
35 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
136 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
58 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
268 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 ...
0
votes
1answer
209 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
38 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
55 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
244 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
144 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
84 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
383 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 ...
0
votes
0answers
23 views

Unable to successfully run omnipitr-restore

I am fairly new (actually very new) to omnipitr. I am trying to restore a backup that was taken via omnipitr-backup. Command for backup is something like this - omnipitr-backup-slave \ --data-dir <...
1
vote
1answer
23 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 ...
0
votes
1answer
154 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
395 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-...
3
votes
2answers
2k 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
619 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.
2
votes
1answer
1k 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
409 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
1k 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/...
1
vote
2answers
880 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
157 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
76 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
140 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
127 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
212 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
5k 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
101 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 ...
9
votes
1answer
9k views

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

According to documentation, for "min_wal_size" and "max_wal_size" parameters default values are: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this ...
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 ...
0
votes
1answer
157 views

Restoring postgres backup with WAL-E fills up disk

I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a ...
2
votes
2answers
5k views

How to change the Write Ahead Log directory

How do you change the defined WAL directory? During the installation I defined a directory, /var/lib/wal and the $PGDATA/pg_xlog has a symbolic link pointing to that directory. How do I change the ...