I'm transferring a database as part of transferring a website. I've not used PostgreSQL before, so assume that I don't know much about it (I primarily use MySQL). We cannot directly connect the machines to use the
pg_dump -h host1 dbname | psql -h host2 dbname trick.
pg_dumpall -c to dump all databases into an SQL file with DROP commands. I've then downloaded and uploaded the backup to the destination server via SCP. But I cannot import the same exported file using
psql -U root postgres backup.sql because of this error:
DROP DATABASE ERROR: database "exfi" is being accessed by other users DETAIL: There are 1 other session(s) using the database.
I suspect that this is caused by the Ruby on Rails app maintaining a persistent connection to the database. We're using Phusion Passenger 3.0.11 with Apache 2.2.21 on RHEL 16.
touch tmp/restart.txt to tell Passenger to restart the website before the import, but it doesn't appear to kill the existing worker.