Skip to main content

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
9 votes
1 answer
2k 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 ...
ChrisJ's user avatar
  • 601
6 votes
0 answers
441 views

Roles and policies in Postgresql 9.5 row-level security

Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come ...
Ruslan Talpa's user avatar
5 votes
0 answers
1k 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 ...
devteam's user avatar
  • 51
5 votes
2 answers
593 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, ...
ALZ's user avatar
  • 161
5 votes
0 answers
480 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 ...
Charles Cooper's user avatar
5 votes
0 answers
51k 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 ...
JustBeingHelpful's user avatar
4 votes
1 answer
2k views

List PostgreSQL extensions per database

I have a Postgres 9.5 server with 30 databases. I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin. I know that SELECT * FROM ...
copossum's user avatar
  • 141
4 votes
0 answers
2k 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 ...
siddharth178's user avatar
4 votes
0 answers
3k 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 ...
fradal83's user avatar
  • 141
3 votes
0 answers
3k 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 ...
Piyush Sharma's user avatar
3 votes
0 answers
2k 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 ...
Ragav's user avatar
  • 131
3 votes
0 answers
1k 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 ...
bryan's user avatar
  • 131
2 votes
0 answers
1k 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 ...
Alireza's user avatar
  • 123
2 votes
0 answers
736 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 ...
arthur's user avatar
  • 888
2 votes
0 answers
2k 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 ...
e7lT2P's user avatar
  • 175
2 votes
0 answers
606 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 ...
arthur's user avatar
  • 888
2 votes
1 answer
2k 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 ...
S Deshmukh's user avatar
2 votes
0 answers
813 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 ...
David B.'s user avatar
2 votes
0 answers
2k 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 ...
deinspanjer's user avatar
2 votes
0 answers
59 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
neonmate's user avatar
  • 103
1 vote
0 answers
1k views

Why this aggregate function cannot be converted with pg_upgrade from PostgreSQL 9.5 to PG 14.3?

I am trying to port old PostgreSQL v9.5 database (or family of databases) to PostgreSQL v14.3, with pg_upgrade utility. After many hassles with old v9.5 binaries, source files (had to be recompiled to ...
zimon's user avatar
  • 23
1 vote
0 answers
6k views

How to run multiple sql statements in Postgres using the tool PGADMIN?

I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint. All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
Ganesh Saragadam's user avatar
1 vote
0 answers
26 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 ...
Valachio's user avatar
  • 111
1 vote
0 answers
43 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)...
Mariano Roberto Medina's user avatar
1 vote
0 answers
90 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 ...
Shaghayegh Mobasheri's user avatar
1 vote
0 answers
659 views

Postgres Create table is hanging for 2 days

We are running a database that nearly 11 TB. We are using PostgresQL 9.5. After the hard restart in adding new replica we are facing the issues that we can't create new table most of indexes are ...
giaosudau's user avatar
  • 111
1 vote
0 answers
42 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 ...
arthur's user avatar
  • 888
1 vote
0 answers
3k 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 ...
cuongnv23's user avatar
  • 111
1 vote
0 answers
591 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 ...
ITGuy's user avatar
  • 11
1 vote
0 answers
162 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 ...
dpecevski's user avatar
1 vote
0 answers
287 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 ...
Adam's user avatar
  • 11
1 vote
0 answers
2k 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 ...
tex's user avatar
  • 214
1 vote
0 answers
21 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 ...
xCloudx8's user avatar
  • 165
1 vote
0 answers
408 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 '...
user606521's user avatar
  • 1,375
1 vote
0 answers
417 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 ...
Sylvain's user avatar
  • 189
1 vote
0 answers
1k 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/...
lese's user avatar
  • 283
1 vote
0 answers
118 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 ...
Ivan Velichko's user avatar
1 vote
0 answers
1k 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: # ...
Dan Mazzini's user avatar
1 vote
0 answers
193 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 ...
Toni's user avatar
  • 11
1 vote
0 answers
284 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: ...
Pistol Pete's user avatar
1 vote
0 answers
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-...
Daniel B's user avatar
1 vote
1 answer
4k views

What is the pgAgent service account?

Im installing pgAgent using Stack Builder. In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one? I try using my DOMAIN\my.user.name also using the ...
Juan Carlos Oropeza's user avatar
0 votes
0 answers
56 views

Postgres usage always using 100 cpu usage

In certain interval (like 60 minutes), postgres always uses 100%cpu. This problem is just starting last Thursday. I had to manually to kill the pid through HTOP. The one that makes me confused is ...
Faris Ponighzwa's user avatar
0 votes
0 answers
89 views

PostgreSQL 9.5 Create Publication?

Based on the official doc, CREATE PUBLICATION command is available on PostgreSQL 10 and later. However, I have a requirement to replicate PostgreSQL 9.5 to BigQuery using datastream, which require us ...
khgasd652k's user avatar
0 votes
1 answer
49 views

Unable to locate and login to by PostgreSQL

I am using Ubuntu 20.04 LTS, and I have configured PostgreSQL 9.5 to work on the server. I am trying to log in but I have forgotten the password for the user postgres and therefore I tried to reset it ...
Fatjon Lala's user avatar
0 votes
1 answer
1k views

how to call lo_unlink(lo) in batches

In Postgres 9.5 I am tying to reproduce the postgres tool vacuumlo to get rid of unreferenced large object to free disk space. References are stored in text type columns by hibernate so using the tool ...
Luky's user avatar
  • 101
0 votes
3 answers
305 views

Postgres Specific table access

We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any ...
Jatin Bisht's user avatar
0 votes
1 answer
4k views

Drop tables but space not claimed in postgres 12

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) ...
erTugRul's user avatar
  • 153
0 votes
0 answers
100 views

Poor performance in a SELECT using Inheritance

I will detail my problem in good detail. I have a marketplace application so I make a decision to make a multitenant per schema approach. I like this approach because of the isolation of data, but I'm ...
wavrik's user avatar
  • 101
0 votes
1 answer
1k views

Default Postgres privileges not showing on \ddp

As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using: CREATE USER foo WITH PASSWORD 'foo'; \connect bar; GRANT SELECT ON ALL ...
Dusan Gligoric's user avatar