Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [write-ahead-logging]

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

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. This is roll-forward recovery, also known as REDO.

122 questions
24
votes
2 answers

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 from my database config: select name, setting, unit…
Oto Shavadze
  • 575
  • 1
  • 7
  • 15
9
votes
2 answers

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 in the source Postgres DB. But when done,…
Aravindan R
  • 271
  • 2
  • 5
9
votes
2 answers

Disadvantages of unlogged tables

To make updates faster, I am using: ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ; What are the drawbacks of this command? What will happen if system crashes during the update? Is all the data present in the table…
user2274074
  • 473
  • 3
  • 8
  • 14
8
votes
2 answers

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 offers high…
dorbodwolf
  • 183
  • 1
  • 1
  • 6
8
votes
2 answers

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 me: 2017-09-09 16:31:37 EEST [6428-6524] LOG: …
inivanoff1
  • 183
  • 1
  • 1
  • 3
8
votes
1 answer

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?
Luciano Andress Martini
  • 1,611
  • 2
  • 15
  • 30
7
votes
1 answer

How can I solve postgresql problem after deleting wal files?

I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. When I tried to restart postgresql I got the…
Sina
  • 115
  • 1
  • 2
  • 7
7
votes
1 answer

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 size of files in pg_xlog directory), it still shows…
DBEnthusiast
  • 71
  • 3
7
votes
2 answers

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 postgres setting to write the WAL files elsewhere -…
Stelios
  • 389
  • 1
  • 5
  • 14
7
votes
2 answers

What causes large INSERT to slow down and disk usage to explode?

I have a table of about 3.1 million rows with the following definition and indexes: CREATE TABLE digiroad_liikenne_elementti ( ogc_fid serial NOT NULL, wkb_geometry geometry(Geometry,4258), tiee_tila numeric(9,0), vaylatyypp…
jeran
  • 73
  • 1
  • 5
6
votes
2 answers

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 been closed on server 'pg' Waiting for the WAL file…
Saurabh Nanda
  • 333
  • 1
  • 4
  • 16
6
votes
1 answer

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 CREATE TABLE main (like other); INSERT INTO…
user2274074
  • 473
  • 3
  • 8
  • 14
6
votes
1 answer

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 the basic functionality provided is wanted and I…
mark
  • 274
  • 1
  • 2
  • 9
6
votes
1 answer

How to verify that a PostgreSQL base + WAL backup has been restored correctly

Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various errors while running queries against the…
Josip Rodin
  • 331
  • 3
  • 9
5
votes
1 answer

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 and subscription features to copy data from A to…
Abhishek B
  • 131
  • 3
  • 10
1
2 3
…
8 9