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
24 views
Creating High Availability Cluster with PostgreSQL
I am new to database design. I have a task to create a high-availability cluster. I Googled a lot, however I haven't found any good way to do this on PostgreSQL.
Where should I start and what are ...
2
votes
1answer
33 views
Model with variable number of “properties” of different types.
I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties".
Imagine one slide having
...
3
votes
1answer
25 views
How to wait for PostgreSQL to be startable / restorable?
I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows:
Start the pg service
Vacuum all DBs (not sure if this is ...
1
vote
2answers
21 views
How can I 'fake' stats for generate_series data?
With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning:
...
0
votes
1answer
23 views
Pgpool install - libpq is not installed or libpq is old
Based on the documentation here, I want to create a pgpool II with PostgreSQL databases. When I try to install, I encounter this error:
configure: error: libpq is not installed or libpq is old
...
0
votes
1answer
36 views
Recommendations for PostgreSQL training [closed]
I'm looking for recommendations for PostgreSQL training (on-site, off-site, remote) that covers administration, performance optimization, and clustering solutions like Slony and pgpool. Finding ...
2
votes
1answer
52 views
Getting a random row from PostgreSQL?
I want to get a random row from my table by id.
My table:
ID|Word |Dificult|Category_id|
1 |'Dumb' |'Easy' | 3 |
2 |'Leopard'|'Medium'| 6 |
If my user selects a category and ...
1
vote
0answers
18 views
When using a master plus multiple slaves, how do I change over from one server to a brand new one with minimal downtime?
In my particular case, I'm using Heroku Postgres (repackaged Amazon EC2 instances running PostgreSQL), but platform-agnostic answers are also good!
Heroku's instructions
describe the process for ...
3
votes
2answers
67 views
How do I implement insert-if-not-found for transactions at serializable isolation level?
I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following.
We have a table called artist with 2 columns, (name, id) where name is the ...
2
votes
1answer
20 views
Does updating a partially indexed field add or remove it from an index in Postgres
We can create a partial index for a field in Postgres.
I am looking at using this to stop rows which are archived from being in an index on a created_at field:
First we create the index:
test=# ...
2
votes
1answer
27 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, ...
2
votes
1answer
62 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, ...
5
votes
2answers
96 views
High Availability for postgresql
I'm new in PostgreSQL database. Recently our developer needed to do some upgrades in our systems.
Because of that we are planning to implement some method in order to implement database failover.
...
1
vote
1answer
37 views
Optimising query on view that merges similar tables with a clear discriminator
Using PostgreSQL 8.4, I have a number of tables that have a very similar structure, but that belong to different categories:
CREATE TABLE table_a (
id SERIAL PRIMARY KEY,
event_time TIMESTAMP ...
3
votes
2answers
98 views
Proper storage size estimation technique for PostgreSQL
We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we ...
