Replacing postgres database

This is applicable to the Peon application, in so far as when the PAF data is updated and a new database is created, normally to a temp test database for testing, afterwhich a renaming or replacing of working database is required.



So, firstly, we need to ensure that there are no active connections, this can be checked with the following query, where target_database is the database name:

SELECT

   *

FROM

   pg_stat_activity

WHERE

   datname = 'target_database';

If we need to kill connections, then the following can be tried where pg_stat_activity.pid is the pid shown by the previous query. target_database is the database name:

SELECT

   pg_terminate_backend (pg_stat_activity.pid)

FROM

   pg_stat_activity

WHERE

   pg_stat_activity.datname = 'target_database';

I never found the above to be successful, so opted instead to shut down the database, which can of course be achieved by the following:

/etc/rc.d/rc.postgresql stop

followed by:

/etc/rc.d/rc.postgresql start

Then as the postgres user, normally postgres, run psql

To drop the database (there should be no active connections now;

DROP DATABASE db_name;

To rename database;

ALTER DATABASE db_name_old RENAME TO db_name_new;

Alternatively, we can of course import the database, but first creating the database and importing. As per here.