patroni – Postgresql Cluster gets shut down signal

I hope you can help me with my current Postgresql Cluster Problem. I deployd the Cluster regarding this instruction.
-> https://github.com/vitabaks/postgresql_cluster
My current problem is that the cluster is not working faultless.
When I use the command <> the following picture shows that it already has over 200 TL. It should be near 0 and not the current value.

enter image description here

When I check the clusters history on sql3 (Leader) I see a bunch of failovers/switchover nearly within minutes/hours. Check below.

enter image description here

I checked the log files from the TL 242 on the sql3 (Leader) as an example.

enter image description here

Somehow does this happened too many times. Like in the first line the cluster gets a request to shut down. But I can’t figure out from where.
Since I came across this problem I’m not sure how to solve it and I hope with the help of the community I will be able to do so.
Maybe this helps others as well.

query performance – PostgreSQL – Simple deletes takes more time

Today I found a weird(may not be) thing in PostgreSQL 12. Im trying to delete rows from a table with where condition from another. Both are small tables. But the delete is going more than an hour but not completed.

Row count:

convo=> select count(*) from convert  ;
 count
--------
 221368
(1 row)

convo=> select count(*) from gredd  ;
 count
--------
 763150
(1 row)

Index

convert  => feed_id - indexed
gredd  => feed_id - primary key

Delete query

 delete from convert   where feed_id not in(select feed_id from gredd );

Explain

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Delete on convert  (cost=0.00..15163945284.00 rows=110684 width=6)
   ->  Seq Scan on convert  (cost=0.00..15163945284.00 rows=110684 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..135098.55 rows=761370 width=6)
                 ->  Seq Scan on gredd  (cost=0.00..128316.70 rows=761370 width=6)

postgresql – How would I improve my “algorithm” for figuring out my average spending on food per month?

I have a database table keeping track of every grocery delivery. It’s stored as timestamps and sums (EUR).

Currently, I do this:

SELECT (SUM("amount in EUR") / 3) 
FROM personal."food purchases" 
WHERE "purchase time" >= now() - INTERVAL '3 months';

Now, let’s say that the current day is 1st of September 2020. This “algo” will count together all the deliveries from exactly three months back from the current moment, which I guess means from the first day of June, and then simply slash this price into 3 to symbolize “three months”.

Sometimes, depending on the day, this will give a reasonable value. Other times, and I cannot fully describe/understand why, this gives either a too high or too low value. I suspect that the reason for this is that my food purchases/deliveries don’t always happen on the exact same day, and vary quite a bit in price as well. Thus, the resulting sum, even though I do an interval of three months and then slash with three, and thus “should” give the average food cost “recently”, is oftentimes misleading.

I can’t tell for sure, but I think that it sometimes includes one purchase “too many”, and sometimes “one too few”. It might even be worse than that.

Can you help me improve my formula to be more “fair”? I want to know the average amount I pay for food each month, and I’m not convinced that I’m getting that value with my current query.

I think I need to drop the part which goes three months back from the current time, and instead somehow calculates the… last three… months… um… Frankly, I don’t know what I need to do. I just don’t think that the current method is correct.

I’d appreciate if your suggestion/solution stays as close as possible to my original query. I want to understand what is happening. I actually suspect that this is a very common issue and probably built into PG and all other database softwares.

foreign key – PostgreSQL Generate create Forging keys from information schema or pg_constraint

Recently on a testing infra, to restore multiple tables(via copy command) I dropped all the foreign keys.
I have restored this database schema dump into a different database before dropping the FK.

Is there any way that I can generate the create FK from information schema or pg_constraint?

I tried to get it from dump file, but the create table statement itself having the FK(not an Alter table statement).

Any suggestions?

amazon rds – Unable to create role on AWS RDS postgresql database

I’m trying to create a read-only user on an AWS RDS PostgreSQL database. I am logging in using psql, with the default user that was created in the RDS dashboard when I created the database. Yesterday I was able to create a role called readonly, but I realized I did not add some parameters I wanted to add such as NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION so I deleted the role. Then I went to create the role again and the operation timed out with a weird error, so I disconnected.

Well today when I tried to log in with the same user and create a role with command CREATE ROLE readonly;, I get: ERROR: permission denied to create role. I ran the command l and see this (I am myuser for example):

aws_pgadmin=CTc/aws_pgadmin+
myuser=CTc/aws_pgadmin

Any tips on how to resolve this? Do you think I triggered some kind of security protection so I am no longer allowed to create a role?

postgresql – Why is there “no unique constraint” error for primary keys?

I have following table:

create table tbl (id_p int, id_c int, primary key (id_p, id_c));

I want to create a new table:

create table tbl_tbl (id_p int references tbl(id_p), id_c int references tbl(id_c), primary key (id_p, id_c));

But this command fails with the following error:

ERROR:  there is no unique constraint matching given keys for referenced table "tbl"

But the following command works:

create table tbl_tbl (id_p int, id_c int, primary key (id_p, id_c), foreign key (id_p, id_c) references tbl (id_p, id_c));

Why does this work where are the earlier command to create a table failed? How are these two ways of creating a table different?

The PostgreSQL database cannot start after modifying the IP

The following is the operation process:

(root@freelab ~)# su - postgres
Last login: Sat Jul  4 10:16:58 EDT 2020 on pts/1
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /postgres -l logfile start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$
-bash-4.2$ date
Sat Jul  4 10:35:13 EDT 2020

But the log does not seem to be updated accordingly

(root@freelab log)# tail -f postgresql-Sat.log
2020-07-04 09:23:14.930 EDT (1832) LOG:  background worker "logical replication launcher" (PID 1840) exited with exit code 1
2020-07-04 09:23:14.930 EDT (1835) LOG:  shutting down
2020-07-04 09:23:14.943 EDT (1832) LOG:  received SIGHUP, reloading configuration files
2020-07-04 09:23:14.943 EDT (1832) LOG:  parameter "data_directory" cannot be changed without restarting the server
2020-07-04 09:23:14.944 EDT (1832) LOG:  configuration file "/postgres/postgresql.conf" contains errors; unaffected changes were applied
2020-07-04 09:23:15.052 EDT (1832) LOG:  database system is shut down