Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
-1 votes
0 answers
7 views

PG Admin schema diff not showing table differences

PG Admin schema diff not showing table DDL differences . I have latest PG Admin GUI 6.13 . However it shows Types,Casts Foreign servers .
  • 1
0 votes
1 answer
16 views

Different authentication behaviour when starting postgres from pg_ctl and when starting from systemd

I am running postgres 9.6 cluster on ubuntu 20. All installation and starting the database is fine, however, I am experiencing a weird behavior. In the pg_hba.conf file, I am trusting any connection ...
0 votes
0 answers
156 views

Loop through a set of values, sum a column and return a value even if no rows exist in sum. similar to an Outer Join

Title is probably not worded very well so here is what I am having problems with... Need to return a row for each region.id, style.style_code, sku.color_code regardless if there is a row in the "...
  • 1
0 votes
0 answers
21 views

Postgres execute requests isolatedly

Users submit schema and query. I need to execute them and return the result. But if multiple requests have the same table in the schema mentioned, then I am getting conflicts (which is as expected). I ...
  • 101
1 vote
1 answer
521 views

Postgres "unnesting" multiple json arrays simultaneouesly with different depths

I am trying to explode/unnest a row of jsonb arrays into individual rows and keep any rows that would result in nulls. Fiddle: https://www.db-fiddle.com/f/pgd6E5yKf8NzPtgboeoC6c/7 Input table: (I am ...
  • 113
0 votes
1 answer
473 views

Foreign key refencing multiple primary keys

I have multiple tables sharing a common unique ID in my Postgres database. Parent tables: table1 - id1 table2 - id2 table3 - id3 id1, id2, and id3 are unique across the database. Child table: ...
1 vote
1 answer
31 views

query to find indexes affected by locales upgrade does not work

While upgrading a system from Debian stretch to Debian buster, I got a warning about collation changes with a link to https://wiki.postgresql.org/wiki/Locale_data_changes the database is currently ...
3 votes
1 answer
562 views

Best way to map different JSON keys to same target columns

I have CSV import data that comes from multiple vendors with 50+ different columns, and each vendor has slightly different naming. Instead of trying to spend hours mapping all of the column names to a ...
  • 203
0 votes
1 answer
64 views

Postgres (9.6) hangs on start after storage upgrade

I have been running Postgres on a Linode with the data directory mapped to an external volume. Yesterday Linode prompted me to upgrade my storage to NVMe so I did. Unfortunately, following that, ...
  • 101
1 vote
0 answers
141 views

Which is the better way to select the last entry row from a table in PostgreSQL?

I need to test an attribute from the last entry row from a table to trigger a trigger function on PostgreSQL 9.6. Just below, there is a little piece of my table. It's doesn't have a timestamp column, ...
1 vote
1 answer
588 views

Failed Postgres Replication from master to standby

I have a master-standby postgres cluster; For availability issue, I want to add a new standy server. So I created a new server, did base installation as installing postgres, creating postgres data ...
0 votes
0 answers
11 views

Update one table from using specific data from multiple columns of another table

Working with two tables, c and t. In table c is an ID from table t. Using some criteria in t, I want to change the referenced t.id in c from one row in t to another: Table c now: | id | tid | | 1 | ...
1 vote
2 answers
655 views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
  • 506
0 votes
0 answers
13 views

Create role with Grants and a its opposite function to drop role, logic correct?

I want to create a function where I do the following: CREATE ROLE old_role GRANT masteruser TO old_role FOREACH DATABASE GRANT CONNECT ON DATABASE the_db to old_role ; FOREACH SCHEMA ...
-1 votes
1 answer
117 views

ALTER DEFAULT PRIVILEGES IN SCHEMA vs ALTER DEFAULT PRIVILEGES FOR ROLE, what is the difference?

What is the functional difference between these two commands? ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE ALL ON FUNCTIONS FROM old_role; ALTER DEFAULT PRIVILEGES FOR ROLE old_role IN ...
2 votes
1 answer
302 views

PgBackrest perfom restore without wal files

Is it possible to restore a full backup from pgbackrest without the archive files? I tried to restore a full backup on another server but when I tried to start the instance via pg_ctl start I get an ...
0 votes
1 answer
157 views

Made a plain-text backup (SQL script file format), how do I restore just the data (assuming the schema is valid)?

I use to backup my DB with pg_dump -cF p my_db > snapshot.sql. How can I restore only the data from one of those files (assuming the schema is valid)? Do I have to issue just and all the COPY ...
0 votes
0 answers
512 views

Postgresql pg_logical sequence replication not synchronized

I'm testing pg_logical extension for Postgresql because we need to replicate some clusters with Postgresql 9.6 before upgrading them to newer version, where logical replication is a core feature. I've ...
  • 125
0 votes
2 answers
170 views

Postgres: Show sql´s of wal segments

I am trying to set up wal archiving for the first time. I did add the folder with correct permissions: backup/wal Then i added these entries to the postgresql.conf: wal_level=archive archive_mode=on ...
0 votes
1 answer
148 views

select time intervals from a nested query

I have a table which has the following columns: id, start_timestamp, device_id I am trying to get results of all start_timestamps within 5 seconds of a range of generated periodic timestamps. For ...
0 votes
1 answer
321 views

how to add a new property in jsonb field type?

I have a jsonb field type named "attributes" in my table, it contains like below: "attributes" { "pid": 5, "extras": { "option1": true, &...
  • 1
0 votes
1 answer
285 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
0 answers
43 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&...
  • 111
1 vote
3 answers
187 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.* ...
  • 115
0 votes
1 answer
75 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\ -...
  • 11
1 vote
1 answer
84 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.
  • 35
1 vote
1 answer
287 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 ...
  • 13
0 votes
0 answers
386 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 ...
  • 11
0 votes
1 answer
37 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
2 answers
249 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
2 answers
146 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
2 answers
96 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....
  • 103
1 vote
1 answer
532 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 ...
  • 121
0 votes
1 answer
1k 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 ...
3 votes
2 answers
244 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 ...
  • 132
0 votes
1 answer
28 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 ...
  • 341
0 votes
2 answers
417 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 "...
  • 506
2 votes
1 answer
437 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 ...
  • 21
0 votes
1 answer
52 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
  • 101
0 votes
1 answer
543 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'...
1 vote
2 answers
8k 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
1 answer
109 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
0 answers
327 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 ...
  • 261
3 votes
3 answers
2k 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 ( ...
  • 149
0 votes
1 answer
53 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, ...
  • 149
0 votes
1 answer
57 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 ...
5 votes
1 answer
6k 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 ...
  • 137
1 vote
1 answer
35 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
2 answers
721 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 | ...
  • 113
0 votes
1 answer
79 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 ...

1
2 3 4 5
11