Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
1answer
2k views

Join JSONB column to normal PK column in Postgres

I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres. Example: Normal table: create table Res ( id uuid default uuid_generate_v4() ...
0
votes
1answer
42 views
+50

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 ...
2
votes
0answers
39 views

CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists

I discovered that CREATE TABLE IF NOT EXISTS will fail with insufficient_privilege if the role executing the statement does, in fact, not have the necessary privileges, but does so regardless of ...
0
votes
0answers
38 views

hierarchical query for all tables in a given schema

I am trying to write a hierarchical query to show me all of the tables in a given schema from parents to children to grand children, etc. I split the code into a view and a query to hopefully make it ...
0
votes
0answers
17 views

Manual Failover in PostgreSQL without changing database connection string in services

our ADO.net application uses npgsql to connect to postgresql database. We have implemented a normal cluster of 2 servers(master and slave) as of now. As we don't want to change the database ...
0
votes
0answers
17 views

Postgres partition search in reverse order of partitions

Our production system is on postgres 9, with a partition table. In one of the select queries, we cannot pass the partition key for the select, but we want the data from the latest partition, is it ...
2
votes
1answer
2k views

PostgreSQL 9.1 and 9.4 faster than 9.6

We decided to migrate to 9.6 after some research about parallel query, so we brought up two identical machines with exactly the same hardware, kernel and others with PostgreSQL 9.1 and 9.6 ...
0
votes
0answers
72 views

How to create multiple pool connection using Psycopg2 in Cloud Functions (GCP)

I am creating Cloud Functions for some queries to read and write in different databases. In GCP concept, I am connected to Cloud SQL - Postgres and using Psycopg2, tested locally - python postgres ...
0
votes
2answers
104 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 | +---------------+-------+--------+----------+...
1
vote
1answer
97 views

Postgres 11: Not using the correct indices after upgrade

We upgraded lately our database from PostgreSQL 9.6 to 11 on RDS (after the upgrade we did vacuum full and analyze for the DB). We noticed that a frequent query changed its execution plan, and now it ...
2
votes
1answer
56 views

Postgres UPDATE with data from another table - index only scan used for correlated subquery but not join

Context I'm tuning a bulk UPDATE which selects from another (large) table. My intention is to provide a covering index to support an index only scan of the source table. I realise the source table ...
0
votes
0answers
21 views

Is It Possible Backing Up Postgres Backups To Windows Backup Server With PgBackRest

I want to take my postgres backups to our backup backup server which works on windows server, with pgbackrest. My postgres databases are on CentOS/7 OS. I can take those backups to my local but i want ...
0
votes
0answers
15 views

Multiple sub query in insert statement for postgreSQL

I am trying to insert into table with multiple queries. And its not working, here is my work. insert into post_topics_ta (id,post_id,topic_id,confidence_score) SELECT * FROM post_topics WHERE ...
1
vote
3answers
4k views

How to restore a pg_dumpall dump without CREATE INDEX?

I am attempting to migrate from Postgres 9.6 to 10.3 and during the restore each index is recreated one by one - this is a problem. So far I thought pg_dumpall is a good option. pg_dumpall -U ...
0
votes
1answer
34 views

How to perform schema migration from array to many-to-many to lookup table?

I currently am storing values in a column of type array like follows (this is just an example): CREATE TABLE blog_posts( id INTEGER PRIMARY KEY, blog_title TEXT, tags TEXT[] ); With the ...
0
votes
2answers
44 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?
2
votes
1answer
26 views

Simple select query hangs

I have a simple query: SELECT a.name AS a_name, b.description AS b_description FROM a JOIN b ON a.id = b.a_id WHERE b.table_name IS NULL; (Names changed to protect the innocent) ...
1
vote
1answer
20 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
0
votes
2answers
45 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
1answer
79 views

PostgreSQL 9.6 pg_rewind - takes a long time to replicate

I use Streaming Replication,and Replication works normally. Two servers are HA and PostgreSQL DB is replicated. The data in the DB is about 40G. When a failover occurs, the slave is successfully ...
0
votes
0answers
12 views

postgres 9.6 - use data from xml to call procedure

I work with a system based on Postgres 9.6 (updated from 9.1 or something similar). One of latest changes requires an ability to remotely call procedure on database with some arguments. Due to various ...
2
votes
1answer
1k views

memory errors on startup in postgresql 9.6 log - MAP_HUGETLB failed

On postgresql startup, I see this in the log files 2017-07-17 05:21:01.743 UTC > LOCATION: CreateAnonymousSegment, pg_shmem.c:484 2017-07-17 05:21:01.868 UTC > LOG: 00000: redirecting log ...
0
votes
1answer
354 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 ...
2
votes
1answer
491 views

Master-slave replication doesn't work on PostgreSQL 9.6

I tried to create a master/slave configuration with two servers and Postgres 9.6 I modified master and slave postgresql.conf in /etc/postgresql/9.6/main Same for master and slave: data_directory = '...
13
votes
1answer
7k views

How to store one-byte integer in PostgreSQL?

In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value ...
1
vote
1answer
18 views

How to block pg_stat_reset() statement in DB

Team, Recently we have found one of our application is triggering pg_stat_reset() executing everyday which is causing performance issue . to disable this statement thru application level will take ...
0
votes
1answer
74 views

Multiple concurrent “REFRESH MATERIALIZED VIEW”: how to manage?

I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a ...
0
votes
1answer
14 views

Is it possible to make simple postgres dump using replication server?

PostgreSQL 9.6, configured backup server via barman tool. Using WAL streaming (pg_receivexlog) and streaming backup (pg_basebackup). All working good. However for development/debugging purposes I may ...
0
votes
1answer
17 views

group several columns together in materialized view

For statistical reasons, I need to group several ids in a single column of a materialized view. This should form a list of ids from other tables with an info. Further processing will be done on this ...
1
vote
1answer
1k views

PostgreSQL database size doesn't match used space on disk

I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which ...
0
votes
1answer
94 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
0answers
27 views

Users mostly care about recently inserted rows. But the table has a high number of rows. What is the best way to improve performance?

I have a huge table with rows > 1000000. However, people will only care about rows that were ordered by updated_at and created recently. There are updated_at and created_at columns in the table. By ...
0
votes
0answers
54 views

AWS Aurora Postgres 9.6 major version upgrade via slony?

The official documentation states that the major in-place version upgrades are not possible. Note Aurora PostgreSQL does not currently support in-place major version upgrades. To migrate a ...
1
vote
1answer
198 views

Postgres 9.6 (pgAdmin 4) - How to query daily statistics

Is there a way I can query through pgAdmin in a way I will get statistics for daily query usage? I need to find what queries are run against my api as I get huge network traffic. I'm looking for a ...
0
votes
1answer
58 views

How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

Here is an example of the json object rawJSON = [ {"a":0, "b":7}, {"a":1, "b":8}, {"a":2, "b":9} ] And I have a table that essentially looks like this. demo Table id | ...(other columns)...
0
votes
2answers
164 views

Postgres list constraints that use a stored procedure

I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions. something like: CREATE TABLE payment ( amount bigint, CONSTRAINT amount_gt_0 CHECK (...
1
vote
1answer
859 views

Relation does not exist while scanning schema

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS ...
1
vote
1answer
53 views

How to take backup of multiple DB in a specific point of time

My test setup is having 3 databases in postgresql-9.6 instance, 2 DBs consumed by 2 live Web apps. I am using pg_dump to take hot backup of all databases. In order to keep CPU load under threshold, ...
2
votes
2answers
37 views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
5
votes
1answer
52 views

What accounts for a large single index size (other than bloat?)

I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising. We have a table (let's call it foos) with about 10 million ...
1
vote
1answer
92 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 ...
3
votes
1answer
608 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
3
votes
2answers
1k views

Track my own custom metadata on Postgres tables and columns

Is there any way to add my own metadata to tables and columns in Postgres, such as key-value pairs of short text? I am migrating tables, columns, and data from another database system. That other ...
2
votes
0answers
148 views

How do I fix multixact wraparound after it has already occurred?

I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition. Manual vacuums fail almost ...
0
votes
2answers
875 views

Cannot revoke permissions or drop user in PgSQL AWS RDS

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
0
votes
1answer
286 views

PostgreSQL query suddenly very slow after new column addition

I have a query that's performing terribly after adding a new column. Here's the slow version: SELECT COUNT(*) FROM activities INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."...
-1
votes
1answer
19 views

open cursor failing

I am using embedded SQL (.pgc files) of PostgreSQL and a cursor to retrieve from a table EXEC SQL DECLARE mycur CURSOR FOR select col1 from table limit 10; if(sqlca.sqlcode != 0) { os &...
1
vote
1answer
30 views

Improve Performance by Left Join and Group By Count - Postgresql

I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN. How do I improve the left join? I am using the below index create index session_opportunity_index on public.session ...
0
votes
1answer
34 views

Is it possible that information_schema.columns doesn't hold an user table column information?

I am facing a strange issue here. I have a table which is not visible in information_schema.columns view. What could be the reason for that. Note: I am using RDS Aurora Postgresql 9.6.12
0
votes
2answers
50 views

Postgres using seq scan with filter on indexed column + EXISTS on related table

I've following query which takes about 25-35 seconds to execute: EXPLAIN (analyze, buffers, format text) SELECT booking.* FROM booking WHERE booking.reference_number = '9999999999' OR booking....

1 2 3 4 5 9