The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
1
vote
1answer
993 views

Parse error in pg_hba.conf [closed]

Hope this is not as stupid a question as I think it is. But I'm in a loop and unable to find a solution. I installed Postgres in centos machine. The installation worked fine (apparently). Now I have ...
1
vote
2answers
149 views

How to recover data from a corrupted /base/ directory? [closed]

I have deleted all directory by mistake except from the given link /var/lib/postgresql/9.5/main/base/18686 and so on. I have 4 such directories, now I want to get data back from those folder as they ...
1
vote
1answer
133 views

How to synchronize a Windows database for Linux?

I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application. Is it possible to synchronize?
1
vote
0answers
37 views

Impact of numerous indexes on query optimizer for a delete statement

We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...
1
vote
1answer
618 views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
1
vote
1answer
236 views

Exclusion constraint on ENUM errors: data type dow has no default operator class for access method “gist”

Following in the style of this, I'm trying to create an exclusion constraint over an ENUM, CREATE TYPE dow AS ENUM ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' );...
1
vote
0answers
746 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
158 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
267 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
345 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
145 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
129 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
96 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
393 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
17 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
90 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
158 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
225 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
849 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
93 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
735 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
237 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
587 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
4answers
70 views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0
votes
1answer
22 views

How to find out unused port on Linux server

We are trying to use port 5444 for our new PostgreSQL instance and we are not sure whether 5444 is used or unused. Could you please help us to find the answers to the below questions. We want to ...
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
3k 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
469 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
57 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
205 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
371 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
328 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
4k 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
778 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
293 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
146 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
141 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
2answers
2k views

Pgpool2 doesn't use the slave for load balancing

I've enabled streaming replication in my postgres 9.5.10 installed from ubuntu xenial repo. I want to enable load balance for pgpool-2 (3.7.0 amefuriboshi) installed from sources. So I have this ...
0
votes
1answer
436 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 ...