Thread: copy/duplicate database schemas

I have an application in a schema and now i need to create other schemas b/c the app needs to support different languages,  is there an easy way to copy an entire schema to a new one (tables, contents, trigges, functions, etc..)?

On Wed, Jun 23, 2010 at 3:21 PM, Jamie Kahgee <jamie.kahgee@gmail.com> wrote:
> I have an application in a schema and now i need to create other schemas b/c
> the app needs to support different languages,  is there an easy way to copy
> an entire schema to a new one (tables, contents, trigges, functions, etc..)?
> right now i just have
>
> schema
>
> what I want is
>
> schema
> schema_ar
> schema_ru
> etc...

sure:
1) pg_dump -n your_schema -s
2) sed the resulting schema s/old_schema/new_schema
if you are lucky, you will have few if any improper replacements, if
not, tweak sed till it's right
3) cat it back into psql

merlin

On 24/06/10 03:21, Jamie Kahgee wrote:
> I have an application in a schema and now i need to create other schemas
> b/c the app needs to support different languages,  is there an easy way
> to copy an entire schema to a new one (tables, contents, trigges,
> functions, etc..)?

Others have replied with suggestions on how to do this, so I won't
repeat that - but I think you need to look at the bigger picture.

If you copy your app into multiple schema then modify each to localize
them, you're going to have a maintenance nightmare on your hands if you
ever intend to fix bugs or add new features to your app. All the copies,
including copies in languages you don't speak, will have to be kept up
to date.

You might want to investigate internationalization options instead,
where you can process your "master" sources to produce a list of
strings, and have translators translate those strings. Your code loads
the string lists, and depending on the setting for the "current
language" decides which mapping of strings to translations to use when
emitting messages.

This adds significant complexity to your code, especially since (AFAIK)
there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc.
However, it'll save you a LOT of pain not to maintain five (or more -
new translations are always required) versions of your code down the track.

--
Craig Ringer

On Thu, Jun 24, 2010 at 11:29:48AM +0800, Craig Ringer wrote:

> You might want to investigate internationalization options instead,
> where you can process your "master" sources to produce a list of
> strings, and have translators translate those strings. Your code loads
> the string lists, and depending on the setting for the "current
> language" decides which mapping of strings to translations to use when
> emitting messages.
>
> This adds significant complexity to your code, especially since (AFAIK)
> there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc.

But there is - whether good or not: Go to
http://gitorious.org/gnumed and browse the tree under
gnumed/server/SQL/. Look at the i18n schema which provides
functions for registering strings for translation and
translating them (i18n.upd_tx()) and using translated
strings nearly transparently in your queries like so:

    SELECT _(label) FROM your_label_table;

Which will return label translated into what the connected
user registered as his preferred language or in the source
language if none registered or no translation available.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Merlin Moncure <mmoncure@gmail.com> writes:
> On Wed, Jun 23, 2010 at 3:21 PM, Jamie Kahgee <jamie.kahgee@gmail.com> wrote:
>> I have an application in a schema and now i need to create other schemas b/c
>> the app needs to support different languages,  is there an easy way to copy
>> an entire schema to a new one (tables, contents, trigges, functions, etc..)?
>> right now i just have
>>
>> schema
>>
>> what I want is
>>
>> schema
>> schema_ar
>> schema_ru
>> etc...
>
> sure:
> 1) pg_dump -n your_schema -s
> 2) sed the resulting schema s/old_schema/new_schema
> if you are lucky, you will have few if any improper replacements, if
> not, tweak sed till it's right
> 3) cat it back into psql

I think you could also :
1) pg_dump -n your_schema -s > your_schema.sql
2) alter schema your_schema rename to your_schema_ar;
3) psql -f your_schema.sql
4) goto 2, pick another name

Regards,
--
dim

> > This adds significant complexity to your code, especially since (AFAIK)
> > there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc.
>
> But there is - whether good or not: Go to
> http://gitorious.org/gnumed and browse the tree under
> gnumed/server/SQL/. Look at the i18n schema which provides
> functions for registering strings for translation and
> translating them (i18n.upd_tx()) and using translated
> strings nearly transparently in your queries like so:
>
>     SELECT _(label) FROM your_label_table;
>
> Which will return label translated into what the connected
> user registered as his preferred language or in the source
> language if none registered or no translation available.

http://gitorious.org/gnumed/gnumed/trees/master/gnumed/gnumed/server/sql

Under this link look at gmI18N*.sql, note that below the versioned
directories there are several improvements and fixes but you'll get
the idea.

Karsten
--
GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl.
Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl