How to debug locked transaction on a remote system? I have a remote database that is currently throwing errors. On a primary key of type BIGSERIAL it says no new rows can be created because a value for the primary key already exists (the value itself is in the mid tens of thousands).

My problem is that we have very little information, just the error. Is there a way to rollback that transaction from a remote location? Is there a way to dump the db contents, not just export the tables, to check what the db engine is doing?
No, you can't undo, rollback or reverse a commit.

STOP THE DATABASE!

(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE or similar, not an rm -rf /data/directory scenario).

If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate so that no checkpoint is run on shutdown.

You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.

If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.

Please have a look here for Detailed Information: