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)

5
votes
2answers
388 views

Best practices to run time offset delayed replication

I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 ...
5
votes
2answers
108 views

Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently: begin; create table foo(id) as values (1); select * from foo; /* id ---- 1 */ savepoint s; create function f() returns ...
5
votes
1answer
874 views

How can I request a flush of the postgresql transaction logs?

I have the following problem: a "vertical" Linux distribution (Sophos UMT) comes with PostgreSQL 9.2 to store its configuration. Unfortunately, since the last update, it seems that the transaction ...
5
votes
1answer
630 views

How can I correctly choose maximum number of occurrences of a string while grouping by another field?

I am using Postgresql 9.0. I have the following fields in a table: id, name. id name 1 John 1 Mary 1 Mary 1 Mary 1 John 1 Mary 3 Paul 3 Paul 3 George ...
5
votes
1answer
55 views

What happens in PostgreSQL checkpoint?

Here's part of my checkpoint log: 2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log ...
5
votes
1answer
110 views

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I ...
5
votes
1answer
50 views

How to see amount of reusable space for a table

I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
5
votes
1answer
536 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 ...
5
votes
1answer
91 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
2answers
8k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
5
votes
2answers
1k views

Problems connecting to Windows-based Postgresql

Other computers are having trouble even finding the running Postgresql 9.1 on my Windows 7 machine. Local connections work just fine of course. In my pg_hba.conf I have: #office subnets host all ...
5
votes
2answers
2k views

Can I have one table be an “alias”, or “symlink”, to another?

I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables. After a discussion with a colleague about the potential to ...
5
votes
1answer
1k views

Postgresql performed a “fast shutdown” by itself. Why? How was it able to do that?

I've already googled and manual read as much as I could before posting this here. Version info: Postgres8.4 on Ubuntu10.x I have a personal laptop that I run a postgres server on, and a Java script ...
5
votes
1answer
267 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
5
votes
1answer
557 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 ...
5
votes
1answer
1k views

PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

Our established replication has broken ("requested WAL segment has already been removed" during downtime) We cannot easily stop the master again. Can we do pg_start_backup(), rsync ${PGDATA}/ ...
5
votes
2answers
1k views

Advantages of using PgBouncer vs HAProxy?

Are there any advantages for using one over the other? I'm only looking at options to hide the actual IP address of the master and slave servers from my processes, and not having to restart them if ...
5
votes
1answer
975 views

Backup a database with a huge number of tables

Is there a way to backup a PostgreSQL database with a huge number of tables? An attempt with pg_dump for a database of about 28000 tables resulted in the following error message: pg_dump: WARNING: ...
5
votes
1answer
2k views

How to turn off header only in psql (postgresql)

I'm using PostgreSQL 9.1.X I am trying to build psql script to print results without a header but including a footer. http://www.postgresql.org/docs/9.1/static/app-psql.html From the document ...
5
votes
1answer
989 views

Load Balancing / MPP with Postgres 9.1

I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes). Since I'm ...
5
votes
1answer
74 views

Hierarchical structure with limited number of records per user

I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem. Any user can refer only 3 other users at max. So for ...
5
votes
1answer
225 views

PostgreSQL: DBLink weird permission/connection error

The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password). I came up with ...
5
votes
2answers
91 views

Inconsistency in repeatable read

http://www.postgresql.org/docs/9.2/static/transaction-iso.html The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, ...
5
votes
1answer
114 views

How to schedule PostgreSQL replication?

I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen. I'll be using postgres internal replication solution but as far as I ...
5
votes
1answer
289 views

How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?

Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling. What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, ...
5
votes
2answers
349 views

How to properly configure PostgreSQL RAM usage?

Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
5
votes
1answer
317 views

PostgreSQL: Recommendation size for shared_buffers

I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago. I am looking for help about the proper memory values for the configuration file. Currently, I ...
5
votes
3answers
307 views

How can I export a subset of tabledata from a prodution database into my local testing database?

We have a relatively big production postgres based db: ~20GB. The PostgreSQL database is hosted on heroku. I would like to copy a small subset of the table data to my local database so I can run ...
5
votes
3answers
833 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? ...
5
votes
1answer
445 views

Is there a repository for pgadmin plugins? [closed]

I've been using PostgreSQL with PGAdmin III for a while now and it's been bugging me that there is a plugins menu option that is empty. I've Googled some and found a plugin here and there but I wanted ...
5
votes
1answer
91 views

Can we use old primary to sync with standby after failover in PostgreSQL?

If my standby (postgres) is behind by few seconds before failover. What is the easiest way to sync back my old primary after failover in PostgreSQL. In Oracle we have the option of Reinstating a ...
5
votes
1answer
83 views

Is Postgres ignoring my function cost annotation?

Why does this: create or replace function dummy() returns double precision as $$ SELECT random() $$ LANGUAGE SQL COST 777; explain select dummy(); return this: Result (cost=0.00..0.01 rows=1 ...
5
votes
1answer
122 views

Improve SQL query

I'm looking for ideas how to speed-up my query which is based on two tables only. I've added indexes, run vacuum. Tables: CREATE TABLE vintage_unit ( id integer NOT NULL, vintage_unit_date date, ...
5
votes
1answer
407 views

TPS too high in PostgreSQL

I created a table: CREATE TABLE foo ( id integer primary key, bar integer NOT NULL ); and inserted one row: INSERT INTO foo (id, bar) VALUES (1, 1); and ran a pgbench of the following ...
5
votes
1answer
230 views

force PostgreSQL into recovery mode

I have a somewhat strange question. Is there a way to manually force PostgreSQL into recovery mode? I've not been able to find aything in the documentation. I've been working with a script ...
5
votes
1answer
1k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
5
votes
1answer
125 views

To Foreign Key or not to Foreign Key… (inet -> cidr)

Below is a very abridged version of a project I am working on. CREATE TABLE cidr_block ( id serial not null unique, block_def cidr primary key, dhcp_server_id int not null references ...
5
votes
1answer
276 views

Database Exceptions with Jetty 8.1.0 with BTM

We are running Jetty 8.1.0 with BTM 2.1.2. We are using Postgres as our DB. Everything works great except that when we run any query with a condition where sometable's primary=null, the server loses ...
5
votes
1answer
1k views

Postgres full text search with multiple columns, why concat in index and not at runtime?

I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns. The postgres docs talk about creating a ts_vector ...
5
votes
1answer
761 views

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
5
votes
2answers
72 views

Altering db schema in production environment

I have some questions on db schema changes in production environment without taking the database server and the webapp offline. Q1- I want to be able to make changes to database schema (adding, ...
5
votes
1answer
125 views

Rebuild a table from history records

A small disaster happened to me on my PostgreSQL 8.4 database. I follow visitors to my webpage in two tables: History +---------------+-------------+ | whereseenlast | varchar(50) | | uniqid ...
5
votes
1answer
153 views

Determining which isolation level is appropriate

This is a homework question. For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer. ...
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 ...
4
votes
2answers
1k 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 ...
4
votes
2answers
322 views

Custom unique column constraint, only enforced if one column has a specific value

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is "true", then ...
4
votes
5answers
2k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
4
votes
2answers
607 views

Pros and Cons of virtualisation technology for database-server and datastorage

Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm ...
4
votes
3answers
2k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
4
votes
3answers
2k views

PostgreSQL - Help Optimizing SQL performance - full text search

I have a big table ~15M records called entities. I want to find top 5 entities matching "hockey" in their name. I have a Full text index built for that, which is used : ...