Learn how to backup and restore PostgreSQL database dump in Ubuntu (Linux) and Mac.

Taking backup/dump of the database:

1

pg_dump -U db_user database_name > database_dump_name.sql

Restoring backup dump

Creating new user

1

CREATE USER your_user_name WITH PASSWORD 'your_user_password';

Creating new database

It is better to switch to new user and create the new database, this way you won’t have to give permission explicitly.

1

CREATE DATABASE your_db_name;

But for some reason you didn’t/couldn’t create the database with new user then go to the last section which has all the commands to give user full access to the database.

Restore using psql

1

psql -U your_user_name -d your_db_name < dump_name.sql

Give Full Access to Database

If you haven’t created database with your new user then you’ll have to give it full permission so that it can perform the operations.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_user_name;

# change owner
ALTER DATABASE your_db_name OWNER TO your_user_name;

# allow user to connect
GRANT CONNECT ON DATABASE your_db_name TO your_user_name;

# grant public schema access
GRANT USAGE ON SCHEMA public TO your_user_name;

# grant access to all tables and sequences in schma public
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;

Reference: https://www.postgresql.org/docs/current/backup.html