This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

3
votes
1answer
1k 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
vote
0answers
180 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. ...
0
votes
1answer
213 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'' '' '' ...
3
votes
1answer
80 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 ...
1
vote
0answers
231 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, ...
1
vote
1answer
70 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?
1
vote
2answers
642 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 ...
3
votes
1answer
182 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
0answers
32 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 ...
3
votes
1answer
1k 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
59 views

RIGHT() function in postgresql 9.1 not behaving as anticipated?

The following is with regard to PostgresQL 9.1 : I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing ...
5
votes
1answer
61 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?
1
vote
2answers
3k 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 ...
1
vote
0answers
60 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 ...
7
votes
1answer
1k views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
0
votes
2answers
116 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
86 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/
0
votes
2answers
140 views

Big table size in PostgreSQL while number of rows is small

Tables' size in the PostgreSQL DB are big while the number of rows is relatively small (less then 500K rows). Vacuum of the specific table did not solve the problem. Only by deleting and importing the ...
3
votes
1answer
612 views

Postgres backup and WAL to S3

We are looking for a solution for the following problem: We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage ...
1
vote
0answers
337 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 ...
2
votes
1answer
1k 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
2k 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
322 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 ...
1
vote
0answers
63 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 ...
0
votes
0answers
269 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
170 views

Write performance of Postgresql 9.1 with read-only slave

I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
1
vote
1answer
82 views

Slave as a master in PostgreSQL [closed]

I have set up a hot standby in PostgreSQL 9.0 on Windows. I want that, at the time of fail-over, the slave should take over the role of master and start performing operations (both read and write) ...
1
vote
1answer
115 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
1k 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? ...
1
vote
2answers
870 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
0answers
57 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 ...
0
votes
1answer
611 views

Truncate table is taking too long in PostgreSQL [closed]

I have many databases on the same server, all with same templates. When I execute truncate command on exceptions table in each database, it works fine and executes immediately but on database named ...
0
votes
1answer
90 views

Recovering Postgres database

OK. I'm in a bit of trouble. I've got an Ubuntu, had Postgres 9.1 installed. There was something wrong with it - I couldn't start it for some reason. Out of panic, I backed up ...
2
votes
2answers
5k 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 ...
2
votes
1answer
438 views

Cannot connect to server. Too many symbolic links. Postgres Error [closed]

I get this error when I try connecting to a PostgreSQL server: psql: could not connect to server: Too many levels of symbolic links Is the server running locally and accepting connections on ...
1
vote
1answer
765 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
302 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 ...
0
votes
1answer
300 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 ...
0
votes
1answer
2k 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
266 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?
2
votes
2answers
2k views

Select a range of integers

I have a 'range' of integers (in this case destination ports) that occur: For example: 33848-33254. I want to select 'all' of these without having to do one at a time. is there a range operator? Or ...
-1
votes
2answers
543 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 ...
0
votes
0answers
17 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 ...
7
votes
2answers
1k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
3
votes
1answer
1k 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
522 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 ...
3
votes
2answers
3k 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
2k 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 ...
0
votes
1answer
57 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 ...
1
vote
1answer
2k views

How to change the default delimiter in PostgreSQL 9.1.9 using SET?

From within a bash script I tried to use a replace and a detection on a field containing a pipe like: IF position ('|' in var) IS NOT NULL THEN ... ...REPLACE(field, '|', '#') The data itself was ...