Copy database to postgres

Question

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

CREATE DATABASE destniationDb TEMPLATE sourceDb; 

But this parameter fails if a connection / session with source code exists. Therefore, I need to truncate this parameter, since there is a high probability that the user will perform a read operation. All command line options like restore_db, backup_db meet my requirements. I need some kind of console command / function / storage procedure to achieve it, i.e. I need to connect to the database and call some storage command / function / procedure that accomplishes this.

Can any of you suggest any solution for my requirement?

Answer 1

Why don't you just dump the existing sourceDb database with the command

 pg_dump sourceDb > destinationDb.sql 

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

 psql destinationDb < destinationDb.sql 

Answer 2

Did you try to lock the table first ?

EDIT: Maybe I'm simplifying. I thought if you lock records on the tables you are copying, the operation might work. But it seems that this is not the case when cloning the entire db.

Based on the link provided in your comment, there should be no active sessions in the database. I solve this by simply restarting the postgres service just before the operation. If the script is fast enough, your next copy should start before new sessions can connect. I believe that postgres will wait up to 90 seconds to end sessions, so this solution should not be too damaging.