1

I dump a postgresql database on one machine as follows:

sudo -i -u postgres
pg_dump ire --clean --create --format=p > xfer.sql

On a second, private machine, whose database is not exposed to the network, I restore the dump with:

sudo -i -u postgres
psql -U postgres < xfer.sql

This works once, but then any attempt to overwrite the database gets this error message:

ERROR:  database "ire" is being accessed by other users
DETAIL:  There is 1 other session using the database.

I can easily fix it using e.g. this solution but how can I avoid creating this dangling session in the first place?

Tom Swirly
  • 2,740
  • 1
  • 28
  • 44
  • Does the same thing happen when your consume the dump using `-f` (`--file`) flag? Eg.: `psql -U postgres -f xfer.sql` – Kamil Bęben May 25 '21 at 22:33
  • Unfortunately, yes it does. – Tom Swirly May 25 '21 at 22:51
  • 2
    look in pg_stat_activity to see where the session is coming from. Then use that knowledge to fix it. – jjanes May 25 '21 at 23:17
  • 1
    It must be some session other than the `psql` session that restored the dump. – Laurenz Albe May 26 '21 at 02:03
  • I just spun up this machine a few days ago. No one else is using it for anything. It's a very generic Ubuntu 20 with nothing installed. I don't know how to "look in pg_stat_activity". I don't see any such database or table. – Tom Swirly May 26 '21 at 08:54
  • I did install `pg_activity` and run it, going through all three query pages. Everything seems empty... – Tom Swirly May 26 '21 at 08:59
  • It is a view with a full name of pg_catalog.pg_stat_activity. It automatically exists in every database. – jjanes May 26 '21 at 18:02

0 Answers0