8

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?

Rais Alam
  • 6,970
  • 12
  • 53
  • 84
Patton
  • 2,002
  • 3
  • 25
  • 36

2 Answers2

12

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
mvp
  • 111,019
  • 13
  • 122
  • 148
JWL
  • 13,591
  • 7
  • 57
  • 63
  • why? can u elaborate on this special case? – JWL Jun 28 '11 at 08:49
  • actually we dont want to use any postgres command line options like createdb,dropdb inthis case pg_dump;and the option we are looking for is some thing different,say programmatic(using JDBC). The option I mentioned CREATE DATABASE destniationDb TEMPLATE sourceDb; takes less than 6 sec to complete the operation.In the case you have mentioned it takes a min to complete the operation BTW I have tried the solution given here http://stackoverflow.com/questions/1237725/how-to-copy-postgres-database-to-another-server – Patton Jun 28 '11 at 09:31
0

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.

SpliFF
  • 38,186
  • 16
  • 91
  • 120
  • http://www.postgresql.org/docs/9.0/static/manage-ag-templatedbs.html Please go through this.It clearly says that when a session for the source Db is open then the operation fails. BTW I didnt try this locking the table can you please give me an example on how to do it in current scenario? – Patton Jun 28 '11 at 06:43
  • @Spiff Your solution looks fine will check out if this is feasible. – Patton Jun 28 '11 at 09:26