PostgreSQL version 9.2

learn more… | top users | synonyms (1)

0
votes
0answers
14 views

Impact of CLUSTER PostgreSQL on performance

I'm trying to optimize my postgres 9.2 database to speed up a queries with date restrictions. I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp ...
0
votes
1answer
12 views

ilike search really uses its index?

I have a column called nickname and have created index for it. create index test_idx on test_table using btree (nickname); My query is, select * from test_table where nickname ilike '%pga%'; ...
0
votes
1answer
286 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 ...
0
votes
0answers
8 views

postgres create a view with data transformation rules

I was hoping someone could point me to a resource that can help me solve my problem. I have a largish dataset (25 Million rows), and I need to export the table based on a set of rules. 6 rules to ...
0
votes
1answer
22 views

SELECT records based on return of COUNT query

I would like to create a query that returns records if values in a column occur more than once. I came across other Stack Exchange questions but these only dealt with returning the number of ...
2
votes
1answer
193 views

Postgres 9.2 - postgres - FATAL: XX000: relation mapping file “global/pg_filenode.map” contains incorrect checksum

We have a ~400GB Postgres database (v9.2.6) (running on CentOS 6.5) that had a problem with free space and with every command (ls, pwd etc) we were seeing a segmentation fault error. We were able to ...
0
votes
1answer
171 views

Postgresql Streaming replication - monitoring

I have setup streaming replication for my postgres servers. I am using PostgreSQL 9.2.6. Replication seems to be working fine. I am monitoring this with two ways using nagios: Log_delay and ...
0
votes
1answer
75 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
1
vote
1answer
158 views

How to measure or estimate the streaming replication lag in Postgres 9.2

I've been asked to reverse engineer an app that uses postgresql in the back end. I can see that there is some database replica tion going on... based on my reading, I *think it's called streaming ...
3
votes
1answer
29 views

How to copy part of a database with inconsistent privileges?

I have a Postgres database and I would like to copy certain tables from a single schema into a new empty database. So far I have copied a schema-only dump from from the old DB and applied it to a ...
3
votes
2answers
82 views

Automate COPY command - Postgres 9.2

I've a backup table with several distinct IDS... I wanna do a BACKUP of those rows, by ID. The function has to be executed by a limit of account_id ( select dump(21); - Where 21 = number of ...
1
vote
0answers
19 views

postgresql 9.2 hash join issue

This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time. When should ...
4
votes
1answer
2k 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 10:16:...
0
votes
1answer
26 views

Get the highest value [duplicate]

I have the following data: account_id note_id size id ---------- ------- ------ -------- 48 1554 196036 28229509 48 1554 18777 28229588 48 1554 4861 28229566 ...
0
votes
0answers
35 views

How we can make “statement_timeout” work inside a function?

I have a requirement to perform a query operation in a specified time limit in postgresql. If the query is not performed in that time limit, we need to catch a exception/a action and need to kill all ...
0
votes
1answer
68 views

How to optimize query with order by

Have query which generate OpenERP ORM. Table have 100k rows. SELECT "tbl".id FROM "tbl" WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ORDER BY ...
1
vote
2answers
73 views

Trigram index for for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
5
votes
2answers
10k 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
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
1
vote
0answers
56 views

Replicate Postgres 9.2 to 9.4 with no downtime

My current set up has a database running on Postgres 9.2.4 and would like to move from AWS EC2 to RDS. However, AWS requires Postgres be running at least Postgres 9.4 in order to perform this ...
-7
votes
2answers
238 views

Postgres Dump and Restore on Linux Terminal [closed]

I need to take a dump in postgres db and to be restored on some other server. please tell me the command in Linux with compressed and encrypted format.
7
votes
2answers
4k 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 ...
1
vote
1answer
49 views

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8. I have table like: CREATE TABLE foo ( foo_date timestamp without time zone NOT NULL, -- other columns, constraints ) This table contains about 4.000.000 rows. ...
2
votes
1answer
22 views

Multiple columns from one table rely on one single table

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables: The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
0
votes
0answers
16 views

Remedying psycopg2:OperationalError in postgresql 9.3.10 database

I have a Django app + postgresql 9.3 DB, hosted on Ubuntu VM. Recently my VM ran out of disk space, resulting in no space left on device errors showing up in my logs. I cleared the space on the device ...
1
vote
1answer
26 views

How to Increment duplicate strings in a column

I have a table similar to what is below. I have listed only one column since that's what I'm interested in. | column --------- | foo | foo | foo | bar | bar | bar Below is the output that I ...
1
vote
3answers
108 views

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" ...
2
votes
1answer
95 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
1
vote
1answer
26 views

Paged query against timestamp populated by trigger missing items

We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out. First some background. We have a "tombstone" table that is populated using a ...
1
vote
0answers
75 views

How to identify file for “The system cannot find the file specified.” during a pg_ctl -D start?

I'm getting one message during pg_ctl -D start of the standby server. Perhaps more of a general troubleshooting question. This is what I'm seeing when I do a pg_ctl -D start: D:\>pg_ctl -D d:\...
1
vote
1answer
53 views

Importing CSV file - PostgreSQL 9.2

I'm trying to import data from a CSV file on a PostgreSQL 9.2 server, but I get errors when doing that. Pgadmin: ERROR: Cannot execute COPY FROM in a read-only transaction Why am I getting that ...
0
votes
0answers
15 views

How to change shutdown mode when pg_ctl is running as a service?

I work on a Windows-based application that uses PostgreSQL as its backend database. When we install the application, it installs pg_ctl as a Windows service. According to the logs, stopping this ...
0
votes
1answer
36 views

Filter query result

I'm using this query: SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT AS variation, ...
1
vote
1answer
37 views

How to optimize view with group by

Have view with query : SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id, sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl....
3
votes
1answer
2k views

SET extra_float_digits = 3

I'm using PostgreSQL 9.2 with my web application that has three servlets with three selects. I turn on the log. When I send my first query: select * from "Nomi" limit 4 before it the log shows me ...
2
votes
1answer
33 views

SETVAL in a Window Function

I'm attempting to do an INSERT into multiple tables using the same key field. According to Sequence Manipulation Functions: Important: Because sequences are non-transactional, changes made by ...
3
votes
1answer
92 views

Full text search in XML documents

I would like to do full-text search in XML documents. However, I can't get matches for attribute values (I get a syntax error if I provide the full attribute value including the : and / characters in ...
4
votes
5answers
978 views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
2
votes
4answers
223 views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
1
vote
1answer
419 views

When can I clean up archived WAL files?

I need some help understanding the how archived WAL files are used and when I can clean them up. Currently we have a master database replicating to a slave, but every so often our master database ...
1
vote
1answer
2k 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 ...
1
vote
1answer
49 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
1
vote
0answers
44 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
0
votes
0answers
25 views

Slow Query - Optimize - PostgreSQL 9.2

I've got a slow query.. I'd like to make it faster.. Make add an index? Query: SELECT j.clientid AS client_id, ni.segment_index AS note_id, f.inode_id AS file_id, f....
3
votes
0answers
96 views

Recovery from Live to a new Slave Server - PostgreSQL - ERROR

I've started a new SLAVE PostgreSQL server set up. master: 192.168.100.1 slave1: 192.168.100.2 slave2(NEW SLAVE) 192.168.100.3 * NOTE: I run the pg_basebackup from another STANDBY SERVER. Not ...
0
votes
0answers
27 views

Postgres 9.2 replica can't catchup to master

I have configured a master/replica setup for postgres 9.2 on Amazon Linux. PS output shows that the replica is connected and streaming, but also recovering. This has been going on for about 24 hours. ...
0
votes
0answers
34 views

postgres versions differ for root / user

I have upgraded postgres 9.2 to 9.4 on Centos7 following the instructions here, but then I am left with what I think is a slightly weird result: [user@machine]$ sudo bash [sudo] password for user: [...
1
vote
1answer
87 views

How do I GRANT for all tables across all schemas

I want to GRANT SELECT ON ALL TABLES TO foo; But postgres tells me I have a syntax error at "TO". It expects a GRANT like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO foo; ...
0
votes
1answer
38 views

What's the difference between betwwen 2 DB and 2 Schema [closed]

My question is : what is the difference between the two next mode in PostgreSQL, for an production application. using two DB in the same SGBD (the same table-space and HDD) Or using two schema in ...
4
votes
1answer
149 views

How to avoid invoking functions twice when using GROUP BY and HAVING?

I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents. The following query works and returns the correct results: ...