Restore PostgreSQL backup dump in Ubuntu

This has been tested on PostgreSQL 12.6 and Ubuntu 20.04

First open the psql prompt so that we can create the user and database.

sudo -u postgres psql

Now, we will create two users:
1. user with the password for database access
2. user with the same name as the user in the OS (.i.e. ubuntu for Linux based AWS EC2 Instances)

CREATE USER your_user_name WITH PASSWORD 'your_user_password';

CREATE USER your_system_username;

Create the database

CREATE DATABASE your_db_name;

GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_system_username;

Quit the psql prompt, by running below command

\q

Now, we will run the database restore command

psql -U your_system_username -d your_db_name < dump.psql

Let’s log back in to psql prompt and check if you’ve got the tables back by running below command:

sudo -u postgres psql

\c your_db_name

\dt

# sample output

List of relations
 Schema |            Name            | Type  |  Owner   
--------+----------------------------+-------+----------
 public | auth_group                 | table | ubuntu
 public | auth_group_permissions     | table | ubuntu
 public | auth_permission            | table | ubuntu
 public | auth_user                  | table | ubuntu
 public | auth_user_groups           | table | ubuntu
 public | auth_user_user_permissions | table | ubuntu
 public | django_admin_log           | table | ubuntu
 public | django_content_type        | table | ubuntu
 public | django_migrations          | table | ubuntu
 public | django_session             | table | ubuntu
(10 rows)

If you notice one thing in Owner column the value is ubuntu, which means that only ubuntu user can create/alter tables, so now we have to update the owner to our user.

GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_user_name;

ALTER DATABASE your_db_name OWNER TO your_user_name;

GRANT CONNECT ON DATABASE your_db_name TO your_user_name;

Let’s connect to the database and grant some more permissions.

\c your_db_name

GRANT USAGE ON SCHEMA public TO your_user_name;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user_name;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user_name;

REASSIGN OWNED BY your_system_username TO your_user_name;

Now you are done, your database is fully restored and you can access it as before. If you face any issue or have any suggestion please comment below. Thanks.

Sources:
1. https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database
2. https://stackoverflow.com/questions/33145349/must-be-owner-of-relation-django-site-when-manage-py-migrate/33145528