Permission denied when I try to restore postgres backup

Question

Recently I used this command to make a backup of my postgres databases

pg_dumpall > BkpOldPG.sql

After removing the old version of postgres I downloaded the last version 9.4 and I have tried to restore my old data using :

mody@debian:~$ su postgres
Password: 
postgres@debian:/home/mody$
postgres@debian:/home/mody$ /usr/lib/postgresql/9.4/bin/psql -d postgres -f Documents/Bkp01dPg.sql 
Documents/Bkp01dPg.sql: Permission denied

As you can see I get Permission denied so I tried using sudo but it doesn't work :

postgres@debian:/home/mody$ sudo /usr/lib/postgresql/9.4/bin/psql -d postgres -f Documents/Bkp01dPg.sql 
[sudo] password for postgres: 
postgres is not in the sudoers file.  This incident will be reported.

any help with that please ?

Thanks!

Answer 1

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.

Answer 2

Your backup file, or the Documents folder it is within, have permissions that do not permit access by the postgres user.

You can give the postgres user (and all other users on your system) the right to read them with:

chmod a+x Documents
chmod a+r Documents/Bkp01dPg.sql

Alternately, you could copy Bkp01dPg.sql into a location that the postgres user already has access to, then give the postgres user ownership of it, e.g.

sudo cp Documents/Bkp01dPg.sql ~postgres/
sudo chown postgres ~postgres/Bkp01dPg.sql

Or you could run the restore under your normal user account, connecting to PostgreSQL as a superuser:

psql -U postgres -f Documents/Bkp01dPg.sql

... though you might need to modify pg_hba.conf or pg_ident.conf to allow your user to connect as postgres if you do it this way. (Or you could temporary ALTER USER to give your normal user superuser rights).

By the way, you don't need to su to postgres. Get in the habit of using sudo -u postgres to run commands, and sudo -u postgres -i if you want an interactive command line.