I am using postgres database with PostGIS and PGAdmin. I have many .sql files with different sizes like 300MB, 280MB etc to be inserted into database. What is the best way to do it i.e through java code or some psql commands. I am very new to java and postgres database also. Please give me some suggestion.
Add a comment
|
4 Answers
Use the psql command line tool:
psql -f file_with_sql.sql
This command executes all commands line-by-line (except when the file contains BEGIN…END blocks. In this case, commands in blocks execute in a transaction). To wrap all commands in a transaction use the --single-transaction switch:
psql --single-transaction -f file_with_sql.sql
For more options:
psql --help
1 Comment
Ben
Does this execute the entire sql file in one transaction or does it use a new transaction for each line in the file?
Another approach is to use the psql command \i. For example,
Let say we have the file run.sql in the path ~/sql/run.sql
$ cd ~/sql/
$ psql -h <host> -U <user> <database>
<database> =>
<database> => \i 'run.sql'
DONE
Comments
Just put it on the command line after psql:
psql example.sql
psql will take the file and run each line to the server.
If the server is not running on your computer, you will need to specify the hostname to the computer and a username to log into the server with:
psql -h server.postgres.com -U username example.sql
To send multiple files, just list them all:
psql example1.sql example2.sql example3.sql
3 Comments
jabu.10245
sasikala
i have very big files and those need to be send to another server. so it is taking long time through psql. is there anyway to do directly from pgadmin?
charan
Better look below link stackoverflow.com/questions/3204274/…
If the .sql file itself creates the database, such as:
\echo 'Delete and recreate database?'
\prompt 'Return for yes or control-C to cancel > ' foo
DROP DATABASE my-db;
CREATE DATABASE my-db;
\connect my-db
The easiest way is to run the command:
psql < my-file.sql
