Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
1
vote
0answers
617 views

Postgresql sometimes gets high IO issue

I have a Sanic app using asyncpg (0.13) to work with postgresql (9.5). My server now facing a problem which sometimes postgresql seems to be freezed and my app can't even read from db and it seems ...
1
vote
0answers
147 views

Autovacuum in Postgresql 9.5 database

I am trying to set up autovacuum in my postgresql 9.5 database, but table stats shows me no evidence of that. In postgresql.conf, the autovacuum is already enabled: autovacuum = on In settings, ...
1
vote
3answers
261 views

How can I optimize a query where a IN clause is replaced with multiples UNION ALL?

I have a query like this: select * from slow_view where id = 1 union all .... union all select * from slow_view where id = 1000 -- 50 ids, exec time: 10.615 sec, This would run in a very ...
1
vote
0answers
313 views

PostgreSQL pg_basebackup tar file does not yield pg_xlog upon unpacking

I configured a basebackup on a Postgres 9.5 server, specifying that the xlogs should be fetched, and that the file will be a compressed tar archive. I configured this basebackup to run on my standby ...
1
vote
0answers
134 views

Query on postgres 9.5 several times slower than on postgres 9.1

I am running performance tests on two systems that have the same postgres database, with the same data. One system has postgres 9.1 and the other postgres 9.5. There are only slight differences in the ...
1
vote
0answers
122 views

PostgreSQL problems after vacuum - multiple rows with same pkey, different counts of those rows from seq scan vs index

We tried to run a full vacuum on our Postgres database the other day because of disk problems. A bunch of things went wrong along the way and eventually we stopped the vacuum. Now we have some new ...
1
vote
2answers
90 views

How to Get the Second Sequence of Characters using Regular Expressions

I have a field with an address in it (ex. 68 TIDAL BREEZE DR) and this regular expression gets everything before the last sequence (ex. 68 TIDAL BREEZE): substring (address from '(.*) ') My question ...
1
vote
1answer
364 views

pg_repack slows down PostgreSQL replication

I have a master PostgreSQL 9.5 server and a standby server. For replication I use repmgr (WAL streaming). Typically the delay between master and standby is <5s: $ psql -t -c "SELECT extract(epoch ...
1
vote
0answers
16 views

Path extraction SQL

I've a table with some ID's of a tree structured like this: term1_id term2_id 1507 ;2 107 ;3 1 ;5 5 ;6 5 ;7 812 ;8 14 ;9 2719 ;10 9 ...
1
vote
1answer
83 views

Postgis distance filter does not work as expected

I have a issue with simple PostGIS query. Here is a query: SELECT id, location, ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance FROM pins WHERE ...
1
vote
0answers
140 views

How to select random groups of similar records?

I have following table: create table users ( id serial primary key, gender text not null, age integer not null ); insert into users (gender, age) select (case when i % 2 = 0 then 'male' else '...
1
vote
0answers
207 views

SET ROLE xxx command extremely slow

On Amazon RDS, I setup a Postgresql database. I have a custom user table, each user in my database maps to a specific role. The role are created via a trigger and I keep the role in the same custom ...
1
vote
0answers
810 views

postgresql Idle for transaction while migrating 30GB database

I'm migrating part of a mysql database into a postgresql database with a different Schema. I've written a python script, using mysql-connector and psycopg2 modules. It takes care(should) to convert/...
1
vote
0answers
87 views

How can I upgrade PostgreSQL while using PgQ queue system?

I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then: sudo ...
1
vote
0answers
703 views

PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume

We have a problem in upgrading postgresql 9.2 cluster to 9.5 using pg_upgradecluster, because the data directory is on a different volume from the root one. At the beginning, we just tried: # ...
1
vote
0answers
138 views

Replication of PostgreSQL cluster from Datacenter to Office with write access

Some information about the cluster: Version: postgres 9.5 Size: /postgres/data$ du -hs . 854G OS: Ubuntu 14.04 LTS Storage: Raid10 SSD's I have a master-slave PostgreSQL 9.5 ...
1
vote
0answers
231 views

Warning after upgrading from dockerized postgres 9.3.10 to Postgres 9.5.2

I've just finished an upgrade on an Ubuntu box with a dockerized Postgres container. The upgrade appears to have gone off great. But when log in to the docker container/server I get a warning message: ...
1
vote
0answers
2k views

Postgres 9.4 to 9.5 using Yum and pg_upgrade - CentOS 6.7 (Final)

I am attempting to do an upgrade between Postgres 9.4 to 9.5 on a CentOS 6.7 system. I have reviewed both: http://www.postgresonline.com/journal/archives/362-An-almost-idiots-guide-to-install-...
1
vote
1answer
560 views

How Can I get multi-Dimensional array by value

In PostgreSQL 9.5 I use jsonb column called user_tag. This column has multi dimensional array like this: [{"id": 1, "tag": "@xang"}, {"id": 2, "tag": "@wang"}] I want to get when id is equal to 1. I ...
0
votes
1answer
2k views

PostgreSQL; Update different column on condition using CASE statement

I have a table which has different types of balance and I need to deduct the balance from different columns based on availability. Here is the query (Sample) I tried to use but it didn't work update ...
0
votes
1answer
37 views

Division by couples

How can it be possible to obtain a division for all the couples in my table? I have: ref alt A T A T C G A A The frequency is obtained by: number_couple / total_number Where ...
0
votes
1answer
50 views

Postgresql sequence always 1 [closed]

I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running SELECT nextval(...
0
votes
2answers
2k views

Replace part of strings in multiple columns and tables

I have several fields in db containing url inside different strings, part of which I need to replace with another one. F.e. the string is abc::http://localhost:5000/pic/img/1.jpg|def:::...
0
votes
3answers
410 views

Postgressql text search: pg_trgm index with order by going very slowly

I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with. I'm searching for ordered text, in a long file path string. So the path could be: folder1/cat ...
0
votes
2answers
2k views

How to copy a PostgreSQL database at a filesystem level between servers

I have a large PostgreSQL database on a server with a failing disk. I cannot run pg_dump because of disk errors. I have another server with the identical OS (Ubuntu 16.04) and the identical version ...
0
votes
1answer
2k views

How to Insert into with postgresql and JSONB

I am using Postgresql 9.5 I have two tables(product and product_temp) with jsonb colums I would like to insert the values from product_temp to product but the name of the data inside the json is ...
0
votes
1answer
40 views

Slow query when using ORDER BY in Postgres

This query SELECT * FROM ( SELECT event_id FROM events_201806 WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN' UNION ALL SELECT event_id FROM events_201805 WHERE ...
0
votes
1answer
48 views

How to store number of years, months and days in PostgreSQL?

How to store number of years, months and days in PostgreSQL ? Example: the life time of bulb is => 3 years, 4 months, 23 days Is there any data type in PostgreSQL
0
votes
1answer
152 views

Postgres replication views

I see some discrepancies between two views in postgres as to replication and lag time. By this query, clearly replication is up: 9.5.6.11 postgres=# select client_addr, sent_location, ...
0
votes
1answer
345 views

How to restore the privileges of the user postgres in PostgreSQL?

Someone logged in my PostgreSQL as the user postgres, because a lack of security in my configuration files. What he did is to create a lot of superusers, and then, he revoked SUPERUSER privilege from ...
0
votes
1answer
208 views

Why does constructing this jsonb_array from two jsonb objects fail? [closed]

Just trying to answer someone's question, and I'm wanting to run this by someone else. Constructing a jsonb array with two key value pairs. SELECT * FROM ( VALUES (jsonb_build_array( '{"k1":"v1"...
0
votes
2answers
34 views

Find Number of Records Higher Than A Specific Number In Other Table

I have two tables with following structure: First Table: ID Name Total_courses_allowed 1 Ahmad 4 2 Khan 3 3 Mahmood 2 Second Table: ID Course_attended emp_id 1 ...
0
votes
2answers
3k views

Use psql --set variable in a function

I want to create a script that will create some custom named schema and within it some tables and functions. Like this: The example.sh file: #!/bin/bash # this is the only place I want to set the ...
0
votes
1answer
1k views

Set values conditionally in Postgres UPSERT

We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1 We ...
0
votes
2answers
2k views

pgAdmin 9.5 not showing all databases

I have a problem with my database. I installed PostgreSQL 9.5 on my Ubuntu server. I changed the postgresql.conf file to allow binding the PostgreSQL server to the localhost. This allows me to run ...
0
votes
1answer
41 views

Unique values depending on context

So i am implementing a system that has PIN (Personal identification number) codes. The PIN is a simple combination of a few letters and some numbers. The PIN could be something like AX7500. Now, a ...
0
votes
1answer
13 views

argument 'and' incorrect, sum values of attributes

excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries?...
0
votes
1answer
523 views

How to repair corrupted PostgreSQL pg_toast error

The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I ...
0
votes
1answer
222 views

SQL update multiple rows in destination table with same id but different values from other table

creditNote table id invoice_id generated_at(timestamp) status 10101 111 2018-02-28 14:42:39.247 generated 23982 111 2018-03-30 11:11:11.247 ...
0
votes
1answer
128 views

How to swap array values in PostgreSQL.?

How to swap array values in PostgreSQL. For example i have a table that has a column whose data type is bigint[]. It has following value in it. value: {111, 222, 333} My desired output is {333, 111, ...
0
votes
1answer
123 views

Schema Dump Without User Mapping Passwords

Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings? For example, I would like items such as the following ...
0
votes
1answer
390 views

How to split table records into aggregate and array in PostgreSQL

I have a table max_values in my PostgreSQL 9.5 database containing four columns. id values lower_limit upper_limit 1 15.3 112 142 2 18.1 142 172 3 17.5 172 ...
0
votes
1answer
441 views

postgresql replay_location in pg_stat_replication is empty

I'm running a cluster of PostgreSQL 9.5.2, and export the stream log with confluentinc/bottledwater-pg: Change data capture from PostgreSQL into Kafka. The replication works fine, but I'm wondering ...
0
votes
1answer
257 views

postgres9.5: can't create extension on standby [closed]

I can't create extension on postgresql9.5 server (hot standby). During to attempting I received: db=# create extension pgrowlocks; ERROR: cannot execute CREATE EXTENSION in a read-only transaction ...
0
votes
2answers
2k views

PGPool II - Failover when current master node dies

I have a 2 node high-availability postgresql 9.5 cluster. I'm using streaming replication, PGPool II (3.5.4 [ekieboshi]), and a virtual (or floating) secondary IP all hosted on aws to accomplish this. ...
0
votes
1answer
642 views

Postgresql Update Table row values by Grouped Column

First of all a little bit of background: I currently have a table which contains call data both inbound and outbound from my business, up until recently there was no api_id data available, however ...
0
votes
1answer
54 views

Types of ways to achieve Many-to-Many relationship between tables in DB?

I have two tables namely 'City' and 'Travels'. City Table will have list of cities in each row with city_id as the Primary Key and Travels Table will have list of travels in each row with travels_id ...
0
votes
1answer
1k views

PostgreSQL: Multiple Inheritance tables (one child to two parents)

If I have a base table and 2 or more tables that inherit from it, child1 and child2, I can insert a record into a child table and it shows up in base as you would expect. However what I want to know ...
0
votes
2answers
539 views

Combining Postgres jsonb query statements

I have test table with votes column and I want to upsert nested jsonb object into it. My current query: INSERT INTO test (post_id, username, votes) VALUES (12345, 'testuser', '{"commentid" : {"vote":...
0
votes
2answers
2k views

Comparing two rows in PostgreSQL

I have an employee table in two different servers with the same structure. I want to shift all the data to main server but there are some records already exists in main server that are duplicate of ...