current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

I have a PostgreSQL database and my app is coded with VB.NET, For my purpose I do Copy my DB(called orginladb) in same server by taking backup of orginladb and create new db(orginladb_copy) and restore orginladb to orginladb_copy

I've written 4 separate code for Bakcup,Drop DB,Create DB and Restore,following is the methods 

/* Backup */
--------
pg_dump  --format=c --username "postgres" originaldb > "D:\Backup\originaldb .backup" 

/*Drop*/
------
psql -U postgres  -d postgres -c "DROP DATABASE  if exists "\"originaldb_Copy"\"" 

/*Create Copy Of Database*/
----------------------------
psql -U postgres  -d postgres -c "CREATE DATABASE "\"originaldb_Copy"\""


/*Restore originaldb.backup to originaldb_Copy*/
---------------------------------------------------
pg_restore  -d originaldb_Copy "D:\Backup\originaldb .backup"

So my question is how to copy original db to the same server in a single step or using a single function ?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

you can create a method in VB.NET like below

Private Sub copy_db()
        Using conn As New NpgsqlConnection("connection_string")
            Dim cmd As New NpgsqlCommand()

            cmd.CommandText = "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'Originaldb';"
            cmd.ExecuteNonQuery()

            cmd.CommandText = "drop database if exists ""Originaldb_Copy"""
            cmd.ExecuteNonQuery()

            cmd.CommandText = "CREATE DATABASE ""Originaldb_Copy"" WITH TEMPLATE ""Originaldb"" OWNER ""owner_name"";"
            cmd.ExecuteNonQuery()
        End Using
    End Sub

and call this function where you want to copy copy_db

share|improve this answer
up vote 0 down vote

You could achieve this using the query (however, a DROP has to be done separately):

CREATE DATABASE originaldb_Copy WITH TEMPLATE originaldb;

Using the "command line" there isn't a shorter way, than the one you've posted, I think.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.