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

Table of Contents

• Taking backup/dump of the database:
• Restoring backup dump
  • Creating new user
  • Creating new database
  • Restore using psql
• Give Full Access to Database

Taking backup/dump of the database:

pg_dump -U db_user database_name > database_dump_name.sql

Restoring backup dump

Creating new user

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.

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

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.

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