Thread: Help to dump tables in a database and restore in another database

Help to dump tables in a database and restore in another database

From
Intengu Technologies
Date:
I would like to dump a number of tables from a database and load them
on another database. How do I accomplish this.

--
Sindile Bidla

Re: Help to dump tables in a database and restore in another database

From
Joshua Tolley
Date:
On Fri, Oct 16, 2009 at 09:38:44AM +0200, Intengu Technologies wrote:
> I would like to dump a number of tables from a database and load them
> on another database. How do I accomplish this.

pg_dump makes this easy. Documentation is here:
http://www.postgresql.org/docs/8.4/static/app-pgdump.html

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Attachment

Re: Help to dump tables in a database and restore in another database

From
Andreas Kretschmer
Date:
Joshua Tolley <eggyknap@gmail.com> wrote:

> On Fri, Oct 16, 2009 at 09:38:44AM +0200, Intengu Technologies wrote:
> > I would like to dump a number of tables from a database and load them
> > on another database. How do I accomplish this.
>
> pg_dump makes this easy. Documentation is here:
> http://www.postgresql.org/docs/8.4/static/app-pgdump.html

Right, additionally, pg_dump has options to specify only destined
tables, views, sequence, schemas etc. to dump.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Help to dump tables in a database and restore in another database

From
Intengu Technologies
Date:
Running Windows7 with Postgresql 8.4

I am trying to dump about 132 tables, this is my command:

pg_dump --host localhost --port 5432 --username postgres -o -v -t
'myschema.*' dbname > mydump.sql

This generates an error no matching tables were found, is the asterisk
not a wild card meaning all tables found in the schema named myschema

When restoring the tables from the dump will i still have the 132
tables or 1 table

Your valued advise is appreciated.


2009/10/18 Andreas Kretschmer <akretschmer@spamfence.net>:
> Joshua Tolley <eggyknap@gmail.com> wrote:
>
>> On Fri, Oct 16, 2009 at 09:38:44AM +0200, Intengu Technologies wrote:
>> > I would like to dump a number of tables from a database and load them
>> > on another database. How do I accomplish this.
>>
>> pg_dump makes this easy. Documentation is here:
>> http://www.postgresql.org/docs/8.4/static/app-pgdump.html
>
> Right, additionally, pg_dump has options to specify only destined
> tables, views, sequence, schemas etc. to dump.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>



--
Sindile Bidla

Re: Help to dump tables in a database and restore in another database

From
Sean Davis
Date:
On Tue, Jan 5, 2010 at 5:42 AM, Intengu Technologies
<sindile.bidla@gmail.com> wrote:
> Running Windows7 with Postgresql 8.4
>
> I am trying to dump about 132 tables, this is my command:
>
> pg_dump --host localhost --port 5432 --username postgres -o -v -t
> 'myschema.*' dbname > mydump.sql
>
> This generates an error no matching tables were found, is the asterisk
> not a wild card meaning all tables found in the schema named myschema

You probably want to use the '-n' argument to specify the schema name
directly.  See the documentation for pg_dump.

> When restoring the tables from the dump will i still have the 132
> tables or 1 table

The database tables will be dumped as is, so you will have pretty much
a copy of your original data (132 tables, if that is the correct
number).

> Your valued advise is appreciated.
>
>
> 2009/10/18 Andreas Kretschmer <akretschmer@spamfence.net>:
>> Joshua Tolley <eggyknap@gmail.com> wrote:
>>
>>> On Fri, Oct 16, 2009 at 09:38:44AM +0200, Intengu Technologies wrote:
>>> > I would like to dump a number of tables from a database and load them
>>> > on another database. How do I accomplish this.
>>>
>>> pg_dump makes this easy. Documentation is here:
>>> http://www.postgresql.org/docs/8.4/static/app-pgdump.html
>>
>> Right, additionally, pg_dump has options to specify only destined
>> tables, views, sequence, schemas etc. to dump.
>>
>>
>> Andreas
>> --
>> Really, I'm not out to destroy Microsoft. That will just be a completely
>> unintentional side effect.                              (Linus Torvalds)
>> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
>> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>>
>> --
>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>>
>
>
>
> --
> Sindile Bidla
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Re: Help to dump tables in a database and restore in another database

From
Tom Lane
Date:
Intengu Technologies <sindile.bidla@gmail.com> writes:
> Running Windows7 with Postgresql 8.4
> I am trying to dump about 132 tables, this is my command:

> pg_dump --host localhost --port 5432 --username postgres -o -v -t
> 'myschema.*' dbname > mydump.sql

> This generates an error no matching tables were found, is the asterisk
> not a wild card meaning all tables found in the schema named myschema

Works for me.  You sure that the pg_dump is actually 8.4?  The -t switch
wasn't bright enough to understand wildcards before 8.2.

Another possibility is that the schema isn't really named myschema
(case sensitivity issues there).

            regards, tom lane

Re: Help to dump tables in a database and restore in another database

From
Thomas Kellerer
Date:
Intengu Technologies, 05.01.2010 11:42:
> pg_dump --host localhost --port 5432 --username postgres -o -v -t
> 'myschema.*' dbname>  mydump.sql
>
> This generates an error no matching tables were found, is the asterisk
> not a wild card meaning all tables found in the schema named myschema

No. The wildcard character in SQL is %

But to select all tables from a specific schema, you can use the -n switch:

pg_dump -n myschema  ...

Check out pg_dump's documentation (Joshua has already posted the link)

Thomas


Re: Help to dump tables in a database and restore in another database

From
Tom Lane
Date:
Thomas Kellerer <spam_eater@gmx.net> writes:
> Intengu Technologies, 05.01.2010 11:42:
>> pg_dump --host localhost --port 5432 --username postgres -o -v -t
>> 'myschema.*' dbname>  mydump.sql
>>
>> This generates an error no matching tables were found, is the asterisk
>> not a wild card meaning all tables found in the schema named myschema

> No. The wildcard character in SQL is %

pg_dump's wildcard is * though.

            regards, tom lane