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)

7
votes
0answers
133 views

Busy table is not getting vacuumed

We're using Postgres 9.2 on Windows to store low-frequency timeseries data: we're inserting around 2000 rows per second every second 24 hours, 7 days a week with no downtime. There is a DELETE that ...
5
votes
0answers
109 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
196 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 ...
4
votes
0answers
47 views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
4
votes
0answers
124 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
150 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
4
votes
0answers
82 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
40 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
3
votes
0answers
99 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 ...
3
votes
0answers
47 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
124 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
95 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
171 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
306 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
220 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
122 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
202 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
17 views

Can you force pgpool to require SSL connections?

Can you force pgpool to drop non-ssl connections? I know you can with postgresql (Force PostgreSQL clients to use SSL?), but dropping that same syntax (hostssl instead of host) results in a ...
2
votes
0answers
27 views

Stack Depth Limit Exceeded (After delete trigger)

CREATE TABLE parent ( parent_id VARCHAR(30) PRIMARY KEY ); CREATE TABLE child ( parent_id VARCHAR(30) REFERENCES parent ON DELETE CASCADE, child_id VARCHAR(30) PRIMARY KEY ); CREATE OR ...
2
votes
0answers
62 views

Is there a way to replicate Postgres databases in Amazon RDS between regions?

We have a Postgres RDS instance in the AWS Oregon Region. For some supposed legal reasons, we have to host our client's European data in the Ireland AWS Region, but we need that data synchronized to ...
2
votes
0answers
131 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
40 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
116 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
44 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
42 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
208 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
59 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
74 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
43 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
148 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
35 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
65 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
487 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
148 views

How do you stop Postgres from performing writes inside base/ directory during pg_dump?

We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec ...
2
votes
0answers
122 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
86 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
241 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
439 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
75 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
106 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
274 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
241 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
319 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
682 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
371 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
768 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
8 views

How to scale a multi table query with a large number of users in Postgres?

I see this as a compound of two related problems, but I might just be confusing things. I want to be able to do a multi table search, but over a large amount of rows. The number of rows being made ...
1
vote
0answers
31 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
12 views

pg_basebackup could not get WAL end position from server

I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...