This tag is specifically for PostgreSQL version 9.2
0
votes
2answers
21 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
29 views
Tools for migration from MySQL to PostgreSQL [closed]
I have been trying with RazoSQL, mysql2pgsql and dbconvert. But none of this worked fine for me.
0
votes
1answer
9 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
53 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
30 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
0answers
12 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
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
1answer
29 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
69 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
82 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
34 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
92 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'
...
0
votes
1answer
154 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:
...
1
vote
1answer
68 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
52 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
111 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
51 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 ...
0
votes
1answer
72 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
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
43 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
211 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
127 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
63 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
143 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 ...
0
votes
1answer
79 views
How to install postgresql on a server? [closed]
Background: I am busy with an MSc in GIS. It involves using postgresql 9.2 with the postgis extension as the spatial database and QGIS to view, edit and analyse the data. I know postgis is also ...
1
vote
0answers
226 views
error: must be owner of relation USER_ACCOUNT_ID_seq
I have granted priviledges to a user for testing in PostGreSql:
GRANT USAGE ON SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL TABLES IN SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL SEQUENCES IN ...
2
votes
1answer
210 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
383 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
0answers
35 views
Preserving tablespace and databases from previous install?
I goofed and destroyed my postgresql 9.2/postgis 2.1 installation under freebsd 9.2 by trying to install mapnik which, for my inconvenience, overwrote it with 9.0.
I have, on another spindle, a ...
0
votes
2answers
143 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
63 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" ...
1
vote
1answer
42 views
Postgres 9.2 under FreeBSD 8.4 - How do I find the databases & tables?
I'm trying to create an osm tileserver and am at the point where i've created the database to hold the output of osm2pgsql. But I can find neither the database (map_data) nor, of course, its tables. ...
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
108 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 = ...
1
vote
1answer
54 views
query errors when all data not present
I have a working query that show success rate of staff. When I run it in for all staff it works flawlessly.
However, when I insert it as a function within a java based page I get an error:
ERROR: ...
