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
7 views
Change built-in default privileges in PostgreSQL?
Introduction.
When I create a database,
postgres=# CREATE DATABASE test2 OWNER test2;
it is created with an empty privileges column:
Name | Owner | Encoding | Collate | Ctype | ...
0
votes
1answer
13 views
Profiling a PostgreSQL function [duplicate]
if I use
explain (analyze true, verbose true, costs true, buffers true)
select * from mystoredprocedurefunction(arg1, arg2);
I do not get any information about what the server is actually doing ...
0
votes
1answer
12 views
Query result with X rows discarded
In a transaction, if I select results to display to the screen, I get the message Query result with x rows discarded.
How can I view those results? Something is amiss with my query and I want to see ...
0
votes
1answer
25 views
Repairing Broken Binary Replication in PostgreSQL 9.0
I have a binary replication that was stopped for so long that the WALs were removed and as a result, it ended up being old. I'm trying to reestablish the replication and the best way I've found so far ...
0
votes
1answer
22 views
When does PostgreSQL write logs?
I turned on the configuration parameter log_planner_stats and would like to examine the details in log. But the log file is empty. It seems all the log files are changed at 12:00am of a day. Does ...
4
votes
2answers
53 views
Is using SUM() twice suboptimal?
I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise):
SELECT id,
sum(hours) AS totalhours
FROM mytable
GROUP BY id
HAVING sum(hours) ...
0
votes
0answers
10 views
postgres-xc: ERROR: cannot set transaction read-write mode during recovery!
I am trying to make a HA in postgres-xc After creating an environment of streaming replication I am trying to create a table in default database as coordinator logged in. But it gives me the following ...
2
votes
1answer
27 views
How to select nodes where all children is satisfied?
I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on.
...
0
votes
2answers
45 views
Can I limit an index to only contain the newest relevant row?
I have a table in PostgreSQL 9.2 that records login sessions called user_login_session. This table contains a user_id, start_timestamp and end_timestamp columns, amongst others. This table has started ...
0
votes
2answers
73 views
How can I increase performance for select in select statement
The item table is like this
id category_id name registered
I have to show total number of items from the beginning. Suppose between 2013-05-01 to 20-05--05, I have to count all the items ...
0
votes
1answer
17 views
Recover PostgreSQL password with admin access to host
Good day.
I have a PostgreSQL server.
Once i had admin login and password, then i created a new login for web application (with low permissions like select form some tables etc). And i just lost my ...
2
votes
2answers
27 views
Can stable (or immutable) functions call volatile functions?
The PostgreSQL documentation states:
Any function with side-effects must be labeled VOLATILE...
Consider the following function:
CREATE OR REPLACE FUNCTION count_items()
RETURNS integer AS
...
4
votes
1answer
47 views
How can I request a flush of the postgresql transaction logs?
I have the following problem: a "vertical" Linux distribution (Sophos UMT) comes with PostgreSQL 9.2 to store its configuration. Unfortunately, since the last update, it seems that the transaction ...
1
vote
2answers
49 views
Join on different types
In a database I have two tables:
The first has a field named taxonomy_id that is an integer
The latter has a field named ID that is a character varying
The two tables are related: if it exists a ...
0
votes
1answer
35 views
Upload (someone else's data) to Postgres, SQL Server [closed]
I work for a company that does program evaluations for state agencies. Thus, I frequently work with someone else's data. They send me the data via sftp:/, mail, etc. and I load it into either SQL ...
1
vote
1answer
32 views
Postgresql function to create table
I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:
CREATE OR ...
3
votes
1answer
21 views
Workaround the snapshot isolation in a writable CTE
The postgres docs say:
The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the ...
0
votes
0answers
29 views
Server doesn't listen
I try in Win7 by means of PGAdmin 3 to be connected to a host 192.168.1.8 with the virtual machine with linux. Already which time I receive the message
Server doesn't listen
The server doesn't accept ...
2
votes
2answers
43 views
Postgres 9.2 select multiple specific rows in one query
I have a table with three columns,
Id Serial.
Value real.
timein Timestamp.
I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
4
votes
1answer
46 views
How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?
Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the ...
1
vote
0answers
33 views
pg_restore and psql asks password for restoring dump file
This will be probably extreme novice question but I want to restore a .backup dump file. I have tried the following command:
pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v ...
0
votes
1answer
14 views
Postgres backup and WAL to S3
We are looking for a solution for the following problem:
We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage ...
1
vote
2answers
48 views
Approaches for deleting unnecessary records from tables
We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older ...
2
votes
1answer
21 views
Why does postgres use more space for a time with timezone than a timestamp with timezone?
The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face ...
2
votes
1answer
24 views
How to schedule PostgreSQL replication?
I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I ...
-1
votes
1answer
34 views
How to put one table on one disk and anther table on another (a ramdisk)?
I need to build a database with one table stored on one disk and anther table stored on a different disk (a ramdisk).
Is it possible to do it?
As far as I know, using two separate databases, one ...
1
vote
1answer
30 views
Postgresql - how to drop a trigger [closed]
A while ago I've created a trigger in Postgresql, update_user, which sets the update_date to now() for table users. Now I want to drop this trigger.
The following queries don't work:
drop trigger ...
1
vote
1answer
28 views
Moving postgresql data directory
I moved postgresql's data directory by following the following steps:
Stop postgres
cp -a source_data_directory destination_data_directory
export PGDATA=destination_data_directory
Changing data ...
0
votes
0answers
24 views
change data directory postgres with database cluster [duplicate]
I have my present database cluster of postgres at /mnt/my_hard_drive which I want to change to /home/myfolder. I also want to move all my databases present in the present cluster to /home/myfolder. Is ...
4
votes
1answer
47 views
LISTEN / NOTIFY privileges
I have a single postgres database, with two users; Alice and Bob.
I would like to be able to do a NOTIFY alice_channel 'sensitive data' without Bob being able to sneakily LISTEN in just by guessing ...
1
vote
0answers
33 views
How to Change location of postgres cluster and database within the same machine? [duplicate]
I have my present database cluster of postgres at /mnt/my_hard_drive which I want to change to /home/myfolder. I also want to move all my databases present in the present cluster to /home/myfolder. Is ...
0
votes
0answers
32 views
How to make continues cluster?
I have a report table with the following index:
providerid, date
The table is around 30M records and it grows about 100K rows per day.
I want to use in the index above as a cluster, but as I ...
0
votes
1answer
38 views
“relation does not exist” trying to import mysql dump into postgres
environment:
ubuntu 10.04
mysql server 5.1.69
postgres 9.2
Here's the sequence of steps:
created a new postgres database, myDatabase
executed this command: mysqldump -u root -p ...
0
votes
0answers
26 views
Gathering data from independent databases to a central one
I need to do the following. In a LAN there are 4 postgres boxes each having a small postgres database. These servers are gathering data from the internet and insert them as records in a table in their ...
0
votes
0answers
28 views
Performance of large queries on low bandwidth connections
I've been running some measurements on bandwidth needs and the effects of latency on query performance against Postgres 8.3 (waiting for management to approve upgrade) on Windows. SELECTs of various ...
1
vote
0answers
31 views
Performance of database with transactional data
There are lots of row modifications (several thousands of rows per minute) in our PostgreSQL database that stores transactional data. We have a problem because PostgreSQL runs vacuuming process and ...
0
votes
1answer
35 views
“Truncate” or “Delete/Vacuum Full” for deleting some of the table rows
I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided ...
0
votes
1answer
22 views
No way to set maximum retrieved rows in pgadmin query tool
I'm using version 1.16.1, and I can't find a "max(imum) rows" param to tweak. Any help would be appreciated.
1
vote
1answer
23 views
How un-clustered is a CLUSTER USING table
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres. Data accessed at the same time is "defragmented" into a small number of disk blocks:
# CLUSTER table USING ...
4
votes
3answers
68 views
PostgreSQL group roles
This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get ...
4
votes
1answer
58 views
How to get the information of the optimizer of PostgreSQL?
I am curious about the information like
total optimization time
the time spent on each stage of optimization
the number of alternative plans and
the cost of each alternative plan.
2
votes
1answer
22 views
psql coloured prompt misbehaving
In theory, there is a possibility to use coloured prompts in psql. I decided (not the first time :) that I need them, so I thought I would give them a try. So I have in my .psqlrc the following:
...
3
votes
2answers
132 views
Configuring PostgreSQL for read performance
Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow.
The primary key (constraint) structure is ...
-1
votes
0answers
15 views
How to connection PostgreSQL 9.2.1 with Hibernate [closed]
I have a blank Spring MVC project, and I've installed Hibernate and the PostgreSQL drivers using Maven.
I'm running short on complete tutorials that show how to connect PostgreSQL with Hibernate.
...
1
vote
0answers
25 views
Can't set up witness in Repmgr 2.0
I am trying to setup repmgr using the autofailover quick setup tutorial provided on Github Link
But I am kind of stuck on the witness section, when trying to create the witness with repmgr -d repmgr ...
0
votes
0answers
16 views
Streaming replication in postgresql 9.1 — sender and receiver processes aren't running
I've followed this guide, and cross-referenced the primary postgresql wiki in order to set up replication. I've configured postgresql.conf and pg_hba.conf as directed, and then restarted the master ...
0
votes
1answer
37 views
String replace using concatenated strings from various columns
I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order.
The specification says:
...
4
votes
1answer
29 views
How to simplify a nested SELECT with PostgreSQL arrays?
I'm trying to speed up and simplify a SQL query against an imported OpenStreetMap database (OSM). The database is stored in a PostgreSQL 9.2.4 server.
This OSM import features two particular tables, ...
0
votes
1answer
32 views
Pattern matching and substring replacement in PostgreSQL [closed]
I would like to do pattern matching and substring replacement in PostgreSQL with a "select-case-when-then-update-query".
Example:
tableA
field1 |field2 | field3
...
0
votes
1answer
30 views
How to install and configure Postgres-XC in windows?
Can anybody suggest me any resources of the complete installation procedure of postgres-XC in windows. I've installed postgresql-9.2 in my windows and can use it. Now I need to know the installation ...
