Postgresql – Postgres Client – save table sorting

What was wrong with the answers on those two questions? They were 100% accurate, so I'm not sure what more we can do to help you here.

I would suggest that you confirm that you never assume that you're in a transaction. Always check to make sure you are. In TSQL it would be as simple as checking @@TRANCOUNT to be greater than 0. That's rather the same as any threaded situation where you want to check mutexes. What happens if you do it with a stored procedure? It kills the stored procedure, yes? Because SPs are intended to be atomic. What you're demonstrating has nothing to do with atomicity.

To be clear, this behavior is by design! Don't run atomic transactions by hand in the console, put them in a program so if the connection goes away it's gone. This is not something that you can "just hope works right".

If you have something that must be ACID, you must put it in a container that can be made ACID. This means a stored procedure or the like.

I think backing up the "data directory" is what you want.

Moving the whole directory to a new server should be what you might look into. This will move everything within the cluster. From there, you can drop, move,... your single databases as you need to.