Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

81 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
276 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
4
votes
1answer
1k views

Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over ...
3
votes
0answers
536 views

Setup a PostgreSQL HA with synchronous replication in windows

I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
3
votes
0answers
263 views

AWS RDS postrgres massive disk use, small tables

I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much ...
3
votes
0answers
404 views

pg_restore error: Extensions installed out of sequence

I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command: $ pg_dump -c -U postgres -Fc -f test.dump testdb When I ...
3
votes
0answers
1k views

PostgreSQL slower query after partitioning

I have a 75GB table with more than 100M records. I've partitioned that table by month. Each partition is ~8M records big. Most of the queries works on recent data and almost every query filters by ...
2
votes
0answers
119 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
2
votes
0answers
234 views

Does VACUUM FULL ANALYZE a partition locks the main table in postgresql?

I'm using postgresql 9.5 and partman for partitioning tables. there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition ...
2
votes
2answers
350 views

Delete records if not present in subquery

I want to delete records from a table that are not found in a subquery. But it's not having the desired result. The table has multiple rows per SKU. Each price row has a valid_from and valid_to date ...
2
votes
0answers
398 views

Bulk Updates (inserts, update) and check constraints

Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
2
votes
0answers
518 views

PostgreSQL Cluster vs PostgreSQL Master-Slave Replication

I want to setup PostgreSQL and PostGIS to run some experiments (geospatial queries). If I setup a PostgreSQL cluster, this means that the read requests will be distributed across the nodes of the ...
2
votes
0answers
269 views

Primary key violation

We see records in a table that violate primary key and unique key constraints. What could have caused it? How can we track the problem (what needs to be logged/recorded to understand the situation ...
2
votes
1answer
159 views

Setting up a cascading trigger on a parent table

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards: Table "postcards" Column | ...
2
votes
1answer
132 views

PostgreSQL: simple equality query returning no results for indexed columns

I have a very large table (around 140M rows) with five columns: CREATE TABLE IF NOT EXISTS citations ( citing_id text, citing_issn text, cited_id text, cited_issn text, year int ); ...
2
votes
0answers
531 views

Any way to squeeze performance from Postgres JSONB queries?

This is using RDS Postgres 9.5.2 We have a table which contains about 25 columns, and one is a jsonb col that essentially is a simple/flat json object (key/value, not complex objects). The data in ...
2
votes
3answers
208 views

Execution Performance Issue with Similar Query

I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows. If I run a query only on the ...
2
votes
0answers
310 views

Pgpool II and Postgresql upgrade scenario using different major versions

Pgpool FAQ states that: You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x And the reason that I understand very well is: Pgpool-II assumes messages from ...
2
votes
0answers
1k views

How to effectively monitor PostgreSQL 9.5 Replication?

Background: I've got quite some experience running and automating MySQL Clusters with several independent replication streams. However I rarely had to run an PostgreSQL replication myself. They were ...
2
votes
0answers
1k views

Why is this dockerized Postgres not able to authenticate with a password?

I am working on changes to an Alpine fork of the official Docker postgres image ( https://hub.docker.com/r/deinspanjer/docker-postgres95-alpine34/ ). It has been working fine for a while, but I ...
2
votes
0answers
219 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
2
votes
0answers
54 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
2
votes
0answers
18k views

List of escape sequences for Postgresql string data types

I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql. So for example, if you need to escape a quote character inside of a quoted string, you ...
1
vote
1answer
163 views

max_stack_depth not changed even stack changed in limits.conf

The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log: DETAIL: "max_stack_depth" must not exceed ...
1
vote
1answer
149 views

Truncation vs Deletion When `session_replication_role = replica`

When I first run SET session_replication_role = DEFAULT; CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts( id serial PRIMARY KEY, user_id INTEGER REFERENCES users(id) ); ...
1
vote
0answers
15 views

After a PC freeze (Windows 7) and disk integrity check, most data from PostgreSQL table was deleted

My development server is on a PC, using 64-bit Windows 7. My PC has been randomly freezing on me lately, requiring a hard reboot (holding down power button until PC turns off) Yesterday, as I was ...
1
vote
0answers
390 views

ERROR: invalid input syntax for type oid: “english”

While creating an index in Postgres I am getting an error: invalid input syntax for type oid: "english" LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',... The query ...
1
vote
0answers
20 views

Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields

This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
1
vote
0answers
19 views

Disconnect specific database from pgpool II

I'm using PostgreSQL 9.5 with pgpool II version 3.4.13 on Ubuntu 16.04.3 I want to drop a specific database so I must disconnect the database from pgpool. How can I do this without disconnecting the ...
1
vote
1answer
112 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
476 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
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
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
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-...
0
votes
0answers
30 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
0
votes
0answers
31 views

how to insert an if check in an insert

I have an after insert at the statment level, which must insert the positive or negative result in an attribute based on the count of the values of other attributes. But control structures cannot be ...
0
votes
0answers
3 views

Postgres break table columns into multiple tables having unique rows

I have this table called transactions. It has two columns id and report_id. id is primary key. I want to create two new tables transaction_ids and report_ids both with just one column id. Both these ...