Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english.

I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on that table where ultra slow) i delete it all. but the queries i make on that table stills super slow. it only have 300 rows now.

i have to clean some cache? i have to wait something? the configuration of the RDS in aws is default.

the engine version of postgres is 12.5, also have postgis installed in it.

i check for vacuum issues and run this command:

SELECT relname AS TableName,n_live_tup AS LiveTuples,n_dead_tup AS DeadTuples,last_autovacuum AS Autovacuum,last_autoanalyze AS Autoanalyze FROM pg_stat_user_tables; 

the table with the problem says:

'appointment_timeslot', 1417, 0, datetime.datetime(2021, 7, 21, 18, 13, 8, 193967, tzinfo=<UTC>), datetime.datetime(2021, 7, 21, 18, 13, 30, 551750, tzinfo=<UTC>) 

check for indexes that Django creates automaticly on that table and i find 4

[ ('appointment_timeslot_pkey', 'CREATE UNIQUE INDEX appointment_timeslot_pkey ON public.appointment_timeslot USING btree (id)') 'appointment_timeslot_home_visit_id_62df4faf', 'CREATE INDEX appointment_timeslot_home_visit_id_62df4faf ON public.appointment_timeslot USING btree (home_visit_id)') ('appointment_timeslot_office_id_6871b47b', 'CREATE INDEX appointment_timeslot_office_id_6871b47b ON public.appointment_timeslot USING btree (office_id)') ('appointment_timeslot_time_range_id_715578fa', 'CREATE INDEX appointment_timeslot_time_range_id_715578fa ON public.appointment_timeslot USING btree (time_range_id)') ] 

PostgreSQL 11 – Can I turn a schema into the wild west?

I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, including to tables they do not own. The reason I have been asked to make the schemas a free-for-all is my supervisor does not want any time spent administering the database beyond me adding/deleting schemas. I am trying to set this up with a PostgreSQL 11 Amazon RDS.

For the test schema, in an attempt to simplify the SQL, I have created a group role and added the users working on the project (each has inherit permissions enabled) to the group. I am then granting permissions to the group at the database and schema level. My hope was that everyone in the group would be able to do anything the group can do, and do it to table(s) created by any other user in the group.

My initial attempt had limited success. I can add a table and the other users can view the table, append data to the table, edit individual cells, and delete rows. However, they are unable to delete the table I created. In addition, the users can only do that to tables I create. If another user creates a table, no one is able to even view the table, even though I set default privileges on the schema before anyone created any tables. I came across the reason for this while trying to make it work and am hoping there is a way around it.

To open things up as much as I can, I took the following actions:

  • Gave the group role full permission to the database with GRANT ALL ON DATABASE dbname TO grpname;

  • After I created the schema I ran: GRANT ALL ON SCHEMA testschema TO grpname;

    ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON TABLES TO grpname;

    ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON SEQUENCES TO grpname;

    ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT EXECUTE ON FUNCTIONS TO grpname;

To confirm the reason I found regarding why ALTER DEFAULT PRIVILEGES only gave those privileges to tables I created, I created a new schema with the group role as the owner and added tables as several users, including myself. Since the group role did not create the tables, its members did, no one could view any table other than their own, let alone modify another user’s table.

A final wrinkle involves where this development database is located. It is being added to a server group that includes our main production database so I cannot make all users a superuser. Not sure that would fix things, but thought I would mention it.

Have I missed/overlooked something that would allow a schema free-for-all?

DBAs using PostgreSQL sought for paid research study

Redgate software a leading provider of database development and monitoring tools are carrying out some research to better understand how organisations monitor their PostgreSQL databases. If you’re a DBA (or similar) looking after production PostgreSQL databases we’d like to speak to you. A $ 150 Amazon voucher (or equivalent in local currency) is available for all participants taking part one of the 60 min research calls.

If you’d like to take part in this research enter your details at: https://redgate.research.net/r/postgresresearch

Please note your details will not be used for any other purpose. There are a limited number of research calls so we can’t guarantee that everyone will be invited to take part in one.

Is it possible to set permission to other user for the table created by current user with PostgreSQL?

In PostgreSQL, created these 2 roles and grant different permissions to them.

create user user1 with login; create role operator; grant operator to user1;  create user user2 with login; create role readwrite; grant readwrite to user2; 

When created a table by user1, such as table1, user2 can’t use this table. It got permission denied.

Is it the right feature for PostgreSQL? If it is, how to make the table also can be used for user2?

Does postgresql statement_timeout also apply to a blocked query

Does postgresql statement_timeout also apply to a blocked query (version 9.6)?

From my tests this does not seem to be the case. I created a blocking query using the following example taken from https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/

CREATE TABLE items (   key text primary key,   value jsonb );  BEGIN; ALTER TABLE items ADD COLUMN last_update timestamptz; 

In a separate SQL session I do the following

set statement_timeout to 10000; commit; select * from items; 

Note there is no global timeout set at the database level.

This query does not return with a timeout after 10 seconds. The timeout documentation states "The timeout is measured from the time a command arrives at the server until it is completed by the server." hence I would expect it to apply to blocked queries as well.

PostgreSQL backend function after an elog(ERROR) had been issued

Getting this weird issue one time per postgresSql session. Like if I run this query first time, will return with below message but if I rerun the same, it works fine.

DEBUG: An attempt was made to call a PostgreSQL backend function after an elog(ERROR) had been issued ERROR: org.glassfish.jersey.internal.ServiceConfigurationError: org.glassfish.jersey.internal.inject.InjectionManagerFactory: : java.io.IOException: An attempt was made to call a PostgreSQL backend function after an elog(ERROR) had been issued.

how to connect to PostgreSQL using a stable way

Now I am install PostgreSQL 13 in remote cloud server and expose the 5432 port to public network. I add a whitle list in PostgreSQL config file pg_hba.conf like this:

host    all             all             127.113.122.1/24           scram-sha-256 

which 127.113.122.x is my public ip address of my network, but the problem is the ip address always change when I restart the Router or other reason. When the IP changed, I have to login into remote server and change the PostgreSQL config file and remove the old ip and add new ip. It make me crazy, I have to change the IP of PostgreSQL config again and again, is there any way to connect PostgreSQL using a stable way? both secure and easy to connect.

SQL Server to PostgreSQL equivalent

I need some help in converting or equivalent of below in postgreSQL..

From MSSQL code to postgreSQL:-

1. CONVERT(bit,CASE WHEN column_a & 1 > 0 THEN 1 ELSE 0 END) as ABC, 2. CONVERT(tinyint,CASE WHEN column_a IS NOT NULL THEN 0  END) as ABC 3. CONVERT(smallint,CASE WHEN col_year = (SELECT year(col_date)      FROM tab A WHERE A.a = 192) THEN 1 ELSE 0 END) as ABC, 4. TO_CHAR(TO_CHAR(YEAR(Date))||'Y') as ABC 5. CASE WHEN COL_A = 1 THEN TO_CHAR(YEAR(Date))||'Y'  6. WHEN COL_A = 4 THEN TO_CHAR (YEAR(Date) ) || 'Q' || TO_CHAR(Month(Date)/3 ) ELSE NULL END as ABC 

Please help..

Pg_rewind Uitility – PostgreSQL Snapshot standby

We have Primary-secondary PostgreSQL Streaming replication.

I have done some testing.

Steps.

1.Promote secondary

2.Run some DML on secondary which is in Write mode(another new primary)

3.Down new primary

4.Re-attach with primary using pg_rewind utility.

So, Can we use these steps to create snapshot standby database like we have in Oracle database.

Calculate days between to dates postgresql

I’m having a problem:

I’m trying to calculate days between two dates.

Let’s say I have the date ‘2005-05-25 06:04:08’ and I want to count how many days are from that day to the current date.

I tried doing:

SELECT DATE_PART('day', AGE(CURRENT_TIMESTAMP, '2005-05-25 06:04:08'::timestamp )) AS days; 

But for some reason this returns 11, it’s not taking into count the HUGE difference in years. How can I solve this?