2830
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
Hello All,
I'm working on a research part of migration from DB2 to postgres, I got to know by using AWS sct we can convert the scheme, could someone help me how we can migrate data from DB2 to postgres?
Please suggest the migration strategy from DB2 to postgres on both scheme and data? Or any tools.
https://www.postgresql.org/docs/current/sspi-auth.html
Читать полностью…
As stated, I never used it, so... Also, OP didn't share his config, maybe his/her pgpool doesn't loadbalance at all ;-)
Читать полностью…
Because the common use case for a database is reading*and* writing, eh?
Читать полностью…
when connecting through pgpool and running
select pg_is_in_recovery();im getting "F". why is it connecting to the master by default?
PostgreSQL Person of the Week interview with: Dirk Krautschick
https://postgresql.life/post/dirk_krautschick/
To clarify, the chain is logical at this point, we had it all physical to start with, the issue is a) transfer and/or bandwidth (on some providers and connections), and b) I/O on the intermediate replica which is on HDD and also serves images (yes, should be separate, our donation-based budget of ~$10/day for all services including unrelated image caches makes that hard to justify; the chain was built on servers that exist for another purpose, the primary is also the app server, etc).
I found logical was less painful for what we're doing, which may of course mean we're doing it wrong. Changes without replicating our unlogged search cache tables and a few others with temporary info we can lose are _relatively_ small. CPU was greater but we had that spare on the replicas.
To put it another way the goal is less high availability than greater assurance of having lost less data to a hardware failure and being able to get back up a bit faster, as well as running secondary DB services with spare capacity that would use up too many resources on the primary and impact the application.
So many replicas. You could also take a backup from an asynch replica instead of a cascaded replica. Logical has always been more painful than Physical for large schema or high dml rate database. Typical 3 node patroni postgres cluster would suit what you describe better than mix of logical and physical + cascaded replica.
Читать полностью…
I will create a bug but FWIW the debug messages immediately prior (the same microsecond) were:
"UpdateDecodingStats: updating stats 0x5610b1380120 0 0 0 1 1 264 1 621710620",,,,,,,,"UpdateDecodingStats, logical.c:1926"
"found top level transaction 2650000463, with catalog changes",,,,,,,,"SnapBuildCommitTxn, snapbuild.c:1136",
"adding a new snapshot and invalidations to 2650000421 at 298B/CB45EAA8",,,,,,,,"SnapBuildDistributeSnapshotAndInval, snapbuild.c:901",
"adding a new snapshot and invalidations to 2650000463 at 298B/CB45DEB0"Читать полностью…
"spill 92 changes in XID 2650000463 to disk"
"UpdateDecodingStats: updating stats 0x5610b1380120 1 1 621710356 0 0 0 0 0"
[next two with temporary position 4/4693920]
"spill 1 changes in XID 2650000463 to disk"
"UpdateDecodingStats: updating stats 0x5610b1380120 0 1 264 0 0 0 0 0"
"UpdateDecodingStats: updating stats 0x5610b1380120 0 0 0 1 1 264 1 621710620"
You should open a bug report with what you have IMHO.
On a side note: why logical replication at all? "The" primary and "the" replica sounds like binary would do just fine...
So, your suspicion that walsender tries to allocate 1.5G when it shouldn't is (obviously...) correct. Also the code you found is where the error happens. That's just the symptom though, not the course.
Did you already open a bug report?
Hello! I have a memory allocation issue when restarting logical replication after an upgrade. Today I moved our primary to PG 16.9 after upgrading the replica. It seems walsender is trying to allocate 1.5GB through an allocation path that seems to only allow 1GB max. Could anyone offer advice as to what might cause this or how to workaround it?
I think we're hitting this code, which means AllocSizeIsValid was false. The specific error is:
ERROR,XX000,"invalid memory alloc request size 1627040720",,,,,,"START_REPLICATION SLOT ""replica"" LOGICAL 298B/CB420338 (proto_version '4', streaming 'parallel', origin 'any', publication_names '""replica""', binary 'true')",,"repalloc, mcxt.c:1487","replica","walsender",,0"
"logical decoding found consistent point at 298B/CABD75E0","Logical decoding will begin using saved snapshot.",,,,,"START_REPLICATION SLOT ""replica"" LOGICAL 298B/CB420338 (proto_version '4', streaming 'parallel', origin 'any', publication_names '""replica""', binary 'true')",,"SnapBuildRestore, snapbuild.c:2043","replica","walsender",,0
Live Postgres vibe hacking with Andrey, Kirk https://www.youtube.com/watch?v=GpTJ06AFvg4 — join!
Читать полностью…
Hi Team,
Is any have idea on how to configure SSO authentication on windows PostgreSQL server?
PGPool-II does load balancing so no reason for query to always go to primary. Load balancing criteria are here
https://www.pgpool.net/docs/pgpool-II-4.2.5/en/html/runtime-config-load-balancing.html#5.7.1.%20Condition%20for%20Load%20Balancing
I've never tried the HA stuff of pgpool. But if I would, I would expect it to route everything to the primary by default. 🤷
Читать полностью…
Probably because that's what 99.9% of users expect it to do?
Читать полностью…
Hi friends
I am looking for Powerbi developer or data Analyst role,
I am immediate Joiner and have around 5 years of experience, any lead will be helpfull
The appropriate form is https://www.postgresql.org/account/submitbug/ (at least that's what I'm doing). Check the bug reporting guidelines linked there to see if there's a reason not to report it? But if it says you should, that's quite a hint.
Читать полностью…
wanna ask if I run into somthing in postgreSQL and found a statement pls report this as a bug....Is it really a bug and should be reported?? and how to report that ?
Читать полностью…
Fair enough, *that* sounds much more like logical replication ;-)
Читать полностью…
There are actually more replicas, including partial ones, based off of the initial replica, including a cascading full replica for backup and performing recommendations queries when the first replica is in maintenance and a more partial one for testing a potential keyword autocomplete microservice.
Using logical replication appeared to reduce churn on the replica and replication bandwidth at a reasonable CPU cost. I could exclude tables which are not needed to recover user data and which only had cached search results (to take one big example).
I'm afraid not. I was trying to find a way to deal with the situation but had to drop the subscription because I ran out of SSD on the master (this is for a community art site that gets a lot of traffic at the weekends, it ate through our ~60GB reserve quickly).
I did create a debug3 log seemingly capturing the issue which suggested it was trying to create(?) a snapshot at the time (will get details shortly, at breakfast now). I also have two WAL files relating to what I believe are the relevant transactions.
Hi
I am looking for change have 5.5 years of experience in SQL DBA along with postgresql please let me know if any vacancy.