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)

6
votes
4answers
2k views

Determine when a PostgreSQL database was last changed

I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed? Instead of using pg_dumpall, I'd ...
6
votes
2answers
105 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
6
votes
1answer
310 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 ...
6
votes
2answers
539 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 ...
6
votes
1answer
2k 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 ...
6
votes
2answers
3k 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 ...
6
votes
1answer
2k 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 ...
6
votes
5answers
6k views

SQL Query Formatter

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux ...
6
votes
3answers
1k views

Postgres, MVCC, and Locking

I have a series of SQL statements that look like the following: BEGIN; SELECT counter FROM table WHERE id=X FOR UPDATE; REALLY COMPLEX QUERY; UPDATE table SET counter=Y WHERE id=X; END; I'd like to ...
6
votes
1answer
466 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 ...
6
votes
2answers
2k 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}/ ...
6
votes
1answer
277 views

in postgres, can one optimize a table across partitions?

in a database with many companies, segregating them using schema and search_path almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting ...
6
votes
2answers
3k views

Non-unique multicolumn foreign key

I have a "comments" table that models a conversation on a topic, like this: id serial topic_id integer parent_comment_id integer body text So, every comment has a reference to its topic AND ...
6
votes
1answer
73 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
6
votes
1answer
235 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 ...
6
votes
1answer
220 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 ...
6
votes
3answers
297 views

Is using SUM() twice suboptimal?

I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise): SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id HAVING sum(hours) ...
6
votes
1answer
159 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
1answer
363 views

Storing logs on per day basis in PostgreSQL

Requirements here are to store historical logs. For the sake of simplicity I'll assume this sample scenario where we are selling some products on our website and we need to keep records of sales of ...
6
votes
2answers
148 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, ...
6
votes
2answers
312 views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
6
votes
1answer
129 views

Can I get the union of the a Postgres fulltext index?

I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible? To clarify: If I have the following table: create table ...
6
votes
0answers
212 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
6
votes
0answers
241 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
5
votes
4answers
2k views

Why are so many MPP solutions based on PostgreSQL instead of MySQL?

Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
5
votes
3answers
120 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, ...
5
votes
3answers
3k views

Postgres Write Performance on Intel S3700 SSD

I'm not seeing the Postgres write performance increases I thought I would with a single SSD vs a hardware RAID 10 array of (16) 15k RPM SAS drives. I have a Dell R820 with a PERC H700 hardware RAID ...
5
votes
2answers
533 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 ...
5
votes
5answers
4k 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 ...
5
votes
5answers
2k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
5
votes
3answers
852 views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
5
votes
4answers
8k views

Visualization of MySQL/PostgreSQL table structure as a graph/UML

Is there any program, source code or API that will take a MySQL or PostgreSQL schema and spit out a UML or some other graph visualizing of it?
5
votes
2answers
7k 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
2answers
4k views

Preventing PostgreSQL from starting on boot in Ubuntu

I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu). How can I avoid this? I need to start it manually, and I don't want it to start unattended because I need to ...
5
votes
2answers
576 views

Save postgres database from broken ubuntu 10.04 server

Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve). I can still access the filesystem. Is there a chance to transfer the database by only ...
5
votes
2answers
153 views

Indexing a large static dataset

My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is ...
5
votes
2answers
5k views

How to turn json array into postgres array?

I have a field user that holds a json document that roughly looks like that: { name: 'foo', tags: ['foo', 'bar'] } What I would like to do is to turn that tags array into a concatenated ...
5
votes
2answers
589 views

PostgreSQL: Executing DDL on every schema

I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my ...
5
votes
2answers
164 views

PostgreSQL: Query DB for rows containing integer[] > or < than specified value

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
5
votes
2answers
3k views

About “Transaction ID Wraparound”

Now , I read the document about "Transaction ID Wraparound " , but there are something that I really don't understand, the document is the following url ...
5
votes
1answer
4k views

disable constraints before using pg_restore.exe

When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same: ERROR: insert or update on table "someTable" violates foreign key constraint ...
5
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
1answer
3k views

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?
5
votes
4answers
2k views

Trouble setting up Postgres for the first time

I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself. Postgres is ...
5
votes
1answer
386 views

PostgreSQL PREPARE query with IN () parameters

I'm trying to prepare a query from PHP like: pg_prepare($con, "prep", "select * from test where tid in ($1)"); and then execute it with: $strpar = "3,4,6,8,10"; pg_execute($con, "prep", ...
5
votes
3answers
9k 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
1answer
471 views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
5
votes
3answers
836 views

“AT TIME ZONE” with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ...
5
votes
1answer
722 views

Check what event called the trigger on PostgreSQL?

I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE. Oracle triggers can check this in a simple 'IF' statement: IF INSERTING, IF UPDATING or even IF DELETING Is there a ...
5
votes
2answers
992 views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...