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)

2
votes
1answer
33 views

Distributing a semi-large key/value list

I have a semi-large (around 30 million records) key/value list which I want to load into redis. The data is stored in PostgreSQL (as master data) and we want to distribute this to clients for faster ...
1
vote
1answer
46 views

What kind of database should I use?

I'm designing a new system and I'm wondering what kind of DB should I use for my needs. I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 ...
2
votes
1answer
217 views

PostgreSQL server problem in Windows XP

2014-10-01 09:22:49 IST LOG: database system was interrupted; last known up at 2014-09-30 18:30:24 IST 2014-10-01 09:22:49 IST FATAL: the database system is starting up 2014-10-01 09:22:49 IST LOG: ...
4
votes
1answer
66 views

Why is a gin index on a jsonb column slowing down my query and what I can do about it?

Initialize test data: CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE TABLE docs (data JSONB NOT NULL DEFAULT '{}'); -- generate 200k documents, ~half with type: "type1" and another half with type: ...
1
vote
0answers
22 views

Postgres 9.4 on kubuntu 14.04 won't start

I've had postgres9.4 running without incident for around 5 months. Today after a restart I can't get postgres running. When I open pgAdminIII and try to connect to my server I get "Server doesn't ...
1
vote
1answer
676 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
3
votes
1answer
113 views

How to determine whether a PostgreSQL Hot Standby is fully mirrored?

I have set up a hot standby of a PostgreSQL server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have ...
4
votes
1answer
60 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
1
vote
0answers
25 views

Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) ...
1
vote
1answer
152 views

difference between pg_dump command's --column-insert and --attribute-insert

Background: I'm trying to create a dump file that only contains the data in my database. I would like it to create the data with the INSERT command. In reading the manual, ...
3
votes
2answers
153 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
1
vote
1answer
28 views

PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel

I dumped an existing PostgrSQL database (generating "Insert" statements), and imported it on another computer. The source runs Debian (UK) OS, the target Mint (FR) OS. For some tables, when I try to ...
0
votes
1answer
103 views

could not open log file “/var/log/postgresql-2015-05-24_181212.log”: Permission denied

I installed postgresql from source on Ubuntu 14.04 (I also installed from repositories too before, I just want to get a feel for both of them right now, as I will heavily rely on this database). So ...
1
vote
0answers
32 views

PostgreSQL 9.4.4 Crashing

We are having 9.4.4 version running on linux( PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) which is crashing unexpectedly and coming ...
-1
votes
0answers
24 views

I want load the Jason File in my local desktop , that i want to load in postgres [closed]

I have .jason file in my mac book, that I want to load in postgres server, Kindly navigate me how to do it in simple way, I'm not jason script user, please describe me each and every step very ...
4
votes
1answer
987 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
1answer
36 views

Postgres 9.1.16 Hot Standby

I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby. We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time. The ...
1
vote
1answer
71 views

Matching data in two tables

I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure. Say, you have two tables, one listing the customer's creditcard_number, and one listing ...
16
votes
2answers
20k views

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
0
votes
1answer
14 views

Any gotchas with changing IP address of Postgres Master?

We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated ...
0
votes
0answers
44 views

Error: File '' couldn't be removed (error 2: No such file or directory)

I get the following pgAdmin III error every time I restore a database, using pgAdmin III: Error: File '' couldn't be removed (error 2: No such file or directory) See the pgadmin log. My ...
0
votes
1answer
28 views

How to return multiple columns (each a composite type) in plpythonu

I have created a plpythonu function that should return a table with multiple columns. At present, it returns a single column with multiple components. I came across this answer which is A) a little ...
0
votes
1answer
14 views

How to use a value from insert in conditional insert in postgres

insert into addresses(street, city, user_id) select 'street1','LA', 2 where exists (select * from users where users.id = $$$$$ AND users.storeaddress='true')) I want to insert address for a ...
0
votes
1answer
24 views

Store Procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
0
votes
1answer
55 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to ...
5
votes
4answers
6k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
0
votes
0answers
21 views

Postgres New trigger being blocked.

Running Postgresql 9.3 We have a trigger that was being blocked by select for update statements, so it couldn't be added. We took the DB down and kicked everyone off (changed network port). When we ...
4
votes
1answer
3k views

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ...
2
votes
2answers
36 views

Calculating the percentage change from the previous row

I just entered the world of window functions. I have a similar problem to solve as in this question. I have this table: The last column (%Grown) is given by: -(1 - price of row 2 / price of row ...
1
vote
1answer
97 views

Querying sums of grouped consecutive rows in PostgreSQL 9

I have data about people traveling in different countries like this: country | begintimestamp | distance Germany | 2015-01-01 00:00:00 | 100 Germany | 2015-01-01 01:12:13 | 30 France | ...
0
votes
1answer
18 views

Looks like a postgres backup/dump structure (A sort of a forensic/data archeology question)

I was given a compressed folder that contains number of subdirectories, and, among others, directories named .pgdir with number of numerically sequenced files in a <9999>.dat.gz format and one in a ...
1
vote
3answers
59 views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
5
votes
3answers
71 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
-6
votes
1answer
49 views

Postgres Dump and Restore on Linux Terminal

I need to take a dump in postgres db and to be restored on some other server. please tell me the command in Linux with compressed and encrypted format.
3
votes
1answer
66 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" , ...
5
votes
2answers
161 views

“Recheck Cond:” line in query plans with a bitmap index scan

This is a spin-off from comments to the previous question: Postgres 9.4.4 query takes forever Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
0
votes
0answers
17 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 ...
8
votes
3answers
136 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
5
votes
3answers
564 views

How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

From PostgreSQL docs: Number of Database Connections -> How to Find the Optimal Database Connection Pool Size for optimal throughput the number of active connections should be somewhere near ...
2
votes
2answers
32 views

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
1
vote
0answers
33 views

PostgreSQL trigram GIST vs. GIN indexes

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes. Initially, I started to use GIN ...
4
votes
3answers
88 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
0
votes
2answers
144 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
4
votes
3answers
569 views

PostgreSQL procedural languages overhead (plpython / plsql / pllua…)

I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks. How does they compare to builtin functions? Is there any difference ...
3
votes
1answer
150 views

How do completely empty columns in a large table affect performance?

I have 400 million rows in a Postgres db, and the table has 18 columns: id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without ...
1
vote
0answers
30 views

Query planner slow to use newly created index on database under heavy load

We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance. Sometimes we need to update/recreate one of the indexes we have, for ...
0
votes
2answers
388 views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
0
votes
1answer
43 views

Postgresql pg_basebackup from slave didnt save WAL's

Good day please tell me whether it is possible to shoot a backup with utility pg_basebackup from the slave server (where of streaming replication with the help of this utility pg_basebackup) I have ...
5
votes
1answer
1k views

Get postgres snapshot from Amazon RDS

I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with. Right now I'm doing a pg_dump when I ...
1
vote
1answer
22 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...