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)

3
votes
1answer
478 views

Variable substitution in psql when using \copy

I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that: The ...
3
votes
1answer
581 views

Can't understand results of pg_stat_statements view

I configured my postgresql.conf (my version is 9.1) with the normal persistent configuration: shared_preload_libraries = 'pg_stat_statements,plperl' custom_variable_classes = ...
3
votes
1answer
528 views

Execution plan in simple query in PgSQL depends on offset clause, why?

Update on 2013-01-16 11:08: see below I am working on PostgreSQL 8.4.14 with the following table fed with 53k lines: CREATE TABLE botany.taxon_data_cache ( taxon_id int NOT NULL, ...
3
votes
1answer
866 views

Distinguish between schema and database

I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code: DECLARE v_tmp_host scan.location.alias%TYPE; v_ip_address character varying; BEGIN ...
3
votes
2answers
78 views

Looking for a possible multi-column index

I have a table highscore containing the columns: game (text) date (timestamp) score (integer) more irrelevant ones... The query most often run on it is: SELECT * FROM highscore WHERE game = ...
3
votes
1answer
292 views

Postgres Plus Cloud Database on AWS for write heavy social game?

I'm one of four founders of a big social gaming iPhone app in Sweden. We now suffer from severe database server issues so I'm turning to you guys to get some help. First of all, let me get this out ...
3
votes
1answer
662 views

High availability in postgresql

We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as ...
3
votes
1answer
2k views

How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)?

I have a Postgresql database that need to read data from two SQL Servers (one SQL Server 2000 and one SQL Server 2008). I want to setup two dblinks to the SQL Servers. After googling, foruming, ...
3
votes
1answer
2k views

Postgres: Server does not listen even listen_addresses is set to '*' in postgresql.conf

I've set listen_addresses to '*' in postgresql.conf and stopped/started Postgres to "reload" configs. Even then, psql SHOW listen_addresses; is showing localhost. I'm sure I've edited and saved the ...
3
votes
1answer
561 views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
3
votes
1answer
3k views

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling?

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features. I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in ...
3
votes
1answer
2k views

Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?

TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why? Context: We run postgresql 8.4, on CentOS 6, ...
3
votes
2answers
4k views

Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)

Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ?? Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
3
votes
1answer
335 views

PostgreSQL memory spike upgrade from 8.2 to 9.1

We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers. Everything seems well with the restore of the databases, ...
3
votes
1answer
379 views

Oracle to PostgreSQL

I'm having som test databases running Oracle 10 & 11 that I plan to try to migrate to PostgreSQL. Some googling hinted that the perl script "ora2pg" could be of use, and free of charge. However, ...
3
votes
3answers
740 views

How do I design a database for continents, countries, regions, cities and POIs?

I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, ...
3
votes
1answer
201 views

PostgreSQL synchronized commit performance

I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can ...
3
votes
1answer
1k views

PostgreSQL 9 'pgstats wait timeout' problem

I'm asking about this here because in the PostgreSQL mailing lists no one answered me. In one of my servers (Windows 2008 R2 Standard/PostgreSQL 9.0.5 64 bits) the logs are being flooded with the ...
3
votes
1answer
1k views

Difference between table fillfactor and index fillfactor

In Postgres, one can set fillfactor for an index as well as a table. What is the difference? How to decide values for either. What are the usecases? I am trying to cluster a spatial relation on a ...
3
votes
1answer
575 views

PostgreSQL WAL Archiving: Does having archive_mode set to 'on' without copying logs cause performance issues?

Using PostgreSQL 8.4/CentOS 6: if I set the archive_mode parameter to on in postgresql.conf, but don't actually store the generated WAL files anywhere via the archive_command parameter, is there any ...
3
votes
1answer
1k views

Working across multiple PostgreSQL database servers

So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but ...
3
votes
1answer
2k views

Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1

I had a crashed opensuse 11.3 that's why I installed opensuse 12.1.. after I installed (via yast) and edited /etc/passwd I do this: rcpostgresql start and this shows on the /var/log/messeges ~ ...
3
votes
1answer
1k views

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

For a new SASS app with postgres 9, that will support disconnected clients. The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other ...
3
votes
2answers
635 views

How do I configure PostgreSQL 8.3 to log bind parameters?

I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system: exception 'UniqueViolation' with message ...
3
votes
3answers
674 views

ts_rank massively slows my query, how can I improve performance?

Here's the table definition Table "public.kb_article_contents" Column | Type | Modifiers ------------+----------+----------- article_id | smallint | not null contents | text | not ...
3
votes
0answers
78 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
3
votes
0answers
76 views

Recovery when failover

1/ Description: I have 2 Centos machine: master, slave (streaming replication). Both of them are built on PostgreSQL 9.3 (image below) and configured failover event (failover PG 9.3 document). Last ...
3
votes
1answer
190 views

Monitoring Amazon RDS with pgtop

I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics ...
3
votes
1answer
60 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ...
3
votes
1answer
93 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
3
votes
0answers
84 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
3
votes
1answer
48 views

Checking for multiple identical values in a Posgresql array

I have a simple table in Postgresql: CREATE TABLE data (id integer, values integer[]); INSERT INTO data VALUES (1, '{1,2,3,4,5}'); INSERT INTO data VALUES (2, '{1,1,2,3,4,5}'); INSERT INTO data ...
3
votes
1answer
87 views

PostgreSQL server problem in Windows XP

2014-10-01 09:22:49 IST LOG: database system was interrupted; last known up at 2014-09-30 18:30:24 IST 2014-10-01 09:22:49 IST FATAL: the database system is starting up 2014-10-01 09:22:49 IST LOG: ...
3
votes
0answers
129 views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
3
votes
0answers
71 views

Postgres multidisk master replicating to single disk slave

I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
3
votes
0answers
1k views

psql: FATAL: sorry, too many clients already

I am suddenly getting this error when either trying to access the website that uses the postgresql database, or even when using the psql utility or pgadmin3. My database is set to handle 150 maximum ...
3
votes
0answers
275 views

PostgreSQL custom operator for equality

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- ...
3
votes
1answer
53 views

Problems while building plpgsql_check on Windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, ...
3
votes
1answer
616 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
3
votes
0answers
120 views

Is PostgreSQL or ElasticSearch/Solr the right tool for this kind of query? [closed]

Here is an example of the current PostgreSQL query: SELECT a.*, a.al_location <-> '(someLon, someLat)' AS d FROM al_app.al_activity AS a WHERE to_tsvector('simple', a.al_keywords) @@ ...
3
votes
0answers
307 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
3
votes
0answers
748 views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
3
votes
1answer
1k views

PostgreSQL: FATAL: database locale is incompatible with operating system [closed]

I'd like to log in to PostgreSQL in my server to change my root (postgres) password . Debian GNU/Linux 7.1 wheezy PostgreSQL 9.1 After changing below: $ sudo vi ...
3
votes
0answers
268 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
3
votes
0answers
281 views

Stubborn autovacuum process slow things dows

We are facing a strange problem since yesterday. In a database of ours there is a partitioned table containing historical data. That is, every week's data goes to a different partition and stays ...
3
votes
1answer
3k views

Moving postgresql data directory

I moved postgresql's data directory by following the following steps: Stop postgres cp -a source_data_directory destination_data_directory export PGDATA=destination_data_directory Changing data ...
3
votes
0answers
140 views

What happen when Postgresql tablespace is null?

I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution ...
3
votes
2answers
438 views

General tool to load dump files [closed]

I am a big fan of Postgres both for its price but also for its features. I am going to have to need to upload into it both Oracle dump and SQL Server files. I will try to ask and beg for plain .csv ...
3
votes
0answers
255 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
3
votes
2answers
341 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 ...