Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
1 vote
1 answer
29 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 ...
user avatar
  • 113
0 votes
1 answer
42 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: ...
user avatar
1 vote
1 answer
24 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 ...
user avatar
3 votes
1 answer
303 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 ...
user avatar
  • 203
0 votes
1 answer
37 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, ...
user avatar
  • 101
1 vote
0 answers
38 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, ...
user avatar
1 vote
1 answer
67 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 ...
user avatar
0 votes
0 answers
9 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 | ...
user avatar
0 votes
2 answers
84 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 | ...
user avatar
  • 455
0 votes
0 answers
11 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 ...
user avatar
-1 votes
1 answer
29 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 ...
user avatar
2 votes
1 answer
165 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 ...
user avatar
0 votes
1 answer
82 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 ...
user avatar
0 votes
0 answers
248 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 ...
user avatar
  • 125
0 votes
2 answers
68 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 ...
user avatar
0 votes
1 answer
56 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 ...
user avatar
0 votes
1 answer
41 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, &...
user avatar
  • 1
0 votes
0 answers
21 views

Create multiple database READonly users for different cluster

I have 15 Postgres 9.6 version clusters runned on different port on same ip, each cluster has near 150 databases How automatically create read only user for each database on each cluster, using one ...
user avatar
0 votes
1 answer
163 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 ...
user avatar
1 vote
0 answers
39 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&...
user avatar
  • 111
1 vote
3 answers
173 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.* ...
user avatar
  • 115
0 votes
1 answer
38 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\ -...
user avatar
  • 11
1 vote
1 answer
29 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.
user avatar
  • 35
1 vote
1 answer
93 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 ...
user avatar
  • 13
0 votes
0 answers
19 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 ...
user avatar
0 votes
0 answers
22 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:/...
user avatar
  • 11
0 votes
0 answers
170 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 ...
user avatar
  • 11
0 votes
1 answer
31 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 ...
user avatar
0 votes
2 answers
115 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 ...
user avatar
3 votes
2 answers
114 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 ...
user avatar
0 votes
2 answers
36 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....
user avatar
  • 103
1 vote
1 answer
226 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 ...
user avatar
  • 121
0 votes
1 answer
851 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 ...
user avatar
3 votes
2 answers
116 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 ...
user avatar
  • 132
0 votes
1 answer
24 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 ...
user avatar
  • 341
0 votes
2 answers
280 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 "...
user avatar
  • 455
2 votes
1 answer
287 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 ...
user avatar
  • 21
0 votes
1 answer
37 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
user avatar
  • 101
0 votes
1 answer
274 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'...
user avatar
1 vote
2 answers
5k 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 -------------+----------------------...
user avatar
0 votes
1 answer
84 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:...
user avatar
0 votes
0 answers
194 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 ...
user avatar
  • 241
3 votes
3 answers
1k 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 ( ...
user avatar
  • 149
0 votes
1 answer
38 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, ...
user avatar
  • 149
0 votes
1 answer
38 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 ...
user avatar
5 votes
1 answer
4k 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 ...
user avatar
  • 137
1 vote
1 answer
30 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 ...
user avatar
1 vote
2 answers
404 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 | ...
user avatar
  • 113
0 votes
1 answer
51 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 ...
user avatar
0 votes
0 answers
415 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 ...
user avatar
  • 101

1
2 3 4 5
11