0

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.

I've used 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.

I've used touch tmp/restart.txt to tell Passenger to restart the website before the import, but it doesn't appear to kill the existing worker.

Robert K
  • 572
  • 1
  • 5
  • 12

1 Answers1

0

Because of Phusion Passenger, Apache needs to be shut down before you can restore the database. You can use apachectl graceful-stop before restoring the database, then apachectl start after. There is probably a more graceful way to do a "System Maintenance" landing page during the process, but I'll let a real sysadmin chime in on that (and anything else I missed).

Robert K
  • 572
  • 1
  • 5
  • 12