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 ...
0
votes
2answers
17 views
Connecting to an external database with pgAdmin III
I'm trying to connect to an external database from pgAdmin III (which is installed on both machines).
The client complains:
Access to database denied
The server doesn't grant access to the ...
0
votes
1answer
9 views
Retrieve list of matched words in PostgreSQL
I'm new to PostgreSQL and really more than your more basic queries/inserts.
I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in ...
0
votes
1answer
19 views
What to look for in bad pg_dump log
We want to programmatically detect errors in cron scheduled pg_dumps.
Apart from cheking whether or not the log file ends in "pg_dump: saving database definition":
What other tell-tale string can I ...
1
vote
1answer
21 views
Taking a backup of a streaming WAL slave
I have an environment with pair of postgresql servers set up to do WAL streaming replication.
I also have a test environment and periodically I want to snapshot the production DBMS and restore that ...
0
votes
3answers
40 views
Vacuum settings for mostly append-only data
I have a table with the following characteristics:
We INSERT a few 100k rows each day
We never UPDATE the rows
We DELETE "old" data once a week
From my shallow knowledge of Postgres VACUUM, it ...
0
votes
1answer
15 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
47 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 ...
0
votes
2answers
28 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
15 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
38 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
32 views
Creating groups and members [on hold]
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
28 views
Trigger that modifies values of firing query [on hold]
I need to write a postgresql trigger that fires BEFORE INSERT ON a specific table.
This trigger should retrieve on database some values that are unknown at the moment of creation and execution of ...
1
vote
1answer
42 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. ...
1
vote
1answer
21 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 ...
1
vote
1answer
42 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
38 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
31 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
11 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
46 views
Is PostgreSQL appropriate for processing this large but simple dataset? [on hold]
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 ...
1
vote
1answer
38 views
How can I generate TPC-DS queries for PostgreSQL?
All,
I am 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. ...
1
vote
1answer
35 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
...
3
votes
3answers
64 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
18 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?
0
votes
1answer
7 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 ...
2
votes
1answer
35 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 ...
0
votes
1answer
26 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 ...
0
votes
1answer
40 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
43 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
32 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
51 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
57 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
48 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
0answers
42 views
Scaling of PostGIS vs MySQL cos/sin calculations
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 ...
-3
votes
1answer
52 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 ...
3
votes
1answer
54 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 ...
-5
votes
1answer
66 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 ...
0
votes
0answers
33 views
How to allow each user to access his own db, and superuser to all dbs in PostgreSQL?
I want to set up some pretty traditional permissions: each user can access his own db, and superuser can access all the dbs.
So far I got the first part right (I think). I edited pg_hba.conf, and ...
2
votes
1answer
43 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 ...
0
votes
1answer
32 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
75 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
42 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
31 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 ...
2
votes
2answers
68 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 ...
1
vote
2answers
47 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 ...
0
votes
0answers
19 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
48 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 ...
1
vote
0answers
51 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
15 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
99 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 ...
1
vote
2answers
53 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:
...
