PostgreSQL 8.4 : 2009 release of PostgreSQL

learn more… | top users | synonyms (1)

4
votes
2answers
15 views

Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
3
votes
0answers
151 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
26 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
vote
1answer
42 views

Spiky COMMIT results in performance slowdowns with Postgres 8.4

We just installed performance monitoring on our web application, and we're seeing a spiky pattern in response times from our Postgres 8.4 server. The spikes correspond with significant slowdown in our ...
2
votes
1answer
70 views

What is the relevance of 'SET default_with_oids = true' in a Postgresql dump?

I have an SQL backup from an old database which may have been a PostgreSQL 7 or 8 database I am importing which contains a SET default_with_oids - true statement, into a an existing 8.4 database and ...
0
votes
1answer
2k views

How to upgrade postgresql 8.4 to 9.4

I want to upgrade my postgresql 8.4 to 9.4. the documentations are not clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after ...
1
vote
1answer
363 views

Postgresql Rename Table without Updating View Definitions

I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off? So I rename the ...
0
votes
1answer
26 views

PostGIS 1.5 Error with PostgreSQL 8.4

I am trying to add PostGIS functionality to a database that I created in PostgreSQL using the following command: psql -d <db_name> -f /location/to/this/file/postgis.sql However, I receive a ...
0
votes
1answer
165 views

When is the postgres auto vacuum executed

I am using an older version of postgres(8.4.20). I know that the auto vaccum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database ...
1
vote
1answer
194 views

create trigger hangs

I have a trigger function that just copies inserted values to another table. Then, each night I need to drop and recreate this trigger for some export operation. --trigger function CREATE OR ...
3
votes
1answer
492 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
1answer
396 views

Split two rows into two columns

I have the following table: id | name | action | count ------------------------------ 1 | Value1 | 0 | 27 1 | Value1 | 1 | 49 2 | Value2 | 0 | 7 2 | Value2 | 1 | ...
3
votes
1answer
481 views

pg_dump does not finish

I am trying to backup our postgres database (8.4.17), which is about 25GB in size. pg_dump database_name > database_db_dump_2014-05-05.sql unfortunately, the backup stops after about 600MB in, ...
2
votes
0answers
242 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 ...
1
vote
1answer
685 views

pg_dump Error while running backup

I encountered an error on PostgresSQL. Here is details for the error which i have seen during the backup. pg_dump: SQL command failed pg_dump: Error message from server: ERROR: found toasted toast ...
3
votes
2answers
1k views

How to take pg_dump of a very large postgres database?

I have a production database server running Postgres 8.4 on Cent OS 6.5.The volume of data has increased recently and within few months database now has 18500 tables amounting to approx 350GB database ...
1
vote
1answer
1k views

PostgreSQL transaction locked database table: “idle in transaction”

I have a web application interacting with PostgreSQL (v8.4 & Centos Linux) which suddenly started locking some of the database's tables. Still have no idea what happened, since the code is not ...
3
votes
1answer
830 views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
6
votes
1answer
2k views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
1
vote
1answer
249 views

Dump PostgreSQL without DROP DATABASE

I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file). Actually I use this command: pg_dump --format=c --no-privileges --no-owner ...
3
votes
2answers
5k views

TOAST Table Growth Out of Control - FULLVAC Does Nothing

Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features and be in line with 30ish other PGSQL servers. This was done by a separate IT group who ...
1
vote
1answer
4k views

Error: Could not read Block X of relation base/Y/Z

We have some serious issues with our PostgreSQL-Server (Version 8.4). Our webapplication uses jdbc to connect to the PostgreSQL Server. Suddenly our webapplication cant connect to the PostgreSQL ...
0
votes
0answers
85 views

looking for a postgresql load emulation client [closed]

Is there any load emulation client for postgresql like mysqlslap? And is there any good monitoring tool for postgresql database server? Thanks
0
votes
2answers
561 views

Run query faster, what index to use

I have a table with 85M rows : CREATE TABLE emissions_totales ( "schema_source" varchar(150), "snap" varchar(20), "gid" varchar(255), "polluant" varchar(50), "emi" float8, "numreg" varchar(3), ...
0
votes
0answers
1k views

Server doesn't listen

I try in Win7 by means of PGAdmin 3 to be connected to a host 192.168.1.8 with the virtual machine with linux. Already which time I receive the message Server doesn't listen The server doesn't accept ...
0
votes
1answer
131 views

Writing with multiple psql processes to database seems to damage data

I have a couple terabytes of CSV data that I am trying to import into a PostgreSQL 8.4 database (on a RedHat 6.2 server), whose data directory is initialized on a multipath hardware RAID. There are ...
2
votes
2answers
777 views

Creating sequence for longer numeric values

I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API. The value must be 20 digit long, numeric and unique. Best ...
6
votes
2answers
935 views

Custom unique column constraint, only enforced if one column has a specific value

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is "true", then ...
1
vote
0answers
810 views

Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below [closed]

The error: Unable to read data from the transport connection. an existing connection was forcibly closed by the remote host. The conditions: I have the following connection string: server = ...
2
votes
2answers
550 views

String handling in postgres 8.4

This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist). I thought I ...
4
votes
1answer
2k views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
1
vote
0answers
293 views

Connecting a GUI to the user-created function in PostgreSQL [closed]

I have been given a task to create profiles for the applications. The Database that we use is Postgresql. I've defined some function that will give me those profile and I've added them to the ...
5
votes
1answer
684 views

Safe way to stop/abort REINDEX

I am running a REINDEX but it takes long time and still working. The application using the DB is not responding right now. Is there a safe way to stop it and do it another time? PostgreSQL version is ...
1
vote
1answer
856 views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
12
votes
2answers
5k views

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...