Questions tagged [postgresql-8.4]
PostgreSQL version 8.4
58
questions
0
votes
2answers
134 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
1answer
68 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.
...
3
votes
1answer
5k 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
1answer
589 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 ...
1
vote
1answer
514 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 ...
1
vote
0answers
61 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 ...
2
votes
1answer
508 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 ...
0
votes
1answer
36 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 ...
5
votes
1answer
3k 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
1answer
544 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', '...
13
votes
7answers
2k views
Grouping or Window
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
...
0
votes
1answer
48 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
1answer
335 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
...
0
votes
1answer
276 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 ...
2
votes
1answer
665 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 ...
1
vote
1answer
50 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 ...
1
vote
2answers
11k 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 ...
3
votes
1answer
132 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 ...
6
votes
2answers
2k 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
1k 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:
...
1
vote
1answer
212 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:...
1
vote
1answer
468 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
vote
1answer
1k 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 ...
4
votes
1answer
2k 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 ...
18
votes
3answers
45k 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 ...
7
votes
1answer
4k 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
83 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 ...
1
vote
1answer
3k 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 ...
1
vote
1answer
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 ...
8
votes
2answers
3k 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
3k 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 | ...
5
votes
1answer
7k 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, ...
11
votes
1answer
2k 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 ...
2
votes
0answers
468 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
2k 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 ...
18
votes
3answers
53k 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 ...
4
votes
2answers
9k 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 ...
0
votes
1answer
330 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 ...
1
vote
1answer
3k 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,...
4
votes
1answer
3k 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 ...
11
votes
1answer
8k 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
2k 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 --...
9
votes
2answers
18k 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 ...
2
votes
1answer
27k 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
2answers
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),
...
0
votes
1answer
280 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 ...
5
votes
2answers
2k 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 ...
20
votes
2answers
7k 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 ...
3
votes
2answers
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 ...
66
votes
2answers
75k 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 ...
