All Questions
Tagged with postgresql-9.2 postgresql
214 questions
0
votes
0
answers
53
views
Multiple but not all executions of the same Postgres SELECT query are getting locked endlessly
I am using Postgresql version 9.2.18 on Centos Version CentOS Linux release 7.3.1611 (Core)
In postgres there is a select query on a single table which is getting executed multiple times per minute.
...
- 23
0
votes
1
answer
1k
views
PostgreSQL + how to clean pg_xlog log without damage the PostgreSQL service
we are using postgresql for ambari DB , in our linux server - rhel 7.2
postgres -V postgres (PostgreSQL) 9.2.13
we noticed that pg_xlog folder consuming 198g , and its gives /var to get 100% size
so ...
- 113
1
vote
0
answers
1k
views
How to increase max_connections in Postgresql-9.2
I need to increase production Postgresql-9.2 max_connections parameter from current 1000 to 3000.
Used PGTune service to get the values and updated accordingly to the postgresql.conf file and /etc/...
- 81
1
vote
2
answers
259
views
How to do Postgresql 9.2 version check in Linux?
$ systemctl restart postgresql-9.2.24
Failed to restart postgresql-9.2.24.service: Unit not found.
- 13
1
vote
1
answer
16k
views
Getting ERROR: could not open relation with OID 6701547 while performing VACUUM FULL on Postgres 9.2
We are facing issue in Postgres database 9.2 on RedHat EL 6.8 with an ext4 file system. We are getting below error every time whenever we preform vacuum full or reindex database db_name operation.
...
0
votes
1
answer
387
views
How to deal with different badges in Postgres Explain Visualizer?
I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
- 175
2
votes
3
answers
14k
views
How to find resource intensive SQL operations in Postgresql? High CPU usage spike
I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period.
...
- 81
3
votes
1
answer
10k
views
How to filter a value in json field type on Postgres 9.2?
My json field data is like this:
{"active":true,"id":"xxxxxxxxxxxxxxxxx","settings":{"secret":"xxxxxxxxxxxxxxxxxxxxxxxxxx","token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","expires":"2019-12-16 01:11:23"},...
- 131
3
votes
1
answer
6k
views
Why does json_agg fail with error "function json_agg(record) does not exist"?
With postgresql version 9.2, I'm trying to run a query that selects certain columns to build a json object. Here's the query, which I derived from this question:
select
a.id
, json_agg((SELECT x ...
- 137
0
votes
2
answers
617
views
Sequential scan with large sub-query filter "never" ends
I'm using postgres 9.2.4, and run the following query:
explain analyze select * from bubu where id not in
(select bubu_id from kuku limit 33554431);
...
- 221
2
votes
2
answers
850
views
Remove persistent privileges on table
I'm trying to remove the role my_schema-writers. I can't because it owns objects:
ERROR: role "my_schema-writers" cannot be dropped because some objects depend on it
DETAIL: privileges for table ...
- 133
0
votes
0
answers
88
views
Create HA PostgreSQL cluster
We want to set up a highly available PostgreSQL cluster in our production environment. As of now, We have set up streaming replication. But we have to manually promote the slave as the master in case ...
- 93
3
votes
2
answers
6k
views
psql, I try to connect to a database and I get "psql: FATAL: Peer authentication failed for user ", why?
I'm using psql (9.2.23) on my Dell Latitude 3540 laptop running Linux CentOS 7.
I want to do a simple thing: create a database username and log in as that username.
My operating system username is "...
- 327
7
votes
3
answers
10k
views
Delete data from a billion row table based on where clause
I need to delete about 400 million rows from a 1.3 billion row table based on an indexed date field.
The table is about 800 GB in size.
What is the most efficient way to delete the data without ...
- 342
2
votes
0
answers
475
views
Postgresql - Primary key index getting corrupted after upgrade from 9.2 to 9.6
Im using a Postgresql-9.2 on Centos 6.7. I tried to upgrade the PostgreSQL using --link. After that, I took a table backup and tried to restore this on Dev server. But Im getting an error like,
...
- 1,986