Questions tagged [postgresql-9.2]
PostgreSQL version 9.2
247 questions
0
votes
0
answers
58
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
0
answers
121
views
Postgres 9.2 Change elements of a JSON object
If I were to perform the following script in newer versions of Postgres (13 and upward), then elements within the JSONB object can easily be added and amended using ||.
DO $$
declare j1 jsonb;
declare ...
- 35
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
269
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
5k
views
How to add a new column if this doesn't exist
I'd like to create a new column in my table if that column doesn't exist. But if the column exists, I would like to update it.
I haven't found a suitable answer yet. Can anyone help me?
- 23
0
votes
1
answer
406
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
0
votes
1
answer
5k
views
In case of inserted a fixed value into autoincrement how to automatically skip into the next sequence value without causing insert to have an error?
In my postgresql 9.2 database I created the following table:
CREATE TABLE dummy(id SERIAL PRIMARY KEY,text TEXT);
And I inserted a record via:
INSERT INTO dummy(text) VALUES ("Hello Word");
Then I ...
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
623
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
857
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
92
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