Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
2
votes
1answer
143 views

Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results

Table a has > 3 million rows and this index: CREATE INDEX idx_a_txt ON a USING gin (txt); This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I ...
1
vote
0answers
33 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&...
0
votes
3answers
1k views

Missing data for one schema in pg_dump

I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like pg_dump -h localhost -d db -U postgres -Fc -f file.dump When I restored a ...
0
votes
2answers
79 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
14 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
193 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
4
votes
1answer
962 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 ...
5
votes
3answers
3k views

How to list all the indexes along with their type (BTREE, BRIN, HASH etc…)

I'm querying to the system catalog in Postgresql 9.6.4 Getting a result set of tables and their indexes is straight forward,what I'm missing is the index type (BTREE, BRIN, etc..) I can't seem to ...
0
votes
1answer
163 views

pgAdmin4 version 3.1 how to update to the last version

We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6. In the log of the server, I notice some errors generated by the pga4dash : ERROR: column "waiting" does not exist ...
1
vote
1answer
17 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.
8
votes
2answers
582 views

How to get non-overlapping distinct intervals from a PostgreSQL table?

Using postgresql 9.6. The table has user sessions and I need distinct non overlapping sessions printed. CREATE TABLE SESSIONS( id serial NOT NULL PRIMARY KEY, ctn INT NOT NULL,...
1
vote
1answer
13 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
1answer
432 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
0
votes
0answers
4 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
3 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
2answers
308 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
0
votes
0answers
31 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 ...
2
votes
1answer
86 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 ...
3
votes
2answers
98 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
159 views

How to allow in-progress transactions to complete when shutting down PostgreSQL?

I'm working on automating as much as possible the mechanics of resizing an EBS volume on which we store all databases. We're going to host a large number of large-but-infrequently-used databases on a ...
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 ...
1
vote
2answers
1k views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
0
votes
2answers
26 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 ...
1
vote
1answer
271 views

Issues with enabling pg_stat_statements

I am having issues quering pg_stat_statements and puzzled what else I missed? See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not ...
0
votes
1answer
752 views

Postgresql Replica is in sync but repmgr says node unattached

I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't ...
1
vote
1answer
29 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
2answers
17 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....
0
votes
1answer
31 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
1answer
503 views

Stored Procedure for Vacuum in PostgreSQL

I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6). The problem Im facing is, I can't ...
3
votes
2answers
28 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
0answers
11 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 (...
0
votes
1answer
141 views

pglogical library

I would like to try pglogical to set up an archive server from a live database, filtering with INSERT, UPDATE statements (DELETE statements will not be replicated in the archive server). I follow the ...
0
votes
1answer
18 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
1answer
615 views

Setup VIP for PostgreSQL Replication

I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time. I have set up replication, now as we don't want to change ...
0
votes
2answers
68 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 "...
5
votes
1answer
1k views

How to atomically replace a subset of table data

In PostgreSQL 9.6 I have a table T like this category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | ...
9
votes
1answer
1k views

Why does RDS PostgreSQL always recover on restart?

I am running an RDS instance of PostgreSQL 9.6.3: select version(); returns PostgreSQL 9.6.3 on x86_64-pc-linux-gnu [...]. I have found that after issuing a stop from the RDS console followed by a ...
2
votes
2answers
3k views

How to Create a PostgreSQL database with different Encoding?

I want to create a New Database that will store data in German(i.e Text with accents etc). Hence I tried to create it using the following SQL: CREATE DATABASE testdb WITH ENCODING='LATIN1' ...
3
votes
2answers
5k views

Postgresql-Server won't start after moving data directory

I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start. I.e. with sudo ...
0
votes
0answers
72 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 ...
1
vote
1answer
174 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
1answer
31 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
0
votes
1answer
87 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'...
0
votes
2answers
631 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 -------------+----------------------...
6
votes
3answers
14k views

WAL archive: FAILED (please make sure WAL shipping is setup)

I am trying to configure Barman to backup. When I do a barman check replica I keep getting: Server replica: WAL archive: FAILED (please make sure WAL shipping is setup) PostgreSQL: OK superuser: OK ...
0
votes
1answer
47 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
64 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 ...
2
votes
3answers
214 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 ( ...
2
votes
1answer
13k views

Is there a query to check the current WAL size in PostgreSQL?

I'm using a hosted PostgreSQL database where I don't have shell access. Is there a query I can use to check the current WAL log size?
0
votes
1answer
35 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, ...

1
2 3 4 5
10