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

SymmetricDS fails to start for PostgreSQL 9.2

I'm trying to get SymmetricDS up and running with PostgreSQL. I've followed the tutorial (almost) exactly. (I have not set up a separate node yet since, for my purposes, I need that separate node to ...
0
votes
1answer
304 views

Help with Postgres 9.1 data import (is 4x expected data size, 4x slower than MySQL, and index still needed)

I have 25 billion <int, int, float> rows that I'm trying to import into Postgres, and after 77% of the data being imported, the Postgres data folder is taking up 840GB, about 4x the storage ...
1
vote
2answers
674 views

Vacuum vs Auto Vacuum

I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer. 1) Out of vacuum or Auto vacuum which is ...
0
votes
1answer
258 views

PostgreSQL postmaster will not start after configuring for SymmetricDS

I'm trying to configure SymmetricDS for use with PostgreSQL, a database platform widely supported by the former. When running through the quick-start tutorial (which leads you to some pre-setup ...
0
votes
1answer
98 views

is there a recommended way to update many postgres schemes

I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions. the system uses one scheme at a time, as if they were separate databases. So ...
0
votes
1answer
58 views

Creating groups and members [closed]

Is this a good idea to manage members of a group like so: *Assuming members can belong to one group and no other *Simplified model Group: name someid User: name ForeignKey(Group) state ...
2
votes
1answer
835 views

PostgreSQL maintenance

I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III. I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. ...
2
votes
2answers
1k views

Setting up binary replication between two PostgreSQL instances

I have two virtual machines: dbrepa (-.175) and dbrepb (-.191). Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone. I need to have the ...
3
votes
1answer
789 views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
0
votes
1answer
2k views

Create Language plperl - Error: could not load library plperl.dll

When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found. But in my computer, ...
1
vote
1answer
1k views

PostgreSQL 8.0 - Windows Server 2008 R2 install - Service won't start

I am trying to install postgreSQL 8.0 on a windows server 2008 R2 machine. After the installation, the service does not start. When I try to start it manually, I get a message saying that the service ...
0
votes
1answer
104 views

Cant get postgresql running on ubuntu 12.04

I ran sudo apt-get install postgresql on ubuntu 12.04. I didnt get a postgresql.config, instead I got a postgresql.config.sample and pg_hba.conf.sample. I renamed these (they installed in ...
0
votes
1answer
88 views

Is PostgreSQL appropriate for processing this large but simple dataset? [closed]

I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y ...
3
votes
2answers
636 views

How can I generate TPC-DS queries for PostgreSQL?

I'm using TPC-DS to run some benchmarks on a few versions of postgres that I have. I noticed that there is no template for generating the TPC-DS queries with the target database as postgres. The ...
2
votes
1answer
1k views

Index for speeding up sorting by boolean, timestamp

For a website that displays recent news, I have this query on the home page: SELECT * FROM "cms_news" WHERE NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00') AND ...
5
votes
3answers
2k views

How to keep an unique counter per row with PostgreSQL?

I need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related ...
0
votes
1answer
271 views

pg_timezone_names view is empty - can be recreated?

For some strange reason pg_timezone_names view is empty. Is there a way to recreate it?
-1
votes
1answer
2k views

how to export postgresql database table into mysql

I am trying to get data from a Postgres database, but I am totally new to using Postgres. I am here to ask my question to get some ideas from the best answer on how can I can do this. If possible, I'd ...
3
votes
1answer
365 views

Choosing A PostgreSQL Authentication Method For A Large Course

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen ...
1
vote
2answers
331 views

When postgresql sequences get out of sync?

For the third time it has happened to me that a sequence has been set to an initial value (zero or one, not sure) while in the table, there are around 1500 records. When it happens, new rows cannot be ...
1
vote
1answer
390 views

Confused over encoding/locale in postgresql

I am a little confused over the difference between encoding and locale as it pertains to the postgresql database. When initializing the database, you can specify both an encoding and a locale. ...
2
votes
2answers
410 views

Trigger update does not seem to finish

I'm new to PostgreSQL triggers & functions and such. I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
0
votes
1answer
328 views

pg_dump: SQL command failed

I am trying to take backup of postgresql 9.0 by using pg_dump command. command here is: cd /opt/PostgresPlus/9.0AS/bin and hit the following command pg_dump -h xxx.xxx.xxx.xxx -p 5432 -U superuser ...
1
vote
1answer
472 views

Should I be concered by large SERIAL values?

I have a Django application that uses PostgreSQL to analyze data from tweets. The data set increases by thousands of records with each request. I am using the database primarily as a cache, so I had ...
1
vote
1answer
1k views

In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account

We use Postgres 9.3, we want to split a large database (evaluated final size will be > 100 T) on many servers. As our users already have a set of existing queries, we cannot use solutions like ...
0
votes
1answer
4k views

How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it. I need to create a table where the elements were previously selected from ...
2
votes
1answer
187 views

Scaling of PostGIS vs MySQL cos/sin calculations [closed]

I need to sort database rows according to GPS coordinates (or any other geographical representation). The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least ...
-2
votes
1answer
109 views

Make “NOT NULL” default in Postgresql 9.2 [closed]

I use NOT NULL a lot when creating columns. Is there a way to make this default when defining columns? In other words, make column is_nullable default to NO. (I realize this would make it harder ...
4
votes
1answer
130 views

Fill factor based on index ranges

I'm designing a Postgres database for an events app. The app lists events sorted by when they start. Initially the app displays only 30 events. As users scroll through the list of events, more ...
-6
votes
1answer
189 views

how to being building a data warehouse [closed]

We have a ERP system that uses a DB in postgres, each table has many rows and we want to build data warehouse (data warehousing). Also we want to add BI using ETL where would begin? would use schemes ...
4
votes
1answer
709 views

PostgreSQL COPY: is it always faster to delete and recreate indexes?

In this answer, Erwin Brandstetter recommends It is also substantially faster to delete indexes before a huge bulk INSERT / COPY and recreate them afterwards as it is much more costly to ...
1
vote
1answer
585 views

postgres+JDBC: fire trigger with non autocommit transaction

i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table public foo() throws MyException { ...
-1
votes
2answers
562 views

PostgreSQL kill - Sighup pid [closed]

To reload the configuration files, we send the SIGHUP signal to the postmaster, which then passes that on to all connected backends. That's why some people call reloading the server ...
2
votes
0answers
416 views

PostgreSQL index array of int4range using GIN / GIST - custom operator class

Here is my table: CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range[] ); I want to index the val column: CREATE ...
1
vote
1answer
182 views

Which all system parameters to be considered for standard Vacuum process

We want to run standard vacuum process on our production database which is over 100 GB and have millions of dead tuples. Can anyone suggest what system parameters we need to keep in mind for setting ...
3
votes
2answers
375 views

Adding an index to a system catalog in Postgres

I'm having a situation very similar to the one described here: I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different ...
5
votes
2answers
4k views

How do I determine if a column is defined as a serial data type instead of an integer based off the catalog?

So I'm currently creating some SQL to read through the postgres (9.1) catalogs to build table definitions. However, I am encountering a problem with SERIAL/BIGSERIAL data types. Example: CREATE ...
1
vote
1answer
155 views

changing server roles in streaming replication

I want to implement streaming replication using postgresql-9.2.4 in my reporting application. I came stuck on the below scenario. create master and slave with synchronous replication which is ...
0
votes
2answers
106 views

Advice on scripting a “whitewashed” and downsized development db from main db

Currently we (an active opensource project) have a large PostgreSQL production database which contains all the site records. Since we can't just allow every developer or contributor access to the ...
3
votes
1answer
982 views

Postgres 9.1.6 Error index contains unexpected zero page at block 0

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index ...
0
votes
0answers
17 views

Postgres 9.1.6 Error: index contains unexpected zero page at block 0 [duplicate]

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index ...
4
votes
2answers
2k views

PostgreSQL CREATE TABLE creates with incorrect owner

I'm using PostgreSQL 9.2.4. When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly ...
2
votes
2answers
756 views

Avoiding a sort on an already clustered index for group by

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid: select count(did), pid from T group by pid Here is the query plan: ...
4
votes
1answer
2k views

PostgreSQL: How to list all stored functions that access specific table

Introduction: PostgreSQL database with several hundred of stored functions, including obsolete, not used etc. Problem I need to find out all the stored functions that have any relationship to the ...
1
vote
1answer
93 views

Try to define appropiate memory size for this postgres database

I have a site with around 150.000 daily page views using a 180Mb postgres database size running on a 800 mb ram db on Heroku. This site does not do many updates, queries are mostly select that are ...
0
votes
2answers
140 views

Recommendation for RDBMS for large table [closed]

I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well). The ...
-2
votes
1answer
317 views

Query sql-server db and insert results into PostgreSQL

I have a query that returns data from sql-server and I need to be able to put the results into PostgreSQL. I'm using C# for the task.
4
votes
3answers
6k views

Select rows, where 3 columns have the same values

I have a structure like this: col0 col1 col2 col3 1 1 1 1 1 1 1 2 1 1 1 3 1 2 1 1 1 2 3 1 2 3 4 5 2 3 4 9 I now want to select ...
2
votes
3answers
458 views

Monthly backup of SQL server DB to PostgreSQL?

The company I'm working for has a SQL Server with read-only access. They use Crystal Reports hooked up to PostgreSQL for reporting. Is there any way to make it so I can move all the data from the ...
3
votes
1answer
1k views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...