Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
9 views

Postgres Database flagged as STORAGE FULL whereas there's 45 Gb available

I will start this post by stating that I am not very experienced in database administration, please accept my apologies for any unclear explanation you may find below. We have a replica postgresql ...
1
vote
0answers
36 views

PSQL 9.6 - OR query is much slower on joined table

We have a production POSTGRES 9.6 db with about 100M records (LOYALTY) and new table (has just 4 data as of now) "INFO" Full index name on table loyalty indexname "loyalty_activated_at&...
0
votes
2answers
81 views

PostgreSQL query performance issue

We are sometimes getting poor performance (~14s) when using the following query (PostgreSQL 9.6) to fetch rows from the table items whose ID is present in the table items_categories: SELECT items.* ...
0
votes
1answer
14 views

pg_basebackup: could not open transaction log file

Version: Postgres 9.6 (Windows) I am attempting to do a pg_basebackup but I receive the following error PS C:\Program Files\PostgreSQL\9.6\bin> .\pg_basebackup -h 192.168.0.185 -Fp -D D:\pg-test\ -...
1
vote
1answer
17 views

Get definiton scripts of all the (mat)views in a schema at once

I got a schema with several mat views in it, now I need a simple solution (a SELECT statement for example) to get all the CREATE queries.
1
vote
1answer
13 views

pg_dump while inserting data

I need to dump some tables of my postgres database and I am scared of getting inconsistent data.The database is verly large and the dump lasts some minutes. On my research I found a command to dump ...
0
votes
0answers
4 views

Nothing happens after backup job is created

I'm running postgreSQL 9.6/Pgadmin4 on a macOS and I'm trying to backup a database in pgadmin4 by : Right click on the DB Backup Once the backup job is created nothing happens afterwards and the ...
0
votes
0answers
3 views

Does postgresql statement_timeout also apply to a blocked query

Does postgresql statement_timeout also apply to a blocked query (version 9.6)? From my tests this does not seem to be the case. I created a blocking query using the following example taken from https:/...
0
votes
0answers
31 views

How to restore a standalone server from a pg_basebackup using the fetch option

I am attempting to make a backup & restore of a production Postgres server (9.6 Windows) for development testing. This server is standalone and does not need to do streaming replication. I have ...
0
votes
1answer
30 views

Pivoting a two-column table to show if a permutation exists

Struggling with the terminology here... I have a table of elements which may or may not be present in a group: elem | grp ---------- 1 | A 1 | B 2 | A 3 | C I'd like to turn this into a ...
0
votes
2answers
26 views

ALTER ROLE on replica

I have a primary server, with a secondary replicating it. I want to change a role from LOGIN to NOLOGIN. I can do this easily on the primary, but the change doesn't replicate to the secondary, and I ...
3
votes
2answers
98 views

Optimization of recursive view

I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long. To expose my problem, here is a ...
0
votes
2answers
17 views

Revoke privileges for a user on tables with like names

Attempting to adapt this solution, but for revoking privileges in this way: DO $$ DECLARE t record; BEGIN FOR t IN SELECT table_schema, table_name FROM information_schema....
1
vote
1answer
29 views

How to resolve duplicate constraint names in Postgres?

On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to ...
0
votes
1answer
41 views

pg_dump --exclude-table not working on one specific table

Wanting to exclude a few very large table in a pg_dump, I use the -T option multiple times, like this: pg_dump --clean clientportal -T article_plain -T callstats -T cdrviewer_cdr -T calls -T ...
0
votes
0answers
11 views

Constraints number is differents after migrating from postgres 9.6 to 12.5

I have 2 postgres versions : 9.6 and 12.5 I have loaded the same database in them ( the famous dvdrental database) I was checking the differences between the 2 databases running multiple queries for (...
3
votes
2answers
29 views

Conflict resolution for hash-backed unique constraint of large text bodies

So we've just run into ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un" Hint: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index ...
0
votes
1answer
18 views

Postgres performing SEQ scan for specific user

I am trying to see why the following query is taking so long to execute (around 5min) for a specific user but for others with the same number of rows involved it is taking 2 / 3 seconds There is one ...
0
votes
2answers
68 views

Possible to disable THE postgres role?

Postgresql 9.6, if it matters. We have an audit finding which says that the "postgres" role must not be used at all. After creating a new SUPERUSER role, is it possible to disable role "...
0
votes
0answers
74 views

docker postgres 9.6 cannot append to pg_hba.conf

I have an instance of postgresql running in a docker container that I use for development, so it's regularly torn down and built up again. End of last week, I suddenly started getting an error when ...
2
votes
1answer
87 views

Very bad query plan in PostgreSQL 9.6

I have a performance problem with PostgreSQL 9.6.17 (x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit). Sometimes very inefficient query plan is chosen for ...
0
votes
1answer
31 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
0
votes
1answer
87 views

Perform update and raise notice in Postgres Function

I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened. I don'...
0
votes
2answers
684 views

Add auto increment to already existing primary key column PostgreSQL

I have a database schema of the following table: database=# \d person Table "public.person" Column | Type | Modifiers -------------+----------------------...
0
votes
1answer
48 views

Error while connecting psql to PostgreSQL 9.6

I am using Postgresql 9.6 and getting an error while connecting psql. Error- ./psql psql.bin: FATAL: the database system is starting up. What could be the issue? In the log it says 2021-02-18 13:23:...
0
votes
0answers
64 views

Update all the sequences

I had to restore a PostgreSQL 9.6 database from dump files (created with the command pg_dump --dbname=$DATABASE_URL --username=postgres --file=/usr/postgres-dump --format=directory). After the restore ...
2
votes
3answers
215 views

Select a row from one table, if it doesn't exist, select from another table

How can I select a row from one table, but if it doesn't exist, fall back to selecting it from a secondary table? Here's a dumbed down version of what I'm trying to achieve create table table_1 ( ...
0
votes
1answer
35 views

How can you recursively query across 2 columns?

Sorry my DBA skills are a bit limited, but here's an example schema (simplified from my real table) create table test ( id serial primary key, person TEXT, start_point TEXT, ...
0
votes
1answer
17 views

How I can check whether 2 databases despite being in different postgresql servers of versions are the same?

I copied an existing posttgresql database from one server to another. The source of my data is a postgresql 9.6 whilst the newer server is in postgresql 11. So I want to know how I can check that both ...
4
votes
1answer
998 views

Could not receive data from WAL stream: ERROR: requested WAL segment has already been removed

Configuration: Postgres 9.6 with a 3 cluster node. db1 is the master, db2 and db3 are replicas. WAL files are archived in AWS S3 using custom pgrsync tool. Cluster managed by patroni. The ...
1
vote
1answer
27 views

Execution plan favorize joins over sort

recently I ran into an issue on our production server (Pg 9.6) and even though I found solution for my problem I would like to understand better the reason behind it. For easier explanation I am using ...
1
vote
2answers
94 views

Postgres prefers slow Seq Scan over fast Index Scan

Tables I have the following two tables. books, which contains ~5 million rows: Table "public.books" Column | Type | ...
0
votes
1answer
44 views

Slow query / wrong query plan for SORT BY LIMIT LEFT JOIN

I have a following 3 tables a,b,c all with compound unique key (application_id, tid) however all those tables are not related with foreign key (it's a limitation of our solution). All tables has ...
0
votes
0answers
71 views

Can't refresh Materialized View Concurrently

I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error: SQL Error [XX000]: ERROR: unexpected attlen: -2 Where: SQL statement "SELECT ...
1
vote
0answers
54 views

Flags Cloud SQL for Postgresql

Context: I have a process that runs daily, with different amounts of data, if the process is on the weekday (process last 30 days), for weekends (process last year). The input is of 10 tables of ...
0
votes
1answer
83 views

postgres cluster: Crash of master crashes the replicas too

Environment: Postgres version: 9.6 Cluster of 3 servers, with Patroni and etcd Scenario: When index of tables were started with 16 parallel requests (on a 16CPU machine), postgres on the master ...
0
votes
1answer
329 views

Why does an alias with a 'having' clause not exist in PostgreSQL?

I am trying to calculate distance between two coordinates and fetching some information based on a few conditions. A similar query with the 'having' clause was working in MySQL. But why is it not ...
1
vote
1answer
57 views

Query to find if there are more than X occurences within any period of given length minutes

I have spent many hours thinking about a solution for my problem but I give up. Let's imagine a table user_id | occurred_at -- OK match example 1 | 2020-01-01 08:00:00 <- First match of ...
2
votes
1answer
457 views

ERROR: role “user1” cannot be dropped because some objects depend on it

On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that ...
0
votes
2answers
21 views

Postgres Specific table access

We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any ...
0
votes
0answers
33 views

Postgresql - extremely slow basic SELECT query

I have a materialized view with about 1.6 million entries that gets refreshed every hour or so. I have a && and ANY() as the filters in the query. I observed the query was taking too long (...
-1
votes
1answer
141 views

Postgresql Regex - escape possessive quantifiers [closed]

I have a query that is formed in Java SELECT * from COMPONENTS WHERE name ~* = 'searchTerm'; This query hits PostgreSQL via JDBC and returns the results. I have to use ~* because the user from the UI ...
3
votes
1answer
131 views

archive_command: Can hardlink (ln) be used instead of cp?

We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool pgrsync. The archive_command was initially set to cp %...
1
vote
1answer
273 views

Issues with enabling pg_stat_statements

I am having issues quering pg_stat_statements and puzzled what else I missed? See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not ...
1
vote
2answers
213 views

In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day?

I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (...
0
votes
1answer
67 views

Postgresql - Distinct array aggregation

This is my current query SELECT project_id, release_id, array_agg(DISTINCT version_bom_id) AS version_bom_ids, sum(case when review_status='REVIEWED' then 1 else 0 end) AS reviewed_count, sum(case ...
0
votes
0answers
191 views

Data mismatch in View2 [SELECT * from View1] with View1, how to fix?

The problem I encounter can be oversimplied as CREATE View#2 AS SELECT * FROM View#1; SELECT * FROM View#2; SELECT * FROM View#1; Then View#1 and View#2 return different results. This challenges ...
0
votes
1answer
34 views

How to pick a chunk from the text column in postgresql

I have a table ABC with the following 2 columns id(PK) Name 1 Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) 56 tablet 4 x 14 tablets 2 Zocor 10mg tablets (You &...
0
votes
1answer
82 views

PostgreSQL 9.6.17 COALESCE function error

I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error: invalid syntax for type double precision: "" Example query: SELECT COALESCE(date_part('...
0
votes
0answers
24 views

PostgreSql - Join two tables to extract a combination of three columns

I have a problem where I need to join two identical tables to extract a column called status. Table 1 id | status ---------------------------------------- 1 | APPROVED 2 | WAITING 3 ...

1
2 3 4 5
10