Questions tagged [postgresql-9.4]
PostgreSQL version 9.4
1
vote
1answer
43 views
Use NEW in FROM clause in Postgres trigger?
I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.
SELECT
unnest(something)
FROM NEW
However, this seems to lead to an error:
relation '...
1
vote
1answer
22 views
Streaming replication archive folder taking all space- Postgresql- 9.4
We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine.
We ...
1
vote
1answer
27 views
Regarding GIN Index in Postgresql
I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time.
select email from customer where lower(email) like '%gmail';
CREATE INDEX idx1 ON customer USING gin (...
2
votes
3answers
54 views
Find all items where the 2 latest statuses meet condition
I have a table in a PostgreSQL database like this:
ID|Item-FK |timestamp|status
=============================
1 | 123 | ... | EXPIRED
2 | 123 | ... | PENDING
...
I want to query all ...
1
vote
0answers
44 views
postgres :: FATAL: could not access file “pglogical”: No such file or directory
We installed pglogical from source code, we are running PostgreSQL 9.4
make USE_PGXS=1 clean all
make USE_PGXS=1 install
Installation was successful after updating the parameters, we tried ...
0
votes
0answers
9 views
Streaming replication Failover
We are using postgres 9.4.15 in our master and slave server. We want to test the failover scenario where we will be manually turn off host 1(master) and see if failover happens to host 2(slave server)....
1
vote
1answer
38 views
Streaming replication version between different minor versions
I want to know few details about streaming replication.
I want to setup streaming replication between version 9.4.4 and 9.4.20 ?
I have the master server in centos6.6 with postgres version 9.4.4 ...
1
vote
0answers
13 views
ERROR: relation “codepool_codepoolseq_seq” does not exist SQL state: 42P01
Below is my script but I keep getting an error.
-- Table: public.codepool
-- DROP TABLE public.codepool;
CREATE TABLE public.codepool
(
codepoolseq bigint NOT NULL DEFAULT nextval('...
1
vote
1answer
58 views
Downgrading PostgreSQL 10 to 9.4
Are there any particular precautions I should take when downgrading PG 10 to 9.4?
I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
1
vote
2answers
34 views
How to dump multiple databases of the same cluster in single execution?
I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.
Is there any way to do ...
0
votes
1answer
23 views
Streaming replication in postgres question
We have recently setup streaming replication in our Postgres server (t01, t02). t01 is master and t02 is the slave. I want to understand the below two issues:
Recently our /var directory of t01 ...
1
vote
1answer
28 views
Merge 2 columns and replace with specific output
The setup is a Debian 8 with Postgres 9.4 (64bit if that matters).
Given following table I want to "search" for a combination of columns first and column sub and replace their output with another ...
1
vote
0answers
13 views
Calculate percentages by using JOINS result in postgresql
I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, ...
0
votes
2answers
36 views
Different results for logically same, but textually different queries
Is there any reason why the following queries would return different results?
select count(id)
from mytable
where
status='active'
and ((textfield1 ilike '%needle%') or (textfield2 ilike '%...
1
vote
1answer
47 views
Postgresql installation, avoid creating “postgres” user
I have a constraint, that I can use only one user on a SUSE Linux Enterprise Server 11.
Now, I need to install PostgreSQL 9.4. Is it possible to somehow avoid the automatic creation of "postgres" ...
0
votes
1answer
247 views
PgBouncer “no more connections allowed (max_client_conn)”
I am using PostgreSQL with PgBouncer, and receive the following error in my application:
08P01: no more connections allowed (max_client_conn)
pgbouncer.ini has the following configuration:
...
3
votes
1answer
441 views
Postgres 9.4 wal_level=logical increases disk space
I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance.
Database Migration Service requires the wal_level to be set as logical ...
4
votes
2answers
75 views
drop database command is cancelled ^C
I want to know what happens when a drop database command is canceled using ^C.
Does it partially removes the data or completely?
1
vote
1answer
42 views
How to get aggregate data from a dynamic number of related rows in adjacent table
EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't ...
0
votes
0answers
22 views
Postgresql 9.4 input invalid for type date “”
I have postgresql94-server.x86_64 (9.4.17-1PGDG.rhel6) installed on CentOS 6, and a database where
SELECT idprovv as idprovv_look,
(case when datacdm is null then '31/12/2020' else datacdm end)::...
2
votes
1answer
68 views
How use cascade delete for array field in PostgreSQL?
How can i remove the value from array field?
I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade ...
1
vote
1answer
30 views
Query Conflicts in Streaming Replication in Postgres 9.4
When using Streaming Replication in Postgres 9.4, when a query makes the standby Postgres "start to count" the max_standby_streaming_delay, changes being made in the primary:
Are dammed at the ...
2
votes
0answers
214 views
Why am I getting permission denied on an ownership change?
My current user is pronto
mediapop_staging=> select current_user;
current_user
--------------
pronto
(1 row)
This user a standard super user created on AWS RDS.
mediapop_staging=> \du ...
2
votes
1answer
47 views
How to use “WHERE” and also “WHERE NOT EXISTS” in the same query and table?
I am trying to add both WHERE and WHERE NOT EXISTS in same query. Below mentioned query1 is working fine, but it returns all the t1 data which does not belong to company1. So I tried query2, but no ...
-1
votes
1answer
690 views
Unpivot a table in PostgreSQL [duplicate]
I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want:
Below is my schema with sample dataset:
http://www.sqlfiddle.com/#!17/3f45c/1
My understanding ...
0
votes
1answer
161 views
How to query inside jsonb column
I have a column type of jsonb named changes
THis is the structure of my column.
[["change","is_enabled",[false,true]]]
I want to query within the column.
I want to find all the rows that "...
0
votes
0answers
18 views
Postgres backup in single user mode
I have started my db in single user mode. postgres --single -D /usr/local/pgsql/data -O my_database and got into the prompt. Can I take a dump of the db / table from this prompt? Or is there any way ...
1
vote
1answer
47 views
How to find the nearest lat and lang in postgresql
I am trying find the nearest lang and lat from the specified lat and lang
i tried the below mentioned query, in my query i have mentioned the place muscat's lat and lang ,so i want to display the ...
10
votes
3answers
1k views
Fastest way to count how many dates' ranges cover each date from series
I have a table (in PostgreSQL 9.4) that looks like this:
CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES
(1, '2018-01-01', '2018-01-31'),
...
0
votes
2answers
79 views
Restarting hot_standby replication when the *slave* fails
Context: master-slave replication on Postgres 9.4, slave died a couple of weeks ago:
the slave copied a WAL-File from the master,
told the master that the file is copied,
the master deleted the ...
0
votes
1answer
15 views
Give particular privileges for a table to existing role
My database have some tables such as:
Table A
Table B
Table C
I'm using user monitoring with read-only permission.
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE ...
3
votes
4answers
983 views
How to count boolean values in PostgreSQL?
How to count, how may true and false for the field public in postgresql user table
i have tried this query
select
sum(case when false then 1 else 0 end) as false,
sum(case when true then 1 else 0 ...
2
votes
1answer
165 views
Lost data during PostgreSQL minor version upgrade
I had PostgreSQL 9.4.5 installed on MacOS which was done using Homebrew. I wanted to update it to the latest minor version of 9.4, so I did this (if I remember correctly):
brew uninstall postgresql
...
0
votes
1answer
273 views
pg_restore: [archiver] could not find entry for ID 12
I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error:
pg_restore: [archiver] could not find entry for ID 12
Exactly the same happens if ...
0
votes
0answers
111 views
PostgreSQL dump issues when importing into AWS RDS
I've been struggling with several adaptations (using sed) of a pg_dump output to match AWS RDS' restrictions on users/roles, but have come to a point where pg_catalog schema access is forbidden and is ...
3
votes
1answer
376 views
PostgreSQL ERROR: cannot freeze committed xmax
First of all, I am new to PostgreSQL database.
In my PostgreSQL log I can see errors bellow and I don't know what is wrong or what I should change.
2018-03-29 10:54:38 CEST::@:[5059]: CONTEXT: ...
0
votes
0answers
21 views
Is it possible restore database from folder in postgresql
I am using postgresql 9.4. Accidentally dropped table don't have any backup system. Is it possible to recover from system image backup.
0
votes
0answers
93 views
Postgresql BDR - size of pg_xlog folder
Trying to figure out why there's a difference in size information for my pg_xlog directory.
Comparing this command:
lab-1:~# du -k /var/lib/postgresql/9.4/data/pg_xlog/
4 /var/lib/postgresql/...
1
vote
0answers
168 views
Postgresql BDR - What causes replication_slots to go inactive?
I'm trying to hunt down what causes replication slots to go inactive. Might seem like a remedial question but I'm noticing that on my master database server, I have many inactive replication slots ...
1
vote
0answers
38 views
Grouping records time interval (start-end time) into groups of specific time interval (eg. 30 min)
I would like a query that would return the count of records that have their start-end time in specific time interval (eg. 30 mins).
For example records:
id|start|end
1|10:00|12:00
2|11:00|12:20
3|10:...
1
vote
1answer
780 views
UPDATE with subquery LIMIT 1 returns multiple rows [duplicate]
I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12
The query is the following :
UPDATE folder as f set locked = 'locked', ...
0
votes
1answer
917 views
Get last modified date of table in postgresql
I want know the last modified date of table in postgresql. In SQL Server can get using
SELECT modify_date FROM sys.objects
How to get same thing in Postgres? I am using Postgres 9.4
1
vote
0answers
39 views
Combining two event tables into one postgres view
I have two tables, events and event_logs.
The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime.
Event
+-----...
0
votes
0answers
72 views
Postgres: high difference between “backend_start” and “xact_start” time for first connection query
From whatever connections are being created by the application layer, the first connection is taking a lot of time in execution on DB, rest of following connections are working fast.
Stats from DB ...
4
votes
1answer
149 views
postgres 9.4 timeline issue
We've had to move around the master a bit. It started on server 01 got moved to 02 (which was a slave) We need to move it again so we built 04 and trying to slave it off 02 and getting the following ...
0
votes
0answers
19 views
Simple query rephrasing for optimization
The following query:
select exists(select id
from avl_adc_equipo cfg
where cfg.imei=354676052451142
and cfg.adc=2)
Executes pretty fast, but as it ...
0
votes
1answer
38 views
New query taking over 60% of running time
As part of a daily automated reporting system, we run a series of queries to check different operating parameters from our customers' assets tables.
Last Tuesday I added a new check that works, but ...
0
votes
2answers
46 views
How do I duplicate constraints from one existing table to another existing table with an identical structure?
I'm using PostgreSQL. I have two tables, table_a and table_b. table_b is a structural copy of table_a but it was created without the constraints of table_a. At a later date, I want to add the ...
0
votes
0answers
14 views
Increase XIDWraparound limit
My Database is having huge data > 5TB, at some point I am doing vacuuming few tables on daily basis which bit hustle for the server. And I can't do purging to get some room for new tuples due to some ...
0
votes
1answer
296 views
Error connecting to the server:FATAL:couldnot open file“base\12135\11946”:No such file or directory
I have a issue from PostgreSQL 9.4 .The issue is given below:
An Error has occurred: Error connecting to the server:FATAL:could not
open file"base\12135\11946":No such file or directory.
This ...
