Questions tagged [postgresql-8.4]
PostgreSQL version 8.4
57
questions
0
votes
2
answers
153
views
Test/Verify Log-Shipping Backup
We recently suffered a power/connectivity outage at a data center housing two PostgreSQL 8.4 secondary servers, that remotely back up our two primaries via log-shipping replication. Backup power kept ...
4
votes
1
answer
118
views
Create WAL at interval, but only if data changed
I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure.
...
- 173
4
votes
1
answer
10k
views
Where can I download an old version of PostgreSQL for Windows? [closed]
I need version 8.4 for a legacy system, but EnterpriseDB (the official link) only shows 9.x
On the PostgreSQL FTP Mirror I see the 8.4.22 binary, but only have binary for solaris:
1
vote
1
answer
740
views
Postgres row count
I have a table with a row count of 985 records. But it is really slow to query this table.
# select count(*) from movie;
count
-------
985
Then I perform query using EXPLAIN ANALYZE The row ...
- 113
1
vote
1
answer
1k
views
Error granting access to sequence with PostgreSQL 8.4
I'm attempting to run this query
GRANT USAGE ON [db].users_uid_seq TO [user];
db and user are redacted (obviously).
I get this error:
ERROR: syntax error at or near "GRANT"
I've tried a number ...
- 58
1
vote
0
answers
101
views
postgres 8.4: makeslave it's own master
For a DR test, I've tried to split the slave from the master. To do this, I edited the master postgresql.conf, turned off archive_mode and restarted. No problem.
Now I'm trying to make the slave a ...
- 508
2
votes
1
answer
707
views
delete hung soon after vacuum verbose analyse
I have a fairly large java webapp system that connects to a postgres8.4 db on rhel.
This app only does inserts and reads.
This has been running without issue for 2.5 years and for the last year or so ...
- 71
0
votes
1
answer
42
views
postgresql v8.4 function runs but doesn't work
I have a postgresql v8.4 database. I need to update several hundred tables with about 3 million rows of data each.
The customer wants a new column added (which I added) and now I'm trying to update ...
- 103
6
votes
1
answer
5k
views
Using TCP keepalive for terminating orphan postgres queries not working
Problem: A user of some application executes a report which involves a heavy sql query. After some minutes, the user then quits the application (or even worse, the app crashes, or the connection to ...
1
vote
1
answer
979
views
ERROR: "4.9E-324" is out of range for type double precision
I saw this error when trying to perform the following insert into a PostgreSQL 8.4.20:
java.sql.BatchUpdateException: Batch entry 12 insert into perf_raw_2017_03_16 values ('3129', '182', '818', '...
- 153
16
votes
7
answers
3k
views
Form groups of consecutive rows with same value
I have a situation I think can be solved using window function but I'm not sure.
Imagine the following table
CREATE TABLE tmp (
date timestamp
, id_type integer
) ;
INSERT INTO tmp (date, id_type)
...
- 163
0
votes
1
answer
60
views
Giving a session-local temporary name to a schema
I have a schema foo_bar but also foo_x. During a particular session, is it possible to just set foo_bar to bar in PostgreSQL (8.4)?
1
vote
1
answer
562
views
Function pg_indexes_size(oid) does not exist
I'm attempting an operation on a pg_class c, but getting this:
ERROR: function pg_indexes_size(oid) does not exist
LINE 10: , pg_indexes_size(c.oid) AS index_bytes
...
- 113
0
votes
1
answer
407
views
PostgreSQL directory very full
In the /var/lib/pgsql/data/base Postgres that in version 8.4 and it was identified that the error single bank is with 37G which is absurd, is not already realized vacuum full declined nothing and also ...
- 1
2
votes
1
answer
1k
views
How to reset a postgres database that has used all of the available disk space?
I have a small ~10G PostgreSQL 8.4 database used for development that has filled up the /var directory and is now unusable. I am unable to connect to the database in this state to delete any data or ...
- 123
1
vote
1
answer
52
views
Is there a way to connect a Postgres 9 database client to a Postgres 8 system?
I a now work mainly with Postgres 9.5, but I alsoo need to connect to a number Postgres 8 databases connect them probably as different schemas, if that is possible.
Are foreign database wrappers the ...
- 915
3
votes
2
answers
15k
views
Declare variable for SELECT statement in Postgres function
I have modified my function, but I have problems with declaring variables. I use Postgres 8.4.
CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz)
RETURNS TABLE ...
- 33
3
votes
1
answer
189
views
Is automatic failback on Postgres 8.4 possible via log shipping/PITR?
I have successfully setup continuous archiving and recovery of a master and slave node with PostgreSQL 8.4 via log shipping. I'm using keepalived to monitor both nodes and I can successfully trigger ...
- 133
6
votes
2
answers
3k
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 ...
4
votes
0
answers
2k
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:
...
- 65
1
vote
1
answer
308
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 server:...
- 111
1
vote
1
answer
534
views
PostgreSQL 8.4 replication of more than 100 tables
I am facing a problem with PostgreSQL version 8.4.
I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to ...
- 1,369
1
vote
1
answer
2k
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 ...
- 507
5
votes
1
answer
4k
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 ...
- 915
18
votes
3
answers
47k
views
How to upgrade PostgreSQL from version 8.4 to 9.4?
I want to upgrade my PostgreSQL from version 8.4 to 9.4.
The documentation is not very 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 ...
- 549
9
votes
1
answer
5k
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 ...
- 193
0
votes
1
answer
99
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 ...
- 103
4
votes
1
answer
6k
views
When is Postgres autovacuum executed
I am using an older version of Postgres (8.4.20). I know that the autovacuum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database ...
- 143
1
vote
1
answer
1k
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 ...
- 304
9
votes
2
answers
5k
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 - ...
- 143
2
votes
1
answer
4k
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 | ...
- 173
7
votes
1
answer
10k
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, ...
- 211
11
votes
1
answer
3k
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 ...
- 978
2
votes
0
answers
512
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 ...
- 304
1
vote
1
answer
3k
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 ...
Wood Chipper
30
votes
3
answers
116k
views
Using PostgreSQL 8.4, how to convert bytea to text value in postgres?
In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly ...
- 451
4
votes
2
answers
13k
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 ...
- 451
0
votes
1
answer
421
views
Altering a parent table in Postgresql 8.4 breaks child table defaults
The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered.
How to replicate:
First, create ...
- 485
1
vote
1
answer
4k
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 new,...
- 11
4
votes
1
answer
4k
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 ...
- 145
11
votes
1
answer
13k
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 ...
user18077
2
votes
2
answers
4k
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 --...
- 133
9
votes
2
answers
21k
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 ...
- 95
5
votes
1
answer
36k
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 ...
nop
0
votes
2
answers
1k
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),
...
1
vote
1
answer
508
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 ...
- 175
7
votes
2
answers
3k
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 ...
- 439
3
votes
2
answers
1k
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 ...
- 937
78
votes
2
answers
106k
views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
- 937
6
votes
1
answer
5k
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,122