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
0answers
105 views

Update query taking 8x times in hot standby environment of Postgresql

I configured streaming replication (hot standby) and tested the update query on it. The update query is taking 8 times more when compared with stand-alone PostgreSQL (9.2 version) system. Some ...
2
votes
1answer
188 views

Recovering a partial PostgreSQL archive

I have $PGDATA/base but nothing else. It was taken from an installation after the postgreSQL service was shutdown, so is clean. Is there any way to recreate the other elements in $PGDATA that allow ...
5
votes
1answer
3k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
3
votes
2answers
528 views

How slow is slow in postgres?

I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now ...
2
votes
1answer
368 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
2
votes
1answer
516 views

Why write ahead logs in PostgreSQL are generated every second

PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute. Is it possible to make them occur less frequently? Is it possible to increase the WAL ...
1
vote
1answer
1k views

Increased performance from single statement, multiple row inserts?

I'm currently obsessing over bulk inserts via prepared statements with libpqxx. I am far from a database expert and even further when it comes to Postgres, but just thinking superficially, it doesn't ...
0
votes
1answer
167 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
3
votes
0answers
204 views

Is PostgreSQL or ElasticSearch/Solr the right tool for this kind of query? [closed]

Here is an example of the current PostgreSQL query: SELECT a.*, a.al_location <-> '(someLon, someLat)' AS d FROM al_app.al_activity AS a WHERE to_tsvector('simple', a.al_keywords) @@ ...
2
votes
1answer
642 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
0
votes
0answers
73 views

Postgresql not working: error

My Postgres was working fine on my ubuntu 12.10 before i installed postgres-xc and execute a command initdb for changing encoding. After that it started showing error like: psql: could not ...
3
votes
3answers
2k views

PostgreSQL 9.0 how to reclaim disk space after delete without rebuilding table?

Our PostgreSQL 9.0 Windows production server is running low on space. In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the ...
2
votes
1answer
3k views

postgresql bigserial or sequence using jpa

I'm a java developer working with jpa, before to define the indexes in my entities i would like to known which one is faster o better to use?.
3
votes
1answer
208 views

Adding unsigned 256 bit integers in PostgreSQL

I was wondering if there would be any way to do the following in PostgreSQL: UPDATE cryptotable SET work = work + 'some big hexadecimal number' where work is an unsigned 256 bit number. Right now ...
1
vote
0answers
255 views

Data Corruption on base backups created with wal-e?

I have streaming replication setup on Postgresql 9.1 with two machines on AWS EC2. The master has a cron job that does a daily base backup using the wal-e tool and uploading it to Amazon's S3 storage. ...
0
votes
1answer
6k views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
-2
votes
2answers
433 views

Select records for a day and a day - 1 day

From one table, I want to get the records from one column position when the created_at = Today and when the created_at = created_at - 1.day COALESCE(array_agg(DISTINCT pr.position)) AS position, ...
2
votes
1answer
6k views

How to Configure PostgreSQL Hotstandby Automatic Failover

So I have configured two postgeSQL servers to have hotstand by streaming, now my question is, how can I configure it so that when the master server goes down, the slave server will become the new ...
3
votes
1answer
58 views

What is the “last” data that get's backed up, when running pg_dump on a live server?

I'm wondering: If I start doing a pg_dump of a very large database (it would take hours), that is still running actively receiving writes, what is then the last data that goes into the backup? Is it: ...
3
votes
1answer
3k views

log_min_duration_statement setting is ignored

I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows. I have 2 databases that are actively in use and they are used from a remote server. I ...
5
votes
2answers
2k views

PostgreSQL, integer arrays, index for equality

I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module ...
3
votes
1answer
365 views

What does “ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table ”fruits“” mean?

After being bitten by the bug mentioned in the release notes of 9.3.4, I have realized that a very small part of my data is corrupt, after doing a failover. The bug showed it self as duplicate rows, ...
0
votes
1answer
180 views

Why is it not possible to alter a view with a “CREATE OR REPLACE”?

The Postgresql documentation is clear on this point: you can not change the columns of an existing view. The new query must generate the same columns that were generated by the existing view query ...
3
votes
2answers
2k views

Postgres Streaming Replication lagging, using lots of CPU and little I/O

We have 2 dedicated DB Servers. One is master and other is its streaming slave. Postgres version 9.0.17. We have similar hardware on both servers and using 15krpm SAS drive in Raid 10 for Database ...
4
votes
1answer
898 views

How to apply a patch in PostgreSQL on Windows?

How to apply a patch in PostgreSQL? I found an advice, but it seems to be suited for *NIX only. My PostgreSQL (9.3.1 EDIT: 9.3.4 now) was installed from an exe installer on Windows XP (32 bit), so I ...
1
vote
1answer
930 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
6
votes
2answers
1k views

Why does my table hold duplicate primary keys?

To my big (and worrying) surprise I just realized that my table with millions of rows, has a few handful of rows, where the primary key (ID) is a duplicate! I don't understand how this can ever happen,...
1
vote
1answer
6k views

Installing pgcrypto Extension

I have this password hash function that hashes a password to sha256. I've already got it running in pgadmin3 in a local database. I've done it by importing the extension, a feature that has been added ...
0
votes
0answers
49 views

Organizing a couple tables in postgresql

I have a data structure that is represented like this: { 'question_id': 1, 'is_required': False, 'question_text': [ { 'id': 1, '...
12
votes
3answers
2k views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
4
votes
2answers
693 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
1
vote
1answer
447 views

PostgreSQL 9.1: vacuumdb vs. autovacuum

Have a question on the difference between just allowing autovacuum to take care of vacuum'ing or having to run a full db vacuum in a certain interval (every night?, every week?). We used to run a ...
2
votes
0answers
800 views

Why does this query cause the Postgres worker process to consume so much memory?

I have a PostgreSQL 9.3.4 instance running in a CentOS 5.x VM (Linux 2.6.18-308.8.2.el5xen) with 6GB RAM. I've set shared_buffers to 1GB and work_mem to 1MB (a low value for testing). Here are my ...
3
votes
3answers
279 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
1
vote
1answer
59 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
10
votes
2answers
11k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
3
votes
2answers
583 views

Problem translating MySQL to PostgreSQL

We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work. In the below, all I did this far is replace IFNULL() with COALESCE() and ...
2
votes
1answer
186 views

RETURN NEXT in Postgres Function

I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data. I was originally going to return an array, but I found a resource that explains the "...
1
vote
1answer
113 views

PostgreSQL: cache, disk, replication. What order?

I am thinking of using two servers in Master/Slave replication I want to add following condition: RAM size = dataset size * x, where x > 2. After that, I plan to force PGSQL to keep all data in RAM ...
2
votes
1answer
235 views

select and count based on a condition

I have two tables, proj: id name and proj_reports: id, position, created_at, key_id, proj_id and I want to select from key_reports for each proj_id: `- total number of `key_id` which can be done ...
2
votes
1answer
7k views

PostgreSQL Error: out of memory

I'm trying to run a query that should return around 2000 rows, but my RDS-hosted PostgreSQL 9.3 database is giving me the error "out of memory DETAIL: Failed on request of size 2048.". What does ...
2
votes
1answer
534 views

Postgres startup fail error

I was running some code when my laptop shut down. Now everytime I connect to the server, I get an error message as follows: ambangra@access1:~$ LOG: database system shutdown was interrupted; last ...
7
votes
2answers
6k views

Privileges for database owner; application user

Quick version: What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account? Longer Version: I am creating ...
1
vote
1answer
2k views

How can I speed up a Postgres query containing lots of Joins with an ILIKE condition

I have an odd problem which I really don't understand. Simply put I have a join containing 4 table joins, which I believe all have appropriate indexes, but which takes a ridiculous amount of time to ...
1
vote
1answer
1k views

Postgres memory usage too low

I have a 400GB Postgres 9.3.4 DB running on Fedora20, and a few indices per table. I have upped shared buffers to 4GB (the system has 16GB available), set shm in the kernel and set the ...
1
vote
1answer
50 views

Preparing a former master to run as slave again

I have a streaming replication setup using postgresql 9.3. I have two servers, the master (server_1) and the slave (server_2). The other day I did a failover to my slave (server_2). Now server_2 has ...
1
vote
1answer
123 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM "...
2
votes
0answers
390 views

How to see all queries via pgBadger

The excellent pgBadger has an interface to reviewing specific queries - the slowest, by host, by user etc. Is it possible to see all queries processed, perhaps in a given timeframe. If not available ...
0
votes
0answers
61 views

Reduce read write delays in specific database setup

I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that ...
0
votes
0answers
564 views

Lost all my Postgres database tables? [closed]

I'm a novice using PostgreSQL on a local database that I created using Postgres.app on my Mac. My dbase included multiple related tables with ~50MB of data, and after restarting my Mac (system update) ...