Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0 votes
1 answer
55 views

Better way to return list of FK "parent" tables?

Here's what I currently do in Postgres 9.6. (I know it's EOL; that's not my choice to make.) I don't want the definitions, FK names, or any of the other clutter you get with \d <table>. Just ...
RonJohn's user avatar
  • 615
0 votes
2 answers
512 views

Performance difference using CTE and subquery

I have run the following queries. CTE WITH temp AS (SELECT id, timestamp FROM table group by timestamp, id) SELECT COUNT(*) FROM temp WHERE timestamp = '2023-01-01 08:28:45' Subquery SELECT COUNT(*) ...
gbox's user avatar
  • 121
0 votes
1 answer
240 views

Fix invalid checksum in database that causes pg_dump to fail

I have a Postgres 9.6 database that is corrupted. The hardware it was running on went bad, and there is at least one corrupted page in the DB. When running pg_dump I get the following error: pg_dump: ...
Fabian's user avatar
  • 103
0 votes
1 answer
2k views

(PostgreSQL CREATE TRIGGER) Is there an equivalent to IF NOT EXISTS?

On PostgreSQL 9.6, how can I do the equivalent of CREATE TRIGGER IF NOT EXISTS? I noticed that in PostgreSQL 14 you can do CREATE OR REPLACE TRIGGER but I'm using 9.6 which doesn't have that.
Starscream512's user avatar
0 votes
1 answer
245 views

How can I recursively query a table containing LTREE to get the tree like structure when some of the parent paths are missing?

Lets say I have this table: CREATE TABLE nodes (node_path ltree); INSERT INTO nodes VALUES ('Top.Science'); INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics'); INSERT INTO nodes VALUES ('...
sudoExclamationExclamation's user avatar
0 votes
1 answer
361 views

Postgres service does not start after abrupt reboot

Postgres 9.6.1 does not start automatically after an "abrupt" reboot (Ubuntu 18.04). Postgres service is configured to start on reboot, but the server does not come up at all! The only way ...
Christian's user avatar
  • 325
1 vote
1 answer
133 views

How to interpret the contents of a replication slot state file in the data directory

We know that there is a state file for slot in the data/pg_replslot/<slot_name> directory. It is needed when server restarts after a crash. Questions: How is the data stored inside this state ...
manjunath's user avatar
0 votes
0 answers
423 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 ...
fa__'s user avatar
  • 123
2 votes
1 answer
139 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 ...
m1602's user avatar
  • 23
1 vote
1 answer
287 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 ...
Yogi's user avatar
  • 113
0 votes
1 answer
39 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 ...
Abraam Magued's user avatar
0 votes
0 answers
32 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 ...
Forece85's user avatar
  • 101
1 vote
1 answer
3k 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 ...
NarayanJr's user avatar
  • 113
0 votes
1 answer
3k 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: ...
SQLSERVERDAWG's user avatar
1 vote
1 answer
78 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 ...
Peter Green's user avatar
3 votes
1 answer
2k 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 ...
Tallboy's user avatar
  • 203
0 votes
1 answer
159 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, ...
Cory's user avatar
  • 101
1 vote
0 answers
904 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, ...
Francisco Camello's user avatar
2 votes
1 answer
5k 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 ...
Abraam Magued's user avatar
0 votes
0 answers
43 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 | ...
Stuart K. Smith's user avatar
1 vote
2 answers
3k 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 | ...
RonJohn's user avatar
  • 615
0 votes
1 answer
949 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 ...
user1013388's user avatar
1 vote
1 answer
887 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 ...
mymarcelsql's user avatar
0 votes
1 answer
1k 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 ...
Claudio Floreani's user avatar
2 votes
0 answers
1k 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 ...
EAmez's user avatar
  • 145
0 votes
2 answers
834 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 ...
Frankenstein's user avatar
0 votes
1 answer
439 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 ...
roy naufal's user avatar
3 votes
2 answers
7k 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, &...
DevDon's user avatar
  • 33
0 votes
1 answer
602 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 ...
Vincent Rolea's user avatar
1 vote
0 answers
60 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&...
zcahfg2's user avatar
  • 111
1 vote
3 answers
226 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.* ...
xav's user avatar
  • 113
0 votes
1 answer
221 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\ -...
Aveer28's user avatar
  • 11
1 vote
1 answer
360 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.
STO's user avatar
  • 135
1 vote
1 answer
1k 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 ...
Nabero's user avatar
  • 13
0 votes
0 answers
755 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 ...
Aveer28's user avatar
  • 11
0 votes
1 answer
51 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 ...
Jack Deeth's user avatar
0 votes
2 answers
801 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 ...
spookypeanut's user avatar
3 votes
2 answers
429 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 ...
Gabriel Valvin's user avatar
0 votes
2 answers
498 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....
user25976's user avatar
  • 103
1 vote
1 answer
1k 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 ...
usethe4ce's user avatar
  • 121
1 vote
2 answers
4k 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 ...
Julien Lamarche's user avatar
3 votes
2 answers
714 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 ...
User1291's user avatar
  • 132
0 votes
1 answer
41 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 ...
DaveB's user avatar
  • 299
0 votes
2 answers
958 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 "...
RonJohn's user avatar
  • 615
2 votes
1 answer
898 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 ...
gozdal's user avatar
  • 21
0 votes
1 answer
111 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
Jason's user avatar
  • 111
1 vote
1 answer
1k 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'...
guillermo_dangelo's user avatar
2 votes
2 answers
18k 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 -------------+----------------------...
Ayman Patel's user avatar
0 votes
1 answer
174 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:...
kruthi prabhu's user avatar
1 vote
0 answers
827 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 ...
Sig's user avatar
  • 383

1
2 3 4 5
11