Skip to main content

Questions tagged [postgresql-9.2]

PostgreSQL version 9.2

Filter by
Sorted by
Tagged with
0 votes
0 answers
31 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. ...
user2710961's user avatar
0 votes
0 answers
107 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 ...
Ruan's user avatar
  • 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 ...
jessica's user avatar
  • 113
1 vote
1 answer
816 views

postgresql + how to recover the wals files

we have postgresql version 9.x on rhel machine , postgresql is the DB of the ambari server some of the files under folder - /var/lib/pgsql/data/pg_xlog was deleted by mistake from total 487 files only ...
sheffadmin's user avatar
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/...
JineshJK's user avatar
1 vote
2 answers
241 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.
Sunil Vijay's user avatar
1 vote
1 answer
15k 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. ...
Suresh Dewasi's user avatar
0 votes
1 answer
4k 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?
MLD's user avatar
  • 23
0 votes
1 answer
348 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 ...
EAmez's user avatar
  • 155
2 votes
3 answers
12k 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. ...
JineshJK's user avatar
0 votes
1 answer
4k 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 ...
Dimitrios Desyllas's user avatar
2 votes
1 answer
8k 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"},...
Juan's user avatar
  • 121
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 ...
fraxture's user avatar
  • 137
0 votes
2 answers
598 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); ...
Evgeny's user avatar
  • 231
2 votes
2 answers
819 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 ...
Dylan Hettinger's user avatar
0 votes
0 answers
83 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 ...
YogeshR's user avatar
  • 103
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 "...
DavideChicco.it's user avatar
1 vote
0 answers
140 views

IN postgressql "pgstartup.log" occupied 350 GB space in disk

How to reduce the pgstartup.log file size. If i delete pgstartup.log what will happen. We are using postgresql in Red Hat Enterprise Linux Server.
harry's user avatar
  • 11
7 votes
3 answers
9k 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 ...
sharadov's user avatar
  • 342
2 votes
0 answers
457 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, ...
TheDataGuy's user avatar
  • 1,950
7 votes
3 answers
38k views

Search for current month data in postgresql 9.2

I'm trying to get the ticket data only from the current month on a PostgreSql 9.2 database. The field called data_cadastro is DATETIME: id_ticket | data_cadastro -----------+--------------------- ...
Flavio Junior's user avatar
4 votes
4 answers
29k views

postgresql: permission denied for sequence <table>_i_seq

I have a schema of a table in PostgreSQL that looks like: CREATE TABLE IF NOT EXISTS data.pmacct ( i BIGSERIAL PRIMARY KEY, And there are users: A: superuser privileges ...
nskalis's user avatar
  • 1,621
1 vote
0 answers
442 views

Postgresql 9.2.1, vacuum freeze process stuck while running

In postgresql 9.2.1, database avoid accepting commands to prevent wraparound dataloss issue.I was running vacuum freeze verbose process in single user mode in the respective database. Suddenly, the ...
Eddie's user avatar
  • 11
2 votes
0 answers
255 views

Postgresql - Auto vacuum is disables in conf file, but still everyday its running

Im using Postgresql 9.2. I have disabled auto vacuum in Conf, but still, I can see this auto vacuum is running on few tables every day. Can anyone help me find out why its running?
TheDataGuy's user avatar
  • 1,950
1 vote
1 answer
1k views

postgres: archive_command can't write to an nfs share

I've enabled the archive_mode with wal_level = hot_standby on a master server. I intended to send the wal logs to a nfs share with: archive_command = 'test ! -f /mnt/psql_wal/%f && cp -i %p /...
Jorge Raimundo's user avatar
1 vote
1 answer
2k views

How to apply patch on postgresql 9.2

Hi all I would like to apply one patch(pg_dump_grouplock.patch) in my PostgreSQL 9.2 database regarding lock table issue advised on below link http://www.postgresql-archive.org/pg-dump-and-...
Prabhat's user avatar
  • 121
1 vote
1 answer
989 views

Another "log_min_duration_statement is ignored" question

Following the recommendations in log_min_duration_statement setting is ignored I set these two values and then reloaded. log_min_duration_statement = 2000 log_statement = 'none' However, I'm still ...
RonJohn's user avatar
  • 647
11 votes
1 answer
5k views

how to dump a case-sensitive table within a specific schema

I'm trying to dump a case-sensitive table named app_auth.User by executing the following command pg_dump --schema=app_auth -t '"User"' -U em3local -h 127.0.0.1 -Fc -a -f <path> <dbname> ...
Bertuz's user avatar
  • 245
4 votes
4 answers
2k views

How to remove an element from a col with a CSV of ints in PostgreSQL 9.2?

I have PostgreSQL 9.2 database and a table: id integer, allowed_types character varying(255)` Sample data as below: id allowed_types 1 3,4,5,13,14 How I can remove 4 and 5 from allowed_types ...
Zhlobopotam's user avatar
0 votes
1 answer
477 views

Pgpool - 3.5 configuration

[enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING: could not open configuration file: "pgpool.conf" 2017-06-20 11:34:41: pid 103313: DETAIL: using default configuration ...
prakash ramakrishnan's user avatar
2 votes
0 answers
737 views

Why so many errors are displaying as "ERROR: current transaction is aborted, commands ignored until end of transaction block"

Production database has restarted unfortunately. When I gone through the logs, I have observed below error several times(More than 1,00,000 times). "ERROR: current transaction is aborted, commands ...
Raghavendra's user avatar
4 votes
3 answers
38k views

POSTGRESQL error Could not create shared memory segment: Cannot allocate memory

Recently my client database has restarted and thrown the below error. FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5433001, size=...
Raghavendra's user avatar
3 votes
2 answers
1k views

Extracting sum of time in a period

I have a login table, which registers datahora_ini (system entry time) and datahora_fim (system exit time). Sample data: CREATE TABLE login(cod_user,datahora_ini,datahora_fim) AS VALUES ( 101::...
Rafael Calino's user avatar
2 votes
2 answers
7k views

UPDATE with LIMIT / OFFSET in PL/pgSQL function

In my Postgres 9.2 database I've got the following SELECT, which will be used by a PL/pgSQL function: SELECT id FROM tablea WHERE migrated = false; I want to get 2000 records each ...
Patrick B.'s user avatar
3 votes
2 answers
232 views

Trigger to generate protocol number

I need help with creating a trigger that will generate a protocol number. The following sequence must be followed: 323926YYYYMMDDXXXXXX 323926 = fixed number YYYY = year MM = month DD = ...
Rafael Calino's user avatar
0 votes
1 answer
1k views

postgresql archive (old data)

My environment: # cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.3 (Maipo) # rpm -q postgresql postgresql-9.2.18-1.el7.x86_64 # I used following Streaming Replication - ...
alexus's user avatar
  • 625
1 vote
1 answer
5k views

postgresql pg_dumpall restore one database

My environment: # cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.3 (Maipo) # rpm -q postgresql postgresql-9.2.18-1.el7.x86_64 # I used following PostgreSQL: Documentation: 9.6: ...
alexus's user avatar
  • 625
0 votes
1 answer
343 views

How to use value return by postgresSQL function in another query

I am calling PostgreSQL function that returns me the text, How can I store this value in local variable and use the value/variable in another query. ?
Gaurav's user avatar
  • 51
1 vote
2 answers
3k views

WITH statement does not persist outside of query [closed]

I run a select to check what records I should register in another table. Each record returned in the query I should give an insert in my table campanha_cliente_chamada. WITH psql as ( /* stuff */ ) ...
Rafael Calino's user avatar
1 vote
1 answer
88 views

Update with id returned from insert using WITH

I need a help with this query I make an insert that returns me an id, then I need to run an UPDATE with that id returned... WITH disca AS ( INSERT INTO callcenter.campanha_cliente_chamada ...
Rafael Calino's user avatar
5 votes
1 answer
9k views

Check if database backup using pg_dump is in progress

I am using PostgreSQL 9.2 and using pg_dump to create DB backup. How could I determine is db backup and restore is in progress. So I can prevent another user in initiating another.
Gaurav's user avatar
  • 51
0 votes
2 answers
235 views

SELECT from tableA if not in tableC

I have a view, which selects data from TableA (TableA will be decommissioned once we migrated it all). We are creating a new table, called TABLEC, where we'll be migrating all the data from TableA to ...
Patrick B.'s user avatar
2 votes
1 answer
2k views

Troubleshooting PgPool Query Lags

I have been using PGpool-II for quite a while but just noticed an intermittent lag (slow request taking about 20s to execute) which I am able to replicate consistently. Before I explain here is a ...
Joseph Persico's user avatar
2 votes
1 answer
6k views

Passing Arguments from INSERT / UPDATE to Trigger

I need a help with a trigger, this trigger I need to pass a value that comes from the queue column in the asterisk.queue table. CREATE TRIGGER gerarconf AFTER INSERT OR UPDATE ON asterisk.fila FOR ...
Rafael Calino's user avatar
1 vote
3 answers
2k views

UPDATE BIGINT[] column from a SELECT

I have to update a bigint[] column named permissao_ver. Example value: '{1,2,3,4,5,11,44,56,75,11}'. Pseudo code: UPDATE callcenter.pausa SET permissao_ver = '{"(SELECT cod_grupo FROM crm....
sealabr's user avatar
  • 249
4 votes
2 answers
7k views

postgres - SUM Time of this Interval and Group by 30 and 30 minutes

I need to SUM Time of this Interval and Group by 30 and 30 minutes. CREATE TABLE foo AS SELECT date_start::timestamp, date_end::timestamp, agent FROM ( VALUES ('2017-01-06 06:52','2017-01-06 06:...
sealabr's user avatar
  • 249
1 vote
0 answers
853 views

How to avoid downtime during postgresql upgrade

Im having a production PostgreSQL server 9.2, it has a few databases around 150GB. I want to upgrade this to 9.6, so I decided to upgrade this on QA server. I have installed PostgreSQL 9.6. Then ...
TheDataGuy's user avatar
  • 1,950
1 vote
2 answers
1k views

LEFT JOIN same table - Postgres 9.2

I've got this table: CREATE TABLE clients ( id BIGSERIAL NOT NULL, company CHARACTER VARYING(255) DEFAULT '' NOT NULL, email CHARACTER VARYING(50) DEFAULT '' NOT NULL, city ...
Patrick B.'s user avatar
1 vote
1 answer
53 views

Rewrite query for version 9.2

I want to count a range between two dates, like this example: AGENT | LOGIN_START | LOGIN_END --------+----------------------+--------------------- 101 | 2016-01-01 06:00:00 | ...
sealabr's user avatar
  • 249
5 votes
1 answer
130 views

Documentation for selecting table name returning csv of the row

It seems that in Postgres 9.2 this syntax SELECT <table name> FROM <table name> Returns rows with all columns aggregated as pseudo-CSV, like so: (1,test,48,,48,,"2016-10-29 00:47:09....
gxtaillon's user avatar
  • 153

1
2 3 4 5