Lists: | pgsql-general |
---|
From: | "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at> |
---|---|
To: | "Antonios Katsikadamos *EXTERN*" <antonioskatsikadamos(at)yahoo(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: postgres import |
Date: | 2006-10-31 08:32:51 |
Message-ID: | 52EF20B2E3209443BC37736D00C3C1380B31A578@EXADV1.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> I have a little problem. I have an .sql file ( db dump )
> and i want to import it to postgres on linux.
>
> Does anyone know how i can do it?
You feed it to the command line interface psql.
Example:
psql -h host -p port -d database -U user <dump.sql
Yours,
Laurenz Albe
From: | "Tomi NA" <hefest(at)gmail(dot)com> |
---|---|
To: | "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at> |
Cc: | "Antonios Katsikadamos *EXTERN*" <antonioskatsikadamos(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgres import |
Date: | 2006-10-31 23:17:11 |
Message-ID: | d487eb8e0610311517n56e44af5ycdca20198c712434@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2006/10/31, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>:
> > I have a little problem. I have an .sql file ( db dump )
> > and i want to import it to postgres on linux.
> >
> > Does anyone know how i can do it?
>
> You feed it to the command line interface psql.
>
> Example:
> psql -h host -p port -d database -U user <dump.sql
It's a good enough solution in most cases, but when the rowcount
starts to skyrocket, it simply doesn't seem to cut it (at least I
couldn't make it to). To load 1,5M rows (~230MB of INSERT statements),
I used gvim (wonderful tool!) to transform the INSERT statements into
a CSV file and then used an ETL (kettle - another wonderful tool) tool
to import the data into the database.
This could have probably been done much easier: I'd welcome a helpful
hint so as I know next time. :)
t.n.a.
From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Text manipulation tools (was Re: postgres import) |
Date: | 2006-10-31 23:59:06 |
Message-ID: | 4547E34A.3030002@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 10/31/06 17:17, Tomi NA wrote:
> 2006/10/31, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>:
[snip]
> It's a good enough solution in most cases, but when the rowcount
> starts to skyrocket, it simply doesn't seem to cut it (at least I
> couldn't make it to). To load 1,5M rows (~230MB of INSERT statements),
> I used gvim (wonderful tool!) to transform the INSERT statements into
> a CSV file and then used an ETL (kettle - another wonderful tool) tool
> to import the data into the database.
> This could have probably been done much easier: I'd welcome a helpful
> hint so as I know next time. :)
Unix is chock full of streaming text manipulation tools. In this
case, awk, Perl or Python would work well.
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFR+NKS9HxQb37XmcRAo3XAKDr2V7T//IjcRIKoHe6IH01eqrE9gCfe8CO
g8eLsgHs7AtNJT6+F/2Byj4=
=RT7u
-----END PGP SIGNATURE-----
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Tomi NA" <hefest(at)gmail(dot)com> |
Cc: | "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>, "Antonios Katsikadamos *EXTERN*" <antonioskatsikadamos(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgres import |
Date: | 2006-11-01 04:23:59 |
Message-ID: | 12109.1162355039@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Tomi NA" <hefest(at)gmail(dot)com> writes:
> 2006/10/31, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>:
>> psql -h host -p port -d database -U user <dump.sql
> It's a good enough solution in most cases, but when the rowcount
> starts to skyrocket, it simply doesn't seem to cut it (at least I
> couldn't make it to).
It certainly should work. We've seen some platforms where libreadline
seems to be unable to tell the difference between input from a terminal
and input from a file, and performs a boatload of processing that would
be useful for interactive input but is just overhead here. If that's
your problem, try this form instead:
psql -h host -p port -d database -U user -f dump.sql
regards, tom lane
From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Tomi NA <hefest(at)gmail(dot)com> |
Cc: | Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>, Antonios Katsikadamos *EXTERN* <antonioskatsikadamos(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgres import |
Date: | 2006-11-01 09:07:43 |
Message-ID: | 454863DF.2010903@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Tomi NA wrote:
> 2006/10/31, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>:
>> You feed it to the command line interface psql.
>>
>> Example:
>> psql -h host -p port -d database -U user <dump.sql
>
> It's a good enough solution in most cases, but when the rowcount
> starts to skyrocket, it simply doesn't seem to cut it (at least I
> couldn't make it to). To load 1,5M rows (~230MB of INSERT statements),
INSERT statements? You dumped with the -d flag, didn't you? Otherwise
you'd have seen COPY statements instead, which are much faster (and of
which much fewer are necessary, usually).
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
From: | "Tomi NA" <hefest(at)gmail(dot)com> |
---|---|
To: | "Alban Hertroys" <alban(at)magproductions(dot)nl> |
Cc: | "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>, "Antonios Katsikadamos *EXTERN*" <antonioskatsikadamos(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgres import |
Date: | 2006-11-01 10:41:13 |
Message-ID: | d487eb8e0611010241j7731ba3k269521ea0e208149@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2006/11/1, Alban Hertroys <alban(at)magproductions(dot)nl>:
> Tomi NA wrote:
> > 2006/10/31, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>:
> >> You feed it to the command line interface psql.
> >>
> >> Example:
> >> psql -h host -p port -d database -U user <dump.sql
> >
> > It's a good enough solution in most cases, but when the rowcount
> > starts to skyrocket, it simply doesn't seem to cut it (at least I
> > couldn't make it to). To load 1,5M rows (~230MB of INSERT statements),
>
> INSERT statements? You dumped with the -d flag, didn't you? Otherwise
> you'd have seen COPY statements instead, which are much faster (and of
> which much fewer are necessary, usually).
No I didn't, actually. :) The data was never in the database in the
first place: it was generated from a different source. True, it was
generated as a CSV file which I converted into INSERT statements, but
conversion between the two is not a problem (given 1.5GB of RAM).
t.n.a.