(Moved question from stackoverflow to dba.stackexchange) I am using AWS Aurora Postgres 13.8. Now we have around 35-40 replication slots in DB and we dropped few replication slots. The reason for dropping was we were seeing restart of DB. CPU, Memory and Write/Read IOPS are normal. We are currently on db.r6i.24xlarge. We have verified that there are no long running transactions and no blocked pids We dropped around 12(First 7 and then 5) replication slot in DB. xmin horizon is increasing and is not completely able to vacuum table. Below is from logs First, we dropped 7 slots, logs after that were like 2024-10-03 14:20:19 UTC::@::LOG: automatic vacuum of table ".pg_catalog.pg_statistic": index scans: 0 pages: 0 removed, 4175180 remain, 0 skipped due to pins, 649889 skipped frozen tuples: 0 removed, 63044551 remain, 62974732 are dead but not yet removable, oldest xmin: 850469227 buffer usage: 7064601 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 9.86 s, system: 0.02 s, elapsed: 50.84 s WAL usage: 0 records, 0 full page images, 0 bytes Then we dropped 5 more slots, logs were then like:- 2024-10-03 17:02:12 UTC::@::LOG: automatic vacuum of table ".pg_catalog.pg_statistic": index scans: 0 pages: 0 removed, 4175180 remain, 0 skipped due to pins, 2299157 skipped frozen tuples: 0 removed, 33025155 remain, 32953376 are dead but not yet removable, oldest xmin: 883545046 buffer usage: 3757715 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 4.93 s, system: 0.00 s, elapsed: 15.72 s WAL usage: 0 records, 0 full page images, 0 bytes I do understand that no. of dead tuples have been cleaned significantly but how many more replication slot do we need to drop in order to remove this error. Also we have started seeing below error in logs now apart from above error. 024-10-04 07:03:15 UTC::@::LOG: automatic vacuum of table "..": index scans: 1 pages: 0 removed, 5949 remain, 0 skipped due to pins, 557 skipped frozen tuples: 5485 removed, 79016 remain, 0 are dead but not yet removable, oldest xmin: 903322403 buffer usage: 3757819 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 2.03 s, system: 0.00 s, elapsed: 13.15 s WAL usage: 0 records, 0 full page images, 0 bytes As per the latest comment by Laurenz Albe in stack overflow, I was asked to to **drop all stale replication slots (where restart_lsn in pg_replication_slots is old)** I ran this query select slot_name, active, catalog_xmin, restart_lsn, pg_current_wal_insert_lsn()-restart_lsn as diff_lsn from pg_replication_slots order by diff_lsn desc; First 2 slots have a huge difference with diff_lsn 793117723072, 427503896816 and then all slots with 92440376. I understand that first 2 definitely needs to be deleted. However what about the remaining since their difference is also huge. Also how can I prevent such scenarios in future.

we are working with a postgres database V13. We're facing a problem that previously permissions were just inherited/created when a new table was created. It is not working anymore, when I create a new table no permissions are set at all. In our database we have a user structure, where we have user groups, e.g. **group1**. Then we have a user belonging to that user group named **user1** and it inherits from **group1**. Next, there is a schema that grants specific default priveleges with **group1** being the grantor in that case. This schema is owned by user group **schema1**. Now question Nr. 1: If I create a table as **user1**, shouldn't the default priveleges defined through the user group **group1** automatically be applied to that table? Because **user1** belongs to that group and inherits from it. Question Nr. 2: the schema in which I create this table is owned by **schema1** user group. When I set the owner of the table to **schema1** does that have any implications for the table priveleges? What I expected was a table that in the permissions has two entries: 1. **other_user_group1** read permissions granted by **user1** or **group1** through the default permissions 2. **schema1** all permissions granted by **schema1** Maybe I have misunderstood how these permissions are inherited,so it would be great to further understand this. Also, if this is not how it works, how this has worked beforehand and what might have changed. Thanks so much for your help!

Why does moving a postgres table from one tablespace to another take so long (and how can I speed it up) I have a postgres database with two main tablespaces, one on a SDD, the other on magnetic HDD. Occasionally I need to move some very large tables from the SDD to the HDD using ALTER TABLE... SET TABLESPACE... this works ok but can take several hours. I know that I can find the physical location for the table being moved by SELECT pg_relation_filepath('myTableName'); which in this case returns the partial path *pg_tblspc/59626/PG_13_202007201/59627/890785* If I switch from postgres to the operating system I can local several dozen files called *890785.nnn* (where *nnn* is a number) I assume (please correct if I'm wrong) that these files make up the bulk of the table data? I can monitor this directory, and see the files are being copied across as the ALTER TABLE command runs. There are several GBytes of files here, experimentation shows me that I can copy the files (via windows) in about on tenth of the time it takes postgres to move them. My question is, why does postgres take so long? is it doing something more than just a move? if so, why? And most importantly, is there anyway of speeding it up? (BTW - all other users are disconnected before running *ALTER TABLESPACE...*