Questions tagged [postgresql-9.2]

PostgreSQL version 9.2

Filter by
Sorted by
Tagged with
0
votes
1answer
129 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
1answer
26 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?
0
votes
1answer
64 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 ...
1
vote
2answers
87 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. ...
0
votes
1answer
25 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 ...
0
votes
1answer
43 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"},...
0
votes
1answer
714 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 ...
0
votes
2answers
241 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); ...
1
vote
2answers
306 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 ...
0
votes
0answers
55 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 ...
3
votes
1answer
3k 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 "...
1
vote
0answers
101 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.
4
votes
3answers
2k 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 ...
2
votes
0answers
224 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, ...
2
votes
3answers
6k views

Search for current month data in postgresql 9.2

I am trying to get the ticket data in a 9.2 database only from the current month. The field called data_cadastro is DATETIME. id_ticket | data_cadastro -----------+--------------------- ...
2
votes
1answer
3k 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 ...
1
vote
0answers
212 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 ...
1
vote
0answers
55 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?
1
vote
1answer
635 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 /...
0
votes
1answer
622 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-...
1
vote
1answer
326 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 ...
5
votes
1answer
1k 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> ...
3
votes
4answers
431 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 ...
0
votes
1answer
281 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 ...
2
votes
0answers
499 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 ...
0
votes
2answers
7k 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=...
3
votes
2answers
461 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::...
1
vote
2answers
2k 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 ...
3
votes
2answers
92 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 = ...
0
votes
1answer
530 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 - ...
0
votes
1answer
1k 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: ...
0
votes
1answer
104 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. ?
1
vote
2answers
537 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 */ ) ...
1
vote
1answer
42 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 ...
3
votes
1answer
2k 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.
0
votes
2answers
217 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 ...
1
vote
1answer
784 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 ...
0
votes
1answer
1k 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 ...
1
vote
3answers
642 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....
2
votes
2answers
1k 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:...
1
vote
0answers
809 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 ...
1
vote
2answers
707 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 ...
0
votes
1answer
46 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 | ...
5
votes
1answer
93 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....
1
vote
1answer
548 views

How to initialize a PostGIS database in PostgreSQL single-user mode?

I'm using PostgreSQL 9.2.15 with PostGIS 2.0.7 through the standard packages available on CentOS 7 with EPEL. I have a server script to automatically setup a PostGIS database, by first executing ...
0
votes
1answer
227 views

Can't create view [closed]

I want to create a view like CREATE VIEW monitors_info AS SELECT monitors_id, nom FROM monitors; But, I got this message ERROR: No schema has been selected to create in. (using psql) ...
1
vote
1answer
148 views

Postgres - Get the query cost assign to a variable

To get the estimated cost of the query, i using EXPLAIN SELECT column FROM table;, to get the current cost of the query, i am using EXPLAIN ANALYSE SELECT column FROM table;, my question is how to get ...
0
votes
0answers
50 views

PostgreSQL Query With Sub-Query

On my production server, this query returns no results, so I took it to SQLFiddle to test the results with test data to see if maybe it was a conversion issue. Well SQLFiddle throws an error anytime ...
12
votes
2answers
9k views

how to track progress of a large postrgresql dump

Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
1
vote
3answers
2k views

Specify cleanup by file age - PostgreSQL 9.2

I want to keep my wal_files for 30 days, using the pg_archivecleanup. I've followed these scripts: https://stackoverflow.com/questions/31346606/postgresql-automatic-archive-purge-not-occuring and ...