I have a .sql file that was created by postgresql a while back. I now want to import this file onto a windows machine running postgresql.
How do I do this. The file is about 1.5gb.
Add a comment
|
7 Answers
You should use psql command line tool:
psql -h hostname -p port_number -U username -f your_file.sql databasename
1 Comment
Learner
what is I want to replace the existing table while loading like this? will it succeed?
click on the SQL Shell and log into the database and use import
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.2.4)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \i c:/data/data01.sql
3 Comments
Jakub Szułakiewicz
Note inverted "/" symbol, instead of "\" usually used in Windows.
burhanuddin abbas
this is giving me "C:: Permission denied" , i tried changing the disk as well. didn't get fixed even after running it as an administrator
AmaChefe
confirm that you are using "/" and not "\" @jakub noted above. It is VERY important. And if you have spaces, enclose the path in quotation marks
start you psql command tool, it will give you dialog like the following
Server [localhost]:
Database [postgres]:
Port [5432]:yourport
Username [postgres]:
Password for user postgres:**********
then connect to your database
postgres=# \c yourdatabase;
then import the file
yourdatabase=# \i c:/path/path/data/data01.sql
note the / for directory separator & no spaces in file path
Comments
This also works for me:
psql dbname username < file.sql
open your cmd window and type the following (make sure the path of postgres is correct)
."C:\Program Files\PostgreSQL\9.4\bin\psql.exe" -h 127.0.0.1 -p 5432 -U postgres -d dbname <./query.sql
Comments
psql -U <dbusername>
if the prompt makes you enter password, do that.
\c <yourdatabasename>
\i 'thepathusing/delimiter.sql'
Two points you need to watch out that
- Use
/as writing path of the file instead of\. - Use single quote
symbol
'instead of".
answered Aug 21, 2020 at 15:00
Soner from The Ottoman Empire
21.2k8 gold badges97 silver badges122 bronze badges
Comments
If you're doing it with a URI connection string make sure the arguments are before the URI, Powershell examples:
Works on windows:
.\psql -f TestFile.sql $connString
.\psql -c 'SELECT Version();' $connString
Won't work on windows (URI connection before arguments):
.\psql $connString -c 'SELECT Version();'
