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 ...
4
votes
1answer
23 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
0answers
72 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 ...
0
votes
2answers
31 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
30 views
Upload (someone else's data) to Postgres, SQL Server
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
28 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
17 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
24 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 ...
3
votes
1answer
85 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
25 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
44 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
2answers
46 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 ...
4
votes
1answer
74 views
General tool to load dump files
I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv ...
1
vote
1answer
158 views
postgis problem with shortest distance calculation
while working with POSTGIS pgrouting, for calculateing the distance between two roads(lines) i got the shortest_path function.
But the logic is based on Start_point(Start_id) and end_point(end_id) ...
2
votes
1answer
18 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 ...
1
vote
0answers
26 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
0answers
9 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 ...
2
votes
2answers
2k views
PostgreSQL not working, pgAdmin always says server not listening
I am brand new at this so maybe it's a simple mistake. I was given a very large .sql file with the instructions to make a copy of a database locally on a computer. I was told "to be able to install ...
2
votes
1answer
19 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 ...
4
votes
2answers
129 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
2answers
93 views
Different queries result in big difference in performance
I have 2 similar functions, different way result in big diff in performance.
The PostgreSQL version: 9.2.1.
Function 1
create or replace function func_1() returns text as
$$
declare
v_i ...
1
vote
2answers
47 views
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 ...
0
votes
1answer
33 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:
...
1
vote
1answer
22 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 ...
0
votes
0answers
27 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 ...
4
votes
1answer
43 views
force PostgreSQL into recovery mode
I have a somewhat strange question.
Is there a way to manually force PostgreSQL into recovery mode?
I've not been able to find aything in the documentation.
I've been working with a script ...
1
vote
1answer
31 views
Can pg_hba.conf trust processes?
Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?
0
votes
2answers
125 views
A stored procedure to bulk load in Postgres or Oracle given a URL
Is there a stored procedure available in Postgres or Oracle that does a bulk load
given a URL, as in:
url_bulkload csvFile.ctl
Where csvFile.ctl is
#
# csvFile.ctl -- Control file to load CSV ...
4
votes
3answers
61 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 ...
-1
votes
1answer
32 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 ...
0
votes
1answer
69 views
PostgreSQL server log connection message
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 ...
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 ...
1
vote
1answer
29 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 ...
3
votes
0answers
41 views
What happen when Postgresql tablespace is null?
I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s.
Long story short, when I check the disk usage per tablespace, I get different distribution ...
1
vote
1answer
25 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 ...
4
votes
1answer
46 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
30 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
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 ...
0
votes
1answer
30 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
32 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 ...
8
votes
6answers
11k views
PostgreSQL is running locally but I cannot connect. Why?
Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
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
29 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 ...
1
vote
1answer
20 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:
...
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.
4
votes
1answer
24 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, ...
4
votes
1answer
46 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.
0
votes
0answers
15 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
0
votes
0answers
14 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 ...
-1
votes
0answers
14 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.
...
