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
1answer
121 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 ...
0
votes
0answers
32 views
Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs
I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs.
All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
3
votes
1answer
282 views
How many queries per second is my Postgres executing?
How can I tell how many queries per second my Postgres database is executing?
5
votes
1answer
409 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 ...
-3
votes
2answers
176 views
Versioning data in Databases [closed]
Is there any ready mechanism in Oracle, SQL Server, PostgreSql or OR framework (C#), which can version information in a database? By versioning informartion I mean that e.g. I have table Person (id, ...
1
vote
1answer
65 views
How to view the current settings of Autovacuum in Postgres?
I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.
Postgres 9.1 is the version I'm most interested in.
0
votes
1answer
69 views
Difference between return next and return record
What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
2
votes
1answer
170 views
returning set of records in stored procedure
I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:
create or ...
1
vote
0answers
131 views
How to calculate running total based on dynamic values?
I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
1
vote
1answer
298 views
Declare variable of table type in PL/pgSQL
I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:
q1 = select * from foo;
q2 = select * from bar;
...
0
votes
0answers
137 views
Postgres error ( XX000: invalid memory alloc request size 4294967290 )
I'm getting the following error in my postgres 8.4 database:
XX000: invalid memory alloc request size 4294967290
Operating System : Windows 7 64 bit
Memory : 8GB
Database Version: Postgres 8.4 ...
2
votes
0answers
179 views
“custom archiver out of memory” error when restoring large DB using pg_restore
I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
2
votes
2answers
66 views
String handling in postgres 8.4
This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist).
I thought I ...
1
vote
1answer
113 views
postgres GROUP BY and ORDER BY problem
I have two tables like this:
CREATE TABLE cmap5 (
name VARCHAR(2000),
lexemes TSQUERY
);
and
CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn (
cid int NOT NULL ...
2
votes
3answers
95 views
PostgreSQL 7.4 data corruption
We're dealing with the following situation (PG version is 7.4.30): somehow, a DB has gone missing from pg_database (psql \l listing doesn't show it anymore). More, while you can still \c to it, a \d ...
1
vote
2answers
55 views
Dumping and restoring on db server - postgres
Is there a way to execute a backup and restore of PostgreSQL DB from App server without calling pg_dump from App server and executing pg_restore also on App server. In this way I'm executing ...
4
votes
1answer
746 views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
1
vote
1answer
132 views
How to get CPU usage of PostgreSQL server?
I'm reading about PostgreSQL monitoring tool. I get a lot of information like database size etc. but can't find info about CPU and memory usage.
Are these informations provided by PostgreSQL server or ...
5
votes
3answers
112 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 ...
1
vote
1answer
60 views
Single column join vs multicolumn join
In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected.
In one scheme a surrogate key is used which means that in the join condition ...
0
votes
0answers
85 views
Truncate table is taking too long in PostgreSQL
I have many databases on the same server, all with same templates. When I execute truncate command on exceptions table in each database, it works fine and executes immediately but on database named ...
1
vote
3answers
90 views
psql asks for password, CREATE USER didn't specify one
I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER ...
1
vote
2answers
80 views
Display user-defined types and their details
I've created a few new UDTs in PostgreSQL. However, now I have two problems:
how to see which UDTs have been defined?
how to see the columns defined within these UDTs?
Unfortunately, I couldn't ...
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 ...
4
votes
3answers
89 views
Filtering data that could have more than one category
I have a table like the following:
Annotation (
document,
term,
category
)
Where document and term are some ID, while category is an integer.
The couple document - term is not unique, ...
1
vote
2answers
29 views
Postgresql: type_modifier in UDT
In the manual for creating UDTs in postgresql I found the following statement:
PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this ...
0
votes
1answer
96 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
46 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
115 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 ...
0
votes
2answers
59 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
98 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 ...
0
votes
0answers
43 views
PostgreSQL Hot Standby
I have two PostgreSQL servers running on my local Ubuntu machine (port 5437 is master, port 5438 is standby).
When I followed the tutorial on
http://wiki.postgresql.org/wiki/Hot_Standby
I get this ...
3
votes
2answers
204 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
1answer
102 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 ...
1
vote
3answers
166 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 ...
0
votes
1answer
173 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
86 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 ...
3
votes
1answer
84 views
Tools and methodologies to keep to DBs aligned
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
84 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, ...
0
votes
0answers
31 views
High memory consumption using Bucardo tool
I am using Bucardo tool on PostgreSQL 9.0 with RHEL-6 as OS. Suddenly I have encountered high memory consumption and the database is running slow. So I had stopped and restarted PostgreSQL; this ...
-1
votes
1answer
83 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
88 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
1answer
172 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
112 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
46 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
36 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
43 views
TPS including connection establishing
I have recently upgrated my DB machine - mainly upgrated disks from 10k to 15k and RAID 5 to RAID 10. I have done some pgbenchmarking:
old db benchmark:
tps = 608.115370 (including connections ...
0
votes
0answers
51 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
47 views
Partition of tablespace
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 ...
4
votes
1answer
121 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 ...
