1
vote
0answers
50 views

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

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
27 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
17 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
1answer
21 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
2answers
67 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
30 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 ...
2
votes
1answer
77 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
33 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
51 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
90 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
89 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
131 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
110 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
28 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
31 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
45 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
46 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
67 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
120 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
48 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
102 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 ...
1
vote
2answers
52 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
42 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
42 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
53 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
207 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
126 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 ...
3
votes
1answer
141 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 ...
2
votes
1answer
209 views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
1
vote
1answer
382 views

Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB. These DB are running on ...
0
votes
2answers
142 views

Can't run PostgreSQL locally

I am working to run PostgreSQL is running locally but I cannot connect. I installed postgress using brew, then I downloaded http://postgresapp.com When I try to create a database createdb: ...
1
vote
1answer
62 views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
0
votes
3answers
69 views

Postgresql: Make Column Default a Query

Is there a way I can set up a column in PostGreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
0
votes
1answer
74 views

Complex “activity” based query optimization

I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's ...
1
vote
1answer
107 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
0
votes
1answer
239 views

pg_upgrade 9.2 to 9.3 error

I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server. I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it ...
2
votes
1answer
210 views

Strange Postgres Hstore syntax with curly braces {key=value}

I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program ...
5
votes
1answer
95 views

Is Postgres ignoring my function cost annotation?

Why does this: create or replace function dummy() returns double precision as $$ SELECT random() $$ LANGUAGE SQL COST 777; explain select dummy(); return this: Result (cost=0.00..0.01 rows=1 ...
2
votes
2answers
219 views

How to define alias in an ARRAY_AGG expression?

I'm trying to return pure JSON from a Postgres 9.2 table. SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW)) FROM ( SELECT album, max(release_year) AS release_year, ...
4
votes
1answer
173 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
1
vote
0answers
297 views

PgPool is pushing Postgres to 100% cpu usage

I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
3
votes
1answer
478 views

Can column name be “Group” in Postgresql or in any databases

I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group". I tried creating it but it always throw a syntax ...
1
vote
1answer
154 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
1
vote
0answers
1k views

PostgreSQL isn't listen on 5432 in Windows 7

I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented: listen_addresses="*" port=5432 And this is the ...
2
votes
1answer
123 views

PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'. Various revisions of this SQL isn't constraining '-/+infinity' input: CREATE TABLE bill ( id ...
2
votes
1answer
192 views

How to solve 'tuple concurrently updated' error?

The following query is performed concurrently by two threads logged in with two different users: WITH raw_stat AS ( SELECT host(client_addr) as client_addr, pid , usename ...
1
vote
3answers
282 views

Newly installed Postgresql 9.2 on same box as 9.1

I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed. I have ...
0
votes
1answer
75 views

How to index refined null values?

I have table with two indexes: latest_channel_snapshots_views_idx (view_count DESC NULLS LAST) latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST) What I want is ...
2
votes
1answer
528 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
1answer
2k 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 ...