This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms

6
votes
3answers
485 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 ...
1
vote
1answer
767 views

Postgresql nested row_to_json not working

This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like ...
0
votes
1answer
440 views

Postgres 9.1 query plan not using check_constraints on partitions after large data load

I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip ...
1
vote
1answer
152 views

PostgreSQL replication

I have two projects working on different databases. The problem is: I need to replicate changes in the databases only for a few tables. I need it to synchronize the data. Need an advice how can I ...
1
vote
1answer
216 views

How to setup a PostgreSQL cluster

Could you point me to a good book about setting up a PostgreSQL cluster? The goal is to set up an active-passive cluster spread across two different data centers to achieve high availability.
1
vote
1answer
2k views

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
3
votes
1answer
236 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 ...
2
votes
1answer
722 views

How to switch master server and warm standby (with streaming replication) in PostgreSQL?

I would like to know what is the way to switch the role of 2 servers when using warm standby? I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
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 ...
4
votes
2answers
2k views

Postgresql 9 speeding up indexed inserts (JPA)

I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
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 ...
5
votes
2answers
2k views

Replication has failed; how to get going on again?

I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of ...
1
vote
1answer
68 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) ...
7
votes
1answer
580 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 ...
2
votes
2answers
649 views

PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
5
votes
2answers
154 views

PostgreSQL: Query DB for rows containing integer[] > or < than specified value

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
1
vote
1answer
433 views

PostgreSQL - How can I manage WAL archiving for different databases on a server?

I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of ...
1
vote
1answer
292 views

Error creating index on PostgreSQL 9.1

I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process. Is it ...
2
votes
2answers
294 views

How can I 'fake' stats for generate_series data?

With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning: ...
3
votes
1answer
342 views

Users groups/permissions when working with PostgreSQL

While I've been working with PostgreSQL for approximately 1 year, I still feel like a bit of a Newbie. I feel like I'm constantly fighting with psql and pg_dump when it comes to files and ...
3
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
999 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 ...
2
votes
2answers
172 views

Which performance can I expect?

I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1. I'm ...
2
votes
2answers
464 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 ...
4
votes
1answer
7k views

PostgreSQL: changing password for a user is not working

I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres I do $ sudo -u postgres psql psql (9.1.5) Type "help" for help. postgres=# ALTER USER postgres WITH ...
2
votes
2answers
390 views

How do I save functions to individual files in PostgreSQL?

I maintain a legacy application that uses a PostgreSQL database. The application is heavily dependent on stored procedures (aka functions). I want to save these functions to files named after the ...
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 ...
1
vote
1answer
176 views

What is the purpose of the keyword “ID” in PostgreSQL?

I'm new to PostgreSQL and I ran into problems using the following query: SELECT * FROM table1 WHERE ID = 1 ERROR: column "id" does not exist LINE 2: WHERE ID = 1 where ID is a column I ...
1
vote
2answers
179 views

postgresql sticking “enable_seqscan=False” in a UDF sensible?

I have a query which is taking 40 seconds to execute over the 80ms that it should if postgres where to filter off the primary key rather than doing a sequential scan. I have to force postgres to ...
5
votes
3answers
1k views

Maximum number of databases for single instance of PostgreSQL 9

Developing a multicustomer application we plan to use a different database for each customer. But it could be more than 1000 customers (applications). Will PostgreSQL handle it without any problems? ...
4
votes
4answers
7k views

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...