Postgres Pro
Downloads
Home   >   mailing lists

Thread: Copy Views From Database?

Copy Views From Database?

From
"lennie@corepat.com"
Date:
Hi Guys,

I'm using PostGreSQL with a Java project since I'm a proffesional Java
developer.

I've a database that have about 120 views in PostGreSQL 7.0 that I need to
copy into PostGreSQL 8.0. I can copy them one-by-one in pgAdmin but I don't
have the time! Is there a maybe a tool that I can use to copy views as-is
from one database into another?

Kind Regards,

Lennie De Villiers

Java Developer
CorePat Systems (Pty) Ltd

www.corepat.com


--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .

Re: Copy Views From Database?

From
Hannes Dorbath
Date:
use pg_dump and pg_restore :)

On 22.09.2005 10:37, lennie@corepat.com wrote:
> Hi Guys,
> 
> I'm using PostGreSQL with a Java project since I'm a proffesional Java
> developer.
> 
> I've a database that have about 120 views in PostGreSQL 7.0 that I need to
> copy into PostGreSQL 8.0. I can copy them one-by-one in pgAdmin but I don't
> have the time! Is there a maybe a tool that I can use to copy views as-is
> from one database into another?
> 
> Kind Regards,
> 
> Lennie De Villiers
> 
> Java Developer
> CorePat Systems (Pty) Ltd
> 
> www.corepat.com
> 
> 
> --------------------------------------------------------------------
> mail2web - Check your email from the web at
> http://mail2web.com/ .
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

cancel

From
light@theendofthetunnel.de
Date:
This message was cancelled from within Mozilla.

Re: Copy Views From Database?

From
Hannes Dorbath
Date:
On 22.09.2005 10:37, lennie@corepat.com wrote:
> Is there a maybe a tool that I can use to copy views as-is from one database into another?

pg_dump and pg_restore :)

Re: Copy Views From Database?

From
Hannes Dorbath
Date:
On 22.09.2005 11:47, lennie@corepat.com wrote:
> How would you use it to only dump / restore the views? I don't want any database tables.

Please RTFM. But because I'm such a nice guy:

Create a binary dump of the schema:
pg_dump <database> -v -s -i -F c -Z 9 -U <superuser> -f /tmp/dump.bin

Use the -l option of pg_restore to create a TOC file and filter your 
views with grep:
pg_restore -l /tmp/dump.bin | grep -E "[[:space:]]VIEW[[:space:]]" > 
/tmp/tmp.toc

Check your TOC file with less to see if everything you want is in it:
less /tmp/tmp.toc

In case all seems fine run pg_restore with that TOC list as argument and 
check the SQL statements it generates:
pg_restore -i -v -O -L /tmp/tmp.toc /tmp/dump.bin | less

In case all seems fine again, run it against your other database:
pg_restore -i -v -O -d <other_database> -U <user> -L /tmp/tmp.toc 
/tmp/dump.bin


Hope it helps, in case it trashes your server, don't blame me and read 
the manual before you're doing anything.

Best regards,
Hannes Dorbath