PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

5
votes
0answers
89 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
5
votes
0answers
141 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
5
votes
0answers
437 views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
4
votes
0answers
46 views

Investigating rollbacks in Postgres

A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have ...
4
votes
0answers
109 views

PostgreSQL Transaction Committing for hours

I'm running into an issue whereby I have two connections from a user to my PostgreSQL server which have been running for about 4 hours and have been in a commit state for quite some time (at least 1 ...
4
votes
0answers
77 views

Postgres TupleTable like data structure

I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to ...
3
votes
0answers
48 views

Will a serializable read only deferrable select block?

I'm unsure from the documentation and wiki if a serializable read only deferrable select, a SELECT preceded by SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE, will block a ...
3
votes
0answers
34 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, ...
3
votes
0answers
83 views

psql: FATAL: sorry, too many clients already

I am suddenly getting this error when either trying to access the website that uses the postgresql database, or even when using the psql utility or pgadmin3. My database is set to handle 150 maximum ...
3
votes
0answers
57 views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
3
votes
0answers
137 views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
3
votes
0answers
214 views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
3
votes
0answers
196 views

Stubborn autovacuum process slow things dows

We are facing a strange problem since yesterday. In a database of ours there is a partitioned table containing historical data. That is, every week's data goes to a different partition and stays ...
3
votes
0answers
117 views

What happen when Postgresql tablespace is null?

I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution ...
3
votes
0answers
185 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
2
votes
0answers
40 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
2
votes
0answers
74 views

Index for finding an element in a JSON Array in PostgreSQL (with multiple JSON fields)

Original question of what this is based on: http://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array This works fine if you only want simple matches. Suppose tracks ...
2
votes
0answers
39 views

Efficiently joining two irregular series representing changes

(This is a PostgreSQL-specific question, using version 9.1, but could be upgraded to 9.3.) I have a number of tables that represent a series of changes. These tables all have the same structure: ...
2
votes
0answers
56 views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
2
votes
0answers
38 views

How to make PostgreSQL default_tablespace work properly?

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'? I made the following script, with it's output following, to show the problem I am having : #!/bin/bash # export ...
2
votes
0answers
39 views

Postgresql import user from linux

I install postgresql 9.2 on a Ubuntu 12.10 machine I tried to import linux's user to postgresql but only way I can figure out is CREATE USER Jack WITH PASSWORD '1234'; and do it 10 times (if ...
2
votes
0answers
85 views

Converting a SQL_ASCII database to UTF-8

I have a postgresql (version 8.1) database that has the encoding set to SQL_ASCII, combined with several apps that some are running using UTF8 and some with LATIN1. With this scope I have a database ...
2
votes
0answers
46 views

Postgres don't start after an unexpected shutdown

After an unexpected shutdown in postgres 8.4, I'm getting this error. couldn't open the file 'pg_xlog/....'. No such file or directory. invalid primary checkpoint record. Complete message in ...
2
votes
0answers
55 views

Postgres: missing disk space after truncate and re-insert

We've got a IP address range dataset stored in a table that was truncated and then re-populated with a slightly modified version of the dataset (almost identical in size). However, after truncating ...
2
votes
0answers
41 views

Postgres Replication From Different Masters?

I'm getting a Postgres 9.3.3 install sorted out. My application is Drupal (so read/write), but low-traffic, so all application traffic is sent to the primary master. I have two sites (main and ...
2
votes
0answers
135 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 ...
2
votes
0answers
33 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 ...
2
votes
0answers
52 views

Postgresql | Database with OID doesn't exist

So, how it started, DB started throwing errors about "database with OID 1" 2014-02-07 22:54:46.561 GET >ERROR: database is not accepting commands to avoid wraparound data loss in database with ...
2
votes
0answers
420 views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
2
votes
0answers
107 views

Limiting PostgreSQL logging

I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this: CREATE TABLE testtable ( id SERIAL PRIMARY KEY, name TEXT UNIQUE ...
2
votes
0answers
70 views

Replication slave re-reading WAL on restart

I have a standard streaming replication setup going with PostgreSQL 9.1. It works fine. However, if I stop the slave cleanly (to move it or perform other maintenance) and restart it a short time ...
2
votes
0answers
206 views

Postgres: PANIC: WAL contains references to invalid pages

I have a slave database that is being replicated from a Master DB. All of a sudden, my slave DB just exited and wasn't able to restart. Here are the error messages when it exits. Similar ones appear ...
2
votes
0answers
346 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
2
votes
0answers
70 views

How can I use an environment variable in a Postgres function?

Let's say I have a windows user environment variable SOME_STRING. Is it possible to use its value in a PL/pgSQL function? If not, do I need to use an untrusted language (like PL/pythonu) or can I ...
2
votes
0answers
83 views

PostgreSQL Change owner of all tables under a specific schema

I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner Is there a way to accomplish that?
2
votes
0answers
49 views

Evenly sized partitions in Postgresql

All, My database has several years worth of temporal data that I am currently partitioning my the month. The problem is that some months there are hundreds of millions of records while other months ...
2
votes
0answers
239 views

PostgreSQL index array of int4range using GIN / GIST - custom operator class

Here is my table: CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range[] ); I want to index the val column: CREATE ...
2
votes
0answers
225 views

Remove Mac OSX Lion pre-installation of PostgreSQL

I'm running Lion 10.7.5, and I'm trying to install Postgresql 9.2.4 for use with Django. I have hit countless roadblocks and errors, so I would like to start with a totally clean slate. I did a brew ...
2
votes
0answers
295 views

How to properly configure pgBouncer transaction level pooling?

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
2
votes
0answers
589 views

Is there a Postgres admin GUI that can execute a master file containing multiple sql files

I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file. example: ...
2
votes
0answers
317 views

Can PostgreSQL's COPY create CSV with Locale-specific number formatting?

I want to create a CSV with PostgreSQL 9.1 COPY that directly opens correctly with a double-click in crazy German Excel 2010. Using ";"-Delimiter is easy. The encoding is solved, but Excel only ...
2
votes
0answers
697 views

“custom archiver out of memory” error when restoring large DB using pg_restore

I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
1
vote
0answers
18 views

postgres - generating documents for full text search taking forever and eating up disk space

We use PostgreSQL and Django-Haystack with Elasticsearch for search on our service, and were thinking of switching to Postgres's built in full text search because of various annoyances using a NoSQL ...
1
vote
0answers
37 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
1
vote
0answers
24 views

Selecting aggregated string from database

I have a table in my database consisting of one column which is NewLanguage(varchar). I insert new languages to the table and I want to select the rows of the table and assign them to a string ...
1
vote
0answers
30 views

Strategy for multiple distinct databases in PostgreSQL

I have a 30 gb db in PostgreSQL 9.2. Running On Amazon Linux on an ec2 (web virtual machine), with the data directory mounted from its own drive (EBS). I'll call the database db30. On production ...
1
vote
0answers
41 views

Does amazon rds PostgreSQL require vacuum?

It isn't clear if auto vacuum is running in rds. Thanks!
1
vote
0answers
22 views

Postgresql service frozen and can restart properly

I have windows server 2008 R2 and postgresql 9.3 Server is still for testing so I'm alone and only have a .net application doing consult with npgsql to the db, mostly doing some store procedures and ...
1
vote
0answers
28 views

Is a commit or abort more costly for a prepared select?

I have found that selects will actually generate WAL records upon commit. Are there other costs to committing a select? Are there costs due to an abort? If so, which has more? What would be the ...
1
vote
0answers
23 views

Amazon Redshift VACUUM error

I'm trying to run vacuum delete only on a table with big amount of columns (800+) and 4 million rows. I know it's recommended to use deep copy instead, but still am wondering if running vacuum is ...