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)

4
votes
3answers
2k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
1
vote
2answers
502 views

LIMIT a query Postgresql

im doing this query and it gives me multiple hits because of each assetid may contain many vb.title, how can i limit the result to only show 1 hit for each assetid? 'select DISTINCT v.id, ...
0
votes
1answer
237 views

postgresql dump and load — need to dump “postgres” db also?

If I pg_dump a db from an 8.4 cluster, and then load it into a different 9.1 cluster, do I need to pg_dump and restore the 'postgres' db as well? (I'm not familiar with what is contained in the ...
1
vote
1answer
724 views

How to verify postgresql db restore from pg_dump file

How can I verify that my restored database is identical to my original database? Here's what I did. Is this a valid way to verify a restore? # dump mydb_orig: $ pg_dump --cluster ...
2
votes
1answer
1k views

How to run specific version (8.4, 9.1) of postgresql pg_* command (e.g., pg_dump)

I have Postgresql versions 8.4 and 9.1 installed. For any given Postgresql command, how do I specify a specific version of the command to run? (e.g., psql, pg_dump, pg_ctlcluster, pg_restore, ...) ...
0
votes
1answer
243 views

Fill factor for later bigserial + index

I'm planning to import and update a huge table (3 Bil. rows) in the following step: create table with fill factor x import the data from csv files (6 files, ~21GB each) add a BIGSERIAL column create ...
4
votes
2answers
2k views

Recover PostgreSQL database from WAL errors on startup?

I'm trying to set up an OpenStreetMap server on an Ubuntu 12.04 machine using the Ubuntu packages listed at switch2osm.org. I initially installed and set up everything using a Northeast US-only map ...
2
votes
2answers
340 views

MySql Vs. Postgresql for a blog application

I am designing a blog application. Which of these fits better? In which use cases will mysql be good and for what use cases will postgresql be good?
0
votes
1answer
370 views

PostgreSQL vacuum duration on a big table

I have a very big table in my postgreSQL (over 300 million rows) that has never been vacuumed. Yesterday I tried a vacuum and analyze (not a full one) and it took about 7 hours to complete. Will it ...
2
votes
2answers
3k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
1
vote
1answer
1k views

Two-way encryption in PostgreSQL version 9.1 database

What is the process of two-way encryption of data in PostgreSQL database version 9.1 using built-in functions?
2
votes
2answers
171 views

Postgresql: Two concurrent COPY FROM

I'm importing several gigantic csv files into a table - so I'm wondering what would happen if I ran COPY FROM jobs concurrently?
5
votes
1answer
404 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?
4
votes
1answer
220 views

PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?

I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf. I do not want to create ...
0
votes
1answer
267 views

Salvaging data from corrupt tables PostgreSQL

Am in the process of salvaging very crucial data from a corrupted database. Am getting the following error from a very crucial table ERROR: invalid page header in block 94206 of relation ...
2
votes
3answers
1k views

Replicating only significant records to specific database

I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can ...
3
votes
1answer
2k views

Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?

TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why? Context: We run postgresql 8.4, on CentOS 6, ...
2
votes
2answers
1k views

Postgres - Create table with foreign key constraint too slow

I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to ...
6
votes
1answer
674 views

PostgreSQL data type text vs varchar without length

In PostgreSQL you can create a column with data type character varying (without length precision) or text like this: ALTER TABLE test ADD COLUMN c1 varchar; ALTER TABLE test ADD COLUMN c2 text; Is ...
6
votes
2answers
506 views

PostgreSQL PITR restore

I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a ...
4
votes
1answer
1k views

Possible to keep max number of records in postgresql?

Basically part of our Postgresql table is used to keep server access logs, and as such sometimes during production this can get pretty large. is there any way of setting in postgresql to have a ...
2
votes
2answers
824 views

How do I set the external editor for PostgreSQL's psql client?

What do I put in ~/.psqlrc file to set the default editor? The editor I want to use (Textmate) is at /Users/username/bin/mate. I found the place in the docs where they talk about it, and tried ...
2
votes
2answers
177 views

Why is postgresql still consuming space on my other drive?

I am running postgreSQL 8.4 on Ubuntu 10.0.4. I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and ...
0
votes
1answer
78 views

Tightly coupling tables in postgresql

Our web-application is something like - users can create groups(request for membership of a group also), ask questions, answer etc. It is still in the development stage. What I want to do is tightly ...
0
votes
2answers
4k views

How to configure postgresql to use multiple cores?

How can I configure postgresql to use multiple cores in a single machine , is it automatically uses it ?
2
votes
1answer
567 views

Using pgsql_fdw in a function result “ERROR: cache lookup failed for type 0”

Now we want to use pgsql_fdw to select a table of remote postgresql database,When we select the table in a session it is okey ,but when we use the foreign table in a funciton it turns out "ERROR: ...
4
votes
2answers
6k views

Easy Postgresql master/slave replication with automatic failover on Linux?

I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the ...
1
vote
1answer
140 views

PostgreSQL Replication for small, low-usage database

I have PostgreSQL 8.4 installed on a headless Ubuntu 10.04 server. Sometimes the server is not available. I would like to continue working on the database, so I thought of installing PostgreSQL onto ...
2
votes
2answers
2k views

Trouble recovering data from corrupted PostgreSQL tables

I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble. When I do select count(*) One table returns: ERROR: could not ...
2
votes
2answers
255 views

Attacks on Postgresql listening for requests on 'localhost'

I am using jboss server for my servlets and the postgresql as the database server, listening on port 5432 on localhost. My jboss server is bound to a public IP. I want to know that since my postgesql ...
1
vote
1answer
1k views

Really slow DISTINCT ON query with multiple joins

Originally posted: http://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres The songs table has only about 4k rows, posts and stations have ...
3
votes
2answers
2k views

Postgres primary key sorted in the reverse order. Will it use the index efficiently?

My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed. My list views show records in the reverse ...
2
votes
1answer
927 views

Syncing two (or more) PostgreSQL 9.1 clusters

This question assumes the use of PostgreSQL 9.1. Say I have two PostgreSQL database clusters. Each cluster contains three PostgreSQL servers. A master, a slave synced via synchronous replication and ...
1
vote
1answer
431 views

Search for null in id column over all tables programmatically

I want to automatically check for null ID's over all id columns in my database, I have to check all tables of which there is about 50,000 so manually doing this is infeasible
4
votes
2answers
774 views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
1
vote
1answer
230 views

PG_bulkload utility postgresql need help

i am using COPY command to export the data to the out file with changes made to the data before export using option ST_asBinary() using command COPY ( SELECT st_asBinary(geom) FROM n_america ) TO ...
0
votes
1answer
709 views

Unable to reset the password of users in postgres

Evrytime i run, su - postgres it asks me for a password which I did not set. I simply installed the postgres. I have gone to the pg_hba.conf. # Database administrative login by Unix domain socket ...
4
votes
1answer
4k views

How to set Postgresql database to see date as “MDY” permanently

Ho can I set my database to see the 'date' as "MDY" without running: SET datestyle = "ISO, MDY"; every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system ...
6
votes
1answer
20k views

How do I reset the postgres password for PostgreSQL on Windows?

I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I ...
3
votes
2answers
2k views

How to create column in DB with default value random string

Can I create column in DB table (PostgreSQL) which have default value random string, and how ? If is not possible, please let me know that.
0
votes
1answer
175 views

What database do most mobile start ups use? [closed]

mongodb? MySQL? PostgreSQL? If someone would add pluses and minus, let me know. Why I ask: I am a mobile startup. I need a database that supports spatial indexes. The spatial index must be able to ...
3
votes
2answers
6k views

How do I copy databases from Postgres 8.4 to Postgres 9.1?

My trac server recently started having issues. It was running a very outdated version of Ubuntu, and I was unable to properly use apt-get to correct the issues I was having. So instead I backed up the ...
2
votes
3answers
5k views

Starting postgresql service and specifying data directory

I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install. It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific ...
3
votes
2answers
3k views

Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)

Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ?? Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
4
votes
1answer
407 views

timelines and history files PostgreSQL 9.1

What is the purpose of timelines, history files, and the setting 'recovery_target_timeline' in recovery.conf? The vague understanding I have from the PostgreSQL 9.1 documentation is when the slave ...
1
vote
2answers
187 views

Perf of query of large table by date?

I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like: entry_id (pk), sensor_id (fk), measurement_value, created_at I ...
5
votes
1answer
308 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 ...
1
vote
1answer
1k views

postgres composite index design

In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign ...
2
votes
3answers
2k views

Passing arguments to psql

I'm running a plpgsql script in Postgres 8.3 - I would like to pass arguments to this script via psql. I'm currently executing the script like: psql -d database -u user -f update_file.sql I came ...
1
vote
1answer
318 views

What is the “Data partitioned, Shared-Nothing, Parallel Database” solution for PostgreSQL ?

I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read ...