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)

6
votes
1answer
1k 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 ...
19
votes
9answers
40k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
3
votes
2answers
420 views

Pipe psql timing to file

I am currently using \timing on to do some simple performance testing in Postgres. I would like to run many queries and pipe the timing results to a file. However, all of the options I have tried ...
3
votes
1answer
2k views

Function/procedure to use dblink to fetch remote data and insert to multiple local tables

Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table. But now I was thinking of creating a ...
4
votes
2answers
2k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
1
vote
1answer
3k views

Drop multiple databases in one script

I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
1
vote
1answer
1k views

Postgresql: what to do about upgrading “main” cluster from 8.4 to 9.1 when a 9.1 “main” already exists?

I have an existing 8.4 cluster (named main) that I want to upgrade to 9.1. However, when I go to upgrade it, I get an error because 9.1 already has a cluster named main (apparently a default cluster ...
3
votes
1answer
1k views

How to rename a Postgresql cluster?

How do you rename a Postgreql cluster? I googled and found this old web page, which said just to change the name of the directory. I tried it but it didn't work: $ pg_lsclusters Version Cluster ...
1
vote
1answer
2k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
1
vote
1answer
256 views

Questions about PostgreSQL backup/restores

I've inherited a large PostgreSQL 8.4.7 database that is currently not being backup at all. The database itself I believe is around 100GB, I've been reading about the different methods to backup this ...
1
vote
1answer
460 views

Is PostgreSQL 9.2 autovacuum sufficient for a busy table?

I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database: CREATE TABLE ch02.markers ( ff_id SERIAL PRIMARY KEY, col1 boolean, col2 smallint, created_at timestamp, lat double precision, ...
2
votes
1answer
290 views

Problem with shortest distance calculation in PostGIS

While working with PostGIS pgrouting, for calculating the distance between two roads (lines) I'm using the shortest_path function. The logic is based on start_point(Start_id) and end_point(end_id) ...
1
vote
1answer
416 views

How to troubleshoot COPY FROM inserting less rows then expected?

I have a CSV (tab-delimited in fact) file with 7,590,051 lines, according to wc -l, which I want to import using COPY FROM. I removed the table's primary key, so it has no constraints. I ran COPY ...
2
votes
1answer
991 views

Default value of serial fields changes after restore

Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serial fields default values are trimmed out. For example: CREATE TABLE testschema.testtable ( id serial, ...
9
votes
4answers
10k 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 ...
1
vote
2answers
114 views

Trying to create PostGIS database

I am trying to create a spatially enabled PostGIS database by following the PostGIS documentation. In the short version, i executed, createdb yourdatabase createlang plpgsql yourdatabase But I did ...
4
votes
3answers
1k views

How to concatenate psql variables?

How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable. I've tried this: \set path ...
3
votes
2answers
4k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
9
votes
3answers
1k views

PostgreSQL Initial Database Size

There are 2 parts to my question. Is there a way of specifying the initial size of a database in PostgreSQL? If there isn't, how do you deal with fragmentation when the database grows over time? ...
0
votes
1answer
381 views

Drop and restore PostgresSQL database without losing settings

This seems like a simple, easy question, but I'm very new to PostgreSQL administration and I'm not sure of the best way to do things. I have a PostgreSQL (9.1) database that somehow got corrupted ...
7
votes
1answer
4k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role ...
2
votes
1answer
2k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
6
votes
3answers
3k 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
604 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
247 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
855 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 ...
3
votes
2answers
2k 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
269 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
360 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
444 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
5k 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
2k 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
198 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
491 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
253 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
291 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 ...
7
votes
1answer
1k 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 ...
7
votes
2answers
603 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 ...
3
votes
2answers
1k 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
190 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
84 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
5k 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
697 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: ...
5
votes
2answers
7k 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
142 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 ...