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)

14
votes
1answer
11k views

Scalability limitations of PostgreSQL and MySQL

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB. I suspect that limits as such do exist as one would not come up with Netezza, ...
13
votes
2answers
13k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
11
votes
3answers
17k views

Automating failover in PostgreSQL 9.1

How does one setup two identical servers for automatic failover in PostgreSQL 9.1. OS Centos 5 PostgreSQL 9.1 compiled from source The postgres user account exists on both machines and has a ...
34
votes
3answers
19k views

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
24
votes
3answers
45k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
13
votes
5answers
14k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
22
votes
3answers
10k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
14
votes
3answers
37k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
11
votes
4answers
16k views

tuning postgresql for large amounts of ram

I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm ...
9
votes
1answer
3k 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 ...
7
votes
1answer
2k views

EC2 - How to correctly back up PostgreSQL data?

Here is the setup: 1 small Amazon Linux (EBS-backed) EC2 instance with 3 additional volumes. This is both a web server and database server. One volume for code, one for the PostgreSQL (8.4) data ...
15
votes
4answers
7k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
6
votes
3answers
17k views

How to drop all connections to a specific database in postgres without stopping the server?

I want to drop all connections (sessions) that are currently opened to a specific postgresql database but without restarting the server or disconnecting connections to other databases. How can I so ...
6
votes
2answers
9k views

How do I detach all other users from a postgres database?

I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive ...
5
votes
4answers
14k 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: ...
24
votes
3answers
5k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
13
votes
3answers
5k views

Set names to attributes when creating JSON with row_to_json

Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns? I can do row_to_json(customers) returning ...
12
votes
1answer
5k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
10
votes
1answer
2k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
7
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
6
votes
5answers
20k views

Why won't PostgreSQL 9.3 start on Ubuntu?

I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04. ...
5
votes
2answers
3k views

PostgreSQL maximize performance SSD

I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the ...
3
votes
2answers
5k views

PostgreSQL crash recovery

We are planning to use PostgreSQL in our application but are concerned about crash-safety and recovery. I can't find any database crash recovery method or process in PostgreSQL. I know there must be ...
13
votes
5answers
8k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
12
votes
2answers
954 views

Modelling constraints on subset aggregates?

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
10
votes
1answer
7k views

PostgreSQL Database Data File Integrity Check

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption? In the ...
7
votes
4answers
6k views

No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
5
votes
1answer
878 views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
5
votes
2answers
6k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
5
votes
2answers
2k views

Possible to keep max number of records in postgresql?

Basically part of our Postgresql table is used to keep server access logs, and as such sometimes during production this can get pretty large. is there any way of setting in postgresql to have a ...
5
votes
1answer
1k views

Ways to speed up IN queries under PostgreSQL

I have a query of the following form: SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000) The IN query has anywhere from 10 to 25000 values. The query runs for minutes ...
3
votes
1answer
2k views

Cannot get my slave - replication server to start

My replication server will not start. I have followed the instructions here: http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/ As well as several other ...
13
votes
2answers
5k views

How to do incremental backup every hour in Postgres?

Trying to do an hourly incremental backup of a single Postgres server (Win7 64). I have the following setup in postgresql.conf: max_wal_senders = 2 wal_level = archive archive_mode = on ...
12
votes
2answers
3k views

Streaming replication and failover on PostgreSQL

I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
11
votes
3answers
1k views

Indexes for SQL query with WHERE condition and GROUP BY

I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow. My query: SELECT group_id FROM counter WHERE ts between ...
11
votes
3answers
530 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
9
votes
1answer
3k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
9
votes
1answer
3k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
9
votes
3answers
558 views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
8
votes
2answers
162 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
7
votes
1answer
773 views

Equality query on NVARCHAR column yields multiple results in SQL Server 2012

I am in the process of moving a pet project from PostgreSQL (9.2.2) to SQL Server (2012 Standard). I've noticed an interesting phenomenon when querying unicode words. Given the definition: CREATE ...
7
votes
1answer
692 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
7
votes
5answers
15k views

PostgreSQL: The database cluster initialization failed

Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights. I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL Every way I ...
6
votes
2answers
3k views

Compare two similar Postgres databases for differences

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host. Is there a Postgres command or ...
6
votes
1answer
2k views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
6
votes
1answer
2k views

PostgreSQL: Unable to run repmgr cloned database

I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave. I am using the instructions on this guide: ...
5
votes
2answers
3k views

Running pg_dump on a hot standby server?

Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask. I would like to run a nightly backup job (via pg_dumpall) from a hot ...
4
votes
2answers
24k 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 ...
4
votes
3answers
6k 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
2k views

How do I know what indexes to create for a table

Is there a way I can figure out the best way to know which indexes to create for a table?