PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
1answer
539 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
0
votes
1answer
70 views

Faster joining on 'most recent' related rows

I have a bug tracking application. The bug table stores very little, because every state change to a bug happens by adding a comment. For example, the current state of a bug is determined by the state ...
4
votes
2answers
726 views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type ...
1
vote
2answers
202 views

Slow access to table in postgresql despite vacuum

I'm running PG 8.4 I had a table with about 20K records. Any query accessing this table with more than 1 record (e.g. joins) would be very very slow. Even a count would take like 20 seconds. Stats ...
3
votes
1answer
1k views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
1
vote
1answer
151 views

PostgreSQL Hot Standby

I have two PostgreSQL servers running on my local Ubuntu machine (port 5437 is master, port 5438 is standby). While following the tutorial on http://wiki.postgresql.org/wiki/Hot_Standby I get this ...
3
votes
2answers
607 views

Why did PostgreSQL choose an elephant as it's logo?

Why did PostgreSQL choose an elephant as it's logo? It wasn't clear from logo wiki. UPDATE: Now wiki updated :) Thanks!
3
votes
2answers
516 views

Problems executing pg_ctl during pg_upgrade from 9.0 to 9.1 on Windows

I'm trying to upgrade my 9.0 PSQL to 9.1 on Windows Server 2003. I've installed 9.1, stopped both services, and generally followed all steps on this guide. When I actually run the command, however, I ...
2
votes
3answers
4k views

Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?

I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default ...
3
votes
2answers
4k views

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of ...
3
votes
1answer
546 views

Database encryption and key management with pg_crypto

I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database. The reason is that local logins will sometimes be required to ...
2
votes
1answer
122 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
1
vote
1answer
114 views

Does PostgreSQL optimize queries in transaction?

In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
-1
votes
1answer
237 views

Undefined reference to my function [closed]

I'm working on a project on postgresql 8.4 server side. I'm adding some functionality in order to store some query informations. I need to call two functions (start_create_profile() and check()) ...
1
vote
3answers
290 views

Altering same table across multiple databases

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases. I am not sure I know what the right procedure for this ...
2
votes
2answers
2k views

issues with encoding and pg_dump/restore between windows and linux

I have a dmp file I have created using pg_dump encoded in WIN1252. What I would like to do is import it using pg_restore to a database on a Linux machine. The problem is that on the windows machine ...
1
vote
0answers
233 views

Connecting a GUI to the user-created function in PostgreSQL [closed]

I have been given a task to create profiles for the applications. The Database that we use is Postgresql. I've defined some function that will give me those profile and I've added them to the ...
0
votes
1answer
489 views

trying to start a newly created cluster in postgresql on windows

I just created a new postgresql cluster in postgres 8.3 and looks like everything worked. What I am having issues with is trying to start the server of this new cluster. My two clusters are on ...
2
votes
1answer
169 views

Postgresql server causing a lot of IO activity

I have a postgresql server running on a small linode partition. It is running a web app for a site which gets on average 0 users / minute (it has not launched yet). At one point, I loaded up the ...
0
votes
0answers
208 views

PostgreSQL can't start after tuning some options

I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this ...
1
vote
1answer
101 views

Partition of tablespace [closed]

My postgres server is running in a windows server and three tablespace are created. Postgresql server is installed in C: drive and the tablespaces are in D: and E: drive. So how to take a backup and ...
6
votes
1answer
371 views

Storing logs on per day basis in PostgreSQL

Requirements here are to store historical logs. For the sake of simplicity I'll assume this sample scenario where we are selling some products on our website and we need to keep records of sales of ...
1
vote
1answer
2k views

PostgreSQL server log connection message [closed]

I'm seeing the following message in PostgreSQL server log: LOG: could not receive data from client: No connection could be made because the target machine actively refused it. There are ...
0
votes
1answer
203 views

Postgresql: Return Unique Rows

I'm using postgresql and I have a large database. A simplified version of the datastructure for my table looks like this: id (primary unique key), data, userID 1, foo, 4 2, foobar, 4 3, lala, 5 4, ...
2
votes
1answer
3k views

Most efficient way of bulk deleting rows from postgres

I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions ...
2
votes
1answer
229 views

Postgresql Two-phase Commit Transaction Identifier

Is it possible to perform a two-phase commit in Postgresql when the databases exist on the same server? As a simple test, I would connect to the first database and run: BEGIN; PREPARE TRANSACTION ...
0
votes
1answer
156 views

Can this array group count query be improved?

So I have the following query explain analyze with tags as ( select unnest(tags) as tag_name from tasks where user_id = 1 ) select count(9), tag_name from tags ...
4
votes
1answer
424 views

Safe way to stop/abort REINDEX

I am running a REINDEX but it takes long time and still working. The application using the DB is not responding right now. Is there a safe way to stop it and do it another time? PostgreSQL version is ...
0
votes
2answers
392 views

Starting a Postgres slave that is in hotstandy - stuck after start

I have a postgres 9.1 hot-standby that has been down for a few days and having problems starting back up. When I try to restart the server I see the following: -sh-4.1$ /usr/local/pgsql/bin/pg_ctl ...
0
votes
1answer
395 views

Permission Denied Copy [closed]

I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS. copy tname from '/Users/Shared/folder/file.cxv' ...
0
votes
1answer
1k views

Moving data between Oracle and PostgreSQL

For a future project I need to use the following setup: Some small (offline) data boxes (colleting data over the time) fill PostgreSQL databases. (Summing up to about 1 TB a year. For time to time, I ...
3
votes
1answer
275 views

PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files?

I have a PostgreSQL 9.1 hot standby slave that had all of its wal files removed. How can I restart the standby server and have the slave resync from the master? Thanks
1
vote
2answers
1k views

Postgresql - Segmentation fault (core dumped)

I'm working on postgresql code (server side). I installed a version from source code following the official tutorial: http://www.postgresql.org/docs/8.4/interactive/install-short.html I did same ...
7
votes
1answer
1k views

Is PostgreSQL replication production ready?

How does PostgreSQL native replication compare to MySQL? I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects? ...
2
votes
1answer
221 views

INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

I am trying to write my first trigger. It is an insert trigger as follows: <b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
1
vote
1answer
585 views

Slow queries to remote PostgreSQL db

I am running a Drupal site that is backed by Postgres. This is my first time running something with Postgres; I come from the MySQL world. For doing dev work on the site, I connect to a VPN and ...
1
vote
1answer
238 views

Efficient query plan for selecting all data duplicated on several columns

The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well. The purpose of the query is to show all ...
0
votes
1answer
736 views

Postgres 9.1 statistics in pg_stat_database

I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be ...
3
votes
1answer
130 views

Aggregating statistics from child rows

I have a parent row and then many child rows, and I must aggregate statistics from these child rows. For a more concrete example let's imagine I have one round of golf (parent row) and then child ...
1
vote
2answers
2k views

I accidentally set password to blank for a PostgreSQL database, how do I change it?

When I set the password to blank, it didn't give any warnings. Now when I try to log in with a blank password, it gives me an error "fe_sendauth: no password supplied". So now I can't log in - any ...
4
votes
2answers
2k views

Struggling with pgBouncer

I've got Postgres 9.2 and PgBouncer (The version that came with the 'stack builder'). I have a .net application connecting via Npgsql version 2.0.12 on a win7 machine. My application can happily ...
3
votes
1answer
448 views

Variable substitution in psql when using \copy

I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that: The ...
2
votes
2answers
263 views

Why one database is so much slower than other?

The same query on postgresql takes 9 min on one database, on other only 5 sec. I use the same postmaser, the same user. I connect directly with psql. There seems to be no differences in database ...
1
vote
1answer
395 views

Something wrong in pg_hba.conf

this my configuration: #postgres.conf: listen_addresses = 'localhost' #pg_hba.conf: local all postgres md5 local all appuser trust local all devuser trust local all all peer i'm connecting in ...
3
votes
1answer
528 views

Can't understand results of pg_stat_statements view

I configured my postgresql.conf (my version is 9.1) with the normal persistent configuration: shared_preload_libraries = 'pg_stat_statements,plperl' custom_variable_classes = ...
4
votes
1answer
293 views

Does the order of fields in SELECT query matter when using composite indexing?

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index? For example, if I ...
3
votes
2answers
696 views

PostgreSQL: Can the archive command for master and standby point to the same directory?

I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server. The only difference in the files is the ...
7
votes
3answers
23k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
7
votes
3answers
1k views

Store a formula in a table and use the formula in a function

I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
5
votes
1answer
141 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 ...