Обсуждение: problem importing data with psql

Hi All,

Am using the following command to import tables in my database. I got list
of tables which i need to import.

So have made ant script and batch file to accomplish the purpose.

Ant script contains all the configurations with following command :

<arg line="/c psql -h ${db.host} -p ${db.port} ${db.name} -U ${db.user} -a
-v -d  -f  ${data.dir}/${db.name}/${table.name}-data.sql"  />

To call this ant file am passing the following command in my batch file as :

call ant -f impdb.xml -Dtable.name=abc
call ant -f impdb.xml -Dtable.name=bcd

But am not able to import the tables with batch file, though am able to run
the same command from the prompt successfully.

I have to import 400+ tables , so need to automate the job rather than
manually.

So Can anyone throw light on this issue? Where am wrong? What's need to be
done?

Awaiting for the reply.

Cheers,
Cha
--
View this message in context: http://www.nabble.com/problem-importing-data-with-psql-tf3956973.html#a11227923
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi All,

Am able to resolve the problem by setting PGPASSWORD=password in my
environment variables and passing the -q(quiet) command in my ant script.

Cheers,
Cha


cha wrote:
>
> Hi All,
>
> Am using the following command to import tables in my database. I got list
> of tables which i need to import.
>
> So have made ant script and batch file to accomplish the purpose.
>
> Ant script contains all the configurations with following command :
>
> <arg line="/c psql -h ${db.host} -p ${db.port} ${db.name} -U ${db.user} -a
> -v -d  -f  ${data.dir}/${db.name}/${table.name}-data.sql"  />
>
> To call this ant file am passing the following command in my batch file as
> :
>
> call ant -f impdb.xml -Dtable.name=abc
> call ant -f impdb.xml -Dtable.name=bcd
>
> But am not able to import the tables with batch file, though am able to
> run the same command from the prompt successfully.
>
> I have to import 400+ tables , so need to automate the job rather than
> manually.
>
> So Can anyone throw light on this issue? Where am wrong? What's need to be
> done?
>
> Awaiting for the reply.
>
> Cheers,
> Cha
>

--
View this message in context: http://www.nabble.com/problem-importing-data-with-psql-tf3956973.html#a11235322
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

cha wrote:
>
> But am not able to import the tables with batch file, though am able to run
> the same command from the prompt successfully.

Why? What errors are you getting?

--
   Richard Huxton
   Archonet Ltd

chandresh rana wrote:
> Hi Richard,
>
> I have eliminated certain tables while exporting as they the size of the
> data in the tables are huge.Am having the schema of all the tables.
>
> Now want to import certain set of records/rows from the eliminated tables.
>
> Is it possible to do that ?? If yes, then can you please tell me how I can
> accomplish this?

The only practical way would be to process the rows outside the
database, perhaps in a Perl script or similar. Pipe COPY-format data
dumps through your script and have it strip out unwanted lines as it
feeds them to psql.

Otherwise, you'll have to import all the rows and then delete the ones
you want.

> How postgresql take care of all the constraints/referential integrity while
> importing the records from the different tables?

It won't do anything different with a standard COPY vs inserting rows
normally. You'll probably want to turn off constraints and indexes and
then re-apply them after the import.

Is there a good reason why you're not using pg_dump/pg_restore for this?

--
   Richard Huxton
   Archonet Ltd

Try and remember to CC: the list - there are plenty of other people who
can help too.


chandresh rana wrote:
> Hi Richard,
>
> Let me explain you exactly what am trying to achieve.
>
> Am trying to export the database of the sever(ver 7.4) to my local
> server(ver 8.2).The problem in exporting ,is that certain tables are of very
> huge size which will be take weeks to complete if they are included.So need
> to eliminate those tables.

OK. How big are we talking about? What is the size:
1. Of the large table(s)?
2. Of the database as a whole?

> After having exporting/importing of the schema and the included tables,now
> need to get certain/limited set of record/rows from excluded tables to get
> the database start locally.

I would copy the rows I want to a new table (still in the 7.4 database)
and then dump that.

You can have pg_dump dump individual tables (use pg_dump from 8.2, it's
smarter) and then either:
1. rename the table in the dumped file
2. rename it after importing

--
   Richard Huxton
   Archonet Ltd