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
33 views
PostgreSQL backup error
I am trying to backup my company PostgreSQL database using pgAdmin III, so I selected our company DB from the tree, then right clicked on it and selected 'Backup', selected the destination folder ...
3
votes
1answer
39 views
Is it safe to call pg_ctl reload while doing heavy writes?
I am running Postgres 8.4, and I would like to change the authentication method for a user. It's my understanding that I have to do this in pg_hba.conf, and then reload the configuration files with ...
2
votes
2answers
61 views
Changing PostgreSQL port using command line
My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).
I saw this solution before: http://stackoverflow.com/questions/187438/want-to-change-pgsql-port
...
0
votes
0answers
15 views
Checking the integrity of two database which are replica of each other
We have a database running on postgres. We replicate the data values in cloud in a customized way. Now we are looking for checking the integrity of certain tables of two databases. What are possible ...
1
vote
4answers
139 views
Daily and Differential Backups
Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:
The first time, make a full backup.
The second time, only the changes since the last ...
0
votes
0answers
36 views
Backup daily for MySQL, MariaDB or PostgreSQL [duplicate]
Good day:
I wonder if there is a program with which I can create daily backups with these requirements:
The first time, make a full backup.
The second time, only the changes since the last backup, ...
0
votes
0answers
23 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 ...
1
vote
4answers
124 views
Database design - People and Organisations
Databases are not my primary skill and I am finding I need some assistance please.
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.
I really ...
-1
votes
1answer
71 views
Use cases for hstore vs json datatypes in postgresql [closed]
In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts:
You can nest with json; you can't with hstore
...
6
votes
2answers
963 views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
3
votes
1answer
35 views
Why do psql queries take longer when inserting many rows. Why is it non linear
So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this:
INSERT INTO contacts (numbers)
SELECT distinct array[
(random() * 99999999)::integer,
...
0
votes
1answer
38 views
Postgres - Schema information within Trigger?
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).
Reviewing patterns for the following problem: I ...
1
vote
2answers
38 views
PostgreSQL: Read Committed Isolation Level
Quote from docs:
Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
3
votes
1answer
902 views
Postgres PgAdmin III Query Builder crash
I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem.
I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300.
...
4
votes
2answers
219 views
Is it ever a good idea to denormalize for integrity?
I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers.
These problems can take a variety of forms - they might be multiple ...
1
vote
1answer
29 views
Foreign Key to Multicolumn Primary Key?
I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried ...
1
vote
1answer
14 views
Big jump in search time for Postgres Index query for results with high selectivity
I am doing some performance comparison of databases and lucene for full-text searching.
So I use Postgres to create an Index for the data to search:
CREATE INDEX bodies_index
ON bodies
USING gin
...
0
votes
0answers
51 views
Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability
I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability.
I have 1 master and 1 hot standby configured to replicate the data from ...
0
votes
1answer
19 views
OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)
I would intuit that it's fine, but I just want to make sure there are
no gotchas from a recovery point of view:
If I were to lose my temp tablespace upon system crash, would this
prevent proper crash ...
1
vote
0answers
28 views
PostgreSQL datasource URL?
My first attempt to use PostgreSQL:
I have the elephant icon on the top right: running on port 5432.
I want to use LibreOffice as an interface:
Connect to an existing database: PostgreSQL
...
0
votes
1answer
38 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
6
votes
2answers
306 views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
1
vote
1answer
214 views
Behavior of PostgreSQL COPY FROM
Let me preface this question by stating that my experience with DBA is limited to an introductory undergraduate university course, and that I'm just getting started with PostgreSQL.
I would like to ...
8
votes
1answer
136 views
Best cloud platform for PostgreSQL
Currently, in our project, we use dedicated servers for our PostgreSQL database.
In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to ...
0
votes
0answers
79 views
Performance difference between MySQL and PostgreSQL for the same schema/queries
I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.
I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
2
votes
1answer
298 views
Ensure correct username when using pg_restore
I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this:
sudo -u postgres psql -c ...
0
votes
0answers
47 views
PostgreSQL: Slave doesn't replicate the Master's data
I'm using PostgreSQL 9.2.3 on both computers, both of which run Win2008R2. I've setup streaming replication by following this guide: ...
0
votes
1answer
178 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
206 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
56 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
245 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
87 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
92 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
41 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
249 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
81 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
86 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
25 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
31 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
52 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
69 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
26 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
26 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
69 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
86 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 ...
