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 ...
2
votes
1answer
18 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 ...
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 ...
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
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 ...
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 ...
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 ...
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 ...
-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 ...
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 ...
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 ...
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
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
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 ...
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 ...
0
votes
0answers
25 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 ...
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
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
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 ...
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 ...
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.
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:
...
1
vote
0answers
71 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
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.
...
1
vote
0answers
22 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
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 ...
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:
...
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, ...
0
votes
1answer
29 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
26 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 ...
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
votes
1answer
65 views
Use select query in stored procedure
I have some doubt about stored procedure.
Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast Or if have ...
0
votes
1answer
25 views
PostgreSQL created a PostgreSQL user on mac. Is this necessary?
I just installed PostgreSQL on a Mac (Mountain Lion) today.
The installer created a mac user PostgreSQL on my machine. After installation, the user still exists.
My question is that why postgreSQL ...
4
votes
1answer
93 views
How can I benchmark a PostgreSQL query?
I want to benchmark a query containing an user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?
I know that timing can be turned on with \timing ...
1
vote
1answer
19 views
How to monitor or do vacuum without stop all?
I have postgres9.1
I try to run full vacuum on a specific table , but it run for more than 5 hours.
I stop all process against this table and i cannot have the process down any longer.
Is there a ...
4
votes
1answer
29 views
How to view the full, flattened query underlying a Postgresql view?
If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?
4
votes
2answers
55 views
Seeting up a PostgreSQL database on another hard drive than my system one on OS X?
I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.
Is there a way to create new ...
0
votes
0answers
34 views
Encryption in PostgreSQL
Does PostgreSQL support data encryption features at transactional and the table level of database? If yes, how?
Oracle provides this via database and network encryption. Oracle provides encryption at ...
0
votes
1answer
19 views
Syntax error when trying to import database from two PostgreSQL databases
Following on from this prior question, I'm trying to export a database from one server and put it on another using phpPgAdmin. My process is like this:
select the database in server 1 and hit the ...
-6
votes
0answers
28 views
PostgreSQL test validators needed! [closed]
I'm looking for people to take a short multiple choice test on PostgreSQL. It should only take a couple of minutes. I just need testers and feedback on the questions or formatting.
You will need to ...
0
votes
1answer
74 views
createdb: could not connect to database postgres: FATAL: could not write init file
RedHat Enterprise Server 3.0 32 Bits
psql (PostgreSQL) 8.2.3
user: postgres
server is running:
/soft/postgres/8.2.3/bin/pg_ctl start
pg_ctl: another server may be running; trying to start server ...
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 ...
