This tag is specifically for PostgreSQL version 9.2
1
vote
0answers
30 views
postgresql replication - pg_stat_replication is showing empty columns
I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list.
$ ps aux ...
0
votes
0answers
25 views
Error: Unable to write inside TEMP environment variable path
I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1.
I get the following, rather famous it seems, error:
There has been an error.
Unable to write inside TEMP ...
1
vote
1answer
19 views
Parsing the value and copying it to another column in postgresql
column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, ...
2
votes
1answer
67 views
Finding by ID in postgresql very slow on big table
I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is ...
0
votes
0answers
29 views
Getting multiple queries with “show transaction isolation level” in pg_activity
I am using PostgreSQL server for my production use.
When I fire a query like
select * from pg_stat_activity
on my server, I am getting 98% of queries like
SHOW TRANSACTION ISOLATION LEVEL
and ...
0
votes
0answers
39 views
Can't connect to service after updating ODBC driver
I am upgrading a VB6 program at work and one of the changes is that it now uses PostgreSQL 9.2.4 instead of 8. I was getting a 'client encoding mismatch' error, so I updated the ODBC driver, and the ...
0
votes
0answers
17 views
postgres-xc : Address already in use
I am starting a postgres-xc server with two datanodes and one coordinator.
I used same parameter for both nodes but from one node I am getting the following error:
LOG: could not bind IPv4 socket: ...
0
votes
0answers
18 views
postgres-xc - ERROR: object already defined [closed]
I am configuring two data nodes and a coordinator on postgres-xc. I get eh following error:
ERROR: PGXC Node datanode1: object already defined
CREATE NODE
pgxc_pool_reload
------------------
t
(1 ...
1
vote
1answer
52 views
Client Encoding Mismatch when creating new database
I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run ...
0
votes
1answer
87 views
Running a UPDATE on 3 million records
I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table ...
0
votes
0answers
28 views
Login in pgAdmin without password (windows 7) [duplicate]
I want login in pgAdmin without user password (I forgot password)
As I konw, I can change pg_hba.conf so, that can be possible to login into pgAdmin without password.
Can you tell please what must ...
1
vote
1answer
30 views
Show clients with staff assigned and no staff assigned
I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query:
SELECT g.name AS group, cl.name_lastfirst_cs AS client
FROM clients ...
1
vote
1answer
45 views
Setting up binary replication between two PostgreSQL instances
I have two virtual machines: dbrepa (-.175) and dbrepb (-.191).
Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone.
I need to have the ...
0
votes
1answer
112 views
Create Language plperl - Error: could not load library plperl.dll
When I create language plperl , I get error: ERROR: could not load library "C:/Program Files/PostgreSQL/9.1/lib/plperl.dll": The specified module could not be found.
But in my computer, ...
0
votes
1answer
55 views
Is PostgreSQL appropriate for processing this large but simple dataset? [closed]
I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y ...
-3
votes
1answer
58 views
Make “NOT NULL” default in Postgresql 9.2 [closed]
I use NOT NULL a lot when creating columns.
Is there a way to make this default when defining columns? In other words, make column is_nullable default to NO. (I realize this would make it harder ...
2
votes
0answers
61 views
PostgreSQL index array of int4range using GIN / GIST - custom operator class
Here is my table:
CREATE TABLE
mytable
(
id INT NOT NULL PRIMARY KEY,
val int4range[]
);
I want to index the val column:
CREATE ...
0
votes
1answer
29 views
changing server roles in streaming replication
I want to implement streaming replication using postgresql-9.2.4 in my reporting application. I came stuck on the below scenario.
create master and slave with synchronous replication which is ...
4
votes
2answers
143 views
PostgreSQL CREATE TABLE creates with incorrect owner
I'm using PostgreSQL 9.2.4. When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly ...
1
vote
0answers
33 views
Do I need to perform any tasks after Postgres goes into recovery mode?
For starters, I do not have any sort of automated failover in place.
After two scenarios, I'm unsure of the state of the database and any required actions, if any, to take:
My master server ...
2
votes
1answer
73 views
I need help understanding Postgres's archive cleanup functionality
I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform ...
1
vote
2answers
40 views
Constraint on Composite Type
How can I create a constraint on a sub-field of composite type?
Pseudocode
create type axis(
major_axis float,
minor_axis float,
angle float constraint angle_constraint check(angle ...
3
votes
1answer
130 views
Group By primary key or DISTINCT increase query time over 1000x with limit
Also see http://stackoverflow.com/questions/17741167/hibernate-jpa-improve-performance-of-distinct-query but I realized this is mainly a PostgreSQL issue.
My application uses a 3rd party extension to ...
1
vote
1answer
66 views
Why does Postgres generate an already used PK value?
I'm using Django, and every once in a while I get this error:
IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
DETAIL: Key (id)=(1) already exists.
My database ...
0
votes
1answer
38 views
Wrong return results
I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
0
votes
1answer
36 views
pg_upgrade fails with lc_ctype cluster values do not match
I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X.
When trying to upgrade the database I ...
4
votes
1answer
153 views
Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?
The pg_xlog directory for my master cluster looks like this:
-rw------- 1 postgres postgres 16M Jun 21 21:42 000000010000000000000001
-rw------- 1 postgres postgres 16M Jun 21 22:42 ...
1
vote
1answer
52 views
How does Postgres know which logs have been archived?
I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave ...
0
votes
1answer
263 views
How to retrieve data from Postgresql index using java
I am writing an application for searching, using text search of Postgresql. I am a beginner in Database. I created a Gin index and i am trying to get data from it. My code is
import ...
5
votes
1answer
101 views
PostgreSQL “freeze”/“unfreeze” command equivalents
In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply ...
3
votes
2answers
66 views
PostgreSQL: Executing DDL on every schema
I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my ...
1
vote
1answer
114 views
Why does `pg_lsclusters` not list my Postgres cluster?
I apt-get installed Postgres 9.2.4 (using PPA).
I used pg_dropcluster --stop 9.2 main to remove the default database cluster, because I intended on creating a new cluster on a separate SSD volume (a ...
0
votes
0answers
155 views
How to import a text file with '|' delimited data to PostgreSQL database? [closed]
I have a text file with | delimited data that I want to import to a table in PostgreSQL database. PgAdminIII only exports CSV files. I converted the file to a CSV file using MS excel but still was ...
0
votes
0answers
128 views
PostgreSQL 9.2.4 (Windows 7) - Service won't start, “could not load pg_hba.conf”
I am trying to get Postgres 9.2.4 to run as a service on Windows 7. After installing postgres, the service was running fine. However, after setting postgres up as a server for another program, the ...
0
votes
0answers
63 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 ...
1
vote
1answer
155 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
29 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 ...
2
votes
2answers
201 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
68 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 ...
0
votes
0answers
34 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
1answer
128 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 ...
2
votes
1answer
103 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
300 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
...
0
votes
1answer
274 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 ...
1
vote
2answers
268 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
100 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
116 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
45 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
63 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 ...
0
votes
1answer
303 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 ...
