Copy database in postgres

Question

I have a requirement in which I need to take a snapshot of a database and restore it in the same machine with some other predefined name in postgres. I have tried to achieve the above task with the following command.

CREATE DATABASE destniationDb TEMPLATE sourceDb;

But this option fails when the connection/session to the sourceDb exists.So I need to truncate this option as there is high possibility of user doing read operation. All command line options like restore_db,backup_db doest suit my requirement.Hence,I need some console command/function/store procedure to achieve it i.e, I need to connect to the database and call some command/function/store procedure that achieves this objective.

Can anyone of you suggest some kind of solution to my requirement?

Answer 1

Why don't you just create a dump of the existing database sourceDb using the command

pg_dump sourceDb > destinationDb.sql

And the in this SQL dump destinationDb.sql, change the db name to the new one in the CREATE DATABASE line. After that, you can then create this new DB on the server using psql like:

psql destinationDb < destinationDb.sql