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)

2
votes
2answers
163 views

Why is postgresql still consuming space on my other drive?

I am running postgreSQL 8.4 on Ubuntu 10.0.4. I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and ...
1
vote
4answers
445 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
2
votes
2answers
2k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
2
votes
2answers
402 views

Is it a good idea to move high-volume and highly-accessed tables to a separate database?

Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
5
votes
1answer
974 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: ...
3
votes
3answers
2k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
3
votes
1answer
200 views

Postgres Backup queries on AWS after reading Postgres docs

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance. So there are 2 ways to do it. Create a dump using pg_dump. File level backup with ...
3
votes
2answers
422 views

Continuous archiving: is it possible to use multiple machines?

Is it possible to use the archive_command to send to multiple machines? Is it just as simple as separating with a && since it is essentially just a command line? command1 && command2 ...
4
votes
1answer
6k views

PostgreSQL: permission denied for relation

I'm a bit confused about setting permissions in PostgreSQL. I have these roles: List of roles Role name | Attributes | Member of ...
21
votes
1answer
2k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
2
votes
1answer
129 views

GRANT privileges to table elements

What happens if I GRANT priveleges (for example SELECT) ON a table TO some group role? What privileges will the table's elements have in this case: indexes, sequences, columns? Should I explicitly ...
0
votes
2answers
664 views

Reloading .sql file from java into PostgreSQL - Windows

I am trying to reload a .sql script file into PostgreSQL from my sample Java application. Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs... This ...
2
votes
1answer
1k views

Logging to a file in PostgreSQL windows service

I need an alternative for running the following command: C:\xxx\pgsql\bin\pg_ctl" -D "C:\xxx\pgsql\data" -l "C:\yyy\log\pgsql.log" start. This way the server starts and logs to C:\yyy\log\pgsql.log. ...
3
votes
2answers
207 views

What are my options for GUI based Administration & Management of PostgreSQL

I have been given the task of working with PostgreSQL 9.1. I have previously worked with Microsoft SQL, and had used the SQL Management Studio. I had used Oracle's SQL developer as well as TOAD when I ...
1
vote
1answer
2k views

PostgreSQL duplicate key violates unique constraint error

CentOS 6.2 64bit Server PostgreSQL 8.4.9 installed via yum I am seeing errors from time to time in PostgreSQL's logs about a table called events. The events table is used to stores information ...
2
votes
1answer
108 views

Audit tables in different databases

I am studying ways to implement audit tables to my PostgreSQL database and would like to know if it is possible to make a trigger for the data to be written to a table in a database other than the ...
0
votes
2answers
4k views

PostgreSQL - insert/update violates foreign key constraints

I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't able to insert data into the Table3. See the code below : DROP TABLE Table1 ...
2
votes
1answer
39 views

Insert command not showing schema details

I need to back up a table from a database schema.The table is located under kdb28_test_h and a schema named kref. I tried with the following code pg_dump --host=110.11.12.16 --port=5432 ...
2
votes
1answer
711 views

What is PostgreSQL database ownership

When creating a PostgreSQL database one can assign another role to be an owner of that database. What can the new owner do with this database? How is it compared to grants?
1
vote
1answer
951 views

restore compressed dump query in postgresql asks password

I'm using ubuntu OS and I took backup using sudo -u postgres pg_dump dbname | gzip > filename.gz in the file path as... /home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname ...
3
votes
2answers
92 views

Why might my Postgres table be growing?

I've got a table that grows under Postgres 9.1. It's a high-throughput table with lots of inserts and deletes, but it should generally remain between 1-2k in terms of number or records. Auto-vacuum is ...
1
vote
0answers
256 views

Finding the bottleneck by analyzing Munin graphs

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
3
votes
1answer
434 views

Distinguish between schema and database

I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code: DECLARE v_tmp_host scan.location.alias%TYPE; v_ip_address character varying; BEGIN ...
1
vote
2answers
58 views

PostgreSQL custom domain with trigger

Is it possible to create custom domain / data type and associate some trigger with it, for example hash password? I will have for example password domain and if I set some value to this column, it ...
4
votes
1answer
492 views

What AWS instance to choose for write-heavy PostgreSQL database server

We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At the moment we are using a smaller cloud service for ...
4
votes
1answer
2k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
3
votes
1answer
5k views

Pg install: “The database cluster initialisation failed”

(Note: This question has been compeltely rewritten from its original form, so it reflects the actual problem and will help others find a solution to the same issue). I still haven't been able to ...
1
vote
3answers
865 views

Ubuntu: upgrading postgresql 8.4 database cluster for use with 9.2 server

I have installed PG 9.2 on my machine. I have a database cluster that was created about a year ago, using initdb, using postgreSQL version 8.4. The (8.4) data cluster consumes several Gb and so its ...
4
votes
1answer
148 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
4
votes
2answers
247 views

What happens to the index of a primary key after a DROP CONSTRAINT?

I am running PostgreSQL 9.1.4. I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to : 1 - Remove the pkey constraint on the ...
4
votes
1answer
217 views

Troubleshooting Postgresql spatial query failure. Next steps?

We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be ...
3
votes
1answer
342 views

Getting SQLSTATE in psql error messages

I have the following problem. SELECT * FROM tgvbn(); ERROR: function tgvbn() does not exist LINE 1: SELECT * FROM tgvbn(); ^ HINT: No function matches the given name and ...
2
votes
1answer
467 views

Archive database on Postgres

I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, ...
7
votes
1answer
948 views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
2
votes
1answer
2k views

How does postgres use the schema search path when making indexes?

Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app. I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table ...
3
votes
1answer
203 views

Why does PostgreSQL aggregate result in the Index or Seq scan outputting all columns of the table?

Doing a query like this: SELECT fruit, avg(cost) FROM fruit_table GROUP BY fruit results in a query plan like this: Sort (cost=3713873.41..3713874.46 rows=420 width=16) Output: fruit, ...
0
votes
1answer
587 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
0answers
67 views

Long disk queue when no traffic

I got dedicated disk (RAID 10 - 4 SAS disks) for Postgres. I am bit worried as recently, I see under Windows Resource Manager disk queues like 3+ in the mornings (low traffic hours), like every 30-60 ...
0
votes
0answers
286 views

multiple postgreSQL running as primary server on pgpool

I'm facing problem with my pgpool setup. Currently, this is my requirement based on the pgpool documentation here : Server A : Install with PostgreSQL database. Run as primary server Server B : ...
1
vote
1answer
2k views

Drop multiple databases in one script

I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
4
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
3
votes
1answer
871 views

How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)?

I have a Postgresql database that need to read data from two SQL Servers (one SQL Server 2000 and one SQL Server 2008). I want to setup two dblinks to the SQL Servers. After googling, foruming, ...
3
votes
2answers
860 views

Manually increase PostgreSQL's table size limit of 32 TB?

I need a free database software and thought of PostgreSQL. But then I read it has a table size limit of 32 terabytes, but I need more (and don't want to spend thousand/millions on high end database ...
0
votes
2answers
411 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
0
votes
1answer
509 views

pgAgent installation with Stack Builder in PostgreSQL [closed]

I have installed PostgreSQL 9.1 on ubuntu 12.04 64-bit. I try to run Aplication Stack Builder and install pgAgent. At first I am asked for root password, which I provide, after I choose PostgreSQL ...
1
vote
1answer
530 views

Tutorial for installing and setting up 'streaming replication' with PostgreSQL on Windows XP

Please refer to the diagram in the link below. While looking at my goals. http://i.stack.imgur.com/GkVWb.png GOAL: Get PostgreSQL 9.1 running on 3 terminals on the 'on site' location. 'on ...
4
votes
1answer
206 views

Postgresql - Internal Array handling - array type or pointer type

I have a question, about the internal handling of array columns in Postgres. Does the engine handle it as a classical array with memory alloc or is it somehow a pointer list. It would be interesting ...
1
vote
3answers
1k views

Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2. To create a simple example, lets say T1 is Cars, ...
3
votes
1answer
820 views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
3
votes
1answer
255 views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | ...