Questions tagged [postgresql-10]

PostgreSQL version 10

Filter by
Sorted by
Tagged with
0 votes
0 answers
25 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/...
iXô's user avatar
  • 207
0 votes
2 answers
222 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 ...
ConanTheGerbil's user avatar
2 votes
2 answers
262 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 ...
Michal Charemza's user avatar
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, ...
ForrestBB's user avatar
0 votes
1 answer
746 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 ...
Kousalik's user avatar
  • 103
0 votes
0 answers
710 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 ...
NEI's user avatar
  • 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 (...
Norba's user avatar
  • 33
0 votes
0 answers
241 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 ...
Siva 's user avatar
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: ...
Sheikh Wasiu Al Hasib's user avatar
4 votes
1 answer
8k 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 ...
user1068636's user avatar
1 vote
1 answer
631 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 ...
Felipe Guajardo's user avatar
0 votes
0 answers
368 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....
Kiran Shankar's user avatar
0 votes
0 answers
62 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 ...
Boodoo's user avatar
  • 45
1 vote
0 answers
144 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 ...
arunwebber's user avatar
0 votes
0 answers
227 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:/...
BigClyde's user avatar
1 vote
0 answers
435 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 ...
Dr Rob Lang's user avatar
0 votes
1 answer
935 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 ...
Cowabunga's user avatar
  • 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 ...
JohnEye's user avatar
  • 173
0 votes
0 answers
53 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=...
ChrisJ's user avatar
  • 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 ...
Itan Reimbergh's user avatar
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....
PHP's user avatar
  • 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 ...
Fatjon Lala's user avatar
-1 votes
1 answer
96 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 ...
Irakli's user avatar
  • 1
0 votes
1 answer
287 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 (-...
F.P's user avatar
  • 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 ...
xplorer's user avatar
  • 11
1 vote
0 answers
369 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, ...
DrJempo's user avatar
  • 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, ...
Louis Gendron's user avatar
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 ...
Brandon's user avatar
  • 163
0 votes
1 answer
198 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.
Rauf Asadov's user avatar
  • 1,253
0 votes
1 answer
167 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 ...
Rauf Asadov's user avatar
  • 1,253
1 vote
0 answers
137 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 = '...
Anton Koval''s user avatar
0 votes
0 answers
101 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 ...
ranamkhalid's user avatar
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)...
ffxsam's user avatar
  • 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 ...
Pham's user avatar
  • 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 ...
Mas's user avatar
  • 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 ...
naPalm's user avatar
  • 3
0 votes
1 answer
97 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 &...
LondonRob's user avatar
  • 233
1 vote
1 answer
451 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 ...
user3143770's user avatar
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?
xehpuk's user avatar
  • 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/...
Farid Chowdhury's user avatar
1 vote
0 answers
163 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(...
techagrammer's user avatar
-1 votes
2 answers
120 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 ...
Andrea Dapor's user avatar
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 * ...
dotconnor's user avatar
  • 135
1 vote
1 answer
557 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 ...
Geison Santos's user avatar
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 ...
khafka's user avatar
  • 79
0 votes
1 answer
270 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:...
xehpuk's user avatar
  • 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 ...
xehpuk's user avatar
  • 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 ...
xtl's user avatar
  • 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 ...
Pete Kirkham's user avatar
2 votes
1 answer
384 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, ...
mateo's user avatar
  • 123

1
2 3 4 5
8