Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
1
vote
1answer
14 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
0answers
8 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 | ...
0
votes
2answers
55 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 | ...
0
votes
0answers
8 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
1answer
18 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
1answer
85 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
1answer
47 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
0answers
107 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 ...
0
votes
2answers
44 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
1answer
23 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
1answer
17 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, &...
0
votes
0answers
18 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 ...
0
votes
1answer
121 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
38 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&...
1
vote
3answers
154 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
21 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
18 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
39 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
18 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
4 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
107 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
66 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
110 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
27 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
108 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
491 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
16 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
66 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
19 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
190 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
128 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
191 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
33 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
0
votes
1answer
173 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
2answers
3k 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
72 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
131 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 ...
3
votes
3answers
965 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
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, ...
0
votes
1answer
29 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
1answer
2k 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
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 ...
1
vote
2answers
258 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
49 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
209 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
83 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
150 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 ...
1
vote
1answer
793 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
66 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 ...

1
2 3 4 5
11