PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
0answers
739 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 ...
2
votes
0answers
57 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 ...
1
vote
1answer
752 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 ...
2
votes
0answers
179 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 ...
2
votes
0answers
71 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
483 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
709 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.
3
votes
1answer
312 views

How un-clustered is a CLUSTER USING table in PostgreSQL

I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL: # CLUSTER table USING index_name; # ANALYZE VERBOSE table; # CLUSTER VERBOSE; A maintenance task periodically ...
5
votes
3answers
1k 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
118 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
77 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: ...
8
votes
2answers
6k 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 ...
7
votes
3answers
302 views

Inconsistency in repeatable read

http://www.postgresql.org/docs/9.2/static/transaction-iso.html The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, ...
1
vote
0answers
1k 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
1answer
447 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: ...
5
votes
1answer
4k 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
330 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
491 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
153 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 ...
-2
votes
1answer
499 views

Use select query in stored procedure

I have some questions about stored procedures. 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 ...
0
votes
1answer
179 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 ...
18
votes
1answer
5k 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
707 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
232 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?
0
votes
1answer
712 views

createdb command error : could not connect to database [closed]

I am using the following command for creating database in postgresql. createdb -D pg_default -E UTF8 -h localhost -p 5432 -U pramil -W pramil mydb but this command results prompt for password.When ...
6
votes
2answers
2k 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 ...
2
votes
1answer
589 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
179 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 ...
0
votes
0answers
2k views

createdb: could not connect to database postgres: FATAL: could not write init file [closed]

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 ...
3
votes
2answers
528 views

General tool to load dump files [closed]

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
224 views

Restrict access for Windows administrator in PostgreSQL

Is there a way to protect PostgreSQL from user with Windows administrator rights? Like this question but for Postgres.
1
vote
2answers
233 views

Group Database Entries by time difference

I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
0
votes
1answer
128 views

Writing with multiple psql processes to database seems to damage data

I have a couple terabytes of CSV data that I am trying to import into a PostgreSQL 8.4 database (on a RedHat 6.2 server), whose data directory is initialized on a multipath hardware RAID. There are ...
2
votes
1answer
1k views

Is replication from SQL Server 2008 to PostgreSql possible?

Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
0
votes
1answer
1k views

pg_dump from 9.2 to 8.4 [closed]

I'm trying to restore a db dump from PostgreSQL 9.2 to PostgrSQL 8.4 (from separate machines, both are running Windows 7). I followed the instructions in this answer: Use pg_restore to restore from ...
3
votes
1answer
889 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 ...
3
votes
1answer
5k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
0
votes
2answers
28k views

Can't connect to the postgres server ls: /tmp/.s.PGSQL.5432: No such file or directory

*Update*I included an update at the bottom of this post with details from the logs explaining the problem that I haven't been able to fix. Following along with this Railscast ...
2
votes
2answers
2k 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 ...
1
vote
4answers
2k views

Daily and Differential Backups [closed]

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 ...
3
votes
1answer
1k 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 ...
2
votes
4answers
3k 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 ...
5
votes
1answer
318 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 ...
3
votes
1answer
123 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, ...
1
vote
1answer
1k 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 ...
0
votes
1answer
308 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
1answer
201 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
127 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 ...
1
vote
1answer
1k 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 ...
2
votes
2answers
1k 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 ...