This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

0
votes
2answers
218 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
814 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
1k 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
229 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
58 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
143 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
139 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
74 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
59 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
632 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
618 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
50 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
477 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
71 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
3k 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
339 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
378 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
243 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
245 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
1k 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
162 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
1k 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
405 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
16 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 ...
2
votes
0answers
653 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 ...
7
votes
2answers
891 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
683 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
365 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
2k 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 ...
7
votes
2answers
3k views

How to do incremental/differential backup every hour in Postgres 9.1?

Trying to do an hourly hot incremental backup of a single postgres server. I have the following setup in postgresql.conf: max_wal_senders=2 wal_level=archive archive_mode=on archive_command='copy ...
0
votes
1answer
47 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
1k 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 ...
0
votes
1answer
620 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 ...
0
votes
1answer
169 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
2answers
13k 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
597 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 ...
0
votes
1answer
459 views

createdb command error : could not connect to database [closed]

I am using the following command for creating database in postgresql. createdb -D pg_default -E UTF8 -h localhost -p 5432 -U pramil -W pramil mydb but this command results prompt for password.When ...
3
votes
1answer
2k 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 ...
1
vote
1answer
207 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
2answers
3k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
3
votes
2answers
691 views

PostgreSQL: Can the archive command for master and standby point to the same directory?

I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server. The only difference in the files is the ...
2
votes
3answers
2k 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 ...
0
votes
1answer
97 views

PostgreSQL insert into table (not origin) based on a condition on fields on different tables

Lets say you have 4 different tables: table_1 is a table holding the original data: table_1 id1 | id2 | score1 | score2 | name_1 | name_2 ...
0
votes
2answers
525 views

Postgres wont shutdown due to wal archiving

I commanded Postgres to shutdown using the init.d scripts (Linux) over 18h ago. I can still see the processes running: -bash-3.2$ ps -fe | grep postg postgres 2299 3265 0 16:06 pts/5 00:00:00 ...
7
votes
1answer
1k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
3
votes
2answers
448 views

Change built-in default privileges in PostgreSQL?

Introduction. When I create a database, postgres=# CREATE DATABASE test2 OWNER test2; it is created with an empty privileges column: Name | Owner | Encoding | Collate | Ctype | ...
-2
votes
1answer
405 views

Use select query in stored procedure

I have some questions about stored procedures. Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast or if ...
1
vote
0answers
54 views

How to make continues cluster?

I have a report table with the following index: providerid, date The table is around 30M records and it grows about 100K rows per day. I want to use in the index above as a cluster, but as I ...
3
votes
0answers
134 views

What happen when Postgresql tablespace is null?

I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution ...