Questions tagged [postgresql-10]
PostgreSQL version 10
354
questions
0
votes
0
answers
23
views
Delegate (virtual) IP is set to both nodes, even if primary and secondary is well identified
I am very new to pgpool, and I want to find why in my 2 nodes cluster, the delegate ip is set on both the nodes, even if the cluster is up, and everything seems fine.
From node-0 (198) :
/usr/bin/...
- 207
0
votes
2
answers
217
views
Postgres - how to work out which tablespace is short of space?
I'm trying to add a INTEGER DEFAULT 0 field to a very large (partitioned) table spread across two tablespaces.
Each tablespace is on a different disc (one on C drive, the other on D). I get the ...
- 1,125
2
votes
2
answers
250
views
Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?
I've got a table with ~2 billions rows of data, and I would like to make another table with some aggregates. It looks like PostgreSQL uses temporary disk space for these queries. I can create the ...
- 171
3
votes
1
answer
1k
views
IN clause makes query slower
I have a question about the performance tuning of in clause in sql query. The Postgres version is 10.18.
I did two queries below. The difference of performance of those two queries are huge. For sql, ...
- 31
0
votes
1
answer
736
views
Investigating which operation is causing high CPU usage on PostgreSQL - How?
Today we had an incident in production which started as a 100% CPU usage on our RDS followed by a general performance degradation. It took us several hours of investigation on the PostgreSQL side to ...
- 103
0
votes
0
answers
705
views
psql: FATAL: no pg_hba.conf entry for host "x.x.x.x", user "postgres", database "cis", SSL off
Please help to resolve below error.
psql: FATAL: no pg_hba.conf entry for host "x.x.x.x", user "postgres", database "abc", SSL off
pg_hba.conf:
host all ...
- 31
0
votes
1
answer
145
views
PostgreSQL stops continuous recovery without error
I have some Postgresql 10 instances running on Windows Server that are in continuous recovery mode. Once in a while they just stop recovering without giving any errors, like in this example log file (...
- 33
0
votes
0
answers
238
views
Error- Index is not a b-tree postgres
In our production, environment suddenly we recieved error that index is not a btree for one index.After performing reindex on the same, we were able to access the table, But we are unable to get root ...
- 1
0
votes
0
answers
132
views
Suddenly TPS is too high like 90K to 3000K but active session is 250
My DB application connected with pgBouncer and pgpool
Node info:
3 Pgbouncer
3 pgpool
3 dbnode (1 is primary 2 is standby)
For pgbouncer: default_pool_size=200 ,reserve_pool_size=10
For pgpool: ...
4
votes
1
answer
7k
views
How do you reset a serial type back to 0 after deleting all rows in a table?
I previously asked this question , and somebody posted a very helpful answer on db fiddle here. This has worked really well for me. However, now I would like to ask a new (related) question :
Suppose ...
- 411
1
vote
1
answer
619
views
how to optimize multiple unions for the same table with different condition into a single query
Environment : PostgreSQL v10.6 (AWS RDS instance)
For the given table:
CREATE temp TABLE lve
("fin" timestamptz, "vehiculo" int, "equipo" int)
;
INSERT INTO lve
...
0
votes
0
answers
364
views
I have an issue with postgresql, something is preventing the postgres to startup. I cant figure out the solution for this issue
I have an issue with postgresql, something is preventing the postgres to startup in the cloud server. I cant figure out the solution for this issue.
here i attached the log file.
2021-12-18 10:19:08....
0
votes
0
answers
61
views
How to drop a table with invalid block in pg_attribute?
QGIS is unable to access one my DB.
Tracking the issue, I found that the broken relation was pg_attribute.
From there, I managed to identify the table causing the issue.
select * from pg_attribute ...
- 45
1
vote
0
answers
143
views
How to write the query in postgres in which i have to join two tables with specific condition?
I have to build a query the example fiddle is given here.
In this ctx.params.user_id Is a variable
The conditions which i have to satisfy is as follows.
If the value in effective_employee_type = ALL ...
- 69
0
votes
0
answers
226
views
pg_ctl error when using pg_upgrade
We are attempting to upgrade Postgres 10.10 to 12.4 using pg_upgrade on a Windows 2016 Server machine using the command
"C:/Program Files/PostgreSQL/12/bin/pg_upgrade.exe”
--old-datadir "C:/...
- 1
1
vote
0
answers
432
views
FK constraint ON DELETE RESTRICT not working on some database instances
My database instances appear to treat FK constraint ON DELETE RESTRICT differently and I don't know why. On production database instance (and a clone, where I do my testing), I believe the RESTRICT is ...
- 111
0
votes
1
answer
926
views
Create table with foreign keys blocked by other Table-level Lock
I'm not pretty sure it is database question or development-process question, but I will kindly give it a try.
When creating a new table B with foreign keys to table A, the operation tries to acquires ...
- 135
2
votes
2
answers
1k
views
pg_dump hangs in Git Bash but works in Windows command line
I tried to make a backup of a database from Git Bash environment, but the executable does not output anything.
If I specify the filename using the -f flag, a blank file gets created, so the executable ...
- 173
0
votes
0
answers
52
views
Postgres connection logged temp file, but no query?
I'm going over some logs for a Postgres database and came across the following logs and I'm at a loss as to what would've caused it.
connection authorized: user=app1 database=pg SSL enabled (protocol=...
- 581
1
vote
1
answer
2k
views
Optimizing a recursive CTE or replacing it with a temporary table
I have a recursive query like this:
with recursive PCte(id) as
(
select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
('123', '124','125', 'n')
union all
...
2
votes
1
answer
2k
views
PostgreSQL slow performance using "or" operator
Development team on my company built the following query:
select
distinct c.customer_id
from table1 c
join table2 l
on c.customer_id = l.customer_id
join table3 cal
on c....
- 23
0
votes
1
answer
43
views
Unable to locate and login to by PostgreSQL
I am using Ubuntu 20.04 LTS, and I have configured PostgreSQL 9.5 to work on the server. I am trying to log in but I have forgotten the password for the user postgres and therefore I tried to reset it ...
-1
votes
1
answer
92
views
db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function
When running the following query I receive an error:
SELECT
$__timeGroupAlias(receivedat,$__interval),
SUBSTRING(message, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}') AS the_address,
COUNT (message) AS ip
...
- 1
0
votes
1
answer
283
views
Is renaming schemas more efficient / faster than drop/create in a single transaction
Given the constraint (from customer side) to having only one database - how can I most effectively deploy new versions of data into a production-facing application.
The new data is packaged into a (-...
- 207
1
vote
1
answer
1k
views
Postgres schema based replication
I have setup streaming replication in postgresql V10 in the production environment. Now for a typical scenario, I would like to know if I can configure replication in such a way that only specified ...
- 11
1
vote
0
answers
366
views
postgreSQL slow query with high shared read
i have a problem with my postgreSQL database, actually i'm developer not DBA so i don't have enough experience to deal with problems like this.
I ran this query at my psql:
explain (analyze true, ...
- 11
0
votes
2
answers
86
views
Seemingly simple query with unexpected execution time
I have the following schema :
And want to get the last price (based on transaction_datetime) per test_acts.code using the following query :
SELECT
code, price
FROM
test_transaction_acts,
...
6
votes
2
answers
2k
views
AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?
I'm using AWS Aurora PostgreSQL Serverless with autoscaling.
It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with ...
- 163
0
votes
1
answer
197
views
What is difference between START WAL LOCATION, STOP WAL LOCATION, and CHECKPOINT indicated in the backup history file?
I am a bit confused about these locations. Could someone give a short definition for these locations.
- 1,253
0
votes
1
answer
164
views
Actual used and free space in WAL segments
I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases.
In SQL Server, we are able to find how much space is actually used in pre-allocated ...
- 1,253
1
vote
0
answers
136
views
An update operation that would target 0 rows, aborted by timeout
I have an update operation that is being executed on a quite large table (postgresql@10):
UPDATE contacts_trashbin
SET op_id='\x60771801dbed2d6e021ec067'::bytea
WHERE contacts_trashbin.company_id = '...
- 143
0
votes
0
answers
100
views
PostgreSQL: Query runs fine in Prod but really slow in QA
Postgres 10.4 dbs hosted in AWS environment. I have a query which joins two tables with many-to-many relationship. I learned while troubleshooting that Analyzer won't run until number of rows inserted ...
16
votes
2
answers
20k
views
What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) [duplicate]
I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it)...
- 271
1
vote
0
answers
150
views
PostgreSQL cannot work when opens SSL on Windows 10
I am not sure this topic will fit here but I don't know ask it where.
I am trying to open the SSL of PostgreSQL 10.16 on Windows10.
I read a lot of documents about creating an SSL certificate to ...
- 11
0
votes
0
answers
173
views
Check when a privilege was granted - PostgreSQL
Is there any information_schema view that can tell when an object privilege was granted?
The pg_namespace view doesn't have that.
select
*
from pg_namespace
join lateral (
SELECT
*
from
...
- 13
0
votes
2
answers
1k
views
Postgres index scan on large table taking a long time
I have a large table (~500M records, 85GB in size) on a PostgreSQL 10.10 database (AWS RDS instance).
The records are created by a bunch of game simulations which insert "events" whenever ...
- 3
0
votes
1
answer
96
views
Why is an unindexed range operator (<@) faster than using BETWEEN with an index?
NB This is the same setup as this question, where here I'm asking specifically about something I was specifically not asking about over there.
I've got a table with a column utc timestamptz, with a &...
- 233
1
vote
1
answer
444
views
Diff in idx_tup_read and idx_tup_fetch in postgres pg_stat_all_indexes
I have gone through documentation and previous stackoverflow questions but not findings those helpful.
Postgres version 10.6
Even for index only scans values of idx_tup_fetch is increasing. So I am ...
- 125
0
votes
1
answer
61
views
PostgreSQL skips WITH query
Consider this CTE:
WITH division_by_zero AS (
SELECT 1/0
)
SELECT 42
It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?
- 287
2
votes
1
answer
6k
views
postgresql: Invalid data directory | Can't open PID file /var/run/postgresql/10-main.pid (yet?) after start: No such file or directory
This error is showing after running this command: sudo systemctl status [email protected]
[email protected] - PostgreSQL Cluster 10-main
Loaded: loaded (/lib/systemd/system/...
- 121
1
vote
0
answers
162
views
Postgresql Database leaking t99_8989898 files in db directory or db size is showing incorrect in postgres
postgresql version is 10
\l+ showing db size to be 7228 GB
i checked all tables sizes those comes out to be 60 GB only
using
SELECT relname AS "relation",
pg_size_pretty(...
- 69
-1
votes
2
answers
119
views
casting custom string to date in SQL
I have a column with strings such as 2015-W02, meaning the second week of 2015. I would like to convert it to a date (e.g., corresponding to the Saturday of that week). It does not seem that CONVERT ...
0
votes
1
answer
2k
views
How to get the last non-null value that came before the current row
Say I have the following table:
create table tab (ts integer, a integer);
insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3);
When i query the data there may be some nulls:
select * ...
- 135
1
vote
1
answer
546
views
How to define a parameter that will be used as an IN argument inside a function?
I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and ...
- 133
3
votes
1
answer
10k
views
Passing parameters for dynamic SQL in a function
I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In ...
- 79
0
votes
1
answer
268
views
Give alias / column definition list to ROWS FROM()
This obviously doesn't work:
SELECT regexp_matches[1], regexp_matches[1]
FROM ROWS FROM (
regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
);
Error:...
- 287
7
votes
3
answers
8k
views
Why is there no max(uuid)/min(uuid) function?
Why can I use a UUID to sort rows:
SELECT uuid_nil()
ORDER BY 1;
But I cannot compute the maximum value:
SELECT max(uuid_nil());
[42883] ERROR: function max(uuid) does not exist
Hint: No function ...
- 287
0
votes
1
answer
140
views
VACUUM FULL restored very small amount of disk space
I have a large table with ~130m entries. I recently removed unnecessary entries and it should be left with ~60m.
I ran VACUUM(FULL) [table_name] and after it finally finished it restored only 2GB of ...
- 5
1
vote
1
answer
124
views
Postgres partition avoiding redundant column
Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value.
Do I then need the ...
- 113
2
votes
1
answer
377
views
Should I duplicate columns between tables to speed-up aggregations like SUM? [closed]
I have two tables in PostgreSQL 10.12 database:
waste_card (id (PK), user_id, manufacture_date, transfer_date, address_id, card_type, .. (and other card_specific_firelds)
wastes (user_id, ...
- 123