Copy database in postgres

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 开发者_JS百科call some command/function/store procedure that achieves this objective.

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

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

Have you tried locking the table first?

EDIT: I may have been oversimplifying. I was thinking if you lock writes to the tables you are copying the operation might work. But seems that isn't the case when cloning the whole db.

Going from the link you provided in your comment the database must have no active sessions. I solve this by simply restarting the postgres service immediately before the operation. If the script is fast enough your subsequent copy should run before new sessions can connect. I believe that postgres will wait up to 90 seconds for sessions to end so this solution should not be too disruptive.