Tuesday, September 11, 2007

Restore Backup on PostgreSQL

To restore the backup on postgresql the steps are:-

1) dropdb
2) createdb
3) psql or pg_restore to import data into the backup database.

If some clients are connected to the backup database during the 1st
Step (dropdb) the postmaster throws an error saying clients connected
cannot drop database.

” ERROR: database “database_name” is being accessed by other users “

Following will solve the problem of killing connections prior to dropping:

kill `ps auxww | grep ‘postgres: postgres <database>’ | grep -v ‘grep’
| perl -F”\\s+” -ane ‘print “$F[1] “;’`

Basically, this takes a process listing, finds all entries
corresponding to connections to the target database, excludes the
calling command, then isolates the pids using Perl.

Following will be Much cleaner than previous

pkill -f ‘postgres: postgres &lt;database&gt;’

Powered by ScribeFire.

1 comment:

Anonymous said...