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)

3
votes
2answers
1k views

PostgreSQL: SET-ing from psql arguments

Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution: SET statement_timeout TO '1min'; This is great, but ...
26
votes
6answers
26k views

How do I find PostgreSQL's data directory?

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
5
votes
2answers
1k views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
0
votes
1answer
200 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
174
votes
6answers
296k views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...
14
votes
6answers
5k views

Are there any good and free tools for managing a PostgreSQL database?

Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP. Are ...
1
vote
1answer
9k views

How do I specify that a column should be auto-incremented in pgAdmin?

I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application. If I create or have created a table with pgAdmin III, how can I add ...
9
votes
3answers
365 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
5
votes
4answers
2k views

Why are so many MPP solutions based on PostgreSQL instead of MySQL?

Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
2
votes
1answer
810 views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II?

Two weeks ago I asked "How do I configure Windows XP to start the postgresql demon after each boot/reboot?", and BenV helped me out with his answer: Make sure the service is set to start ...
7
votes
1answer
1k views

BLOBs or references in PostgreSQL

I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files ...
16
votes
3answers
4k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
6
votes
1answer
356 views

Can PostgreSQL tell me what configuration files it's using?

I am having trouble with some changes I'm making to a configuration file. I think PostgreSQL is not using the ones I'm modifying. Is there any way to get postgres (maybe some environment variable) to ...
8
votes
1answer
900 views

How do I stop/disable PITR and safely clean WAL segments?

Our production server is running PostgreSQL v8.2.3 on CentOS release 5.2 (Final). We've setup PITR in our production server. For some reasons, after setting up PITR, we're not able to manage and ...
7
votes
3answers
4k views

Can't rename columns in PostgreSQL views with CREATE OR REPLACE

In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names. This works CREATE OR REPLACE VIEW gfam.nice_builds AS SELECT ...
1
vote
1answer
2k views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP?

Yesterday I installed PostgreSQL 9.0 on Windows XP, and after the installation the DB was running. After booting the system this morning, the data base server did not start automatically. How do I ...
2
votes
2answers
419 views

Transferring concepts - users/groups/permissions of db objects from mysql to postgresql

This seemed like a better fit here than stackoverflow, so here goes: I've been using mysql for a while since on most of the cheap webhosts it is the provided database. However, now I've started ...
7
votes
5answers
1k views

Are there any professional fulltime PostgreSQL DBA's?

For my job I work on a JavaEE application with PostgreSQL as the database. Although we have a sysadmin for our productions servers, who also manages our database servers, we have no full-time DBA ...
20
votes
3answers
11k views

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
4
votes
1answer
80 views

Can I activate PITR after the DB has been used

My Postgres-Server is running for quite a while and contains probably 4-5 GB of data. Backups are done using dumps, and this works ok. Though not most efficient, it just works. Due to future growth ...
5
votes
2answers
401 views

Best practices to run time offset delayed replication

I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 ...
56
votes
5answers
5k views

Do stored procedures prevent SQL injection?

Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection ...
8
votes
2answers
361 views

Biological Sequences of UniProt in PostgreSQL

What is the best way to store UniProt biological sequences in PostreSQL? Data Details We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month. The length of ...
19
votes
1answer
2k views

Is there a good way to run a trigger for each record in a postgres table?

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the ...
9
votes
1answer
936 views

What is the difference between PostgreSQL 9.0 Replication and Slony-I?

Is there still any use for Slony-I, and if so, what is it? For clarification, from 9.0 Postgresql supports built in streaming replication.