255 questions
0
votes
1
answer
59
views
How to reset Debezium offset to start streaming from different DB server after migration
We are using Debezium connectors to stream PostgreSQL data into kafka. We will have a major version upgrade on PostgreSQL(13 to 16) by side by side approach using logical replication to new server. ...
- 194
0
votes
0
answers
57
views
Setting from UNLOGGED to LOGGED takes a long time
I am trying to load data with pictures stored in a BLOB column. Each picture is around 1MB in size. I have about 200K rows.
First try, I copy the table straight away. After about an hour, it stops and ...
- 265
0
votes
0
answers
41
views
PostgreSQL Logical Replication Slot size is getting bigger without new records beeing generated
I have a postgresql source database where I have enabled logical replication to replicate data in another postgresql destination database. Publication contains only one table which is arround 3MBs of ...
- 1,452
0
votes
0
answers
123
views
How to get postgres to clean up pg_wal at patroni standby instance?
We have a lot of postgresql clusters set up with patroni.
Most of the time and most of all clusters are behaving as expected.
But at one of the standby nodes WAL files are not removed and so pg_wal ...
- 11
0
votes
1
answer
158
views
Forcing a WAL switch every 30 mins
I'm doing postgres pg_basebackup's (Via barman) on a primary cluster and a read only standby postgres cluster (they are in different data centres)
I'm hitting an issue on DB's with low amounts of ...
- 25
0
votes
0
answers
21
views
Proper way to use transactions in SQL with WAL
I frequently get a Database Locked error when multiple workers access a database of tasks. As the tasks themselves are considerably longer than updating their status, I wrap each update call with a ...
- 3,427
0
votes
1
answer
68
views
wal_init_zero in PostgreSQL
In the original CL here, author mentioned a discussion linked here. However, this discussion seems to focus mainly on the allowance for disabling WAL recycling. Is this the correct discussion, or is ...
- 291
0
votes
0
answers
94
views
What's the proper way to enable SQLite WAL mode with Kotlin's Exposed?
I tried this, using setupConnection, got it from a github issue:
import org.jetbrains.exposed.sql.Database
import org.sqlite.SQLiteConfig
Database.connect(
"jdbc:sqlite:$dbPath",
...
- 33
0
votes
0
answers
140
views
Postgres 15 in AWS RDS WAL disk space consumption > 200GB with Debezium 2.6 with Kafka connectors
we are using Debezium 2.6 with Kafka connectors and has a Spring boot microservice that consume messages from Kafka. We also have 10 databases (Postgres 15) with many schemas per tenant in the same ...
0
votes
0
answers
44
views
Sqlite-jdbc check if WAL mode is enabled
Is there a way to check if WAL mode is enabled for a database using sqlite-jdbc?
- 1,556
3
votes
2
answers
416
views
Are PostgreSQL data files only written to disk during checkpoint?
On every commit, PostgreSQL flushes WAL to disk synchronously (configurable via wal_sync_method) to guarantee durability. Data files only need to be in shared buffers, can be flushed to disk later.
My ...
- 35
0
votes
0
answers
116
views
AWS RDS PostgreSQL: How to adjust wal_level from 'replica' to 'minimal'?
Our AWS RDS PostgreSQL v.16 db uses wal_level=replica. This causes more data to be written to the transaction logs (for PITR recovery ++). We'd like to reduce logging in order to increase performance, ...
- 11
1
vote
0
answers
396
views
Postgres connection failed while Debezium connector streaming changes
On Our Spring boot Auditing service(used Debezium embedded engine) occurs error at different time intervals, I don't know the cause, please help me to understand and fix the problem.
Another ...
0
votes
0
answers
88
views
PostgreSQL writes several trancations into loop as one transaction with subtransactions
Sometimes, if database is big, PostgreSQL writes inserts in loop as a transaction with sub transactions into wal file.
For example,
begin
for i in 1..10000000 loop
INSERT INTO src.customers
(id, ...
- 156
0
votes
0
answers
629
views
PostgreSQL: How and Why does the WAL (Write-Ahead Log) Grow?
I have a problem with the growing number of WAL files, and they just keep increasing. How can I influence this? I have already deleted old transactions and unnecessary replication slots, which ...
0
votes
1
answer
62
views
How does Write Ahead Logs work in conjunction with inserting with STDIN CSV in Postgres?
I'm curious for how the WAL works when I load a CSV file with
COPY persons(first_name, last_name, dob, email)
FROM 'C:\sampledb\persons.csv' DELIMITER ',' CSV HEADER;
So if, for example, I'm ...
- 1,891
0
votes
0
answers
83
views
Postgresql 14 Logical Replication
I have set up a logical replication between Aurora Postgresql to AWS RDS Postgresql which was running fine to a certain point and got stuck.
I disabled and dropped the publication and subscriber slot. ...
1
vote
0
answers
42
views
Why flag "begin" in leveldb WAL is defined as ptr == slice.data()?
This code is in leveldb to get the type of fragment type in WAL
Status Writer::AddRecord(const Slice& slice) {
RecordType type;
// Why not defined as left == slice.size();
const bool ...
- 21
0
votes
1
answer
1k
views
Why isn't it possible to disable archive mode in CloudNativePG PostgreSQL operator in kubernetes? [closed]
I'm configuring a CloudNativePG PostgreSQL operator in kubernetes. The data there is not very important and it is ok if we loose it. I want to disable WAL archiving because the archive files in the ...
- 1,758
1
vote
0
answers
2k
views
How to change wal_level to logical in postgresql database when configured via Helm charts
I deployed timescale-postgresql using helm:
Chart Name: timescalepostgres timescale/timescaledb-single --version 0.33.1
In the values.yaml file I kept the wal_level = logical, however after the ...
- 11
0
votes
1
answer
1k
views
Is there a limit regarding the number of files that are stored in the pg_wal directory?
I am trying to find an explanation regarding the actual number of files that are being stored in my pg_wal directory.
I am using postgres v13 on a large DB with a lots of updates, inserts and deletes ...
0
votes
0
answers
325
views
WAL getting full on db using pg_restore in postgres
I'm trying to restore my database from a dump. I use the command pg_restore -Fc -j 8 mydumpfile -c and while in the process of restoring the tables the WAL keeps growing up to a point where the db ...
- 2,289
1
vote
0
answers
138
views
wal streaming and logical replication slot: offset of stream
Good day!
I was using pgsync for change data capture (CDC) from postgres (wal=logical) to elasticsearch.
What I noticed while using pgsync is that it captures all data from postgres on initial run.
...
- 141
0
votes
1
answer
61
views
In WAL mode, does SQLite re-evaluate the WHERE-clause whenever another transaction commits first?
According to its documentation, Postgres re-evaluates all WHERE-clauses within a transaction whenever any other transaction is committed, in order to check if the updated row still matches the search ...
- 884
0
votes
0
answers
481
views
where can I find the documentation for the output format of pg_waldump?
when asking that question : what tables and how many rows are affected by the actions in a single wal file
I was directed (correctly) to using pg_waldump and treating the zzz in rel xxx/yyy/zzz as the ...
- 51.6k
1
vote
2
answers
399
views
what tables and how many rows are affected by the actions in a single wal file
We use postgresql as a database. For the backup part, we store full cluster backups and wal files to allow point in time restoration of our database.
Our wal files take up quite a bit of space when ...
- 51.6k
0
votes
1
answer
206
views
Do transactions need WAL
I'm considering using unlogged tables for my project and found out that operations with this kind of tables are not logged to WAL, only transactions ids. Do I undestand correctly that transaction ...
- 521
0
votes
1
answer
553
views
How WAL flush the data into disk?
In WAL file, if we don't keep the actual data with Insert/Update commands, how will we get the data in a new fresh DB by applying the WAL file?
If some data is updated in diskblock in memory and wal ...
0
votes
0
answers
315
views
How is interpreted the difference between postgres buffers_alloc and sum of buffers_backend, buffers_clean, buffers_checkpoint?
When checking pg_stat_bgwriter in postgres, I am noticing that there is a difference between buffers_alloc and the sum of buffers_backend, buffers_clean and buffers_checkpoint.
What could be assumed ...
- 301
3
votes
2
answers
2k
views
PostgreSQL logical replication - Copy of the initial data
I'm starting from a scenario where a PostgreSQL instance is not configured for logical replication and has a table T with X rows.
Now, when I'll enable the logical replication, create a ...
- 187
0
votes
1
answer
629
views
Embedded Debezium WAL piling up restart_lsn not moving
So we are using debezium in our spring boot application. And while the messages are getting consumed, the restart_lsn is not moving and the replication slot lag is increasing.
postgres=# SELECT
...
- 59
1
vote
1
answer
337
views
Is rollback transactions' data written into the Postres WAL?
I have a Postgres replication slot. I rollbacked a couple of transactions but the slot size increased drastically. I do not have any slot changes in it but the size has become huge.
Is it possible ...
0
votes
1
answer
646
views
Minimizing I/O in a PostgreSQL benchmark
I am running some experiments with PostgreSQL, and it's all for the purpose of learning how Postgres works underneath, and there's no production value to what I'm testing. I'm just fiddling around ...
1
vote
1
answer
635
views
PostgreSQL not deleting old WAL archives
I have Postgresql 13 database and Barman 2.7 deployed for backing it up. The Barman is deployed on separated server. Barman is using streaming backup and WAL streaming via pg_recievewal.
However the ...
- 13
0
votes
1
answer
212
views
WAL in a Kafka producer
I have an application (in Java 8) that sends messages to Kafka. In order to improve the performance I want to configure the producer to send messages in batches (using ProducerConfig.BATCH_SIZE_CONFIG ...
- 1,727
-4
votes
1
answer
326
views
How to activate sqlite WAL with PHP?
I write this:
$db = new PDO("sqlite:$dbname");
$db->exec("PRAGMA journal_mode = WAL;");
#.....
$wal_status = $db->exec("PRAGMA journal_mode;"...
- 41
1
vote
1
answer
300
views
How to show the WAL status in PHP?
My PHP code:
$db = new PDO("sqlite:$dbname");
$db->exec("PRAGMA journal_mode = WAL;");
How do I show the status of WAL e.g. "echo....."?
Thx in advance for your hints....
- 41
1
vote
0
answers
612
views
PostgreSQL 12 WAL archive, WAL file restore
I see another questions about this but not clear or working for me.
I have one database PostgreSQL 12 version.
I made one general backup approx 3 month ago, then turned on the WAL archive.
Here is my ...
- 59
0
votes
1
answer
254
views
PstgreSQL WAL archiving wal_size change
I set up in my database WAL archive.
But when I want to set up the WAL file size,
min_wal_size = 5MB
and
max_wal_size = 5MB
( My database generating one day only 5 MB data so I like to get one WAL ...
- 59
0
votes
2
answers
2k
views
Postgresql wal log limiting (RDS)
We're using Debezium as a change data capture connector for PostgreSQL (actually RDS).
If for any reason the debezium connector has a terminal failure, the WAL will continue to grow until either ...
- 75
1
vote
1
answer
3k
views
Postgres wal archiving: archive_command not being executed
here is my configuration file:
wal_level = replica
archive_mode = on
archive_command = 'touch /var/backups/test'
archive_timeout = 1min
Permissions are ok. Postgres user can write into the aproppiate ...
0
votes
1
answer
213
views
Having trouble implementing incremental backups (wal archiving) in a postgres container
I am using a postgres image and I would like to implement incremental backups. I have mounted a backup directory from my host machine to a backup dir in the container where postgres would supossedly ...
0
votes
3
answers
2k
views
PostgreSQL - checkpoint interval behaviour in different WAL levels
I couldn't find a definite answer for my concerns, so I might as well ask it from you guys!
Long story short:
We need to perform an UPDATE command on roughly 400M rows. The command could be modified ...
- 15
3
votes
5
answers
2k
views
Why does PostgreSQL need both WAL buffer and WAL segment file?
I'm trying to understand more about the apacheAGE extension therefore I'm reading the inner workings of PostgreSQL. From what I understand every operation that alters the table is written at the WAL ...
0
votes
1
answer
665
views
ERROR: extension "pgoutput" is not available while trying to use it with airbyte to replicate the data from PostgreSQL in real-time using CDC
I am planning to use airbyte to replicate data from my PostgreSQL database to Elasticsearch in real-time using CDC. According to the documentation
The pgoutput plug-in is always present, meaning that ...
- 6,412
0
votes
2
answers
1k
views
postgreSQL how to human read WAL files
due to a bad foreign key, we had some non written inserts in to database, but i have the wal files generated when this actions, just a few, so im wondering if is possible to decrypt them and read the ...
- 11
0
votes
0
answers
237
views
Nominatim - Postgres DB growing very fast in size when doing the daily updates
I have done a full import with the planet from OSM website and scheduled the updates to run daily on a cronjob.
But I have noticed that the disk usage is growing very fast in size on a daily basis.
...
- 42
0
votes
1
answer
591
views
SQLite refuces to be set to WAL with Microsoft.Data.Sqlite .Net 7 in C#
I am unable to set WAL mode for SQLite programmatically.
My code is called when I create the database file
var conn = new SqliteConnection(_connectionString);
await using(conn....
- 327
0
votes
0
answers
149
views
Avoid 'interleaved LSN' in postgres replication slot
I am trying to stream data from postgres replication slot with output plugin 'test_decoding'. When there are big, multiple transactions running, the output plugin seems to have interleaved LSN, ie, ...
0
votes
0
answers
701
views
Archiving not works
I've setup the WAL archiving already and when I'm running the following query,
SELECT * FROM pg_stat archiver; The system gives me back this:
Archived count 0, last_archived_wal (empty), ...
