Importing .sql file on windows to postgresql

Question

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.

Allusive

Answer 1

You should use psql command line tool:

psql -h hostname -p port_number -U username -f your_file.sql databasename 

Illconditioned

what is I want to replace the existing table while loading like this? will it succeed? – Cablet

Answer 2

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

Eduard

Note inverted "/" symbol, instead of "\" usually used in Windows. – Rationality

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 – Operose

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 – Eduard

Answer 3

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

Crib

Answer 4

This also works for me:

psql dbname username < file.sql

Briarroot

Just a note: the "<" operator won't work in Windows Powershell. – Electrothermics

Good point. I've been using cmder for so long I forgot the differences! – Briarroot

Answer 5

command prompt

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

Astraea

Answer 6

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 ".

Sharanshard

Answer 7

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();' 

Juvenility