Import SQL dump into PostgreSQL database

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"


[![enter image description here][1]][1]

## 2. Give the file a name.

Like `test12345`. Click backup. This creates a binary file dump, it's not in a `.sql` format

[![enter image description here][2]][2]

## 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

[![enter image description here][3]][3]

## 4. Find the location of downloaded file

In this case, it's `/users/vincenttang`

[![enter image description here][4]][4]

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

[![enter image description here][5]][5]

## 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:

[![enter image description here][6]][6]

## 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

[![enter image description here][7]][7]

## 8. Restore said file

You'll see this page again, with the location of the file selected. Go ahead and restore it

[![enter image description here][8]][8]

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

[![enter image description here][9]][9]

Execute this code block:

```
DROP SCHEMA public CASCADE; CREATE SCHEMA public;
```

[![enter image description here][10]][10]

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


[1]:

[2]:

[3]:

[4]:

[5]:

[6]:

[7]:

[8]:

[9]:

[10]:

make sure the database you want to import to is created, then you can import the dump with

sudo -u postgres -i psql testdatabase < 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;"

Postgresql12

from sql file:
pg_restore -d database < file.sql

from custom format file:
pg_restore -Fc database < file.dump

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.

[To see links please register here]

If you are using a file with `.dump` extension use:

`pg_restore -h hostname -d dbname -U username filename.dump`

Follow the steps:

1. Go to the psql shell
2. `\c db_name`
3. `\i path_of_dump` [eg:-C:/db_name.pgsql]

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][1] describe all this quite well.


[1]:

[To see links please register here]

"PostgreSQL docs"

I used this

```
psql -d dbName -U username -f /home/sample.sql
```

Either do this way

pg_restore --dbname=DB_NAME --verbose 2023-05-09T221119Z_pgdump


OR

psql fawkes_development < 2023-05-09T221119Z_pgdump

If you're on mac, quick one would be:

`sudo -u username psql database < /link/to/backup.sql`