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 ...
5
votes
4answers
990 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 ...
5
votes
5answers
689 views
Altering table schema takes too much time
I am using PostgreSQL database.
I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
5
votes
4answers
2k views
Aggressive Autovacuum on PostgreSQL
I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:
autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
...
5
votes
3answers
195 views
Casting issue when calling a function with composite type parameter
I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it.
what could be the issue?
CREATE OR REPLACE FUNCTION ...
5
votes
2answers
364 views
PostgreSQL procedural languages - differences between PL/pgSQL and SQL
Can anybody please summarize the differences between:
http://www.postgresql.org/docs/9.1/static/xfunc-sql.html
and
http://www.postgresql.org/docs/9.1/static/plpgsql.html
?
Main points:
...
5
votes
5answers
1k views
Are there any good tools for monitoring postgresql databases
I am planing to have very soon few highly loaded postgresql databases. I have some expirience managing mysql databases with high load, but now we have to use postgresql.
I want to know what are the ...
5
votes
2answers
511 views
Save postgres database from broken ubuntu 10.04 server
Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve).
I can still access the filesystem. Is there a chance to transfer the database by only ...
5
votes
3answers
110 views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
5
votes
2answers
105 views
PostgreSQL: Query DB for rows containing integer[] > or < than specified value
I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
5
votes
3answers
1k views
Is there a way to access temporary tables of other sessions in postgres?
I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis ...
5
votes
1answer
210 views
Unexpected Seq Scan when doing query against boolean with value NULL
I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.
CREATE INDEX index_table_on_auto_renew ON table USING ...
5
votes
2answers
3k views
Force drop db while others may be connected in postgresql
I need to remove a database in postgresql DB. How can I do it even if there are active connections? I need a sort of -force flag, that will drop all connections and then the DB.
How can I implement ...
5
votes
2answers
479 views
Force PostgreSQL clients to use SSL?
I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL?
(I.e. does ssl = on it make it impossible to ...
5
votes
2answers
1k views
About “Transaction ID Wraparound”
Now , I read the document about "Transaction ID Wraparound " , but there are something that I really
don't understand, the document is the following url
...
5
votes
2answers
797 views
Single data type for imprecise date values, as allowed by ISO 8601
How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?
ISO 8601 allows date values with reduced precision. ‘1964’, ...
5
votes
3answers
117 views
“AT TIME ZONE” with zone name PostgreSQL bug?
I was answering this stackoverflow question and found strange result:
select * from pg_timezone_names where name = 'Europe/Berlin' ;
name | abbrev | utc_offset | is_dst
...
5
votes
2answers
4k views
PostgreSQL: The database cluster initialization failed
Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights.
I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL
Every way I ...
5
votes
2answers
2k views
How do I create an index to speed up an aggregate LIKE query on an expression in postgres 8.4?
I may be asking the wrong question in the title. Here are the facts:
My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
5
votes
4answers
2k views
Finding empty columns of a table in PostgreSQL
What query would return the name of the columns of a table where all rows are NULL?
5
votes
2answers
266 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.
...
5
votes
2answers
540 views
Should I use the PostgreSQL bit string?
I've been learning about the bit string data type lately, and I am quite curious about:
At the bottom of this doc page there is the sentence:
... plus 5 or 8 bytes overhead depending on the ...
5
votes
1answer
513 views
EC2 - How to correctly back up PostgreSQL data?
Here is the setup: 1 small Amazon Linux (EBS-backed) EC2 instance with 3 additional volumes. This is both a web server and database server. One volume for code, one for the PostgreSQL (8.4) data ...
5
votes
1answer
91 views
Upgrade postgres without messing with ports?
The standard procedure for upgrading Postgres appears to require that I temporarily run both the old and the new version. This means that I have to change the port of the old version.
This seems ...
5
votes
3answers
2k views
How to convert mysql to postgresql?
I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:
Free
Works under Linux
Simple to use and not buggy
You actually tried and ...
5
votes
3answers
1k views
Postgresql row-level encryption
Looking over the docs, I didn't see any mention of row-level encryption.
Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with ...
5
votes
1answer
67 views
Privileges required to install PostgreSQL
I am new to PostgreSQL Database. I need to install it in my machine.
I am not administrator in my system.
Can some one suggest, if Non-Administrator can install it.
If there is any other way of ...
5
votes
1answer
225 views
How to customize constraint violation messages?
I want define my own message for each constraint. Is it possible to customize constraint violation messages in PostgreSQL?
5
votes
1answer
6k views
How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?
I´m trying to setup an PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this?
I have installed the PostgreSQL ...
5
votes
1answer
1k views
Fast general method to calculate percentiles
I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile.
An obvious solution is to count and sort the column and then do a ...
5
votes
1answer
384 views
PostgreSQL partition hierarchy design for centralized logging
I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with ...
5
votes
2answers
338 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 ...
5
votes
1answer
151 views
How can I correctly choose maximum number of occurrences of a string while grouping by another field?
I am using Postgresql 9.0. I have the following fields in a table: id, name.
id name
1 John
1 Mary
1 Mary
1 Mary
1 John
1 Mary
3 Paul
3 Paul
3 George
...
5
votes
1answer
2k views
PostgreSQL 9.1 Hot Backup Error: the database system is starting up
I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under ...
5
votes
4answers
834 views
Determine when a PostgreSQL database was last changed
I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed?
Instead of using pg_dumpall, I'd ...
5
votes
1answer
403 views
PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly
We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
5
votes
1answer
56 views
How to tell if database tuning is fruitful (PostgreSQL)
As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the ...
5
votes
2answers
2k views
SQL Query Formatter
Are there any (Linux based) SQL Query Formatting programs/plugins/extensions?
I use PostgreSQL and MySQL but other DB's are welcome as well.
I can use a VM to test with but would prefer a Linux ...
5
votes
2answers
2k views
Non-unique multicolumn foreign key
I have a "comments" table that models a conversation on a topic, like this:
id serial
topic_id integer
parent_comment_id integer
body text
So, every comment has a reference to its topic AND ...
5
votes
1answer
118 views
How to improve performance on PostgreSQL when using multiple concurrent processes?
Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
5
votes
1answer
618 views
How to get notices to the output stream?
I have debugging messages in functions. Those message are raised like
RAISE NOTICE 'Value of id : %', id;
I set my log file with \o messages.txt
Then I do what I need to do with \i process.sql
And ...
5
votes
1answer
1k views
PostgreSQL Failover - What tools should I use?
Here is the scenario:
There are two machines running CentOS 6.2 - machine0 and machine1
Both have PostgreSQL 9.1 installed.
One of them should be active, as a master system and through asynchronous ...
5
votes
1answer
845 views
Load Balancing / MPP with Postgres 9.1
I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes).
Since I'm ...
5
votes
1answer
91 views
How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?
Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling.
What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, ...
5
votes
1answer
287 views
PostgreSQL: Can I do pg_start_backup() on a live, running db under load?
Our established replication has broken ("requested WAL segment has already been removed" during downtime)
We cannot easily stop the master again.
Can we do
pg_start_backup(),
rsync ${PGDATA}/ ...
5
votes
1answer
384 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 ...
5
votes
1answer
335 views
Using MongoDB and PostgreSQL together
My current project is essentially a run of the mill document management system.
That said, there are some wrinkles (surprise, surprise). While some of
the wrinkles are fairly specific to the ...
5
votes
1answer
88 views
To Foreign Key or not to Foreign Key… (inet -> cidr)
Below is a very abridged version of a project I am working on.
CREATE TABLE cidr_block (
id serial not null unique,
block_def cidr primary key,
dhcp_server_id int not null references ...
5
votes
2answers
134 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 ...
5
votes
1answer
201 views
Database Exceptions with Jetty 8.1.0 with BTM
We are running Jetty 8.1.0 with BTM 2.1.2.
We are using Postgres as our DB.
Everything works great except that when we run any query with a condition where sometable's primary=null, the server loses ...
5
votes
1answer
75 views
Rebuild a table from history records
A small disaster happened to me on my PostgreSQL 8.4 database.
I follow visitors to my webpage in two tables:
History
+---------------+-------------+
| whereseenlast | varchar(50) |
| uniqid ...
