399 questions
1
vote
0
answers
17
views
Can't see lock on table begin truncated in PostgreSQL
I am truncating a large table - a few Terrabytes in size. To ensure that there are no locking issues, I executed the following query in another window and the results are also there. Why isn't the ...
- 1,434
0
votes
1
answer
175
views
Problem upgrading Postgresql due to "The locale requested by the environment is invalid" error
I am trying to upgrade a Postgresql 13 database to version 15 using the command pg_upgradecluster 13 main but it keeps failing with this message:
Stopping old cluster...
Restarting old cluster with ...
- 5,838
-2
votes
1
answer
104
views
How to migrate 5TB of data from PostgreSQL 13 on Oracle Linux 7.9 to PostgreSQL 17 on Ubuntu 24? [closed]
I need to migrate a PostgreSQL database (~5TB in size) from version 13 running on Oracle Linux 7.9 to version 17 running on Ubuntu 24. The database includes critical data and downtime must be ...
- 57
2
votes
0
answers
54
views
Impact of azure_pg_admin Role on Query Performance in Azure PostgreSQL Flexible Server
We’re using Azure PostgreSQL Flexible Server (v.13) and noticed a significant performance disparity between users that have the azure_pg_admin role and those that don’t.
For example, queries that ...
3
votes
3
answers
249
views
Slow query on big table - with many different filters possible
I have a table:
create table accounts_service.operation_history (
history_id bigint generated always as identity primary key,
operation_id varchar(36) not null unique,
operation_type ...
- 829
-1
votes
1
answer
155
views
Issue with Debezium Snapshot: DateTimeParseException in PostgreSQL Connector
I'm encountering an issue with Debezium version 2.3.1 while processing a snapshot from a PostgreSQL database. The error occurs during the snapshot phase and seems to be related to parsing a date/time ...
0
votes
1
answer
240
views
Conflicting requests - installing postgresql
I am trying to install Postgresql on my Amazon Linux machine on AWS, and I am getting the error of the conflicting request. and I got an option to add --skip-broken. whenever I add this flag it doesn'...
- 1
0
votes
1
answer
113
views
plainto_tsquery returns empty result for string 'what' [duplicate]
I'm converting search text to tsquery with plainto_tsquery function. When running the following statement
select plainto_tsquery('english'::regconfig, 'what');
I get empty result. Server is: "...
- 101
-1
votes
2
answers
287
views
Postgresql Timeouts
Although the value of the "statement_timeout" parameter in the config file in one of my Postgresql databases is "0", there is a timeout period in my database. What is the reason ...
0
votes
3
answers
82
views
PostgreSQL CTE by id string is very cheap, but by id from query is very expensive
I use the id directly in the query, it has a low cost:
-- Query 1.
-- Nested Loop Semi Join (cost=2.27..280.78 rows=1 width=129)
EXPLAIN SELECT *
FROM view_companies_extra
WHERE company_id iN (
...
- 447
-2
votes
4
answers
186
views
Recursive query to replace null values [closed]
With the following table, I need to make a query to replace the null values of “time” (number of minutes, integer) by a value consistent with the preceding and following values, with partitioning by “...
- 23
2
votes
1
answer
1k
views
Where this read/write operations limit being set on CloudSQL?
Is this value configurable by us, or this is limit by GCP?
Other question is that write apparently are more than the limit after paralelizing from 1 to 10 threads more (only increase throughput by 2x,...
- 27.1k
0
votes
1
answer
313
views
Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?
We recently setup netdata to monitor a self-hosted PostgreSQL server.
Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?
What we did
PostgreSQL is running in v13
We've ...
- 301
1
vote
2
answers
117
views
Updating pg_attribute did not effect on actual table (postgresql 13.8)
i had 2 postgresql server with different version (9.6 and 13.9). I'm trying alter some varchar column from 20 to 50.
As many views depend on it, I'm trying to avoid using UPDATE syntax in actual ...
- 15
2
votes
0
answers
287
views
How to Optimize SQL Query Performance with Large Datasets in PostgreSQL?
I'm working on a PostgreSQL database and facing performance issues with a specific query that involves large datasets. The query is supposed to join three different tables, each containing over a ...
- 21
0
votes
1
answer
446
views
Postgres 13.12: error: { error: column cnst.consrc does not exist
I'm using AWS RDS postgres 13.12 with Node js typeorm 0.2.19.
error: { error: column cnst.consrc does not exist
query failed: SELECT "ns"."nspname" AS "table_schema", &...
- 646
0
votes
1
answer
86
views
PostgreSQL Prepared Transactions Value and Table Growth
In my PostgreSQL database, I have a table with a partition structure for a one-month time interval. In the 10th month, the data size of my partition doubled compared to other months.
enter image ...
1
vote
0
answers
2k
views
FATAL: configuration file postgresql.conf contains errors
I used postgresql almost a year on my local.
Yesterday I used it and after I update the windows. Today I can not connect to the postgresql. It gives the below error:
connection to server at "...
- 41
0
votes
1
answer
928
views
Postgresql History Error: Could not restore file "00000004.history" from archive: command not found
I am encountering an error in one of the replication clusters on my Ubuntu machine with PostgreSQL version 13 database. The error message is as follows:
FATAL: could not restore file "00000004....
22
votes
2
answers
23k
views
Install Postgresql on Ubuntu 18.04 Bionic (repo removed)
I'm getting
E: The repository 'https://apt.postgresql.org/pub/repos/apt bionic-pgdg Release' no longer has a Release file.
on sudo apt update. That means that repo for bionic is not accessible.
In my ...
- 1,083
3
votes
2
answers
2k
views
How to Connect To Delphi 11.3 CE 32 bit With Postgres 16 64 bit?
I am trying to connect to a DB made with Postgres 13. The problem is, Delphi is a 32 bit program and Postgres is 64 bit. Delphi says it needs 32 bit, but there are no 32 bit files for version 16.
I ...
- 149
0
votes
1
answer
341
views
jOOQ How do you unnest multiple columns
cf. unnest ( anyarray, anyarray [, ... ] ) in the psql docs
The example given there:
select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) →
a | b
---+-----
1 | foo
2 | bar
| ...
- 8,239
1
vote
0
answers
39
views
Replace multiple values in a single column from a mapping table [duplicate]
I have a table employee_start_dates:
employee_id
date
1
2023-01-01
2
2022-01-01
3
2021-01-01
The IDs have moved to a new system so each employee gets a new ID. I have a mappings table that maps the ...
- 1,436
2
votes
1
answer
207
views
find out elevated privileges in Postgres database
I was trying to find out the user/roles which got elevated privileges in the postgres database.
Elevated privileges like alter/drop/create/owner of table i.e. any user who can change the existing ...
- 459
-1
votes
2
answers
50
views
Create new related entity and enter its id into a link table
My foos each have a bar.
At least they're supposed to.
I now want to create and assign bars to all foos that don't.
bar is completely detached from foo, it only has an id serial that serves as its ...
- 8,239