PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
2answers
324 views

Avoiding a sort on an already clustered index for group by

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid: select count(did), pid from T group by pid Here is the query plan: ...
1
vote
1answer
514 views

PostgreSQL: How to list all stored functions that access specific table

Introduction: PostgreSQL database with several hundred of stored functions, including obsolete, not used etc. Problem I need to find out all the stored functions that have any relationship to the ...
1
vote
1answer
56 views

Try to define appropiate memory size for this postgres database

I have a site with around 150.000 daily page views using a 180Mb postgres database size running on a 800 mb ram db on Heroku. This site does not do many updates, queries are mostly select that are ...
0
votes
2answers
113 views

Recommendation for RDBMS for large table [closed]

I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well). The ...
-2
votes
1answer
118 views

Query sql-server db and insert results into PostgreSQL

I have a query that returns data from sql-server and I need to be able to put the results into PostgreSQL. I'm using C# for the task.
3
votes
3answers
567 views

Select rows, where 3 columns have the same values

I have a structure like this: col0 col1 col2 col3 1 1 1 1 1 1 1 2 1 1 1 3 1 2 1 1 1 2 3 1 2 3 4 5 2 3 4 9 I now want to select ...
2
votes
3answers
196 views

Monthly backup of SQL server DB to PostgreSQL?

The company I'm working for has a SQL Server with read-only access. They use Crystal Reports hooked up to PostgreSQL for reporting. Is there any way to make it so I can move all the data from the ...
3
votes
1answer
351 views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...
1
vote
1answer
169 views

Do I need to perform any tasks after Postgres goes into recovery mode?

For starters, I do not have any sort of automated failover in place. After two scenarios, I'm unsure of the state of the database and any required actions, if any, to take: My master server ...
0
votes
1answer
564 views

pgsql slow query on table of 500 million rows

I have a table that keeps measurements of latencies between nodes running MPI tasks in a large cluster. The table looks like this: CREATE TABLE latency( from_rank int, to_rank int, from_host ...
2
votes
2answers
108 views

Scaling beyond 16 concurrent connetions

I've just adopted a Postgres 9.0 database running on RHEL 6.1 on a rather beefy 256 core, 2TB system with ok drive (Hardware RAID 50). I've started benchmarking the system using pgbench so I can get ...
2
votes
1answer
325 views

I need help understanding Postgres's archive cleanup functionality

I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform ...
4
votes
1answer
319 views

PostgreSQL strategies to deal with disk filling up

I'm using PostgreSQL (8.4) to store data produced by an application making frequent inserts (in the table structure described below). The database keeps growing with time and, since the newer data is ...
1
vote
1answer
168 views

Have aggregate function group results in one row

I have a table that has the following columns: debit credit payment_method class Now I will perform this query: select sum(debit - credit), payment_method, class from my_table group by ...
3
votes
1answer
284 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 ...
1
vote
1answer
556 views

Connecting to postgres from windows to virtual machine

I have the following setup Windows 8 Oracle VM virtual box with arch Linux running on it Installed and configured PostgreSQL on Linux Setup the ports in virtual box so that 45432 port on windows ...
1
vote
0answers
174 views

How to configure pgpool for reasonable speed

I have a problem with pgpool II configured in master-slave mode with replication on slony. I have configured postgres databases on each server to run as fast as they can and this was verified by me ...
2
votes
2answers
679 views

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
1
vote
2answers
69 views

Constraint on Composite Type

How can I create a constraint on a sub-field of composite type? Pseudocode create type axis( major_axis float, minor_axis float, angle float constraint angle_constraint check(angle ...
4
votes
1answer
460 views

Transpose two-dimensional array from n rows to 2 columns

Background Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available. Problem Looking to simplify the code that pivots a two-dimensional array. Code A working, overly-verbose example ...
1
vote
1answer
109 views

Index method for very few updates and many inserts

I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches. I will use pg_trgm but ...
2
votes
1answer
755 views

Postgresql out of memory while trying to a request of ~500mb

I started getting out-of-memory errors on os postgresql-9.1 server running on a x64 Debian with 4GB of RAM, dedicated to postgresql. Top returns something like this, which seems that the problem is ...
3
votes
1answer
287 views

Group By primary key or DISTINCT increase query time over 1000x with limit

Also see http://stackoverflow.com/questions/17741167/hibernate-jpa-improve-performance-of-distinct-query but I realized this is mainly a PostgreSQL issue. My application uses a 3rd party extension to ...
0
votes
1answer
142 views

How to real-time sync local Postgres instance to Cloud Postgres

I have a local postgres database (9.2) running on Ubuntu that is constantly being written to by an application capturing sub-second data. (99.9% of the transactions are inserts). I also have a ...
1
vote
2answers
81 views

Using an index for both uniqueness and fast lookup?

Right now I have a postgresql 8.4 database set up for players in a multiplayer game. I want the username column to be unique. I also want to be able to lookup a player by username quickly. Here's the ...
2
votes
1answer
65 views

Why does PostgreSQL require its own user/group for installation?

When installing PostgreSQL via MacPorts (and I assume via other means too), a postgres:postgres user/group is created. Are there any reasons why this user/group is needed?
0
votes
1answer
71 views

PostgreSQL - Date added row metadata

Is there a means of differentiating rows added after a certain date without explicitly storing that information as a column item? I working with a database that is constantly being filled with ...
4
votes
3answers
8k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
3
votes
2answers
947 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 ...
-1
votes
0answers
105 views

Postgres database locked up [closed]

I was running a process to update a bunch of rows in postgres database and it locked up. Now I can't select anything out of it. None of my processes are "idle in transaction" when I execute ps ...
3
votes
1answer
131 views

Inconsistent query execution times using trigram indexes?

I have a table residences... I installed trgm: create extension pg_trgm; After adding trigram indexes to both grp and name columns using: CREATE INDEX residences_name_trgm ON residences USING GIN ...
2
votes
1answer
153 views

SPI postgresql - insert data into table from postgres.c

I'm working inside postgres.c (inside exexc_simple_query) of postgresql 9.1 (line 1094 of http://doxygen.postgresql.org/postgres_8c_source.html#l00829 ). I need to insert some query information in a ...
4
votes
1answer
514 views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
4
votes
1answer
136 views

Optimizing PostgreSQL for transient data

I have several tables with 100-300 columns of integer types each, that hold highly volatile data. The datasets are keyed by one or two primary keys, and when refresh occurs, the whole dataset is ...
1
vote
1answer
78 views

Dump PostgreSQL without DROP DATABASE

I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file). Actually I use this command: pg_dump --format=c --no-privileges --no-owner ...
2
votes
4answers
174 views

PostgreSQL : Restrict (another) superuser from accessing database

I'm new in PostgreSQL and looking to find if this is possible. There are two superusers, 'postgres' (which is default superuser from PostgreSQL) and 'super2'. Superuser 'super2' creates a new ...
4
votes
2answers
474 views

Have Postgresql accept 1 and 0 as true and false for boolean

I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to ...
1
vote
1answer
225 views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My database ...
1
vote
1answer
76 views

Postgres WAL logs— point in time recovery with (time) gaps in the logs?

I'm setting up continuous archiving/ point in time recovery on Postgres and I've noticed some odd behavior. Here's the sequence of events: Save a base backup Archive WAL logs during normal DB ...
0
votes
1answer
35 views

Postgresql: No data left on drive, how can I wipe it and start over?

I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files. I would like to just ...
7
votes
2answers
1k views

How to do incremental/differential backup every hour in Postgres 9.1?

Trying to do an hourly hot incremental backup of a single postgres server. I have the following setup in postgresql.conf: max_wal_senders=2 wal_level=archive archive_mode=on archive_command='copy ...
4
votes
1answer
610 views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
-1
votes
2answers
92 views

Find records in same table with different company id but some other same values [closed]

I have subscriptions table that looks roughly: table subscriptions id (auto generated id) company_id city state subscription_date I would like to find records that: A) are of different company ...
3
votes
1answer
85 views

Streaming replication setup, sane setup?

Not being a DBA as main profession, I would like to know if the following assumptions are true. I have been searching the net for days and I think I have understand it correctly, but I would like to ...
2
votes
2answers
930 views

TOAST Table Growth Out of Control - FULLVAC Does Nothing

Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features and be in line with 30ish other PGSQL servers. This was done by a separate IT group who ...
1
vote
2answers
306 views

Bulk update of all columns in postgresql

I am wondering if postgresql has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
-1
votes
1answer
73 views

PostgreSQL select optimization

Can you please explain, how I can optimize select like this: SELECT * FROM calls WHERE id_temp % 5 = 0 LIMIT 300000 This select is working on table with 50 millions of records. Thanks a lot and ...
0
votes
1answer
120 views

Get old database off external hard drive

My hard drive crashed yesterday, luckily I had a back up. I didn't restore from backup though cause I needed a fresh system anyways. However there was a development database I would like back. Is ...
0
votes
3answers
314 views

What are the valid formats of a PostgreSQL schema name?

I can't seem to find documentation that describes the valid formats of a PostgreSQL schema name. I know that a schema name cannot: start with a number have spaces start with pg_ What else? Where ...
-1
votes
0answers
434 views

Can't connect to my Postgres databases [closed]

*** THIRD AND LAST UPDATE *** Excuse me very much, the error was highly stupid: I modified start.conf and changed it to manual. I thought that it was related to the start of the service at init, ...