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 ...
0
votes
1answer
182 views
Postgresql could not find pg_xlog
I'm trying to mount the WAL to a different disk. I stopped the server, and did the following:
(I'm using Fabric)
# Move pg_xlog to wals/path.dev/
dest_dir = "/home/ec2-user/%s/wals/%s" % ...
4
votes
1answer
209 views
Is there a repository for pgadmin plugins?
I've been using PostgreSQL with PGAdmin III for a while now and it's been bugging me that there is a plugins menu option that is empty. I've Googled some and found a plugin here and there but I wanted ...
1
vote
1answer
59 views
Table for optional parent/child relationship
Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.
The item can either belong to a parent or child and not both.
I have other tables that are similar to ...
2
votes
1answer
253 views
Violates foreign key constraint
I'm trying to implement the below represented UML and I can't figure out where is my mistake since I have already place a value into the table.
-- Schema: public
DROP SCHEMA IF EXISTS public ...
2
votes
2answers
105 views
Optimizing bulk update performance in Postgresql
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
0
votes
1answer
36 views
Storing no of players the last seven days in an online game
I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how ...
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 ...
1
vote
2answers
43 views
Dropping a group of schemas with similar name patterns
Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object.
In my case, I need to drop some schemas, all of the ...
0
votes
1answer
250 views
FATAL: terminating autovacuum process due to administrator command [closed]
I am running postgresql 8.4 on Ubuntu 10.0.4. I was in the process of creating schemas/importing data when I came across some error messages. Here is a snippet of the output at the console:
CREATE ...
1
vote
2answers
91 views
Getting SELECT to return a constant value even if zero rows match
Consider this select statement:
SELECT *,
1 AS query_id
FROM players
WHERE username='foobar';
It returns the column query_id with value 1 along with a player's other columns.
How would ...
2
votes
1answer
89 views
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is ...
0
votes
1answer
28 views
Cannot create PostgreSQL user
I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04.
Using the following StackOverflow question, I tried to create a user/role in postgressql:
...
1
vote
1answer
32 views
PosgreSQL: setting high work_mem does not avoid disk merge
This is not quite my day with postgres. On my server machine with PosgreSQL 9.2.3 I have set work_mem to 4MB to avoid Sort Method: external merge Disk: 2072kB but it did not help:
cwu=# vacuum ...
1
vote
1answer
103 views
Pattern similarity in time-series
I have a PostgreSQL database and my data-set include time-series of temperature for two stations. I would like to look their pattern's similarity in similar time sequences.
For station number 1:
...
0
votes
0answers
28 views
Should I keep pg_xlog on the same disk as data if using disk snapshotting?
We're running on EBS volumes on EC2. We're interested in leveraging
EBS snapshotting for backups. However, does this mean we'd need to
ensure our pg_xlog is on the same EBS volume as our data?
(I ...
1
vote
2answers
58 views
Creating a PostgreSQL SERIAL column using pgAdmin3
When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?
Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
0
votes
1answer
102 views
Usage of COPY FROM STDIN in postgres
I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org.
I understand the commands, since I previously ...
1
vote
2answers
27 views
Why PostreSQL 9.2 does not use index scan if it can?
My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.
Here is my table. The index I am talking about is name_left_prefix
cwu=# \d web_city;
...
1
vote
0answers
31 views
Failover - automatic switching between servers in Postgresql 9.1
I'm pretty new to Postgresql 9.1 and failover replication. We have a simple master-slave setup, for failover replication, which our application (client) is connected to. On a failure of the master, ...
1
vote
1answer
74 views
Get all partition names for a table
I want to list all the Partitions created by dynamic triggers in Postgresql 9.1
I was able to generate a count of partitions for tables which are partitioned using Frank Heikens' answer in ...
0
votes
0answers
24 views
Query Postgres For Combinations Where Both values are Unique to Each Other [migrated]
Confusing title, let's see if I can explain.
I have a large Database of Records. The values of interest are an EIN and A Registration Number.
There are lots of records for each value.
What I want ...
1
vote
2answers
47 views
“Rewinding” a Postgresql database
I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
4
votes
1answer
91 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
1
vote
1answer
48 views
How to allow a user to create databases only with a certain prefix?
We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must ...
4
votes
1answer
45 views
Is there better way to 'touch' a row than performing a dummy update?
I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:
...
0
votes
1answer
93 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY querry
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
2
votes
1answer
56 views
How to fine-tune Postgres for IO usage?
I have a Postgres database I am hosting in a quad core server with 8 GB of RAM.
At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When ...
5
votes
1answer
120 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 ...
1
vote
1answer
31 views
Postgresql socket not listening on the right one
I'm trying to get my postgresql to work.
It is clearly running, according to: ps aux | grep postgres | grep -v grep
I am getting -
username 5781 0.0 0.0 2441364 524 ?? Ss ...
0
votes
1answer
43 views
How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?
I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could ...
2
votes
2answers
67 views
PostgreSQL and default Schemas
Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:
Now from my understanding Schemas are like folders for organization, etc. So the ...
0
votes
2answers
29 views
Error installing pgcrypto extension [closed]
I want to install the pgcrypto extension on my postgresql installation on Windows. I found that I just have to call CREATE EXTENSION pgcrypto; but I've got an error :
Syntax error on « CREATE »
LINE ...
3
votes
1answer
115 views
Witnessing standby failure with repmgr
We're setting up a PostgreSQL cluster, and we're looking at using repmgr to handle standby promotion and so on.
Our cluster has one master and a hot standby, and uses the streaming replication ...
0
votes
2answers
69 views
Storing data in PostgreSQL: One table or two?
I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month.
Question: Because every product can have different ...
1
vote
1answer
35 views
Does PostgreSQL work well as a workstation-based DBMS?
I've been considering PostgreSQL for an application I am writing in .NET WinForms, and the app is intended to be standalone with occasional sync-ing with a web service. I plan to use Sql Server for ...
2
votes
1answer
20 views
Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?
I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache ...
1
vote
1answer
42 views
Postgres: Given some parent id's how can I SELECT X child records for each?
In Postgres, if I have :
Parent Table
id, name
1, Parent1
2, Parent2
.....
5, Parent5
Child Table
id, parent_id, name
1, 1, Child1
2, 1, Child2
.....
6, 2, Child6
.....
25, 5, Child25
so 5 ...
3
votes
2answers
167 views
Full Text Search With PostgreSQL
i have a table with this rows:
Stickers
------------------------------------------------------
ID | Title |Keywords (ts_vector)
------------------------------------------------------
...
0
votes
1answer
98 views
How to insert binary data into a PostgreSQL BYTEA column using libpqxx?
I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.
What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is ...
5
votes
1answer
370 views
Using MongoDB and PostgreSQL together
My current project is essentially a run of the mill document management system.
That said, there are some wrinkles (surprise, surprise). While some of
the wrinkles are fairly specific to the ...
0
votes
2answers
57 views
PostgreSQL stored procedure which can return one of two custom rowtypes
I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:
CREATE OR REPLACE FUNCTION ...
4
votes
5answers
6k views
How can I use a default value in a Select query in PostgreSQL?
I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?
E.g. ...
2
votes
2answers
55 views
Creating sequence for longer numeric values
I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API.
The value must be 20 digit long, numeric and unique. Best ...
1
vote
1answer
52 views
Equivalent of MRG_MYISAM in databases other than MySQL? [closed]
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?
I know about fragmenting, but this is not quite the same AFAIK. We're using ...
1
vote
1answer
25 views
How to get user-specific settings in PostgreSQL?
I have to collect some information of PostgreSQL roles. One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters ...
3
votes
0answers
47 views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
2
votes
1answer
64 views
PostgreSQL: Cannot change directory to /root
I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump.
Problem: However I'm getting the error could not change directory to ...
0
votes
1answer
45 views
PostgreSQL, How to keep only one schema?
I don't want some users be available to create schemas, and only use the public schema.
I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 ...
4
votes
1answer
64 views
Recovering a dropped table in PostgreSQL
I have :
CREATE TABLE ketoan_vn.Customers
(
CustomerId character varying(10) NOT NULL,
CustomerName character varying(150),
CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
);
I ...
3
votes
3answers
138 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
