Tagged Questions

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)

5
votes
5answers
2k 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 ...
11
votes
1answer
525 views

Why is there a PostgreSQL Turtle?

I was just checking out the propaganda page for PostgreSQL and I found this little piece of art: It is the "Turtle Style" PostgreSQL logo. How old is this, and did PostgreSQL ever officially use a ...
1
vote
4answers
523 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
4
votes
1answer
336 views

How do I resolve Postgresql error, 'no collation was derived for column “foo” with collatable type citext'?

Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error: ERROR: no collation was derived for column "vin" with collatable type citext ...
2
votes
1answer
342 views

How do I resolve Postgresql error, “could not determine which collation to use for string comparison”?

When ever I run this SQL script I get: ERROR: could not determine which collation to use for string comparison This is what is on line 34: CREATE TEMPORARY TABLE weight_options AS SELECT ...
1
vote
0answers
146 views

pg_dump dumps casts after views using them [closed]

My small but annoying problem is that the regular backups of a database of ours always list the user-defined casts after those views that would make use of them. For this reason I have to maintain a ...
3
votes
2answers
1k views

Why is pg_dumpall throwing an "OID does not exist'' error?

When I run pg_dumpall I'm now getting this: $ pg_dumpall -f may2012 pg_dump: schema with OID 7549789 does not exist pg_dumpall: pg_dump failed on database "dealermade", exiting How do I resolve it? ...
1
vote
1answer
203 views

How to know there is a maintenance job running on a postgresql database

I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. ...
1
vote
2answers
5k views

Recover from Postgres FATAL: could not open file pg_tblspc/

I have a pretty big PostgreSQL 9.1.3 running on Ubuntu 10.04. The data is spread over multiple tablespaces = physical drives. One of these drives is gone, so the directory of that tablespace doesn't ...
1
vote
1answer
150 views

Postgres table size doubles are transaction completes

I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding ...
1
vote
1answer
459 views

PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
11
votes
1answer
1k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
3
votes
1answer
358 views

Oracle to PostgreSQL

I'm having som test databases running Oracle 10 & 11 that I plan to try to migrate to PostgreSQL. Some googling hinted that the perl script "ora2pg" could be of use, and free of charge. However, ...
1
vote
1answer
331 views

PostgreSQL schemas, select same table from all schemas

My app is used by about 80k users every month. I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection). My app allows users to search other user's profiles, via: ...
5
votes
5answers
4k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
2
votes
2answers
4k views

How to convert PostgreSQL to MySQL

How can I convert database from PostgreSQL to MySQL? I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error: ERROR ...
2
votes
2answers
2k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
15
votes
3answers
6k views

Is the CPU performance relevant for a database server?

This is a purely theoretical question. Let's say I have an application deployed on multiple servers. A load balancer, Multiple/scalable applications servers A (single) database server (for the ...
7
votes
1answer
3k 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 ...
2
votes
1answer
133 views

Is it possible to sync a Pervasive SQL database to a PostgreSQL database, and vice-versa?

I was wondering if it is possible to sync a Pervasive SQL database to a PostgreSQL database. If I set this up I would need the ability to sync both ways either on command or via a schedule. Does ...
5
votes
1answer
3k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
0
votes
1answer
4k views

How to export a table on postgresql command line

Anyone can do tell me how to do that? (I know maybe is a dumb question but I'm trying to learn how to use command line of postgresql) ~$ psql mybase psql (9.1.3) Type "help" for help. and then ...
9
votes
2answers
7k views

How to view data in pgAdmin III

I can't believe they make it this hard. I am at a loss about how to view the data in my database. Is there an easy way to see what data is in my tables with pgAdmin III? Alternatively, is there a ...
-1
votes
1answer
319 views

Important distinctions between DBMSs [closed]

DBA is a career I'm strongly considering after school, but my limited experience mostly involves some PL/SQL Oracle web programming, some basic PHP/MySQL web pages, and some queries in SQL Server. ...
2
votes
1answer
1k views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
2
votes
1answer
589 views

Dropping indexes doesn't seem to release space to the OS. Can it?

I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g: evlampts=# SELECT pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS ...
6
votes
2answers
9k 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 ...
1
vote
0answers
117 views

PostGIS - Effects of polygons with large number of verticies [closed]

I have a large table that stores, among other attributes, a geometry type. I have a GIST index on this attribute. However, when I perform even the most basic queries [say, select count(*) from table ...
4
votes
2answers
1k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
0
votes
1answer
136 views

Postgres Client - save table sorting

Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. ...
4
votes
1answer
1k views

Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id). Clients compute new intervals for one variable and store ...
4
votes
3answers
2k views

PostgreSQL - Help Optimizing SQL performance - full text search

I have a big table ~15M records called entities. I want to find top 5 entities matching "hockey" in their name. I have a Full text index built for that, which is used : ...
22
votes
1answer
3k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
4
votes
3answers
996 views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
1
vote
1answer
2k views

pg_restore: [archiver] unsupported version (1.11) in file header postgresql

My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a ...
2
votes
3answers
9k views

How to drop all connections to a specific database in postgres without stopping the server?

I want to drop all connections (sessions) that are currently opened to a specific postgresql database but without restarting the server or disconnecting connections to other databases. How can I so ...
2
votes
2answers
261 views

Can I replicate views with Slony?

Can I feed some SQL that creates a view to Slony? I have some SQL that does CREATE OR REPLACE VIEW name_of_view and so on, passes it to slonik_execute_script, and then tries to run slonik_create_set ...
3
votes
1answer
157 views

send jobs via psql asynchronously

I want to backup a huge number of records and delete them, but I don't have complete access to database server so I can't run my script on the server. because it takes so long I need to submit ...
3
votes
3answers
698 views

How do I design a database for continents, countries, regions, cities and POIs?

I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, ...
2
votes
2answers
294 views

pg_relation_size does not show any difference after VACUUM ANALYZE

I wrote a script to backup a postgresql using COPY and then delete those records. I have used pg_relation_size before and after delete. pg_relation_size DELETE VACUUM ANALYZE pg_relation_size But ...
2
votes
1answer
159 views

How not to distribute reads in pgpool

I want to use pgpool to replicate one database to another. I want to do my processing on my master database (reads/writes/inserts/deletes), replicate that database to another database as it is ...
0
votes
1answer
842 views

slow index scan on postgresql 9.1

I have a view which is amazingly slow :) It joins two tables only, having a few simple WHERE conditions and one in a form of a subquery (see SubPlan1 below). The simplest condition is checked as an ...
1
vote
2answers
251 views

PostgreSQL Database Migration

How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?
4
votes
3answers
4k views

How to connect to remote PostgreSQL server

I wrote a web app, using java + spring+ PostgreSQL, which I have up and running. I have a tester for my system and I want him to be able to connect to my database directly to check the result of ...
2
votes
1answer
254 views

Initial recovery Postgres with backup in sync commit mode

I've got a master and a slave postgres 9.1.3 database. I would like to init the backup database for the first time. I did the following steps: Stopped master Copied all files to Slave Create ...
2
votes
1answer
6k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
3
votes
1answer
170 views

PostgreSQL synchronized commit performance

I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can ...
1
vote
1answer
2k views

Is there any way to clear all tables using Liquibase?

I'm currently using Liquibase for creating test data in my database before each test run. I came across a problem where some of the test cases need special data, while others need the tables to be ...
1
vote
2answers
1k views

Request suggestion for a database between MySQL and Postgresql

We maintain a list of all the SAN Switch Ports that are either free or allocated to hosts. Currently, we enter the information on the company portal. But we need to move if off to a database because ...
2
votes
2answers
1k views

psql won't list databases

I have a situation that I'm scratching my head over, but I'm new to postgresql, so I may be overlooking something obvious. I set up a testbed server and set access in pg_hba.conf: local all ...