0
votes
1answer
20 views

High volume data aggregation

I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below. id,time 1967,2014-04-12 00:42:55+00 1967,2014-04-12 00:42:51+00 1972,2014-04-12 ...
0
votes
0answers
49 views

Adding additional condition significantly degrades performance

Have the following query: SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' ...
1
vote
0answers
19 views

Data Corruption on base backups created with wal-e?

I have streaming replication setup on Postgresql 9.1 with two machines on AWS EC2. The master has a cron job that does a daily base backup using the wal-e tool and uploading it to Amazon's S3 storage. ...
1
vote
1answer
32 views

log_min_duration_statement setting is ignored

I am running Postgresql 9.1 on Ubuntu. Exact Postgresql version is 9.1+129ubuntu1 as my package manager shows. I have 2 databases that are actively in use and they are used from a remote server. I ...
-1
votes
1answer
42 views

Select Into Regexp_replace doesn't write changes into newly generated table

I have been using this query in postgres 9.1 in order to remove '' from existing data. Below is a sample of the data before running regexp_replace. 6'' 6'' 6'' 20'' 12'' 18'' 20'' 8'' 10'' '' '' ...
1
vote
0answers
29 views

how do I debug postgresql 9.1 server terminating

my /var/log/postgresql/postgresql-9.1-main.log shows 2014-03-25 10:39:30 CET HINT: In a moment you should be able to reconnect to the database and repeat your command. 2014-03-25 10:39:30 CET LOG: ...
1
vote
1answer
45 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
1
vote
0answers
46 views

Reset postgresql [closed]

I have trouble: I tried to restore PG backup from CentOS on my Ubuntu computer. But had a trouble: after placing backuped Centos /var/lib/pgsql/9.1/data/ to my /var/lib/postgresql/9.1/main at Ubuntu, ...
3
votes
1answer
46 views

restarting postgresql quickly

I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do ...
0
votes
1answer
50 views

Postgresql restore data from file [duplicate]

I have a tar archive with full backup of my CentOS filesystem with pg9.1 installed. How can I restore one database from it?
3
votes
1answer
42 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
0
votes
2answers
153 views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
2
votes
1answer
82 views

Debugging why postgres is snowballing with system cpu usage

I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database. ...
1
vote
1answer
41 views

PostgresSQL authentication error

I recently forgot my PostgreSQL 9.1 password on my windows 8 and it asks for password on pgAdmin III console. Can I retrieve the password or reset it?
5
votes
1answer
50 views

How to see amount of reusable space for a table

I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
0
votes
0answers
18 views

Changed password on Postgres but I still can log in with the old one [closed]

I changed my password via alter user, but can still log in with the old one. In the 9.1.6 version of postgresql I resolved this problem by killing all the processes through pg_terminate_backend, but ...
0
votes
1answer
114 views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
0
votes
1answer
84 views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
1
vote
0answers
23 views

Pg_restore table size differences

What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file? On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my ...
0
votes
2answers
147 views

Postgres client unable to detect UTF-8 database

Context I am deploying a Vagrant box for my development team, and have encountered a problem restoring UTF-8 collated dumps. I don't believe it is related to the now-infamous "UTF8" has no equivalent ...
0
votes
2answers
50 views

How to change table/indexes structure for a query with two aggregated colums

Do you have any suggestion on how to improve a table structure or its indexes in order to have a single query run faster? The table has this structure: docid | character varying(17) | ...
0
votes
1answer
42 views

Rotate Postgres log at 5GB

How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ? At the moment my log file is over 500GB. /var/lib/postgres/
1
vote
0answers
100 views

Correct pg_hba.conf to allow script to run locally with a specific db-user

I've got a working postgreSQL database with following pg_hba.conf: local all postgres peer local all all ...
3
votes
2answers
468 views

PostgreSQL: How to backup only One Schema from a database and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new ...
0
votes
2answers
107 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
2
votes
1answer
194 views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
0
votes
1answer
272 views

Howto disable Postgres listening on TCP?

Simply to avoid many problems in the first place I do not want my postgres server program to accept/listen to anything from any network (i.e. TCP/IP 4/6) connections. My setup is a Postgres 9.1 on ...
1
vote
0answers
50 views

using separate schemas or databases

For my web application with a postgresql backend I'm now looking into schema's. As I switched from MySQL to Postgresql(because of (amongst others) the proper geospatial extension) I was not aware of ...
1
vote
0answers
137 views

postgresql multi column unique or check constraint

First a word of pleading. To say I am a newbie to postgresql would be an insult to the newbies! I have been working on this for only a day or so. I have found several questions on this site, and ...
0
votes
0answers
88 views

Data migration of specific data from one DB to another

I am new to this site, I have one major question of data migration, I apologize before hand if the question is too silly. I have two data base on one server, I have one table with the same schema on ...
1
vote
1answer
43 views

Parsing the value and copying it to another column in postgresql

column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, ...
1
vote
1answer
218 views

Drop an index in postgresql

I had created an index wrongly and now im trying to drop that index. Since the datas are large for my table, dropping index is taking lot of time. Is there any other way to drop the index quickly? ...
0
votes
0answers
40 views

postgres-xc - ERROR: object already defined [closed]

I am configuring two data nodes and a coordinator on postgres-xc. I get eh following error: ERROR: PGXC Node datanode1: object already defined CREATE NODE pgxc_pool_reload ------------------ t (1 ...
2
votes
2answers
622 views

How to dump PostgreSQL database whose owner has no password?

First, some background on my setup: The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands. I created a ...
1
vote
1answer
108 views

showing locks from all postgres relations

I'm trying to show all locks (DBMS-wide) in Postgres. I initially tried adapting the documented example to: SELECT pg_database.datname AS database, relation::regclass, pg_locks.database AS db_oid, ...
1
vote
0answers
177 views

Postgresql Streaming Replication - pgpool2 - failover

In my scenario I want pgpool to forward read only queries to slaves when the master goes down - I want my app to be in the "read only mode". How do I setup pgpool to accept read only queries when ...
1
vote
2answers
303 views

Taking a backup of a streaming WAL slave

I have an environment with pair of postgresql servers set up to do WAL streaming replication. I also have a test environment and periodically I want to snapshot the production DBMS and restore that ...
0
votes
1answer
157 views

Help with Postgres 9.1 data import (is 4x expected data size, 4x slower than MySQL, and index still needed)

I have 25 billion <int, int, float> rows that I'm trying to import into Postgres, and after 77% of the data being imported, the Postgres data folder is taking up 840GB, about 4x the storage ...
1
vote
2answers
184 views

Vacuum vs Auto Vacuum

I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer. 1) Out of vacuum or Auto vacuum which is ...
0
votes
1answer
826 views

Create Language plperl - Error: could not load library plperl.dll

When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found. But in my computer, ...
0
votes
1answer
63 views

pg_timezone_names view is empty - can be recreated?

For some strange reason pg_timezone_names view is empty. Is there a way to recreate it?
-1
votes
2answers
254 views

PostgreSQL kill - Sighup pid [closed]

To reload the configuration files, we send the SIGHUP signal to the postmaster, which then passes that on to all connected backends. That's why some people call reloading the server ...
1
vote
0answers
325 views

Postgres 9.1.6 Error index contains unexpected zero page at block 0

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index ...
0
votes
0answers
15 views

Postgres 9.1.6 Error: index contains unexpected zero page at block 0 [duplicate]

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index ...
3
votes
1answer
356 views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...
1
vote
1answer
168 views

Have aggregate function group results in one row

I have a table that has the following columns: debit credit payment_method class Now I will perform this query: select sum(debit - credit), payment_method, class from my_table group by ...
2
votes
2answers
694 views

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
2
votes
1answer
768 views

Postgresql out of memory while trying to a request of ~500mb

I started getting out-of-memory errors on os postgresql-9.1 server running on a x64 Debian with 4GB of RAM, dedicated to postgresql. Top returns something like this, which seems that the problem is ...
4
votes
3answers
8k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
0
votes
1answer
35 views

Postgresql: No data left on drive, how can I wipe it and start over?

I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files. I would like to just ...