This tag is specifically for PostgreSQL version 9.1
0
votes
2answers
70 views
looping through a table type variable
I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.
CREATE OR REPLACE FUNCTION ...
0
votes
2answers
167 views
Interactive INSERT / UPDATE function to implement UPSERT
I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
5
votes
3answers
195 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
232 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
112 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
85 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
112 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
177 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 ...
2
votes
1answer
94 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
114 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 ...
3
votes
1answer
262 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
427 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 ...
5
votes
1answer
381 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 ...
3
votes
1answer
381 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
0answers
38 views
Slave as a master in PostgreSQL
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) ...
5
votes
1answer
210 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
277 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
105 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
241 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
73 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
160 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:
...
1
vote
1answer
173 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
261 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
130 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
103 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
234 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 ...
2
votes
1answer
2k 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
145 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 ...
3
votes
1answer
1k 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
74 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
101 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 ...
3
votes
3answers
260 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?
...
