Thread: Help to dump tables in a database and restore in another database
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
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
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
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 >
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
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
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