This tag is specifically for PostgreSQL version 9.2

learn more… | top users | synonyms

0
votes
1answer
334 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
1answer
513 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
669 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 ...
1
vote
1answer
729 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
2k 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 ...
1
vote
1answer
249 views

Postgres: Given some parent id's how can I SELECT X child records for each?

In Postgres, if I have : Parent Table id, name 1, Parent1 2, Parent2 ..... 5, Parent5 Child Table id, parent_id, name 1, 1, Child1 2, 1, Child2 ..... 6, 2, Child6 ..... 25, 5, Child25 so 5 ...
2
votes
2answers
139 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 ...
0
votes
0answers
95 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
1answer
150 views

Postgres sql dump import error

I'm facing a issue while importing the dump in my system, i'm getting the error as " Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;" and exiting with "out of memory" We had ...
1
vote
1answer
627 views

which postgres do i have installed? what directory needs to be in my path to run initdb?

I need to use postgresql with ruby on rails. I have these directories /Library/PostgreSQL and /usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2 This 2nd directory name is ...
1
vote
2answers
1k views

Recursive query using plpgsql

I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure. I have a data table and a table to link the data together: DATATABLE ----------- id ...
1
vote
1answer
128 views

clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server

After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1. Below is the actual command: ...
1
vote
1answer
3k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
0
votes
0answers
619 views

Install PostgreSQL 9.2 on Windows using WIN1252 encoding.

I had installed PostgreSQL 9.2 earlier and it always installed with the encoding being WIN1252 (the default database was WIN1252). I then some time ago reinstalled it with the encoding being UTF8 (I ...
0
votes
1answer
254 views

Postgresql Run 'Jobs' At Specific Times

I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute . Scenario : PostgreSQL 9.2 Need To Run A ...
4
votes
2answers
5k views

PostgreSQL user can not connect to server after changing password

I've met this with 4 roles I created: After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more. pgAdmin III show error message: An error has occurred: ...
4
votes
1answer
152 views

Materialized View PostgreSQL

Is there any possibility of having built-in materialized views in PostgreSQL ? I don't want Gardner version or somehthing like physical table populated by triggers. I've read that was/is something ...
3
votes
2answers
224 views

Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost ...
0
votes
0answers
441 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 ...
1
vote
1answer
1k views

Tuning Postgres config for new Database Server

I have finally gotten a Purpose Built Database machine for a project i'm working on. The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives. I have just got the database moved ...
0
votes
2answers
266 views

Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12

At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2? I checked their website and to my understanding it is not possible... I hope that's not the case. Can anyone ...
0
votes
1answer
339 views

slow queries - set weight to token type in tsquery - postgresql

Postgresql version 9.2.3! I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm ...
1
vote
1answer
2k views

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of ...
2
votes
1answer
116 views

Tools and methodologies to keep to DBs aligned [closed]

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 ...
0
votes
1answer
143 views

Can this array group count query be improved?

So I have the following query explain analyze with tags as ( select unnest(tags) as tag_name from tasks where user_id = 1 ) select count(9), tag_name from tags ...
4
votes
2answers
1k views

Struggling with pgBouncer

I've got Postgres 9.2 and PgBouncer (The version that came with the 'stack builder'). I have a .net application connecting via Npgsql version 2.0.12 on a win7 machine. My application can happily ...
3
votes
2answers
468 views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
1
vote
2answers
439 views

inet_client_addr in PostgreSQL don't return IP

i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return is '::1/128'. This is a problem with my script or with the server? CREATE OR REPLACE FUNCTION ...
0
votes
1answer
49 views

Basic Question on input to PostgreSQL

I have installed PostgreSQL on a Windows XP laptop so I can learn about using PostgreSQL. My problem is that the PostgreSQL tutorial starts of with the instruction To create a new database, in this ...
4
votes
1answer
816 views

How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
1
vote
1answer
497 views

Lost postgreSQL password in Windows installation

I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password. I looked at answers given to a similar question asked 6 months ago. The answer given there required the ...
0
votes
3answers
714 views

Postgresql 9.2 won't start after rerunning installer

All, I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that ...
4
votes
1answer
103 views

Postgresql, write archive file over mount, secure against simultaneous access?

I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share. I just use the basic command, given as an example in the postgresql.conf test ! -f /mnt/server/archivedir/%f && cp ...
2
votes
3answers
1k views

Cannot shutdown old postmaster when upgrading to Postgres 9.2

I'm upgrading to Postgres 9.2.2 (from 9.1.4). When I try to upgrade the DBs using: pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.2/bin -d ...
2
votes
1answer
403 views

Performance impact of setting PostgreSQL to log all statements

PostgreSQL 9.2.2 on Windows 7 64-bit. The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting ...
6
votes
3answers
3k views

What can I do with the new json field?

PostgreSQL 9.2 introduced the json field type. Why and when should I use it? What benefits does it have over a text field? I thought there were new query options available, however I haven't seen ...