cloning database

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 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" ?

There is. You can use database_source as the template for creating new databases. There are a few restrictions, like the template 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 &lt;

philipp.kraus@

&gt;
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general