PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
0answers
3 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 ...
1
vote
2answers
18 views

Apply limit and conditions per grouped data

I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold ...
1
vote
0answers
18 views

Count childs of parent with sql or save in column?

In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this: WITH RECURSIVE ...
0
votes
1answer
15 views

how can I count by week, month, and YTD with postgres?

under PGSQL 9.3 I am trying to run a query to summarize some data from table a: | Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date | I ...
3
votes
0answers
20 views

What do the shared buffers metrics for various types of index scans represent?

TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages ...
0
votes
1answer
15 views

PostgreSQL - Who or what is the “PUBLIC” role?

I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist. => Who or what is ...
0
votes
0answers
6 views

A query to know RDS instance name in Amazon RDS for Postgres

Do you know a way to fetch the RDS instance name by a query run against a RDS-powered PostgreSQL database in Amazon? Something similar to: curl http://169.254.169.254/latest/meta-data/ami-id in ...
0
votes
2answers
8 views

Create postgres schema in db with multi user in AUTHORIZATION

Is it possible to create a schema that is owned or accessible by more than one user in the same database? I was reading postgres create schema documentation and postgres alter schema documentation ...
1
vote
0answers
26 views

How to represent multiple unique constraints across hierarchical tables?

My (shortened) table definition is: CREATE TABLE bus ( id integer NOT NULL PRIMARY KEY, name character varying NOT NULL CHECK (name<>'') ); CREATE TABLE network_node ( id ...
0
votes
0answers
15 views

“could not write to log file, Bad file descriptor” error when working with huge table

I'm having a big problem with a PostreSQL database. Whenever I do something with a huge table (over 9 million rows) the whole postgres process crashes with the following entries in the log file: ...
0
votes
1answer
37 views

Priority search on columns

Description: We are using PostgreSQL 9.3 - Centos 6 x64 . We have a dtsc_search_data table as below: dtsc_search_data id ---- c1 ---- c2 ---- c3 1 ---- 1 ---- 1 ---- 1 2 ---- 1 ---- 2 ---- ...
0
votes
0answers
14 views

Tuning Postgresql autovacuum [duplicate]

Setup I have a problem similar to the one described and not solved here , with some differences... I am using Postgresql 9.3 on Ubuntu 14.04 where I have a Django project running with celery. I ...
0
votes
0answers
17 views

pgAdmin - Retrieve function's full source code [on hold]

I would like to retrieve my function's source code. I've tried to obtain it from pg_catalog.pg_proc.prosrc column and pg_catalog.pg_get_functiondef(oid) but it always return a truncated source code ...
0
votes
0answers
23 views

backup only few tables in postgresql [on hold]

I have PostgreSQL database with more than 20 tables. I need to take backup only certain tables along with schema that should stored as zip. So that i can restore to empty database with tables and ...
3
votes
1answer
28 views

Postgres query plan has high rowcount, slow execution

I've identified this snippet of a query as running at incredibly slow speeds, although it's quite simple. It aims to find all the records in Table1 which don't have a more recent entry in Table2 with ...
0
votes
0answers
11 views

Running `gman_servers_set` in PostgreSQL's startup

I've compiled PostgreSQL's gearman extension and now I can connect to a gearman server using triggers and such in PostgreSQL. As you may know, before a job can be registered in a gearman server, you ...
0
votes
0answers
22 views

Multi-core PostgreSQL server - how to improve performance [on hold]

I have a PostgreSQL 9.3.5 server with 8 CPU cores. I've noticed that adding cores to the server doesn't improve its performance. It is probably caused by the fact that Postgres doesn't support ...
-1
votes
0answers
11 views

simulate “do not match” in postgresql stream replication [on hold]

I have setup database stream replication using pgqadm & londiste. I use comparison script provided in https://wiki.postgresql.org/wiki/Londiste_Tutorial_%28Skytools_2%29#Launching_replication I ...
1
vote
1answer
9 views

Postgres streaming replication between hosts with different timezone

I am running Postgres 9.1 on Windows 2012 servers. I am using postgres's streaming replication to replicate data between 2 servers. It has so happened that the time zone of the slave has not been set ...
1
vote
1answer
24 views

Pausing the import of an SQL file in Postgresql?

I am currently importing a large database (6 - 7 GB's) into my postgresql on Ubuntu. It's taking sometime to do. A problem has come up where I need to physically relocate the laptop the postgresql is ...
3
votes
1answer
80 views

A costly approach to filtering on a calculated date

This builds off my previously wonderfully answered question Running a complex query for every date in a range I've got an expanded problem and I'm certainly not solving it in a performant way. The ...
2
votes
1answer
26 views

How to examine PostgreSQL server's SSL certificate?

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate? I'm hoping for output similar to what you ...
1
vote
1answer
15 views

Use of smallserial gives error with PostgreSQL

I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1. At the moment I'm designing a new database and I use the tool ...
1
vote
0answers
34 views

Postgres - Error while adding constraints

Can anyone tell me what this error is? And How to resolve it? ERROR: could not read block 27651 in file "base/114689/118316": read only 4096 of 8192 bytes CONTEXT: SQL statement "SELECT ...
7
votes
2answers
140 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
1
vote
1answer
23 views

Storing Large Nested Objects

What is the best way to store a large (thousands of properties) nested JSON object in Postgres? It does not need to be index, queried, or anything. Just written and read. Write speed isn't an issue ...
0
votes
0answers
18 views

Postgres 9.0 data huge folder

On our production environment we are using alfresco-4.0.c as content repository. Alfresco uses an instance of postgresql to index the contents. Looking at the filesystem size, I noticed I'm running ...
0
votes
0answers
22 views

How to populate values in drop down list in php postgresql [migrated]

I am getting error while populating the values in drop down list using php postgresql. Error means no output in drop down list. Below is my code. <!DOCTYPE html> <html> <body> ...
1
vote
1answer
24 views

How to have remote standby PostgreSQL server in different location (AWS)?

We have a primary PostgreSQL 9.4 server running multiple instances in our DC. It is using Continuous Archiving via WAL-E out to AWS S3. I would like to have a hot/warm standby server in our VPC within ...
0
votes
1answer
21 views

Restore Postgresql data after hdd crash

My HDD crashed, so, I can't make a Postgresql backup. It seems that my data tables are OK (I can view the data via pgAdmin). However, when I run pg_dump I get this: pg_dump: Error message from ...
-1
votes
2answers
34 views

postgres9.4 streaming replication not streaming [closed]

I configured a postgres pair for streaming replication, the pg_basebackup command was invoked successfuly on the slave, so I now have a copy of the master on it. The problem is that the slave does ...
3
votes
1answer
35 views

Can jsonb do fast search by *value* or even a partial value? Other types?

I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice. Example data: id: 1, name: "Joe Doe", preferences: { color: "red" , ...
1
vote
1answer
20 views

How to determine if pg_dumpall is still running

Environment: Postgres 8.4 Debian Squeeze LTS Recently we switched from a backup solution around hot_backups to using pg_dumpall. This solution used a pid file as long as the hot_backup was ...
0
votes
0answers
21 views

Postgres 9.1.16 Hot Standby

I am using postgres 9.1.16 (and i cannot upgrade :/) with Hot Standby. We have a few batch that perform some heavy writes, resulting in a lot of WAL produce in a short period of time. The standby ...
2
votes
1answer
42 views

Data synchronization across multiple databases

I have a Django application which uses PostgreSQL as a database. The application stores hierarchical data. It is like a warehouse management system with multiple branches and hierarchy. The current ...
0
votes
0answers
28 views

insert value based on check constraint on another column [closed]

I want to have domain_list field in db which will contain multiple domain values only if allowed_domain field is set to 1. I want to insert values in domain_list based on value of allowed_domain ...
1
vote
1answer
42 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
3answers
59 views

Intentionally corrupting an index in PostgreSQL

I need to know if there is any method for intentionally corrupting an index in PostgreSQL. I have been searching for it for a long time but never seen anyone mentioning such an act.
0
votes
0answers
18 views

DEFERRABLE INITIALLY DEFERRED in postgres after migration

I have used dbconvertool to migrate database from mysql to postgres. It is migrated successfully. But while comparing the table on mysql and postgres, i am seeing "DEFERRABLE INITIALLY DEFERRED" on ...
0
votes
1answer
35 views

Best Admin Tool for Postgres [closed]

can anyone tell me best the open source tool for postgres 9.4. Right now i am using pgadmin3. please suggest me if any other best tool is there for both developer level and admin level. Note : Tool ...
0
votes
0answers
4 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
6
votes
2answers
71 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
1
vote
0answers
22 views

Single Redshift table vs several mysql/ pgsql (on RDS) tables for small (200 tables * 20 columns * 1 million) monthly aggregation data

We're providing SaaS Data Analytics Dashboard for around 200 clients. We're using Amazon Redshift for storing data in in around 5 tables. We're getting around 30 million hits per day. Currently our ...
3
votes
1answer
39 views

Why does this fast postgres query show up in the slow query log?

On investigating an entry in our Postgres log, I noticed a large discrepancy between the reported duration and the speed of the query. 2015-07-28 17:27:26 UTC [dms_segment_3] LOG: duration: 2924.763 ...
-1
votes
0answers
22 views

Modifying a query for the UPSERT command [closed]

Here is the current query select pt.name, slr.name from stock_route_product srp inner join product_template pt on srp.product_id = pt.id inner join stock_location_route slr on srp.route_id = slr.id ...
12
votes
2answers
152 views

Simultaneous calls to the same function: how are deadlocks occurring?

My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not ...
2
votes
1answer
46 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
4
votes
0answers
27 views

Should I use a separate postgres schema for application logins? [closed]

I'm building an application. I have users, roles, and permissions; users can have one role, and a role can have multiple permissions. As such, I'll have a Users table, a Roles table, a Permissions ...
2
votes
0answers
34 views

Improve postgreql insert performance when compared to Oracle. Low memory utilization by Postgresql threads

I am trying to improve the performance of Postgresql (version 9.2.3) inserts for a simple table with 1 bigint, 1 varchar, 1 float and 2 time stamps. A simple replication of my JDBC program is ...
0
votes
1answer
27 views

postgres: Converting '' into NULLS

I'm trying to run this query to change my latitude coordinates, which is saved as text into a double precision ALTER TABLE mapdata ALTER COLUMN lat TYPE double precision USING (lat::double ...