All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
3answers
761 views

pgAgent Job's “last result” is continuously “running”

I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content ...
0
votes
2answers
192 views

300 inserts per minute, 6 million rows per month

I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot ...
0
votes
0answers
22 views

PostgreSQL: Slow JOIN on three large tables

Edit: Proposed this question as a LEFT JOIN problem, but as @wildplasser pointed out, due to my WHERE clause I was actually doing a JOIN all along. Adjusting the question from LEFT JOIN to JOIN. The ...
1
vote
1answer
108 views

postgresql 9.1 failed insert occupies increasing disk space

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and ...
0
votes
0answers
16 views

How can I improve this nested postgresql query?

I have a mildly complex query that is having rather poor performance: UPDATE web_pages SET state = 'fetching' WHERE web_pages.id = ( SELECT web_pages.id FROM ...
1
vote
0answers
39 views

The definitive fastest radius search query with Postgis 2.0

I come from mysql world. I read the manual and saw some examples, after that, I'm not able to say witch method is the fastest for a radius search. Nobody seems to agrees. My simple case: I need to ...
0
votes
2answers
239 views

Whats the best and fast way to import a huge DBF file to a Postgres in Windows?

I have some huge DBF files (about 35 GB) to import to Postgres. Whats the best and fast way to do it? OBS: I have 300 GB of free disk space and 8 GB RAM.
1
vote
1answer
119 views

PostgreSQL WAL recovery not working due to missing WAL

I am using pgBarman for PITR recovery. Now it's a bit of a crisis, because the Barman backup and the WAL archive seems like it cannot be recovered. We're running PostgreSQL 9.3.6 and latest stable ...
14
votes
3answers
4k views

How do I find out how far along my PostgreSQL query is?

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize). I understand Postgres won't tell me percentage completeness, is ...
1
vote
0answers
48 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
3answers
148 views

Trying to create PostGIS database

I am trying to create a spatially enabled PostGIS database by following the PostGIS documentation. In the short version, i executed, createdb yourdatabase createlang plpgsql yourdatabase But I did ...
0
votes
2answers
4k views

How to make Postgres autovacuum not impact performance?

I am running into problems where Postgres autovacuum processes are interfering with the overall performance of my database. It is making my select, insert, and update queries slower than usual. What ...
2
votes
1answer
53 views

Aggregate count over variable number of json keys

How can one get a list of json objects like this: [ {"band": "beatles", "bass": 1, "drums": 1, "guitar": 2}, {"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1} ] from ...
1
vote
1answer
218 views

pg_ctl promote is not working

I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared ...
0
votes
2answers
132 views

SQLAlchemy connection error with Postgres

I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by ...
0
votes
1answer
137 views

Is it possible to set a timer for autovacuum in PostgreSQL

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My ...
0
votes
0answers
12 views

Prevent Update/Insert/Delete based on Date

I have a simple Table that holds financial data with a date. I would like to endure, that historic values are protected to INSERT, UPDATE or DELETE. Example: All values of March 2016 should not be ...
0
votes
0answers
14 views

Diagnosing why postgresql query is going in a seemingly endless processing loop

I maintain a social forum built with Django and postgresql 9.3.10. There's a maintenance query I run via psql, to clean up my postgresql table. It goes like this: begin; DELETE FROM links_seen WHERE ...
1
vote
4answers
2k views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
1
vote
1answer
189 views

PostgreSQL on Windows fails to start - 'could not create shared memory segment'

PostgreSQL 9.4 & 9.5 (beta) on my Windows 7 laptop fails to start. In the Windows event log I see the following error: 2015-11-10 13:53:49 IST FATAL: could not create shared memory segment ...
4
votes
1answer
162 views

Postgres multidisk master replicating to single disk slave

I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
27
votes
4answers
7k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
0
votes
1answer
27 views

Start, stop, configure PostgreSQL through Mac terminal

I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
0
votes
1answer
48 views

How to convert postgresql dump file into mysql dump file

I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance
1
vote
1answer
309 views

Postgres function to merge two json objects with overlapping keys into one object

I've got the following json object: { "a" : { "0" : 2, "1" : 4, "3" : 6, } "b" : { "2" : 8, "1" : 10, /*note this key exists in "a" too*/ ...
0
votes
1answer
135 views

Tuning postgresql.conf when benchmark

Description: We build a new database server Enterprisedb v9.3 (PostgreSQL commercial), so we use JMX to benchmark it with 1000 users. Here our detailed information: Operating System: Centos 6 x64 ...
5
votes
1answer
70 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
1
vote
1answer
19 views

Top N Results Ordered by Joined Table Column

I'm trying to optimize a fairly simple query that just gets a list of the top N users ordered by the number of followers they have. I am running into performance issues when I run the query a certain ...
0
votes
1answer
35 views

Updating column from another table - PostgreSQL 9.2 [closed]

There are two tables: ja_jobs junk.test_test1 I need to update the time_job column from junk.test_test1 to ja_jobs. Here is what I'm doing now: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM ...
1
vote
1answer
26 views

Alias Column Name [closed]

I am trying to run a basic select Query in PostgreSQL, but I am having trouble using the column alias. I tried to create the alias as randomfield As [Space In Name] but it gave an error of Error: ...
2
votes
1answer
20 views

Multiple columns from one table rely on one single table

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables: The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
2
votes
1answer
107 views

Is there a way to make an OOo form for PostgreSQL updateable view?

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in ...
1
vote
2answers
2k views

What is best strategy for backup on streaming replication(hot standby) environment

I have questions. What is best strategy for backup on streaming replication(hot standby) environment. I’d like to do backup on slave server and restore(PITR). First, I set up streaming ...
0
votes
1answer
525 views

Tomcat7 JDBC connection pool -> Connection has been abandoned

I have the following configuration for the JDBC connection pool initialSize="2" minIdle="10" maxIdle="50" maxActive="100" maxWait="15000" ...
1
vote
1answer
28 views

Trigger for a particular row?

I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like: CREATE ...
1
vote
0answers
16 views

parallel pg_restore with materialized views

I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS. To troubleshoot the restore process, I have split the restore ...
0
votes
1answer
99 views

Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database?

From the docs: pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, ...
1
vote
1answer
19 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and ...
0
votes
1answer
12 views

select duplicated rows by modified_date - PGSQL 9.2

Query: SELECT j.id INTO junk.ja_jobs FROM ( SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id FROM ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
0
votes
0answers
10 views

Remedying psycopg2:OperationalError in postgresql 9.3.10 database

I have a Django app + postgresql 9.3 DB, hosted on Ubuntu VM. Recently my VM ran out of disk space, resulting in no space left on device errors showing up in my logs. I cleared the space on the device ...
1
vote
2answers
78 views

Postgresql server is running, but service appears stopped

I am running Postgresql 9.4 as a service on Windows Server 2008 R2. It usually works as a charm, but now there is a situation when the server is apparently running (I can connect to it, Task Manager ...
7
votes
2answers
173 views

Looking for a simpler alternative to a recursive query

The actual query is more involved, but the problem I'm facing can be distilled to this: A query to filter a rowset of monotonically increasing integers so that - in the final result set, ...
12
votes
3answers
3k views

PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

Our established replication has broken ("requested WAL segment has already been removed" during downtime) We cannot easily stop the master again. Can we do pg_start_backup(), rsync ${PGDATA}/ ...
1
vote
1answer
18 views

How to make trigger function to make daily entering-exiting report?

I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in. CREATE TABLE daily_report ( id serial, ...
1
vote
0answers
24 views

When was a table created? [duplicate]

Is there any way to find out when a table was created in PostgreSQL or Greenplum, other than finding the underlying table and looking at the OS creation timestamp?
0
votes
0answers
5 views

Ubuntu: How to soft-link to the latest version of PostgreSQL CLI tools [duplicate]

I have just upgraded my RDS PostgreSQL version from 9.3 to 9.5. In order to access the server, I need to upgrade the Ubuntu CLI tools as well. I have upgraded the tools using the apt.postgresql.org ...
0
votes
1answer
10 views

Postgresql error: failed to re-find parent key in index

I haven't seen this before and am stumped. I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently I VM ran out of disk space (resulting in no space left on device ...
0
votes
0answers
34 views

Improving query performance involving similarity and full-text search

I have been trying to improving the performance of this query involving similarity and to_tsquery. I have a table (440,000 rows, PostgreSQL 9.5.2) with the following characteristics: test_db=> \d ...
1
vote
1answer
12 views

Delete duplicated rows by modified_date - PostgreSQL 9.2

I've got some duplicated rows on a table called ja_jobs: To find those duplicated data, I'm running this Query: select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted ...
0
votes
1answer
154 views

psql: could not connect to server

when I'm executing U postgres -d "phoenix" -a -f "/northstar/phoenix/runtime/Daily-script/query.sql" this command i got the following errore psql: could not connect to server: No such file or ...