Import SQL dump into PostgreSQL database

Question

We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site   Now  I m trying to set this up on a local WAMP server to test this   The only problem is that I don t have an idea how to import this database in the PostgreSQL 9 that I have set up   I tried pgAdmin III but I can t seem to find an  import  function  So I just opened the SQL editor and pasted the contents of the dump there and executed it  it creates the tables but it keeps giving me errors when it tries to put the data in it   ERROR   syntax error at or near  t  LINE 474  t 2011-05-24 16 45 01 768633 2011-05-24 16 45 01 768633 view     The lines  COPY tb abilities  active  creation  modtime  id  lang  title  description  FROM stdin  t   2011-05-24 16 45 01 768633  2011-05-24 16 45 01 768633  view    nl         I ve also tried to do this with the command prompt but I can t find the command that I need   If I do  psql mydatabase  lt  C  database db-backup sql    I get the error  ERROR   syntax error at or near  psql  LINE 1  psql mydatabase  lt  C  database db-backu                What s the best way to import the database

User · Answer

Here is the command you are looking for   psql -h hostname -d databasename -U username -f file sql

User · Answer

Follow the steps   Go psql shell  c db name  i path of dump   eg -C  db name pgsql

User · Answer

You can do it in pgadmin3  Drop the schema s  that your dump contains  Then right-click on the database and choose Restore  Then you can browse for the dump file

User · Answer

make sure the database you want to import to is created  then you can import the dump with   sudo -u postgres -i psql testdatabase  lt  db-structure sql   If you want to overwrite the whole database  first drop the database    be sure you drop the right database      sudo -u postgres -i psql -c  drop database testdatabase     and then recreate it with  sudo -u postgres -i psql -c  create database testdatabase

User · Answer

I tried many different solutions for restoring my postgres backup  I ran into permission denied problems on MacOS  no solutions seemed to work   Here s how I got it to work   Postgres comes with Pgadmin4  If you use macOS you can press CMD SPACE and type pgadmin4 to run it  This will open up a browser tab in chrome       If you run into errors getting pgadmin4 to work  try killall pgAdmin4 in your terminal  then try again      Steps to getting pgadmin4   backup restore  1  Create the backup  Do this by rightclicking the database -   backup     2  Give the file a name   Like test12345  Click backup  This creates a binary file dump  it s not in a  sql format    3  See where it downloaded  There should be a popup at the bottomright of your screen  Click the  more details  page to see where your backup downloaded to    4  Find the location of downloaded file  In this case  it s  users vincenttang    5  Restore the backup from pgadmin  Assuming you did steps 1 to 4 correctly  you ll have a restore binary file  There might come a time your coworker wants to use your restore file on their local machine  Have said person go to pgadmin and restore  Do this by rightclicking the database -   restore     6  Select file finder  Make sure to select the file location manually  DO NOT drag and drop a file onto the uploader fields in pgadmin  Because you will run into error permissions  Instead  find the file you just created     7  Find said file  You might have to change the filter at bottomright to  All files   Find the file thereafter  from step 4  Now hit the bottomright  Select  button to confirm    8  Restore said file  You ll see this page again  with the location of the file selected  Go ahead and restore it    9  Success  If all is good  the bottom right should popup an indicator showing a successful restore  You can navigate over to your tables to see if the data has been restored propery on each table   10  If it wasn t successful   Should step 9 fail  try deleting your old public schema on your database  Go to  Query Tool     Execute this code block   DROP SCHEMA public CASCADE  CREATE SCHEMA public      Now try steps 5 to 9 again  it should work out  Summary  This is how I had to backup restore my backup on Postgres  when I had error permission issues and could not log in as a superuser  Or set credentials for read write using chmod for folders  This workflow works for a binary file dump default of  Custom  from pgadmin  I assume  sql is the same way  but I have not yet tested that

User · Answer

Postgresql12 from sql file  pg restore -d database  lt  file sql from custom format file  pg restore -Fc database  lt  file dump

User · Answer

I believe that you want to run in psql    i C  database db-backup sql

User · Answer

I use   cat  home path to dump file   psql -h localhost -U  lt user name gt  -d  lt db name gt    Hope this will help someone

User · Answer

Just for funsies  if your dump is compressed you can do something like  gunzip -c filename gz   psql dbname  As Jacob mentioned  the PostgreSQL docs describe all this quite well

User · Answer

Works pretty well  in command line  all arguments are required  -W is for password  psql -h localhost -U user -W -d database name -f path to file sql

User · Answer

That worked for me   sudo -u postgres psql db name  lt   file path

User · Answer

psql databasename  lt  data base dump   That s the command you are looking for    Beware  databasename must be created before importing   Have a look at the PostgreSQL Docs Chapter 23  Backup and Restore

User · Answer

I noticed that many examples are overcomplicated for localhost where just postgres user without password exist in many cases   psql -d db name -f dump sql

User · Answer

If you are using a file with  dump extension use  pg restore -h hostname -d dbname -U username filename dump

User · Answer

I had more than 100MB data  therefore  I could not restore database using Pgadmin4  I used simply postgres client  and write below command  postgres khan     pg restore -d database name   home khan Downloads dump sql It worked fine and took few seconds You can see below link for more information  https   www postgresql org docs 8 1 app-pgrestore html

User · Answer

I m not sure if this works for the OP s situation  but I found that running the following command in the interactive console was the most flexible solution for me    i  path to file sql    Just make sure you re already connected to the correct database  This command executes all of the SQL commands in the specified file

[database] Import SQL dump into PostgreSQL database

Examples related to database

Examples related to postgresql