This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms

4
votes
2answers
2k 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 ...
4
votes
1answer
5k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
7
votes
1answer
837 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 ...
6
votes
3answers
450 views

Casting issue when calling a function with composite type parameter

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. what could be the issue? CREATE OR REPLACE FUNCTION ...
4
votes
3answers
16k 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 ...
1
vote
1answer
715 views

Create log file only for my executed queries

I have been using the following to set up the log file: log_statement='all', log_collector='on', log_destination='on', log_filename='filename' After that, for testing I just executed a query ...
5
votes
1answer
2k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
3
votes
1answer
229 views

Postgres Backup queries on AWS after reading Postgres docs

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance. So there are 2 ways to do it. Create a dump using pg_dump. File level backup with ...
1
vote
0answers
73 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, ...
8
votes
1answer
1k views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
6
votes
3answers
5k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
4
votes
2answers
1k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
2
votes
2answers
7k 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 ...
2
votes
2answers
451 views

Is it a good idea to move high-volume and highly-accessed tables to a separate database?

Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
1
vote
1answer
234 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 ...
7
votes
2answers
2k 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 ...
3
votes
1answer
65 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. ...
1
vote
2answers
276 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 ...
1
vote
0answers
427 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 ...
1
vote
1answer
666 views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
1
vote
1answer
479 views

Create database on new partition

I use postgresql 9.1, on ubuntu 12.04. I had installed the depot package. I have added a new partition to my system, and I would like to create a postgresql database on this partition ( which will be ...
0
votes
2answers
148 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 ...