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)

2
votes
1answer
283 views

Pattern similarity in time-series [closed]

I have a PostgreSQL database and my data-set include time-series of temperature for two stations. I would like to look their pattern's similarity in similar time sequences. For station number 1: ...
4
votes
1answer
1k views

What is the purpose of session pool_mode in pgbouncer?

Since in this mode pgbouncer creates outbound connection for each inbound connection it seems to me that pgbouncer doesn't work as pooler. It's just 1 to 1 mapping. Where am I wrong?
2
votes
1answer
379 views

Removing duplicate entries for each hour

I recently asked the same question about MySQL, which has since been migrated to Postgres. Old question. To sum it up: I have a table with an id (type UUID), a timestamp, and some text (row3). How ...
4
votes
2answers
685 views

ON DELETE CASCADE on pg_class in PostgreSQL does not work

I added a foreign key on my table which references pg_class. ALTER TABLE summary_table ADD FOREIGN KEY (table_oid_column) REFERENCES pg_class (oid) ON DELETE CASCADE; I have a summary table which ...
3
votes
2answers
5k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
5
votes
1answer
3k 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
1answer
340 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 ...
8
votes
1answer
13k 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 ...
4
votes
4answers
6k 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 ...
2
votes
1answer
2k views

How to switch master server and warm standby (with streaming replication) in PostgreSQL?

I would like to know what is the way to switch the role of 2 servers when using warm standby? I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
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 ...
2
votes
1answer
202 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 ...
2
votes
1answer
3k 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. ...
0
votes
2answers
1k 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
145 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 ...
6
votes
3answers
764 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 ...
0
votes
3answers
12k 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 insert data into the Table3. See the code below : DROP TABLE Table1 CASCADE; ...
2
votes
1answer
46 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 ...
4
votes
1answer
2k 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
0answers
440 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 ...
1
vote
1answer
4k 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 ...
0
votes
1answer
110 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 ...
1
vote
0answers
2k views

restore compressed dump query in postgresql asks password [closed]

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
274 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
1answer
78 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 ...
3
votes
1answer
1k 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 ...
6
votes
1answer
5k 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 ...
4
votes
1answer
162 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 ...
3
votes
1answer
9k 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 ...
4
votes
2answers
566 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
990 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 ...
4
votes
2answers
391 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
3answers
1k 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
2answers
3k 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 ...
2
votes
1answer
1k 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, ...
8
votes
1answer
3k 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 ...
0
votes
0answers
97 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 ...
3
votes
1answer
3k 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 ...
0
votes
1answer
888 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 ...
4
votes
2answers
2k 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
1answer
1k 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 ...
3
votes
2answers
82 views

Looking for a possible multi-column index

I have a table highscore containing the columns: game (text) date (timestamp) score (integer) more irrelevant ones... The query most often run on it is: SELECT * FROM highscore WHERE game = ...
1
vote
1answer
392 views

Upgrade production database during software development lifecycle

Background Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be ...
0
votes
2answers
769 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 ...
4
votes
1answer
277 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
5k 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, ...
1
vote
1answer
178 views

Postgres not vacuuming 1 table

I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB ...
0
votes
0answers
516 views

Multiple PostgreSQL running as primary server on pgpool

I'm facing a 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 : ...
0
votes
1answer
630 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 ...
3
votes
1answer
2k views

Violates foreign key constraint

I'm trying to implement the below represented UML and I can't figure out where is my mistake since I have already place a value into the table. -- Schema: public DROP SCHEMA IF EXISTS public ...