PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

2
votes
1answer
90 views

What is the equivalent of a “Certificate of Destruction” when using a cloud-hosted database?

We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
4
votes
3answers
11k views

PostgreSQL user can not connect to server after changing password

I've met this with 4 roles I created: After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more. pgAdmin III show error message: An error has occurred: ...
2
votes
0answers
543 views

Can PostgreSQL's COPY create CSV with Locale-specific number formatting?

I want to create a CSV with PostgreSQL 9.1 COPY that directly opens correctly with a double-click in crazy German Excel 2010. Using ";"-Delimiter is easy. The encoding is solved, but Excel only ...
4
votes
1answer
208 views

Materialized View PostgreSQL

Is there any possibility of having built-in materialized views in PostgreSQL ? I don't want Gardner version or somehthing like physical table populated by triggers. I've read that was/is something ...
3
votes
2answers
363 views

Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost ...
0
votes
1answer
643 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
1
vote
1answer
2k views

Tuning Postgres config for new Database Server

I have finally gotten a Purpose Built Database machine for a project i'm working on. The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives. I have just got the database moved ...
5
votes
1answer
1k views

How to improve performance on PostgreSQL when using multiple concurrent processes?

Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
0
votes
0answers
618 views

Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs

I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs. All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
4
votes
1answer
5k views

How many queries per second is my Postgres executing?

How can I tell how many queries per second my Postgres database is executing?
5
votes
1answer
684 views

PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
0
votes
2answers
279 views

Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12

At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2? I checked their website and to my understanding it is not possible... I hope that's not the case. Can anyone ...
-3
votes
2answers
890 views

Versioning data in Databases [closed]

Is there any ready mechanism in Oracle, SQL Server, PostgreSql or OR framework (C#), which can version information in a database? By versioning informartion I mean that e.g. I have table Person (id, ...
3
votes
1answer
3k views

How to view the current settings of Autovacuum in Postgres?

I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration. Postgres 9.1 is the version I'm most interested in.
0
votes
1answer
959 views

Difference between return next and return record

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
2
votes
1answer
892 views

returning set of records in stored procedure

I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures: create or ...
1
vote
0answers
472 views

How to calculate running total based on dynamic values?

I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
2
votes
1answer
5k views

Declare variable of table type in PL/pgSQL

I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like: q1 = select * from foo; q2 = select * from bar; ...
0
votes
0answers
1k views

Postgres error ( XX000: invalid memory alloc request size 4294967290 )

I'm getting the following error in my postgres 8.4 database: XX000: invalid memory alloc request size 4294967290 Operating System : Windows 7 64 bit Memory : 8GB Database Version: Postgres 8.4 ...
2
votes
0answers
1k views

“custom archiver out of memory” error when restoring large DB using pg_restore

I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
2
votes
2answers
439 views

String handling in postgres 8.4

This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist). I thought I ...
1
vote
1answer
2k views

postgres GROUP BY and ORDER BY problem

I have two tables like this: CREATE TABLE cmap5 ( name VARCHAR(2000), lexemes TSQUERY ); and CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn ( cid int NOT NULL ...
2
votes
3answers
253 views

PostgreSQL 7.4 data corruption

We're dealing with the following situation (PG version is 7.4.30): somehow, a DB has gone missing from pg_database (psql \l listing doesn't show it anymore). More, while you can still \c to it, a \d ...
1
vote
2answers
162 views

Dumping and restoring on db server - postgres

Is there a way to execute a backup and restore of PostgreSQL DB from App server without calling pg_dump from App server and executing pg_restore also on App server. In this way I'm executing ...
20
votes
2answers
13k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
1
vote
1answer
1k views

How to get CPU usage of PostgreSQL server?

I'm reading about PostgreSQL monitoring tool. I get a lot of information like database size etc. but can't find info about CPU and memory usage. Are these informations provided by PostgreSQL server or ...
6
votes
3answers
2k views

How to pass a table type with an array field to a function in postgresql

i have a table called book CREATE TABLE book ( id smallint NOT NULL DEFAULT 0, bname text, btype text, bprices numeric(11,2)[], CONSTRAINT key PRIMARY KEY (id ) ) and a ...
1
vote
1answer
155 views

Single column join vs multicolumn join

In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected. In one scheme a surrogate key is used which means that in the join condition ...
0
votes
1answer
560 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 ...
1
vote
3answers
1k views

psql asks for password, CREATE USER didn't specify one

I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER ...
1
vote
2answers
2k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
5
votes
1answer
110 views

How to tell if database tuning is fruitful (PostgreSQL)

As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the ...
5
votes
3answers
126 views

Filtering data that could have more than one category

I have a table like the following: Annotation ( document, term, category ) Where document and term are some ID, while category is an integer. The couple document - term is not unique, ...
1
vote
2answers
57 views

Postgresql: type_modifier in UDT

In the manual for creating UDTs in postgresql I found the following statement: PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this ...
1
vote
1answer
608 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
0
votes
1answer
72 views

Faster joining on 'most recent' related rows

I have a bug tracking application. The bug table stores very little, because every state change to a bug happens by adding a comment. For example, the current state of a bug is determined by the state ...
7
votes
2answers
1k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type ...
1
vote
2answers
235 views

Slow access to table in postgresql despite vacuum

I'm running PG 8.4 I had a table with about 20K records. Any query accessing this table with more than 1 record (e.g. joins) would be very very slow. Even a count would take like 20 seconds. Stats ...
4
votes
1answer
2k views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
1
vote
1answer
167 views

PostgreSQL Hot Standby

I have two PostgreSQL servers running on my local Ubuntu machine (port 5437 is master, port 5438 is standby). While following the tutorial on http://wiki.postgresql.org/wiki/Hot_Standby I get this ...
3
votes
2answers
744 views

Why did PostgreSQL choose an elephant as it's logo?

Why did PostgreSQL choose an elephant as it's logo? It wasn't clear from logo wiki. UPDATE: Now wiki updated :) Thanks!
3
votes
2answers
581 views

Problems executing pg_ctl during pg_upgrade from 9.0 to 9.1 on Windows

I'm trying to upgrade my 9.0 PSQL to 9.1 on Windows Server 2003. I've installed 9.1, stopped both services, and generally followed all steps on this guide. When I actually run the command, however, I ...
2
votes
3answers
5k views

Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?

I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default ...
4
votes
2answers
5k views

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of ...
4
votes
1answer
687 views

Database encryption and key management with pg_crypto

I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database. The reason is that local logins will sometimes be required to ...
2
votes
1answer
127 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
1
vote
1answer
119 views

Does PostgreSQL optimize queries in transaction?

In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
-1
votes
1answer
269 views

Undefined reference to my function [closed]

I'm working on a project on postgresql 8.4 server side. I'm adding some functionality in order to store some query informations. I need to call two functions (start_create_profile() and check()) ...
1
vote
3answers
349 views

Altering same table across multiple databases

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases. I am not sure I know what the right procedure for this ...
2
votes
2answers
3k views

issues with encoding and pg_dump/restore between windows and linux

I have a dmp file I have created using pg_dump encoded in WIN1252. What I would like to do is import it using pg_restore to a database on a Linux machine. The problem is that on the windows machine ...