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)

0
votes
1answer
213 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
0
votes
1answer
165 views

OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)

I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view: If I were to lose my temp tablespace upon system crash, would this prevent proper crash ...
0
votes
2answers
201 views

Storing data in PostgreSQL: One table or two?

I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month. Question: Because every product can have different ...
0
votes
1answer
298 views

How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?

I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could ...
0
votes
2answers
2k views

PostgreSQL cannot start after replacing contents inside its Data folder

When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
0
votes
1answer
254 views

Postgres 9.1 wait events

Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle? I need to find queries which are waiting for a long time, and the events they are blocked on.
0
votes
1answer
104 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
0
votes
1answer
689 views

Getting error while importing large data through csv

I am new Postgres. I am getting constraint violation errors while importing a large CSV file. I want to disable all of the database constraints temporarily - I do not know how to do this.
0
votes
1answer
422 views

Passing argument in trigger dynamically

CREATE TRIGGER audit_proc_tr AFTER INSERT OR UPDATE OR DELETE ON "log".hi FOR EACH ROW EXECUTE PROCEDURE "log".audit_proc(argument); CREATE OR REPLACE FUNCTION ...
0
votes
1answer
42 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
0
votes
1answer
474 views

Cannot use python3 as stored procedure language in posgresql database

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
0
votes
1answer
304 views

Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table. I have pgAdmin III. Is there a way to do this automatically and not go over each table manually?
0
votes
1answer
300 views

Postgresql Run 'Jobs' At Specific Times

I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute . Scenario : PostgreSQL 9.2 Need To Run A ...
0
votes
2answers
273 views

Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12

At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2? I checked their website and to my understanding it is not possible... I hope that's not the case. Can anyone ...
0
votes
1answer
70 views

Faster joining on 'most recent' related rows

I have a bug tracking application. The bug table stores very little, because every state change to a bug happens by adding a comment. For example, the current state of a bug is determined by the state ...
0
votes
2answers
392 views

Starting a Postgres slave that is in hotstandy - stuck after start

I have a postgres 9.1 hot-standby that has been down for a few days and having problems starting back up. When I try to restart the server I see the following: -sh-4.1$ /usr/local/pgsql/bin/pg_ctl ...
0
votes
1answer
395 views

Permission Denied Copy [closed]

I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS. copy tname from '/Users/Shared/folder/file.cxv' ...
0
votes
1answer
736 views

Postgres 9.1 statistics in pg_stat_database

I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be ...
0
votes
1answer
245 views

postgresql out-of-memory

I am running postgresql 9.2 on a 32-bit machine running windows xp with 4 gb of memory and I am getting out of memory errors. I suspect it has something to do with these two parameters I have changed ...
0
votes
2answers
1k views

Interactive INSERT / UPDATE function to implement UPSERT

I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 ...
0
votes
1answer
3k views

Save output from multiple SQL SELECT commands to a file

Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a ...
0
votes
1answer
339 views

MAX function and group by usage - postgresql

I am building a ranking algorithm using IMDB's formula: http://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268 The formula for calculating the Top ...
0
votes
1answer
236 views

Storing 5TB web server access log vs OLAP DB

We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop. It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it. ...
0
votes
1answer
547 views

pg_hba.conf from PostgreSQL

I have no idea how to configure that pg_hba.conf file, to connect with my server via pgAdmin. Right now i was trying to add: host all all host.name.org.pl trust host all all host.name.org.pl md5 ...
0
votes
1answer
350 views

Is there a recommended way to update a database schema when updating and app's RPMs?

I have a pretty standard server layout: 2 Application servers running Tomcat on Redhat. 2 Database servers running Postgres on Redhat. One database is a master and replicates to the slave. I'd ...
0
votes
1answer
1k views

libreadline.so.6

when I was installing PostgreSQL from source code I had to install the libreadline.so.6 library. From that moment I can't update my kubuntu system. The error is: Setting up ubuntu-keyring ...
0
votes
1answer
128 views

Troubles with creating initial PostgreSQL base

I'm trying to create a new base /usr/pgsql/bin/initdb -D /var/lib/pgsql/data --locale=en_USand got the following: creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL: could not ...
0
votes
1answer
1k views

Postgres Incremental Backup (Continuous Archiving WAL)

I need to take incremental backup of Postgres DB. I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want. As per Continuous Archiving, I ...
0
votes
1answer
249 views

Use data in a tablespace to populate another postgres database instance without actually inserting

I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there ...
0
votes
1answer
223 views

Can't connect to a PG database from MicroOLAP, but can connect from SSH?

I'm able to connect to my PGSQL database from SSHv2. But when I try to do the same thing on MicroOLAP Database Designer, I get this message: (Note: From my college PC, I can connect. But I can't ...
0
votes
1answer
289 views

Any advantages if we use multiple database instead of only one in PostgreSQL?

I would like to get the suggestion for my usecase. We have a database in our production machine.We are doing continuous writes in that database since we are doing crawling,parsing and it is being ...
0
votes
1answer
2k views

Error restoring Postgres DB

I'm trying to do a pg_restore on some SQL files. Here's the command I used when I dumped from the other database: pg_dump -f /home/mctools/public_html/cp/upload/manufacturers.sql \ -Fc -i -v ...
0
votes
1answer
82 views

How to determine why connection creation lasts so long?

On one of my servers (Windows 2008 Server), creating connection in Java to PostgreSQL server takes from 1,5 to 4 seconds! I have no clue how to start checking what can be wrong. I tried different ...
0
votes
1answer
780 views

How to Restore Postgresql Cluster from data directory

in our office we have a development database server that runs Debian 5, on this server we had a postgresql 8.1 running but because of the lack of internet access from the server we backed up with ...
0
votes
1answer
538 views

What's included in the default OSX 10.8 install of Postgres?

I've seen several sites claim that Postgres is installed by default for OSX 10.7 and above and I see there's a psql command in /usr/bin/, but there doesn't seem to be a pg_ctl anywhere. So is the ...
0
votes
1answer
307 views

Heroku PostgresApp - Fails to start with edited postgresql.conf

I am trying to tune my local pg running from the Heroku Postgress App. I am seeing strange errors in the OSX console whenever I change some of the internal settings: PM Postgres[78709]: Could not ...
0
votes
1answer
426 views

postgresql 9.1 Multiple Cluster on same host

I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok ...
0
votes
1answer
462 views

Converting SQL Server 2005 schema to PostegreSQL 9

I have a fairly simple SQL Server 2005 schema that I exported to a tSQL file. I am looking for a tool, preferably open source and runs on Win 7 (Ubuntu 2nd choice) that will convert the transact SQL ...
0
votes
1answer
416 views

PL/pgSQL function + client-side lo_import

I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this: create function add_file(flag integer, sth varchar) returns void as begin ...
0
votes
2answers
226 views

Transfer data from database1 to database2 using task queue

We have a SQL Server legacy database, db1, to put data of Project1. With change of certain tables(insert/update) in db1, we need to transfer the changes in another postgres database, db2 (used by ...
0
votes
1answer
1k views

How to create a new instance of PostgresSQL on OS-X 10.8 Server

After upgrading my MacMini home server from OS-X 10.7 (Lion) Server to OS-X 10.8 (ML) + Server.app, I saw PostgreSQL 9.1.4 was released. Unfortunately, the setup process for PostgreSQL 9.1.4 has ...
0
votes
1answer
2k views

What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced?

I am using PostgreSQL 9.1. Trying to enforce UTF8 encoding as default. This is what I am doing. service postgresql initdb -E 'UTF-8' \ --lc-collate='en_US.UTF-8' \ ...
0
votes
1answer
274 views

Fill factor for later bigserial + index

I'm planning to import and update a huge table (3 Bil. rows) in the following step: create table with fill factor x import the data from csv files (6 files, ~21GB each) add a BIGSERIAL column create ...
0
votes
1answer
438 views

Can't get past password lock on fresh install of postgreSQL

I feel really dumb writing this, I wanted to have a mess around with databases so I just installed postgreSQL (9.1) During the installation I am asked to provide a superuser password. I provide a ...
0
votes
1answer
138 views

Postgres Client - save table sorting

Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. ...
0
votes
1answer
875 views

slow index scan on postgresql 9.1

I have a view which is amazingly slow :) It joins two tables only, having a few simple WHERE conditions and one in a form of a subquery (see SubPlan1 below). The simplest condition is checked as an ...
0
votes
2answers
251 views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
0
votes
1answer
382 views

postgresql recovery after reboot

I installed Postegresql and added a database which I was using as part of a local server. However, I restarted the computer and my Postgresql database seems to be gone. The Postegresql documentation ...
0
votes
1answer
288 views

Slony - Wait for Event

What is the behavior of Slony waiting for an event after lock set command? Basically I am trying to do a switchover and it takes a lot of time. We are following these steps: Lock Set Wait for Event ...
0
votes
1answer
2k views

restoring a table from sql file in postgresql through Windows Command prompt

Im trying to restore an sql file which contains the dumpf a table (with COPY commands) into a table into the database, it would be great if someone could share an example, as the pg_restore doesn't ...