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
232 views

Problem with PostgreSQL and the geqo optimizer

I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types. These are the variables I use for geqo: SET geqo = true; ...
2
votes
1answer
1k views

postgresql replication - pg_stat_replication is showing empty columns

I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list. $ ps aux ...
0
votes
1answer
3k views

Error: Unable to write inside TEMP environment variable path

I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1. I get the following, rather famous it seems, error: There has been an error. Unable to write inside TEMP ...
2
votes
3answers
315 views

Oracle Equivalent of PostgreSQL CLUSTER

PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves ...
0
votes
1answer
202 views

Partitioning table with billion row

I have a big table referenced by other tables and need to split the table on several parts. I create table by CREATE TABLE product_part0 () INHERITS (product); Move some rows to product_part0 by ...
3
votes
0answers
287 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 ...
2
votes
2answers
2k views

pg_upgrade unrecognized configuration parameter “unix_socket_directory”

I'm trying to upgrade Postgresql from 9.2 to 9.3 in Fedora 18 using this command as the postgres user $ pg_upgrade -b /bin -B /usr/pgsql-9.3/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.3/data/ -j ...
2
votes
1answer
107 views

getting the output of delete statement in UDF in postgres

In Postgres in a user defined function I do execute format('delete from table where session_id = %L', id); ran without execute postgres (may) return(s) the following: DELETE 0 Is there any way I ...
0
votes
1answer
89 views

How to store values of all columns in an index in PostgreSQL?

As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes indexes do not store the entire row data themselves. Is it possible to store the whole row ...
1
vote
1answer
69 views

Parsing the value and copying it to another column in postgresql

column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, ...
11
votes
3answers
6k views

How to optimization database for heavy I/O from updates (software and hardware)

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some ...
0
votes
1answer
218 views

Measuring SQL execution time in PostgreSQL?

MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link). I was ...
1
vote
2answers
622 views

Get time format in PostgreSQL

If I want get Ubuntu system time, I have two options: $ cat /etc/timezone US/Eastern $ date Sun Sep 15 14:45:02 EDT 2013 How can I find out if PostgreSQL is using utc or a utc offset, such as ...
1
vote
1answer
89 views

How do I query a table with many trees of data, for one tree?

I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql? The table looks something like this, =========NAICS_2012========= naics | ...
1
vote
1answer
877 views

Drop an index in postgresql

I had created an index wrongly and now im trying to drop that index. Since the datas are large for my table, dropping index is taking lot of time. Is there any other way to drop the index quickly? ...
1
vote
1answer
2k views

Duplicate key violates unique constraint

When I use this query everything is fine: UPDATE users SET f_name='Mike', l_name='MyLastName', username='blabla', "password"='asdfsdaf', "class"=12, ...
8
votes
1answer
2k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
1
vote
1answer
78 views

Text-based interface for PostgreSQL?

I know that psql is an option, but I'm looking for an easier text-based interface for PostgreSQL. Something that's similar to what 'tig' is to 'git', or 'phpmyadmin' is to 'mysql' (but using the ...
2
votes
1answer
648 views

Finding by ID in postgresql very slow on big table

I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is ...
0
votes
2answers
790 views

Recursive update for tree structure in PostgreSQL

I have the following structure of table. id chNum parentid --- ------ --------- 1 1 NULL 2 1.1 1 3 1.1.1 2 4 2 ...
2
votes
3answers
157 views

In what order does postgresql handle queries which have conflicting locks?

Let's say that I have a long running query SELECT and this query holds a AccessShareLock on a table. While this query is being processed a new query which needs a AccessExclusiveLock on the table ...
2
votes
0answers
240 views

Getting multiple queries with “show transaction isolation level” in pg_activity

I am using PostgreSQL server for my production use. When I fire a query like select * from pg_stat_activity on my server, I am getting 98% of queries like SHOW TRANSACTION ISOLATION LEVEL and ...
4
votes
2answers
4k views

Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)

First, I'm not a DBA, so pardon me if any of this question seems "off." I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making. ...
22
votes
6answers
24k views

How do I efficiently get “the most recent corresponding row”?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
1
vote
1answer
1k views

PostgreSQL transaction locked database table: “idle in transaction”

I have a web application interacting with PostgreSQL (v8.4 & Centos Linux) which suddenly started locking some of the database's tables. Still have no idea what happened, since the code is not ...
1
vote
2answers
827 views

Assign values with the := operator

When I assign a variable with result := title || '', by '' || author; it is taking more time (approx. 15 sec) to run the function. However, when I assign the variable with result = title || '', by ...
0
votes
1answer
416 views

How to install UTF8 locale on PostgreSQL?

I have a PostgreSQL 8.4 on Windows 7 machine, and my issue is that I have only the win1251 locale. So when I create a database, I choose the UTF8 encoding and can select only lc_collate = ...
0
votes
0answers
223 views

Create a high availability cluster configuration PostgreSQL for Windows

I want a server to be replicated, here is the scenario: When my primary server goes down or fail I want the secondary or the standby server to take over, I'm using Windows 7 for the primary server, ...
2
votes
2answers
1k views

Postgres corrupted DB recovery

Is there a way to recover data from partially damaged Postgres DB? My HDD was damaged in transport, I was able to recover most of the files but I cannot connect to the DB or use dump: I'm getting ...
0
votes
0answers
51 views

postgres-xc - ERROR: object already defined [closed]

I am configuring two data nodes and a coordinator on postgres-xc. I get eh following error: ERROR: PGXC Node datanode1: object already defined CREATE NODE pgxc_pool_reload ------------------ t (1 ...
0
votes
2answers
738 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
0
votes
1answer
232 views

Export/Import Subset of data in postgres

Using the following query, I'm able to export two tables, selecting 500 items randomly from the first: copy (select * from ( select 1 + floor(random() * 1040000)::integer as "documentUUID" ...
4
votes
1answer
288 views

What is the safe+fast way to move all Postgresql data to another machine?

I need to copy all data from one Postgresql 9.2.4 server to another (both on similar hardware, Ubuntu 12.04). Is it safe to shut down the source Postgres server, rsync the data directory to the new ...
0
votes
1answer
79 views

Renaming a column in a PostgreSQL database table

This is essentially the same question as How do I rename a column in a database table using SQL?. So, I want to rename a column in a table. This column is part of a set of tables that lives inside a ...
1
vote
1answer
343 views

Client Encoding Mismatch when creating new database

I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run ...
0
votes
2answers
953 views

Backup with pgAdmin ordered by ID

I want to backup a postgres table with pgAdmin and have the dump ordered by ID (or any other column). How do I do that? [EDIT] The dump should contain INSERT queries, not the pure data! [/EDIT] What ...
0
votes
1answer
736 views

PostgreSql allocate memory for each connection

When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ? I know 25% of the memory of the Server should ...
1
vote
1answer
2k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
0answers
68 views

Postgresql function taking long time on newly restored database

I have developed one function in my PostgreSQL database. It was taking 12-15 seconds to execute. Now I have restored a new PostgreSQL 9.0 dump using following command: pg_restore -hx.x.x.x -p5432 -d ...
0
votes
2answers
696 views

Running a UPDATE on 3 million records

I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table ...
2
votes
4answers
278 views

psql, getting a list of databases to be parsed by a script

I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them: psql -l -A -t but the output shows an obvious issue: the records are ...
1
vote
1answer
142 views

Benefit to keeping a record in the database, rather than deleting it, for performance issues?

So I have a client that I am building a Rails app for....I am using PostgreSQL. He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I ...
0
votes
1answer
76 views

Why my query works on VIEW and doesn't work on a similar table?

I have a view openstreetmapview that combines two tables with different columns. The first table is openstreetmap and the second is B. When I execute this query on the view: select this_.gid ...
-1
votes
1answer
272 views

Recovering a dropped column?

I have a PostgreSQL table: CREATE TABLE essays ( id serial primary key, content text ); I inserted a few rows, then ran: ALTER TABLE essays DROP COLUMN content; How can I recover the ...
2
votes
0answers
171 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
-1
votes
1answer
218 views

PostgreSQL indeterminate ordering of results [closed]

When I have this query: SELECT DISTINCT "spree_products".id FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND ...
0
votes
2answers
1k views

pg_dump format => custom vs sql

There are 2 main formats for pg_dump: custom vs sql. For custom, it's compressed by default, and not readable if you try to open it. But is it faster to dump into this format as opposed to SQL format. ...
2
votes
2answers
4k views

How to dump PostgreSQL database whose owner has no password?

First, some background on my setup: The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands. I created a ...
1
vote
1answer
122 views

Replicating from master to slaves and from slaves to master

I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local ...
4
votes
1answer
2k views

Postgres Dump To TXT File

Is there a way to convert a Postgres dump file to a text file? I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text. ...