This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

0
votes
1answer
826 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
62 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 ...
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 ...
0
votes
2answers
117 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) | ...
1
vote
1answer
61 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 ...
0
votes
1answer
90 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/
2
votes
1answer
177 views

PostgreSQL's Query Hierarchical data poor performance

Badly need some technical assistance on this query performance issue I'm working on. Summary of the data structure is: 1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many ...
0
votes
2answers
146 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 ...
1
vote
0answers
386 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 ...
0
votes
1answer
897 views

FATAL: catalog is missing 1 attribute(s) for relid 2662

I have a server with PostgreSQL9.1-903 (Windows 7 Professional). After a power cut my database is inaccessible (I presume that the database is corrupted). The postgreSQL service starts but the ...
3
votes
3answers
7k 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 ...
2
votes
3answers
2k views

How can I create readonly user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I ...
0
votes
2answers
492 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
2k 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 ...
1
vote
1answer
937 views

Add a column contains a photo

I installed PostgreSQL 9.1 under Ubuntu 12.10 And now I have a table called property I want to add a field that can be used to store photo(link to the photo or import a photo into a column?) But I ...
0
votes
1answer
3k 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
66 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
343 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
298 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
149 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
2k 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
58 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
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 ...
1
vote
1answer
854 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
308 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
930 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
307 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
717 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
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
287 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
580 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 ...
3
votes
1answer
1k 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
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 ...
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
564 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 ...
4
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 ...
10
votes
3answers
70k 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
59 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 ...
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 ...
0
votes
1answer
295 views

pg_upgrade fails with lc_ctype cluster values do not match

I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X. When trying to upgrade the database I ...
3
votes
1answer
456 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
965 views

How to convert Postgres from 32 bit to 64 bit

I would like to convert from PG 32 bit to 64 bit. I am testing with pg_upgrade (in several scenarios) but I think it cannot do that. With pg_upgrade, I can upgrade PG version 8.5 to v.9 (both of ...
4
votes
2answers
24k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the Postgresql database on my Windows 7 professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
0
votes
1answer
93 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 ...
3
votes
1answer
5k views

delete rows in 3 tables with on delete cascade

For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk. Let two constraints on the t3 table, each one with "on delete cascade", if I do: delete ...
0
votes
1answer
888 views

Full text search on multiple tables in PostgreSQL

I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, ...). Is it possible to ...
1
vote
1answer
353 views

Keeping version history of functions in PostgreSQL

For many of my tables, I've added auditing triggers (based on this wiki page). It works very well and has helped me several times figure out who changed what in the system. We have a Python/Django ...
3
votes
3answers
3k views

Postgres connection access denied on IPv6 address

Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP ...