Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,510 questions
0
votes
0
answers
31
views
Postgres query tuning studying/upskilling resources
I'm a SQL Server DBA that's starting to cover Postgres/Aurora. I was curious of the best resources for upskilling on query tuning. I particularly like to read books, I find that the authors can go ...
- 23
0
votes
0
answers
40
views
Payment offer data modelling
A payment offer system, where we create offers on payment methods for users so they can get a discount paying via that payment method.
payment_method :
card - bank | network | type | bin
upi - ...
- 101
0
votes
0
answers
47
views
How to sync database with large network connectivty gaps
I have a postgres+postgis+timescaledb database running on a ship. It collects performance data (RPM, battery, fuel etc), location data (a bunch of different GPS, pitch/roll/yaw) and some sensor data (...
- 145
-1
votes
0
answers
57
views
Aurora limitless keeps creating foreign tables when I specify to create a real table
I’m facing some difficulties creating standard tables in Aurora limitless. I created a new instance of Aurora PostgreSQL 16.6 Limitless, and I simply tried to create a table.
It keeps creating a ...
- 1
-3
votes
0
answers
55
views
Monitoring tools [closed]
Currently, we use SolarWinds DPA as our database monitoring tool, but SolarWinds recently changed its billing model, making DPA very expensive. Any suggestions for a monitoring tool? We need to ...
-1
votes
2
answers
36
views
Scaling Aurora serverless databases for read-only queries
I'm supporting an environment that uses both Aurora PostgreSQL and MySQL and I'm working on a best practices document. I'm trying to figure out what would be the best strategy for scaling clusters for ...
- 189
0
votes
0
answers
26
views
PostgreSQL / TimeScaleDB issue after reimporting a huge set of data
I have a PostgresQL 17 DB with a quite simple table (no outer relations) but with a lot of data (around > 446 000 000 records) storing some events on a day basis representing more than 2 years of ...
- 1
0
votes
1
answer
27
views
Is it (still) possible to pass multiple arguments to a shell command in PostgreSQL COPY FROM PROGRAM?
I am trying to execute a simple shell (MacOS zsh/bash) command from within PostgreSQL using data queried from a table.
I've tried various experiments but they all come back with
[38000] ERROR: command ...
- 594
0
votes
1
answer
40
views
Analyzing logged slow queries on Aurora PostgreSQL
I manage a bunch of Aurora MySQL clusters, and recently took on some Aurora PostgreSQL clusters. For MySQL I use the slow query log in combination with a script I have to download the logs locally, ...
- 189
0
votes
1
answer
44
views
Why does the PostgreSQL mode() aggregate use WITHIN GROUP?
The mode() aggregate function selects the statistical mode. Some examples:
coredata_api=# create table t1 (pet text, meme int);
coredata_api=# insert into t1 (pet, meme) values ('dog', 6), ('dog', 6),...
- 719
0
votes
1
answer
27
views
RDS Blue / Green fails with "cancelled due to a long-running transaction"
I'm trying to run a RDS blue / green migration on a Postgres database. The error log shows an immediate failure (at least the 'start' and 'fail' log messages have the same time stamp).
December 09, ...
- 1,667
1
vote
0
answers
39
views
\connect to a Pg db in a one-liner/bash command, does not find the end of line?
I am trying to automate initialising a database via a bash script which creates SQL code to be executed by psql. I use environment variable PGSERVICEFILE to supply credentials.
In the following script:...
- 113
1
vote
0
answers
32
views
Function-by-function definition of config parameters in plpgsql?
Are all of the SHOW ALL configuration parameters something that can be defined on a function-by-function basis as described in
https://www.postgresql.org/docs/current/plpgsql-implementation.html#...
- 395
1
vote
0
answers
119
views
Postgres 18: UPDATE ... FROM with CTE + FOR NO KEY UPDATE sometimes returns fewer rows under concurrency
I have a strange issue that I can reproduce only on PostgreSQL 18.
The same exact workload on PostgreSQL 16 or 17 works perfectly.
I have the following table:
CREATE TABLE public.ledger_account
(
...
- 11
4
votes
1
answer
214
views
Postgres logical replication to restored snapshot
I am attempting to logically replicate a production postgres instance to facilitate a blue/green major version upgrade from 13 -> 18 (don't ask).
First, I start a replication slot on the blue (...
- 49
