Обсуждение: cloning database

Hello,

I need around 150 copies of a database (for an exam). I have got a
database with tables and data and for my exam I would copy this
database in his way:

database_source

database1
database2
….
database150

Is there a buildin way to clone the "database_source" with all
structure and data into a new database "database1..150" ?

Thanks a lot

Phil

On 19 Sep 2014, at 8:35, Philipp Kraus <philipp.kraus@flashpixx.de> wrote:

> Hello,
>
> I need around 150 copies of a database (for an exam). I have got a database with tables and data and for my exam I
wouldcopy this database in his way: 
>
> database_source
>
> database1
> database2
> ….
> database150
>
> Is there a buildin way to clone the "database_source" with all structure and data into a new database
"database1..150"? 

There is. You can use database_source as the template for creating new databases. There are a few restrictions, like
thetemplate database not being active, for example. See the docs for CREATE DATABASE and the createdb command. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Philipp Kraus wrote:
> I need around 150 copies of a database (for an exam). I have got a
> database with tables and data and for my exam I would copy this
> database in his way:
> 
> database_source
> 
> database1
> database2
> ….
> database150
> 
> Is there a buildin way to clone the "database_source" with all
> structure and data into a new database "database1..150" ?

CREATE DATABASE database1 TEMPLATE database_source;

Yours,
Laurenz Albe

On 09/19/2014 07:04 PM, hubert depesz lubaczewski wrote:
>
>
> for i in {1..150}; do createdb -T database_source database$i; done

Unless `datistemplate` is set for the database, you'll need to do this
as a superuser.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 2014-09-19 11:04:36 +0000, hubert depesz lubaczewski said:

On Fri, Sep 19, 2014 at 8:35 AM, Philipp Kraus <philipp.kraus@flashpixx.de> wrote:

Is there a buildin way to clone the "database_source" with all structure and data into a new database "database1..150" ?

assuming you're using bash shell, this should work:

for i in {1..150}; do createdb -T database_source database$i; done

it's not a built-in, but very close.

depesz

Thanks this is nice, to define a template and run the shell script

Thanks a lot

Phil

hubert depesz lubaczewski wrote
> On Fri, Sep 19, 2014 at 8:35 AM, Philipp Kraus <

> philipp.kraus@

> >
> wrote:
>
>> Is there a buildin way to clone the "database_source" with all structure
>> and data into a new database "database1..150" ?
>>
>
> assuming you're using bash shell, this should work:
>
> for i in {1..150}; do createdb -T database_source database$i; done
>
> it's not a built-in, but very close.
>
> depesz

Any advantage to this compared to using pl/pgsql via a DO command while
connected to the postgres database?  Note it would require dynamic SQL
(i.e., EXECUTE).

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/cloning-database-tp5819599p5819805.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.