Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-9.2]

PostgreSQL version 9.2

-1
votes
0answers
23 views

PostgreSQL error in startup

I have a PostgreSQL 9.2 server which after an inappropriate restart (VM restarted) shows the error that I explain below: LOG: database system was interrupted while in recovery at 2018-11-05 21:57:39 ...
0
votes
0answers
98 views

DETAIL: Could not open file “pg_subtrans/01EB”: No such file or directory

I'm trying to help one of our clients to solve a database issue. It seems that their database has corruption (v9.2.5) and I'm trying to solve it. I'm working on a test env. I upgraded the database ...
0
votes
1answer
156 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
95 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
147 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
46 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 ...
0
votes
0answers
156 views

Postgresql - Unable to connect slave after replication starts, but able to connect from remotely

I have a PostgreSQL master/slave setup. Today I created a new slave from the existing slave (cascading replication). Master: 10.0.0.1 Slave1: 10.0.0.2 Slave2: 10.0.0.3 (this is the new one I have ...
3
votes
1answer
2k 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
60 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
598 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 ...
0
votes
0answers
18 views

Collection of data from system tables postgres 9.2. What privilege are given to the user for collection?

There is an application that picks up data from system tables postgres 9.2, for example pg_stat_activity -The application can not change the name of the system tables from which the fence occurs (i.e.,...
2
votes
0answers
161 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, ...
0
votes
2answers
1k 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 -----------+--------------------- ...
0
votes
0answers
450 views

How to remove obsolete WAL files in postgresql replication?

I've setup streaming replication with Postgres 9.2 My problem is that on the master server the pg_xlog folder just gets fuller and fuller and log files in C:\Program Files\PostgreSQL\9.2\data\pg_xlog ...
2
votes
1answer
1k 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
127 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
40 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
409 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
277 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
187 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 ...
4
votes
1answer
632 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
276 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
220 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 ...
1
vote
0answers
308 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
4k 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
196 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
1k 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
74 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
303 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
869 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
75 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
230 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
38 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 ...
2
votes
1answer
1k 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
203 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 ...
0
votes
0answers
349 views

PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

I have Apache, PHP, and Postgres all installed on one CentOS 7.3. I have a .PHP script that does not use interactive user input that successfully connects to the Postgres database. So I know PHP and ...
1
vote
0answers
1k views

Permission denied when starting postgres [closed]

I try to setup postgres in different location then default, but is seems like there is permission problem with new location. I set the same perm as default data_directory dir. When I try to start ...
1
vote
1answer
529 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
540 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
456 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....
3
votes
2answers
874 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
737 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
501 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
45 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
441 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
135 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
78 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
49 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 ...
5
votes
1answer
5k 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.