Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

PostgreSQL version 9.3

2
votes
2answers
59 views

Performance of large transactions and concurrency?

If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this? Assuming it's indexed correctly, it shouldn't take long, but what ...
0
votes
1answer
26 views

Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime

I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime. I thought I could : on the old Postgres, activate archive_command to copy WAL to a NFS ...
0
votes
0answers
19 views

Postgres-xc database crashing when creating table

I have configured postgres-xc with gtm, one master node and two data nodes. When I creating a new database it is being created but when I am creating a table the server is crashing. postgres=# ...
0
votes
1answer
40 views

Problem migrating PostGIS

I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 ...
0
votes
0answers
42 views

There seems to be a postmaster servicing the old cluster. | Upgrade from postgres 9.3

I am working on upgrading postgres from version 9.3 to 10. I have encountered a lot of issues along the way since the install is way more custom done than it should be (People here LOVE complex and ...
0
votes
1answer
105 views

Postgres taking 3h to DELETE CASCADE

I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
2
votes
0answers
153 views

Postgres throws syntax error at CREATE SCHEMA [closed]

I'm trying to execute an sql file on postgresql with the following command: psql -U postgres -d postgres -a -f file.sql -v ON_ERROR_STOP=1 The following error is shown: CREATE SCHEMA IF NOT EXISTS ...
0
votes
0answers
37 views

EDB Replication Server - Replicate from ppas 9.3 to ppas 9.6

I am new to EDB replication server and I was thinking if I can use this tool as upgrade tool - to upgrade from postgres plus advanced server 9.3 to postgres plus advanced server 9.6 - is this a bad ...
0
votes
0answers
115 views

Idle (not idle in transaction) connections are not released/closed in PostgreSQL AWS RDS after load test

I'm using C3P0 connection pool and PostgreSQL(10.3) in AWS RDS. I did a load test at low TPS (1 TPS) for 2 minutes, after load test finished, the number of connections were not dropped according to ...
2
votes
1answer
46 views

How to use “WHERE” and also “WHERE NOT EXISTS” in the same query and table?

I am trying to add both WHERE and WHERE NOT EXISTS in same query. Below mentioned query1 is working fine, but it returns all the t1 data which does not belong to company1. So I tried query2, but no ...
5
votes
0answers
35 views

Postgres: query planner not respecting table inheritance constraint when querying for null

I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint. I have a table with 0 rows called "search_result". This ...
0
votes
0answers
12 views

Poor regex performance for word matching

I have a list of blocked phrases and I want to match for the existence of those phrases in user inputed text, but performance is very bad. I am using this query : SELECT value FROM blocked_items ...
0
votes
0answers
15 views

Post checks for after postgres upgrade

We are planning to upgrade postgres 9.3 to 9.6. How can we validate whether postgres upgrade is successful? What are checks we can do after upgrade. Can anyone help me on this Thanks
0
votes
1answer
137 views

How to query inside jsonb column

I have a column type of jsonb named changes THis is the structure of my column. [["change","is_enabled",[false,true]]] I want to query within the column. I want to find all the rows that "...
1
vote
1answer
38 views

How to find the nearest lat and lang in postgresql

I am trying find the nearest lang and lat from the specified lat and lang i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the ...
0
votes
1answer
28 views

Failover in Streaming Replication

I have one doubt on master to slave replication in streaming replication in postgresql. I confgured ONE Master and One Slave with streaming replication in my system If Master fails slave act as a ...
0
votes
0answers
18 views

Setting auto vacuum parameters in larger table

currently have a table that is updated daily easily 30% rows are updated, and the vacuum running for 16 hours and automatically stopped and again its running, But when i checked last auto vacuum ...
0
votes
1answer
188 views

If else statement on join query

i am working with a Postgresql database having two tables: professionals[professional_id,user_id,account_id] users[user_id email] accounts[account_id email] professionals [professional_id,...
0
votes
2answers
178 views

Tracking database and table size growth

Is there way we can get the table size and DB size growth from last one month day-wise? We are able to give the current size of DB and table size.
0
votes
1answer
105 views

Not a able to kill the DB session in Postgres RDS

We are trying to killing one of DB session but not able to do it. Tried with following command getting "TRUE" after issuing command. Still we can see the session is active. select ...
0
votes
1answer
1k views

Count records with same id but different column value

I have the following table: +------+------+ |userID|Name | +------+------+ |1 |sport | +------+------+ |2 |it | +------+------+ |3 |game | +------+------+ |1 |sport | +------+----...
4
votes
1answer
72 views

Custom location for postgis extension .control file [closed]

I am trying to install the postgis extension on my database, which resides on a web hosting service. The problem is that the control files seem to have to be found in /package/host/localhost/postgresq-...
0
votes
0answers
11 views

get an array with a crosstab as multiple join

I have a ad_id from action_states u1 join with the table ad_media on u1.ad_id=ad_media.ad_id so basically as a return result i must have a set of array ad_media_id , it keeps giving me the results ...
1
vote
1answer
16 views

How to fetch the records which is belongs to only one group in postgresql

I want to fetch the student_id which is only belongs to one collage expected op 6 and 7 stud_id's so how can i do this? _id | stud_id | collage_id | university_id | active -----+---------+-------...
1
vote
1answer
98 views

How to optimize a fetch into a crosstab

I have a schema like: [ad_id] . [name] . [valueofname] 1 . name . "brian" 1 . age . "23" 2 . job . "IT" 2 . name . "Jack" The row name contains ...
0
votes
3answers
42 views

Optimize query to filter active IDs for a given day from big table

I have a PostgreSQL table called users with 4 columns: user_id integer, -- we can have a lot of the same user_id name text, -- name of the user value text NOT NULL, -- ...
1
vote
0answers
47 views

PostgreSQL is not working after installing Postgres-XC

I am using PostgreSQL 9.3 on Ubuntu 17. This set up was working fine, but it stopped working after installing Postgres-XC. I tried uninstalling Postgres-XC and reverting back the data directory to ...
0
votes
0answers
48 views

what version of postgresql 9.6.8 with what version of postgis

I am a bit confused with the upgrade of a dataserver postgres from 9.3 to 9.6.8. Despite the readings of http://postgis.net/windows_downloads/ and the board (not up to date) https://trac.osgeo.org/...
0
votes
1answer
28 views

Correct way to re-use or recover databases from “PostgreSQL 9.3” to “PostgreSQL 9.5”

I was using PostgreSQL 9.3, then I installed PostgreSQL 9.5, then I unistalled PostgreSQL 9.3. Now I want to move the database from the folder of PostgreSQL/9.3/data/base of PostgresSQL 9.3 to ...
2
votes
1answer
2k views

column must appear in the GROUP BY clause or be used in an aggregate function

I have a simple table with columns col1, col2, col3. All not nullable. I want to delete all rows where the tuple (col1, col2) has several entries. Background: a unique constraint for (col1, col2) ...
3
votes
1answer
1k views

How to disable Postgres from listening on IPv6?

I am trying to disable Postgres from listening on IPv6 because of a bunch of strange trace files that are constantly being written to syslog. I'd prefer not to disable IPv6 in the OS itself, so ...
0
votes
1answer
77 views

How to query row containing the given element inside a json column Laravel Postgres

Hello all I have a postgresql table that has a json column called agents. See below SELECT agents FROM cnms_rosters; result agents ------------------ [1,13,3,16,15] [12,13,14,15,15] [...
-1
votes
1answer
2k views

How to update JSONB array in postgres

I have a table with following definition: create table json_test ( my_data jsonb); Inserted to it values like this: {"details": [ { "f1": "", "f2": "free text" }, { ...
0
votes
1answer
234 views

PostgreSQL: How find functions that use sys_eval

my server was hacked, I found this in the postgres log 2017-12-19 06:30:16 COT STATEMENT: select sys_eval('pgrep baou87r1D9;pgrep 9rMhx24;pgrep Ps1fnYF3;pgrep P15xAw4;pgrep 8yV192n7;pgrep ktU5R693w2;...
0
votes
1answer
2k views

Postgres Error: must appear in the GROUP BY clause or be used in an aggregate function

I'm a novice when it comes to Postgres, and I realize similar questions have been asked before, however, I'm struggling to make this query work: SELECT p_search.id, count(kia.image_id) AS count ...
0
votes
1answer
112 views

Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB

I am very new to database design. I am hoping for suggestion or solutions for a feature I am working. Note: We are majorly using JSONB type in Postgres Feature: I have a orders table which would ...
0
votes
1answer
51 views

Cleaning up archives in Postgres after using systemd

I'm using Postgres 9.3 and I have a master database and a hot standby one that is following the master. The operating system is CentOS 7. I've changed the way of starting Posgres from SysV to systemd....
0
votes
1answer
56 views

Can PosgreSQL databases be restored from physical files?

We had an EC2 instance running PostgreSQL. We created a new EBS volume for our more important DBs and stored them on the new volume by creating new tables spaces and setting their location. The system ...
2
votes
2answers
323 views

WAL archiving w/ streaming replication

I'm running postgresql 9.3. I've currently got WAL archiving configured, but I'm doubting it's utility based on my configuration. On the master, I've got archive_command set to rsync -az --ignore-...
5
votes
2answers
171 views

Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken?

I am using Postgres 9.3. I have 2 tables : authn_session and customer. Every authn_session belongs to a customer (and hence has a customer_id as a column which is a FK to customer's id). Note : ...
2
votes
2answers
69 views

How to efficiently sort a column from two tables?

In our app, we have assignments. We create this by unioning two other tables with a due_date column: checklists and tasks. We want to order assignments by the due_dates. Right now, we are doing ...
1
vote
0answers
37 views

Postgres production server: more rollbacks than commits?

I'm studying a little Postgres 9.3 stats, and I've observed that in my servers, this simple query: select sum(xact_commit) as commits,sum(xact_rollback) as rollbacks from pg_stat_database; I have ...
3
votes
2answers
511 views

PostgreSQL extremely slow after upgrade from 9.3 to 9.4

We recently upgraded an Amazon RDS instance from PostgreSQL 9.3 to 9.4. There were no problems during the upgrade, and afterwards we ran VACUUM ANALYZE. Performance is now very slow across the board. ...
0
votes
1answer
192 views

How can I reset the counters from pg_stat_database view?

I know I can reset some counters by running: select pg_stat_reset() but some counters like xact_commit aren't reset. How can I do it? I've tried also: pg_stat_reset_shared('database') but doesn'...
1
vote
1answer
556 views

High delay lag between master/slave in Postgres 9.3

I have a Postgres 9.3 database server with this configuration: # grep archive_ postgresql.conf archive_mode = on # allows archiving to be done archive_command = 'rsync -a %p postgres@...
1
vote
1answer
53 views

Postgresql 9.3 GROUP BY on an indexed column in 30m rows table taking a long time

I have a simple table which holds GPS data for various vehicles with 30 million rows. Column | Type | Modifiers | Storage | Stats target | Description --------...
0
votes
0answers
699 views

Steps to clean up pg_xlog safely without db corruption

I need some safe steps to delete pg_xlog files as px_xlog is growing like anything.I want to delete old files which are not in use. how can I do that without db corruption?
2
votes
1answer
963 views

Poor query performance over timestamp range by week in PostgreSQL 9.3

I have a slow query that generates a report of account activity per week over the past year. The table currently has nearly 5 million rows and this query currently takes 8 seconds to execute. The (...
0
votes
1answer
277 views

Getting pg_dump and pg_restore right for postgresl (9.3.10)

I'm to transfer a postgresql 9.3.10 DB (~55GB) from Azure VM to an AWS EC2. Being a novice, this question is about getting the process right. I've seen this post, and need to go a bit beyond that: Is ...
1
vote
1answer
417 views

Time taken by VACUUM FULL to reclaim space

After receiving some insightful guidance in a previous post here, I'm going to run VACUUM FULL on 4 PostgreSQL 9.3.10 tables. Table sizes are: 1) links_publicreply: ~30M rows, 9 columns, 3 indexes (...