Questions tagged [postgresql-9.6]
PostgreSQL version 9.6
513
questions
0
votes
0
answers
27
views
Postgresql alternative for trigger in multiple-row insert
I have a table where the values of different variables are stored. Basically, it is a table of the form data_table (timestamp, variable, value). One of the variables represents a state value.
Certain ...
- 103
2
votes
1
answer
43
views
Vacuum limited to 1 worker
I'm currently working with a relatively big postgres 9.6 server with multiple databases and observing the following behavior:
The server has 5 max parallel workers configured but currently, only one ...
- 23
1
vote
1
answer
41
views
How to merge 2 postgres databases using WAL files
We have two different postgres databases one for STAGING and one for PROD disconnected from each other but they have same schema, same tables with same structures and same data. The requirement is ...
- 113
0
votes
0
answers
28
views
Difference between WALs archival from primary db and Barman streaming WALs from the primary
In fact, it is not a problem, yet I find it hard to differentiate between 2 concepts
1- WALs archival: Usually done from the primary, by defining the archive_mode and the archive_command in the ...
0
votes
1
answer
21
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
444
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
25
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
2k
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
2k
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
48
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 ...
- 121
3
votes
1
answer
1k
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
107
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
345
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
2k
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
18
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
2k
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 | ...
- 559
-1
votes
1
answer
347
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 ...
- 101
1
vote
1
answer
453
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 ...
- 21
0
votes
1
answer
353
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
839
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
434
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
...
- 103
0
votes
1
answer
290
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 ...
- 187
1
vote
2
answers
1k
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,
&...
- 13
0
votes
1
answer
397
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
46
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
209
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.*
...
- 113
0
votes
1
answer
132
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
170
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.
- 135
1
vote
1
answer
587
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
558
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
42
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 ...
- 101
0
votes
2
answers
391
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 ...
- 183
3
votes
2
answers
206
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
235
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
898
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
1
vote
2
answers
2k
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 ...
- 131
3
votes
2
answers
401
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
31
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
608
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 "...
- 559
2
votes
1
answer
626
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
72
views
Postgresql - Identify process issuing a query
With this table
Table "scheduled_message"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-------...
- 111
0
votes
1
answer
940
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'...
2
votes
2
answers
12k
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
-------------+----------------------...
- 143
0
votes
1
answer
147
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
493
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 ...
- 321
3
votes
3
answers
3k
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
93
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
72
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 ...
6
votes
1
answer
9k
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 ...
- 147
1
vote
1
answer
37
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 ...
- 115