This tag is specifically for PostgreSQL version 9.2

learn more… | top users | synonyms (1)

0
votes
2answers
17 views

Why Postgresql 9.2 word boundary not working!

In my postgresql version 9.1 this query is working fine: select id,name from flash_card_set where name ~* '\yACT\y' and matching: text ACT txt,ACT tests,tests ACT But in my postgresql version 9.2 ...
0
votes
1answer
17 views

PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

I have created DB instance on RDS and used below function it gaves error while with same postgreSQL version on local development machine works fine. create or replace function uuid() returns uuid as ...
1
vote
1answer
38 views

Problem with LISTEN - NOTIFY mechanism in PostgreSQL

I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism. I open two consoles and connect to my database using psql client: psql -U postgres test_db In the first console I subscribe to some ...
0
votes
0answers
11 views

PostgreSQL-9.2 Error while restoring Database through Ant

I am using PostgreSQL-9.2 and I have created a function in one database . While restoring this database through ant ,Error is thrown from the function as syntax error at or near "VARCHAR" Where: ...
1
vote
0answers
9 views

Restore postgresql database from remote sql file

I would like to know if it's possible to restore a database from a remote sql file. The problem: - Working with PostgreSQL 9.2.4 - I have an small remote server with 10GB left in storage - I have a ...
2
votes
1answer
41 views

Diagnosing the error in a Postgresql to Redshift migration through psycopg2

I am running a job class that contains the following: A Postgresql connection that can issue SQL statements A Redshift connection that can do the same an S3 connection to function as an intermediary ...
0
votes
1answer
31 views

PostgreSQL replication with periodic log shipping

I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally ...
0
votes
0answers
21 views

Error while inserting values in array in Postgresql

I am writing a Postgresql function that saves some Ids information into the array and later on processing them one by one. I am new to postgresql and finding some difficulties to work with arrays ...
2
votes
1answer
43 views

pg_upgrade and tablespaces (postgres 9.2 to 9.3)

I have a 9.2 installed with tablespaces sitting at: /var/lib/postgresql/9.2/main and /opt/postgres/9.2/second And unfortunately they are on different filesystems. This is a Debian package ...
0
votes
1answer
27 views

Need some help with this query or some ideas for change it to better model

I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can ...
0
votes
1answer
22 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
2
votes
1answer
52 views

How to determine if postgres database contains GiST indexes and what type?

Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?
1
vote
1answer
123 views

Postgres Performance Over Group by with MAX and MIN

I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system. Table details : CREATE TABLE customer_location ( id UUID, location_id ...
0
votes
2answers
89 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
1answer
14 views

PostgreSQL 9.2 EBS Snapshot on AWS

I found this post here back from 2011, and wanted to verify that it's still valid and good advice to take a hot snapshot of an ebs volume (PostgreSQL still running) so long as you're snapping all of ...
1
vote
0answers
66 views

Where Can I find *Current* PostgreSQL advice for a SQL Server DBA? [closed]

Been a MSSQL DBA for about 7 years. I have the fundamentals of performance, backups, replication, security and design down for MSSQL. I'm not setting up new applications using PostgreSQL 9.2. I have ...
0
votes
0answers
77 views

On Heroku in production, how can I copy database A to database B on command?

From my local terminal, I can use the following commands to do what I need to do: heroku pgbackups:capture --expire heroku pgbackups:restore charcoal --confirm max-kiosk However, I want to do this ...
0
votes
1answer
51 views

Creating unique constraint to be validated from input

I have this table name tbl with this table definition: create table tbl ( id serial primary key not null, counter integer not null default 1, ref_id integer not null, date_booked date ...
0
votes
0answers
32 views

How to increase the size of the tablespace in Postgresql?

I have created a tablespace in PostgreSQL with name abc and location of the tablespace is /opt/PostgreSQL/9.2/data/dbspace,Now i want to increase the size of the tablespace is it possible in ...
0
votes
1answer
41 views

PostgreSQL: After restoring data, unable to use Views (permission denied)

I'm attempting to get a PostgreSQL environment running locally. I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment. The restoration worked fine ...
1
vote
1answer
56 views

database not restoring from dump file

I'm running the following command to create a dump of all database on my production server: prodserver:/# pg_dumpall -U postgres -h 1.1.1.1 > prod.sql on my development box, I also take a ...
1
vote
2answers
130 views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
1
vote
1answer
33 views

Limit balancing results by a column

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed. I also have a function that returns me the ...
3
votes
1answer
340 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
2answers
51 views

Get distinct results based on column and satisfy condition

I have the below select which is selecting from a table with the columns: id, project_id, keyword_id, position, Data example: i, p_id, k_id, p 1, 1, 1, 4 2, 1, 1, 5 3, 1, 1, 6 5, ...
2
votes
1answer
68 views

Postgresql AUTOVACUUM ANALYSE parameters for a specified table

I would like to set autovacuum parameters for a specified table. My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete. This table has only 2 columns (including the ...
1
vote
1answer
223 views

Upgrading using pg_upgrade on windows fails

When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails: RUNAS /USER:postgres "CMD.EXE" ...
3
votes
2answers
114 views

PL/pgSQL fuction to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
2
votes
1answer
200 views

Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

Steps to reproduce Create database CREATE DATABASE citiesdb WITH OWNER = citiesowner ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' ...
1
vote
1answer
474 views

Migrating Postgres from 9.2 to 9.3

I have 2 CentOS servers, one is running PostgreSQL 9.2 and the other, 9.3. I'm trying to migrate the database but I'm getting 'relation does not exist' errors. What I did was on the 9.2 server: ...
0
votes
1answer
95 views

How to set isolation to SERIALIZABLE DEFERRABLE for a whole Postgresql database? [closed]

I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore. In fact, I would like to force the ...
2
votes
0answers
83 views

2 versions of Postgis installed

For some reason I manage to have 2 versions of postgis installed at the same time due to a wrong update. I can see this as by issuing a select postgis_full_version() i get back version 2.1.0 ...
1
vote
1answer
161 views

INSERT using results of CTE INSERT to provide unique id values

I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as ...
0
votes
0answers
31 views

Postgresql replica lagging behind

I restored a master database after a fallback by creating a new basebackup and making the promoted slave stand_by again. Per the logs everything is fine, however the following functions are not ...
0
votes
1answer
39 views

Postgresql: How to add a role that inherits from another?

Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?
1
vote
0answers
55 views

Postgresql 9.2 - Auto vacuum running continuously for 18+ hours

I currently have a table that is updated daily (easily 20%+ rows are updated, all floats/integers) and the vacuum is kicking off and running for more than 18 hours. There are 19 million rows total. ...
-1
votes
0answers
55 views

Bloating effect in Postgresql [closed]

I've done some performance analysis on my DB and I have releved that: Read head 8192 CPU power used 25% Bloating 56% (84GB of 150) 20% occupied by index unused Now I have find that 99% of ...
1
vote
1answer
99 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
4
votes
1answer
147 views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
0
votes
1answer
186 views

Close all connections to postgres without having a connection

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
1
vote
2answers
150 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
0
votes
1answer
98 views

How to configure postgresql-9.2 to know blk_write_time in pg_stat_database

Interested in the procedure for collecting block write statistics of PostgreSQL 9.2. Steps followed for configuration, could get blk_read time but not blk_write_time CREATE EXTENSION adminpack; and ...
1
vote
2answers
56 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
0
votes
0answers
72 views

Postgres 9.2 on Windows

Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
1
vote
1answer
52 views

Postgres settings reported differently in pgsql

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = ...
2
votes
1answer
106 views

PostgreSQL 9.2; Altering a table to use another sequence

I've created a table like so: create table doki_data_item2 (like doki_data_item including defaults); but this new table uses the same sequence as the old table. So two questions: How can I copy ...
0
votes
1answer
371 views

Streaming replication between postgresql 9.2 and 9.3

Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?
2
votes
1answer
202 views

Postgresql 9.2.6 ignoring archive_command

I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between. My ...
0
votes
1answer
91 views

postgresql backup and recovery

I’m trying to set up backup strategy for a production postgreSQL DB. It will have large amount of data and it should be running 24 X 7. Could you recommend some backup & recovery strategies that ...
3
votes
1answer
222 views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...